Not the answer you need?
Register and ask your own question!

The table size between InnoDB and MyISAM engines differs in 7 times

ChavyChavy ContributorInactive User Role Beginner
InnoDB table:
CREATE TABLE `TABLE_InnoDB` (
`id1` tinyint(3) unsigned NOT NULL,
`id2` mediumint(8) unsigned NOT NULL DEFAULT '0',
`id3` mediumint(8) unsigned NOT NULL DEFAULT '0',
`id4` tinyint(3) unsigned NOT NULL,
`p0` blob NOT NULL,
`p1` blob NOT NULL,
`p2` blob NOT NULL,
`p3` blob NOT NULL,
`p4` blob NOT NULL,
`p5` blob NOT NULL,
`p6` blob NOT NULL,
`p7` blob NOT NULL,
`p8` blob NOT NULL,
`p9` blob NOT NULL,
PRIMARY KEY (`id2`,`id3`,`id4`,`id1`) KEY_BLOCK_SIZE=1024
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=2;
With 1000 rows it takes 79,52 MB (83 378 176)
On MyISAM engine:
CREATE TABLE `TABLE_MyISAM` (
  `id1` tinyint(3) unsigned NOT NULL,
  `id2` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id3` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id4` tinyint(3) unsigned NOT NULL,
  `p0` blob NOT NULL,
  `p1` blob NOT NULL,
  `p2` blob NOT NULL,
  `p3` blob NOT NULL,
  `p4` blob NOT NULL,
  `p5` blob NOT NULL,
  `p6` blob NOT NULL,
  `p7` blob NOT NULL,
  `p8` blob NOT NULL,
  `p9` blob NOT NULL,
  PRIMARY KEY (`id2`,`id3`,`id4`,`id1`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=2;
this table takes only 12,02 MB (12 608 756) - that is 6.6 times less. Is it a bug? Can i reduce table size on InnoDB engine (without compression use)?
Server: Percona 5.6.19-67.0
InnoDB table dump: http://rghost.ru/59332840
MyISAM table dump: http://rghost.ru/59332879

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Normally, InnoDB tables require more disk space than MyISAM tables. Please check below posts for more idea.

    http://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/
    http://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space

    Can you tell me how you are calculating table size? are you using innodb_file_per_table ?

    This query can give you exact table size.
    SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
  • ChavyChavy Contributor Inactive User Role Beginner
    Can you tell me how you are calculating table size?
    I see it in midnight commander. But SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable'; returns the same result.
    are you using innodb_file_per_table ?
    yes. innodb_file_format_max = barracuda

    Table struct:
    CREATE TABLE `TABLE_InnoDB` (
    `id1` tinyint(3) unsigned NOT NULL,
    `id2` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `id3` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `id4` tinyint(3) unsigned NOT NULL,
    `p0` blob NOT NULL,
    `p1` blob NOT NULL,
    `p2` blob NOT NULL,
    `p3` blob NOT NULL,
    `p4` blob NOT NULL,
    `p5` blob NOT NULL,
    `p6` blob NOT NULL,
    `p7` blob NOT NULL,
    `p8` blob NOT NULL,
    `p9` blob NOT NULL,
    PRIMARY KEY (`id2`,`id3`,`id4`,`id1`) KEY_BLOCK_SIZE=1024
    ) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=2;
    
    In table 1000 Rows with 1024 bytes of data in each blob. So it mast take 1 kilobyte * 10 blobs in row * 1000 rows = 9,765625 Mb.
    But it takes 62,52 MB.
    Here http://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/ I found
    Another thing to beware with Innodb BLOB storage is the fact external blob pages are not shared among the blobs. Each blob, even if it has 1 byte which does not fit on the page will have its own 16K allocated. This can be pretty inefficient so I’d recommend avoiding multiple large blobs per row when possible. Much better decision in many cases could be combine data in the single large Blob (and potentially compress it)
    This means that innodb blob uses disk space very inefficiently, and if I want to migrate my project from myisam to innodb, it is need to replace 128gb ssd with 1 tb ssd. In my opinion, 650% overhead - bug, but bugs.mysql.com hardly will confirm this. If I combine 10 blobs in one, than it takes 16,52 MB, that is only 50% overhead. But a lot of unnecessary data will be read and transmitted with SELECT queries. And update will require a SELECT, because it is impossible to update only a part of a blob. So that is not a solution of the problem.
  • psongpsong Contributor Inactive User Role Beginner
    Chavy,

    ROW_FORMAT=DYNAMIC doesn't give any compression. I have tried the data you provided, compression works well with your data. With key_block_size=2, .ibd file is about 20M. With key_block_size=1, storage is further reduced to 13M.

    ALTER TABLE <tbl> ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;

    You may want to test query performance with compression.
  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    psong wrote: »
    You may want to test query performance with compression.

    Mainly if the environment/workload is CPU Bound...
  • ChavyChavy Contributor Inactive User Role Beginner
    CPU load with compression is quite high under heavy traffic. And it does not solve this problem. I have selected 1000 rows from my table (from a working project) in a separate table:
    http://rghost.ru/60112059 TABLE_InnoDB.zip (149 KB)
    InnoDB Dynamic: 62,52 MB (65 552 384)
    InnoDB Compressed, key block size 1: 22,60 MB (23 697 408)
    MyISAM Dynamic: 9,80 MB (10 275 968)
    In table 1000 Rows with 1024 bytes of data in each blob. So it mast take 1 kilobyte * 10 blobs in row * 1000 rows = 9,765625 Mb. And I see this number with MyISAM table. InnoDB with compression does not compress the data, and increases the amount of used disk space in 2.3 times (9,80 MB -> 22,60 MB). I expected to see a number less than real size of data 9,76 MB (m.b. 1, 2 or 4 megabyte). Compressed dump takes only 149 kb...
  • Rick JamesRick James Contributor Current User Role Novice
    Are your blobs compressible? (For example, text is often compressible 3:1, most image formats do not compress.)

    Are your client programs on the same machine? If not, the it would be better to do the compression in the clients. This would prevent the high CPU load and decrease the network traffic. And it might make more of your blobs fit in the main part of the record. (Note: manual compression is often better in multiple ways than InnoDB compression.)
  • ChavyChavy Contributor Inactive User Role Beginner
    Rick James, my last post contains answers on all your questions...
    Rick James wrote: »
    Are your blobs compressible?
    Yes. Data size 9.76 Mb, in 7z archive it becomes 19 KB. But I don't want to use XtraDB compression, it loads server CPU up to 99%.
    My performance testing:
    | Data Size    | Comp. size    | Table size    | CPU load
    --------------------------------------------------------------------------------------------
    MyIsam          | 9.76 MB      | 19 KB         | 9,80 MB       | 5%
    XtraDB          | 9.76 MB      | 19 KB         | 62,52 MB      | 40%
    XtraDB comp.    | 9.76 MB      | 19 KB         | 22,60 MB      | 99%
    TokuDB          | 9.76 MB      | 19 KB         | 9,79 MB       | 20%
    ARCHIVE         | 9.76 MB      | 19 KB         | 76,59 KB      | -
    
    I will use TokuDB. In my case, for XtraDB it is necessary to buy 1 Tb ssd, that I can't do
    Table dump: http://rghost.ru/6hVdCGzmY (TABLE_InnoDB.zip 147 KB) http://rghost.ru/6P89Jqz7Y (TABLE_InnoDB.7z 19 KB)Attachment not found.
  • Rick JamesRick James Contributor Current User Role Novice
    Move your clients to another machine(s); do the compression there.

    The sample data had one character repeating forever; I assume that is not realistic, hence the 19KB is not realistic, but just a flawed benchmark.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.