Mysql – slow disk with InnoDB page compression

compressioninnodbmariadbmariadb-10.5MySQL

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 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

Best 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.

Related Solutions

InnoDB INSERT Performance Capabilities in MySQL

You need to tune your InnoDB settings in the following areas:

Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
- High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
- High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
- Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
- Your current setting for innodb_flush_log_at_trx_commit is good
- Your current setting for innodb_flush_method is good
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000

Here are my past posts on tuning the InnoDB storage engine

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Why does InnoDB store all databases in one file? (Mar 25, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)
How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

Best Answer

Related Solutions

InnoDB INSERT Performance Capabilities in MySQL

Mysql – Cannot write to thesql database while select query in process

Related Question