slow disk with InnoDB page compression

Posted on

Question :

I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems:

  1. The database gets slow after a while. INSERTs get noticeably slower after one day. When restarting the machine or MariaDB, it gets backs to normal speed.

  2. It takes a long time to shutdown MariaDB (~30 min). During this time, the SSDs have heavy disk activity (as checked by iostat). Note that it is not flushing the data into the disk. Before shutting down, the number of dirty pages is zero, and there is absolutely no activity in show engine innodb status G.

I think it might be due to the rearrangement of data on the SSD drives (about 50% full). Some sort of activity like SSD TRIM.

How can I diagnose the underlying causes?

This is part of my config, which might be relevant

key_buffer_size     = 20M
max_allowed_packet  = 5G
thread_stack        = 256K
thread_cache_size       = 8
innodb_buffer_pool_size = 50G
innodb_log_buffer_size = 128M
innodb_log_file_size = 10G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_autoinc_lock_mode = 0
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
tmp_table_size=2G
max_heap_table_size=512M
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-fast-shutdown=0
innodb-ft-result-cache-limit=4G
innodb_compression_default=ON

Answer :

I face a similar issue with MariaDB when using page compression.
My inserts would be fast, but after a few hours, inserts would ‘freeze’ for many seconds/minutes at a time.

I tried tweaking the innodb_log_file_size and set it to a higher value (like
56GB) and it reduced the number of times would happen.
https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_file_size

You can also try tweaking innodb_io_capacity and innodb_io_capacity_max
https://mariadb.com/kb/en/innodb-page-flushing/

I got these recommendations from AWS RDS support team.

Leave a Reply

Your email address will not be published. Required fields are marked *