sorted by: recent | see : popular

Content Tagged with InnoDB + row

Here is a quick comparison of the new InnoDB plugin performance between different compression, row formats that is introduced recently.

The table is a pretty simple one:

CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT ‘0′,
  `c` char(120) NOT NULL DEFAULT ”,
  `pad` char(60) NOT NULL DEFAULT ”,
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

The table is populated with 10M rows with average row length being 224 bytes. The tests are performed for Compact, Dynamic and Compressed (8K and 4K)  row formats using MySQL-5.1.24 with InnoDB plugin-1.0.0-5.1 on Dell PE2950  1x Xeon quad core with 16G RAM, RAID-10 with RHEL-4 64-bit.

Here are the four test scenarios:

1. No compression, ROW_FORMAT=Compact
2. ROW_FORMAT=Compressed with KEY_BLOCK_SIZE=8
3. ROW_FORMAT=Compressed with KEY_BLOCK_SIZE=4
4. ROW_FORMAT=Dynamic

All the above tests are repeated with innodb_buffer_pool_size=6G and 512M to make sure one fits everything in memory and another one overflows. The rest of the InnoDB settings are all default except that innodb_thread_concurrency=32.

Here is the summary of the test results:

Table Load:

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

File Sizes:

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

Data and Index Size from Table Status:

Here is the Data and Index size in bytes from SHOW TABLE STATUS and you can see the original data size here rather than the compressed size

Compression Stats:

Here is the compression stats after the table is populated from information_schema.InnoDB_cmp; and you notice that 4K takes more operations and time for both compression and un-compression

Performance:

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

Observations:

Few key observations from the performance tests that I performed without looking to any of the sources, as I could be wrong, someone can correct me here. Its hard to draw from these input scenarios, but helps to estimate what is what.

• The load time is almost same except that the 4K compression seems to take longer than the rest; and compression in general is hitting the INSERT/Load performance a little bit.
• Compact or Dynamic, there is no compression; so the data and index file sizes will be almost same
• The SHOW TABLE STATUS for compressed table will have its original Data_Length and Index_Length statistics rather than the compressed statistics (may be a bug or InnoDB needs to extend SHOW TABLE STATUS to show any compressed sizes or other means, right now only option is to view your files manually)
• 8K compression reduced the .ibd file by nearly 50% (1.2G out of 2.3G) and 4K compression reduced the size by 1/4th (592M out of 2.3G); and it could vary based on table types and data.
• 8K compression takes less ops and time for both compression and de-compression when compared to 4K (obvious)
• When there is enough Innodb buffer pool size to act data in memory, the compression is a bit overhead, but you will be saving space
• When there is a overflow from buffer pool (IO bound), compression seems to really help
• 4K compression in general seems to be slower when compared with 8K or any other row_format.