1

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
Improve this question
15
  • Ram size? Disk size? Value of innodb_buffer_pool_size? Frequent queries?
    – Rick James
    Commented May 11, 2021 at 2:29
  • @RickJames 128GB RAM< 2TB NVMe SSD, 50GB pool size. Yes, it is constantly working with heavy queries (not repeated queries for web service or something like that). It is for the purpose of data analysis. I frequently write and read into/from tables with tens/hundreds of million rows.
    – Googlebot
    Commented May 11, 2021 at 2:50
  • Would "summary tables" help you?
    – Rick James
    Commented May 11, 2021 at 4:46
  • @RickJames I had checked performance_schema before, but this should be impertinent (I should find/inspect the disk activity when shutting down). I think the problem is an essential task of MariaDB/InnoDB for organising the data written onto the disk. Maybe the reason I faced it (but not reported on the internet) is that I work with large tables for which a massive set of data should be organised. It's common for me to INSERT 100-200 million rows in a single query. The key is that I had the same SDDs before moving from row to page compression, and never faced such behaviours.
    – Googlebot
    Commented May 11, 2021 at 5:35
  • Aha. A single query with 100M rows builds a huge undo log. This is probably what is causing the issue.
    – Rick James
    Commented May 11, 2021 at 14:43

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.