MySQL 5.6 too much write after enabling compression

by Carson Ip   Last Updated June 13, 2019 09:06 AM

Recently I have enabled table compression (i.e. ROW_FORMAT=COMPRESSED) on half of the tables in a MySQL 5.6 server. I am observing:

  1. much higher write IOPS (~700 -> ~3000)
  2. dangerously lower dirty pages in buffer pool (7G -> 16MB)
  3. no change in number of buffer pool write requests
  4. much higher data write (200->1.2k) and fsync (60->300)

The compression aspect looks fine: 10ops compression attempts and 50ops uncompressed attempts, compression success ratio > 99%

~300K used 8192-byte pages in buffer pool

This number of write IOPS is worrying. Any possible explanation and solution?

server spec: Amazon RDS r5.xlarge (32GB ram)

variables:

innodb_log_compressed_pages=0
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=1500
innodb_io_capacity_max=3000
innodb_log_file_size=3221225472
innodb_buffer_pool_size={DBInstanceClassMemory*3/4} (=32GB*3/4)
mysql> show global status like '%buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 1507853     |
| Innodb_buffer_pool_bytes_data         | 24750940160 |
| Innodb_buffer_pool_pages_dirty        | 289         |
| Innodb_buffer_pool_bytes_dirty        | 4472832     |
| Innodb_buffer_pool_pages_flushed      | 585246048   |
| Innodb_buffer_pool_pages_free         | 2042        |
| Innodb_buffer_pool_pages_misc         | 2873        |
| Innodb_buffer_pool_pages_total        | 1512768     |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 19708056    |
| Innodb_buffer_pool_read_ahead_evicted | 180068      |
| Innodb_buffer_pool_read_requests      | 54954180112 |
| Innodb_buffer_pool_reads              | 202976263   |
| Innodb_buffer_pool_wait_free          | 52377       |
| Innodb_buffer_pool_write_requests     | 11397188948 |
+---------------------------------------+-------------+
17 rows in set (0.00 sec)

Thanks in advance. Please let me know what else is needed to diagnose this issue.



Related Questions


Updated May 17, 2018 08:06 AM

Updated May 08, 2018 01:06 AM

Updated January 04, 2019 14:06 PM

Updated May 24, 2019 00:06 AM