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:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: compression, regression
Triage: Needs Triage: D3 (Medium)

[21 Jan 2014 1:33] Jervin R
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.
[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