| Bug #71436 | Compressed Tables Regression from 5.6.13 | ||
|---|---|---|---|
| Submitted: | 21 Jan 2014 1:33 | Modified: | 28 Feb 2014 13:33 |
| Reporter: | Jervin R | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.6.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | compression, regression | ||
[21 Jan 2014 1:34]
Jervin R
sysbench test on different MySQL versions from 5.6.12 to 5.6.15, 64threads, 512M buffer pool size
Attachment: 64thd-512M.jpg (image/jpeg, text), 236.50 KiB.
[22 Jan 2014 9:28]
Jervin R
InnoDB settings: innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 16M innodb_buffer_pool_dump_at_shutdown = OFF innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = OFF innodb_checksum_algorithm = INNODB innodb_concurrency_tickets = 5000 innodb_fast_shutdown = 0 innodb_file_format = Barracuda innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_io_capacity = 10000 innodb_lock_wait_timeout = 120 innodb_log_buffer_size = 8M innodb_log_file_size = 4G innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 90 innodb_purge_threads = 8 innodb_read_ahead_threshold = 0 innodb_read_io_threads = 16 innodb_strict_mode = 1 innodb_thread_concurrency = 32 innodb_write_io_threads = 16 transaction_isolation = REPEATABLE-READ
[23 Jan 2014 17:33]
Sveta Smirnova
Thank you for the report.
Verified as described.
Results with 5.6.12:
OLTP test statistics:
queries performed:
read: 7315406
write: 0
other: 1045058
total: 8360464
transactions: 522529 (290.27 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 7315406 (4063.84 per sec.)
other operations: 1045058 (580.55 per sec.)
General statistics:
total time: 1800.1193s
total number of events: 522529
total time taken by event execution: 115198.2566s
response time:
min: 12.32ms
avg: 220.46ms
max: 2803.56ms
approx. 95 percentile: 401.08ms
Threads fairness:
events (avg/stddev): 8164.5156/18.16
execution time (avg/stddev): 1799.9728/0.04
Results with 5.6.15:
OLTP test statistics:
queries performed:
read: 1755474
write: 0
other: 250782
total: 2006256
transactions: 125391 (69.64 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1755474 (974.99 per sec.)
other operations: 250782 (139.28 per sec.)
General statistics:
total time: 1800.5080s
total number of events: 125391
total time taken by event execution: 115211.8265s
response time:
min: 154.38ms
avg: 918.82ms
max: 3333.11ms
approx. 95 percentile: 1306.42ms
Threads fairness:
events (avg/stddev): 1959.2344/6.61
execution time (avg/stddev): 1800.1848/0.16
I used mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz and mysql-5.6.15-linux-glibc2.5-x86_64.tar.gz packages
Test command used:
/home/sveta/src/sysbench/sysbench/sysbench --test=/home/sveta/src/sysbench/sysbench/tests/db/oltp.lua --oltp-table-size=100000 --oltp-test-mode=simple --oltp-tables-count=1 --max-requests=0 --max-time=1800 --mysql-port=13000 --mysql-host=127.0.0.1 --mysql-user=root --report-interval=5 --oltp-skip-trx --oltp-read-only=on --num-threads=64 --mysql-db=t1 run
[28 Feb 2014 13:33]
Daniel Price
Fixed as of 5.5.37, 5.6.17, 5.7.4 and here's the changelog entry: A regression introduced by Bug #14329288 would result in a performance degradation when a compressed table does not fit into memory. Thank you for the bug report.
[27 Mar 2014 14:35]
Laurynas Biveinis
5.5$ bzr log -r 4606 -n0 ------------------------------------------------------------ revno: 4606 tags: clone-5.5.37-build committer: Satya Bodapati <satya.bodapati@oracle.com> branch nick: mysql-5.5 timestamp: Fri 2014-02-28 12:45:53 +0530 message: Bug#18124788 - COMPRESSED TABLES REGRESSION FROM 5.6.13 After incrementing the number of pending uncompression operations, buffer pool zip mutex can be released immediately. Approved by Vasil. rb#4514
Description: When a compressed table does not fit in memory, 5.6.13 degrades up to 20% compared to 5.6.12. Graph will be attached. How to repeat: I have 4 tables with 5M rows each, the test case shown here can be simpler. The data on each table is copied from a sysbench --prepare from an original table because the test tables has different structures. However, the tests are down with sysbench --oltp-read-only via the id columns. mysql [localhost] {msandbox} ((none)) > show create table t2.sbtest1 \G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `pk` int(10) unsigned NOT NULL AUTO_INCREMENT, `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 (`pk`), UNIQUE KEY `id` (`id`,`k`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > show create table t3.sbtest1 \G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `pk` int(10) unsigned NOT NULL, `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 (`pk`,`k`), KEY `k_1` (`k`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=1000000 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > show create table t3.sbtest1 \G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `pk` int(10) unsigned NOT NULL, `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 (`pk`,`k`), KEY `k_1` (`k`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=1000000 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > show create table t4.sbtest1 \G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `pk` int(10) unsigned NOT NULL AUTO_INCREMENT, `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 (`pk`), UNIQUE KEY `id` (`id`,`k`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 1 row in set (0.01 sec) -rw-rw----. 1 revin revin 6.1G Jan 20 01:44 data/t2/sbtest1.ibd -rw-rw----. 1 revin revin 2.6G Jan 20 01:45 data/t3/sbtest1.ibd -rw-rw----. 1 revin revin 720M Jan 20 01:47 data/t4/sbtest1.ibd -rw-rw----. 1 revin revin 724M Jan 20 01:48 data/t5/sbtest1.ibd /home/revin/bzr/sysbench/sysbench/sysbench --test=/home/revin/bzr/sysbench/sysbench/tests/db/oltp.lua --oltp-table-size=5000000 --oltp-test-mode=simple --oltp-tables-count=1 --max-requests=0 --max-time=1800 --mysql-user=msandbox --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=5613 --report-interval=5 --oltp-skip-trx --oltp-read-only=on --num-threads=64 --mysql-db=tN run Suggested fix: Increasing memory helps - and most likely not using compressed tables.