Hi,
From what I can measure we get only the 70% filling.
The data is sorted in ascending order,
however there is a catch in the sense that the first colummn consist of
all identical variables (this is kept to maintain some consistency with
our organization),
and thus we have set the primary key to sort on the second column which
is in the right ascending order.
I guess this is why we get only a 70% filling and not the full 15/16
eventhough the data is well sorted.
Thanks,
Xavier
Heikki Tuuri wrote:
>Hi!
>
>Sorry, InnoDB does not have key compression.
>Every row has a 6-byte trx id, a 7-byte roll ptr, and
>a header of 6 bytes. In addition, every field uses
>1 byte as a pointer to it.
>
>Calculating:
>
>180 million x (6 + 7 + 6 + 2 + 4) bytes = 4.5 GB
>
>Since every page is filled only 15/16 full even if
>rows are inserted in an ascending order,
>there will be some additional overhead.
>
>Did you insert the rows in the ascending order
>of the primary key? That is the most
>space-efficient way, since with random inserts
>pages will only be about 70 % full.
>
>Regards,
>
>Heikki
>http://www.innodb.com
>
>Copied message:
>..............
>
>>Lose (some of) the keys!? Keys, especially on small rows (where a key
>>introduces a lot of overhead), can take a significant percentage of
>>space.
>>
>>Turn on key compression? (dunno if innodb supports that)
>>
>>Other then that I wouldn't know. But live with the fact that keys
>>introduce overhead. It will be like that on any database system.
>>
>>>-----Original Message-----
>>>From: Xavier Leoncini [mailto:[EMAIL PROTECTED]]
>>>Sent: 30 July 2001 04:34
>>>To: [EMAIL PROTECTED]
>>>Subject: Innodb table size
>>>
>>>
>>>Hello,
>>>I was wondering if someone could help me;
>>>When I load about 6Gb of raw data into a innodb table
>>>the size used by the table is about 14G.
>>>I noticed in preleminary tests that the sized used depends a
>>>lot on the
>>>number of rows
>>>in the case described above the data has about 180 millions rows, 4
>>>columns and three keys
>>>on the first three columns.
>>>Does anyone know how to get a better ratio, something closer
>>>to 6-9Gb use. Thanks in advance. Xavier
>>>
>>>
>>>
>>>---------------------------------------------------------------------
>>>Before posting, please check:
>>> http://www.mysql.com/manual.php (the manual)
>>> http://lists.mysql.com/ (the list archive)
>>>
>>>To request this thread, e-mail <[EMAIL PROTECTED]>
>>>To unsubscribe, e-mail
>>><[EMAIL PROTECTED]>
>>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>>
>>>
>>
>>.
>>
>To be more precise,
>I have a primary key defined on the first three columns, and I kind of
>do not have much choice
>on that. I am surprised by the overhead as innodb has clustered indexing
>defined on the primary key.
>A typical row is about 35 bytes.
>
>
>
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>.
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
