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

MySQL & MariaDB Other MySQL® Questions
1

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: [URL]http://rghost.ru/59332840[/URL]
MyISAM table dump: [URL]http://rghost.ru/59332879[/URL]

2

Hi,

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

[url]http://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/[/url]
[url]mysql - How do MyISAM and InnoDB Utilize HD Space? - Server Fault

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’;

3

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.

yes. innodb_file_format_max = barracuda

Table struct:

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

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.

test_innodb_dump.zip (51.7 KB)

4

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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;

You may want to test query performance with compression.

5

Mainly if the environment/workload is CPU Bound…

6

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:
[URL]http://rghost.ru/60112059[/URL] 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…

7

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.)

8

Rick James, my last post contains answers on all your questions…

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)[ATTACH=CONFIG]temp_283_1423238996309_764[/ATTACH]

TABLE_InnoDB.zip (18.2 KB)

9

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.