InnoDB plugin row format performance – II

This is a continuation post on InnoDB plugin row format performance with few more test scenarios to cover based on the feedback and questions that I got in my Inbox.

This time the initial table looks like this

This time, the table does not have a secondary index in the create time, instead it will be added after the table is populated with the data to test the performance of the new faster index support from the plugin. I also added a new scenario to test 2K compression as we noticed 4K compression seems to be a bottleneck in the earlier case.

Same test environment is chosen as of the first one, RHEL-4 64-Bit having MySQL-5.1.24 with InnoDB plugin 1.0 statically linked.

InnoDB configuration variables:

Normally I disable the following InnoDB configuration options when running any benchmark tests; but this time enabled all of them (including in the first case)…

Table Load:

Load time from a dump of SQL script having 10M rows (not batched)

Secondary Index Creation:

Secondary Index create time after the table has been populated. The index added using

File Sizes:

Here is the size of the .ibd file after each data load

Testing:

I used the sysbench to run the tests by varying the threads to run mixed read/write on a  pre-populated table by creating the table with different ROW_FORMATS along with KEY_BLOCK_SIZE. The server is taken down by cleaning its logs and data directories completely and a fresh server is started back to re-run a different row-format .

Here is the sysbench options:

Some variables are non-relevant to test, but I use the same scripts to run lot of other cases, –mysql-ignore-duplicates is not a standard sysbench option, its a patch that I created to make sure the test does not bail on duplicate key errors due to poor random number generator.

The test typically executes 950000 queries in each iteration (700K read queries and 250K write queries) with max of 50000 transactions in this test and 100K transactions in the part-I case. Here is the typical output from sysbench:

Performance:

Here is the performance of various row formats with threads ranging from 1 to 512 for 6G buffer pool size for both concurrent reads and writes.

 Observations:

• The load time is almost same for both Compact and Dynamic row formats. In this table and data scenario, both perform same amount of transactions (as you can see from the graph, they overlap)
• The load/insert time increases based on the compression ratio from 8K, 4K, 2K, 1K etc
• When the whole data set can fit in memory; then compression will relatively slowdown the transaction rate
• When the whole data set can not fit in memory, then compression on the table seems to be a right choice (8K in this case)
• Creation of secondary index still takes lot of time when the compression is enabled. The 8K compression is much better and bit close to Compact/Dynamic; but 4K/2K seems to take ~6 times more time than 8K
• The best compression in terms of performance is 8K; but it may vary based on the table structure and data and its distribution.
• Part-I observations can be found from here
  

Looks like there is something wrong in terms of performance of 4K/2K compression when compared with 8K when the data set can fit everything in memory (as we have 6G buffer pool). I will dig further and see if we can find a solution for the actual problem or file a bug report..more later.

Convert from one row format to another:

Alter table seems to be a right choice to change from one row format to another one or to change the compression ratio and it is efficient. For example, here is a case where the table is converted from compressed to compact and compact to Compressed and InnoDB re-creates both .frm and .ibd by using temp files. Even here you can see that 4K takes much longer time than expected.

Nice to have:

It will be nice if InnoDB team can extend the SHOW TABLE STATUS to show the compression details. Right now the Information schema.cmp will only lists general compression statistics but not on per table basis.

Also, it will be added advantage if they can support the following two config variables:

innodb_default_row_format (default compact or dynamic) and innodb_default_key_block_size (optionally, and make sure it does not raise a warning on default row_format)