Re: Table Size

27/10/2007, 16h24

mysql> show create table UserReports\G
*************************** 1. row***************************
Table: UserReports
Create Table: CREATE TABLE `UserReports` (
`rolID` int(10) unsigned NOT NULL,
`repID` int(10) unsigned NOT NULL,
PRIMARY KEY (`rolID`,`repID`),
KEY `repID` (`repID`),
CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`)REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE,
CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00sec)

----- Original Message ----
From: Baron Schwartz <baron@xaprb.com>
To: Josh <josh2780@yahoo.com>
Cc: mysql@lists.mysql.com
Sent: Saturday, October 27, 2007 10:17:32 AM
Subject: Re: Table Size

Josh wrote:
> Hello,
>
> I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad butI'm trying to clean up the tables to minimize the amount of space they take up.
>
> I have one particular table that has 2 columns:
> rolID int(10) unsigned
> repID int(10) unsigned
>
> Both are foreign keys to other tables.
>
> A "show table status" shows that this table has aData_length=1081344 bytes.
> http://dev.mysql.com/doc/refman/5.0/...uirements.html shows that integers take up 4 bytes of space.
> There are currently 10472 rows in the table.
>
> 10472 x 4 x 2 = 83776
>
> What am I calculating wrong? Why is the Data_length value so much larger?

It depends on the storage engine, indexes, and possibly other things.
What's the output of SHOW CREATE TABLE for this table? If you can't
tell us that, what's the storage engine?

(yes, I know Index_length is separate, but humor me).

Baron

27/10/2007, 16h30

InnoDB has the following extra things, plus some things I might forget:

a) the primary key B-Tree
b) row versioning information for every row
c) 16k page size; each page might not be completely full

Those are all counted towards the table size. Actually, the primary key
B-Tree might not be; I'd need to look that up. But I think it is.
Hmmmm. I just tested -- yes, the PK counts towards table size.

Josh wrote:
> mysql> show create table UserReports\G
> *************************** 1. row ***************************
> Table: UserReports
> Create Table: CREATE TABLE `UserReports` (
> `rolID` int(10) unsigned NOT NULL,
> `repID` int(10) unsigned NOT NULL,
> PRIMARY KEY (`rolID`,`repID`),
> KEY `repID` (`repID`),
> CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE,
> CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
>
>
> ----- Original Message ----
> From: Baron Schwartz <baron@xaprb.com>
> To: Josh <josh2780@yahoo.com>
> Cc: mysql@lists.mysql.com
> Sent: Saturday, October 27, 2007 10:17:32 AM
> Subject: Re: Table Size
>
> Josh wrote:
>> Hello,
>>
>> I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up.
>>
>> I have one particular table that has 2 columns:
>> rolID int(10) unsigned
>> repID int(10) unsigned
>>
>> Both are foreign keys to other tables.
>>
>> A "show table status" shows that this table has a Data_length=1081344 bytes.
>> http://dev.mysql.com/doc/refman/5.0/...uirements.html shows that integers take up 4 bytes of space.
>> There are currently 10472 rows in the table.
>>
>> 10472 x 4 x 2 = 83776
>>
>> What am I calculating wrong? Why is the Data_length value so much larger?
>
> It depends on the storage engine, indexes, and possibly other things.
> What's the output of SHOW CREATE TABLE for this table? If you can't
> tell us that, what's the storage engine?
>
> (yes, I know Index_length is separate, but humor me).
>
> Baron

28/10/2007, 05h52

In the last episode (Oct 27), Baron Schwartz said:
> InnoDB has the following extra things, plus some things I might forget:
>
> a) the primary key B-Tree
> b) row versioning information for every row
> c) 16k page size; each page might not be completely full
>
> Those are all counted towards the table size. Actually, the primary
> key B-Tree might not be; I'd need to look that up. But I think it
> is. Hmmmm. I just tested -- yes, the PK counts towards table size.

In fact, in InnoDB, all indexes count towards table size, since there
is a single .ibd file for the whole thing. So you've got the space
taken up by your `repid` index to consider as well.

Useful reading:

http://dev.mysql.com/doc/refman/5.0/...al-record.html
http://dev.mysql.com/doc/refman/5.1/...structure.html

Note the 5-byte header per record in all indexes, plus another 13 bytes
per record in the primary key, and the fact that the columns in your
primary key are not omitted from the record data. So repid is actually
stored three times in the .ibd file; once in the primary key, once in
the record, and once in the `repid` index. There's quite a lot of
overhead in making a transaction-safe multiversioned table. What I've
seen is that for small row lengths (under 50 bytes) an InnoDB table is
about twice the size as the same data in MyISAM format (including
indexes).

For your particular table, you're probably seeing the effect of 16k
page sizes. With only 10K rows, your leaf pages are mostly empty. Try
putting 100K rows in and see how big the .ibd file is.

--
Dan Nelson
dnelson@allantgroup.com

28/10/2007, 15h25

Dan Nelson wrote:
> In the last episode (Oct 27), Baron Schwartz said:
>> InnoDB has the following extra things, plus some things I might forget:
>>
>> a) the primary key B-Tree
>> b) row versioning information for every row
>> c) 16k page size; each page might not be completely full
>>
>> Those are all counted towards the table size. Actually, the primary
>> key B-Tree might not be; I'd need to look that up. But I think it
>> is. Hmmmm. I just tested -- yes, the PK counts towards table size.
>
> In fact, in InnoDB, all indexes count towards table size, since there
> is a single .ibd file for the whole thing. So you've got the space
> taken up by your `repid` index to consider as well.

It's true they're in the same file, but the secondary indexes show up in
the 'Index_length' column in SHOW TABLE STATUS. I was double-checking
that the primary key contributes to the 'Data_length' column, not the
'Index_length' column.

27/10/2007, 16h24	#1
Josh Messages: n/a Hébergeur:	Re: Table Size mysql> show create table UserReports\G ************************* 1. row************************* Table: UserReports Create Table: CREATE TABLE `UserReports` ( `rolID` int(10) unsigned NOT NULL, `repID` int(10) unsigned NOT NULL, PRIMARY KEY (`rolID`,`repID`), KEY `repID` (`repID`), CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`)REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE, CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00sec) ----- Original Message ---- From: Baron Schwartz <baron@xaprb.com> To: Josh <josh2780@yahoo.com> Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: > Hello, > > I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad butI'm trying to clean up the tables to minimize the amount of space they take up. > > I have one particular table that has 2 columns: > rolID int(10) unsigned > repID int(10) unsigned > > Both are foreign keys to other tables. > > A "show table status" shows that this table has aData_length=1081344 bytes. > http://dev.mysql.com/doc/refman/5.0/...uirements.html shows that integers take up 4 bytes of space. > There are currently 10472 rows in the table. > > 10472 x 4 x 2 = 83776 > > What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron

27/10/2007, 16h30	#2
Baron Schwartz Messages: n/a Hébergeur:	Re: Table Size InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. Hmmmm. I just tested -- yes, the PK counts towards table size. Josh wrote: > mysql> show create table UserReports\G > ************************* 1. row ************************* > Table: UserReports > Create Table: CREATE TABLE `UserReports` ( > `rolID` int(10) unsigned NOT NULL, > `repID` int(10) unsigned NOT NULL, > PRIMARY KEY (`rolID`,`repID`), > KEY `repID` (`repID`), > CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE, > CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > 1 row in set (0.00 sec) > > > > ----- Original Message ---- > From: Baron Schwartz <baron@xaprb.com> > To: Josh <josh2780@yahoo.com> > Cc: mysql@lists.mysql.com > Sent: Saturday, October 27, 2007 10:17:32 AM > Subject: Re: Table Size > > Josh wrote: >> Hello, >> >> I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. >> >> I have one particular table that has 2 columns: >> rolID int(10) unsigned >> repID int(10) unsigned >> >> Both are foreign keys to other tables. >> >> A "show table status" shows that this table has a Data_length=1081344 bytes. >> http://dev.mysql.com/doc/refman/5.0/...uirements.html shows that integers take up 4 bytes of space. >> There are currently 10472 rows in the table. >> >> 10472 x 4 x 2 = 83776 >> >> What am I calculating wrong? Why is the Data_length value so much larger? > > It depends on the storage engine, indexes, and possibly other things. > What's the output of SHOW CREATE TABLE for this table? If you can't > tell us that, what's the storage engine? > > (yes, I know Index_length is separate, but humor me). > > Baron

28/10/2007, 05h52	#3
Dan Nelson Messages: n/a Hébergeur:	Re: Table Size In the last episode (Oct 27), Baron Schwartz said: > InnoDB has the following extra things, plus some things I might forget: > > a) the primary key B-Tree > b) row versioning information for every row > c) 16k page size; each page might not be completely full > > Those are all counted towards the table size. Actually, the primary > key B-Tree might not be; I'd need to look that up. But I think it > is. Hmmmm. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well. Useful reading: http://dev.mysql.com/doc/refman/5.0/...al-record.html http://dev.mysql.com/doc/refman/5.1/...structure.html Note the 5-byte header per record in all indexes, plus another 13 bytes per record in the primary key, and the fact that the columns in your primary key are not omitted from the record data. So repid is actually stored three times in the .ibd file; once in the primary key, once in the record, and once in the `repid` index. There's quite a lot of overhead in making a transaction-safe multiversioned table. What I've seen is that for small row lengths (under 50 bytes) an InnoDB table is about twice the size as the same data in MyISAM format (including indexes). For your particular table, you're probably seeing the effect of 16k page sizes. With only 10K rows, your leaf pages are mostly empty. Try putting 100K rows in and see how big the .ibd file is. -- Dan Nelson dnelson@allantgroup.com

28/10/2007, 15h25	#4
Baron Schwartz Messages: n/a Hébergeur:	Re: Table Size Dan Nelson wrote: > In the last episode (Oct 27), Baron Schwartz said: >> InnoDB has the following extra things, plus some things I might forget: >> >> a) the primary key B-Tree >> b) row versioning information for every row >> c) 16k page size; each page might not be completely full >> >> Those are all counted towards the table size. Actually, the primary >> key B-Tree might not be; I'd need to look that up. But I think it >> is. Hmmmm. I just tested -- yes, the PK counts towards table size. > > In fact, in InnoDB, all indexes count towards table size, since there > is a single .ibd file for the whole thing. So you've got the space > taken up by your `repid` index to consider as well. It's true they're in the same file, but the secondary indexes show up in the 'Index_length' column in SHOW TABLE STATUS. I was double-checking that the primary key contributes to the 'Data_length' column, not the 'Index_length' column.

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email