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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.