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:
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.
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
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
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.
You can also try tweaking
I got these recommendations from AWS RDS support team.