October 21, 2014

Testing InnoDB “Barracuda” format with compression

New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is


in fact this is not exact schema – difference is we are using DEFAULT CHARSET=utf8, which seems not working with fast index creation (bug 33650) , so I use latin1 for tests. Also we actually store ‘bodyuc’ as compressed field, doing compress()/uncompress() to store/restore. To test InnoDB compression I use only uncompressed text, though I tested compression on compress()-ed data just to see if there is any benefit from index compression.

For test I use dump created with mysqldump, final size 30286M.
To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create table only with primary key, without additional indexes.

Numbers I got:

load dump into table with default format and all indexes (case 1) – 152m34.792s
size of final .ibd file for (case 1) – 43032M

What if load data and indexes separately:

load dump into table with default format and without indexes (case 2a) – 59m12.575s
size of .ibd after (case 2a) – 36968M
create all indexes (case 2b) – 18min46.05s
size of .ibd after (case 2b) – 40400M

So as you see time to load (case 2) (with fast create index way) is faster almost two times and space decreased by 6%.

Now with compressed table:

load dump into table with default format and all indexes (case 3) – 228m55.251s (time took 1.5 times longer than (case 1) )
size of final .ibd file for (case 3) – 16284M (space ratio is 0.37 compare to (case 1) )

What if load data and indexes separately:

load dump into table with default format and without indexes (case 4a) – 71m10.760s (longer 1.2 times than (case 2a))
size of .ibd after (case 4a) – 13208M (smaller 0.35 times)
create all indexes (case 4b) – 42m54.63sec (longer 2.28 times)
size of .ibd after (case 4b) – 14968M (smaller 0.37 times)

Total time for (case 4) , 6844 sec, took 1.46 times longer than (case 2) , 4678 sec, but space decreased by even more ( 0.37 from original)

Also interesting to note that fast index creation allows to load time two time faster than usual load.

Ok, There are a lot of numbers, let me summarize it:

TestLoad time, secSize, MB
Baseline (1), default format, usual load915443032
default format, fast index creation4678 (0.51)*40400 (0.93)
compress format, default load13735 (1.50)16284 (0.38)
compress format, fast index creation6844 (0.74)14968 (0.35)

* – ratio to baseline

So in conclusion
– Fast Index creation allows to speedup load 2 times (even indexes fit into memory)
– Load in compress format slower by 30-50%
– Table in compress format takes only 1/3 of original table

Of course we also expect significant performance gain for I/O bound queries in case of compression tables, this is topic for different research.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. venu says:

    You did what am currently doing. Great job, thanks..

  2. It would be very interesting to see how large the performance gains were for I/O bound queries for the compression tables.

  3. 2Daniel Crabtree: Check out my post regarding possible performance gain (of course, it was a specific use case, but still could be interesting: http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

Speak Your Mind

*