Testing InnoDB “Barracuda” format with compression

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *