I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (insert/updates) start to get more and more slower. When the queries get slower, the throughput goes down and the database gets fewer inserts/updates. This causes the database to get fast again, and then we're stuck in a loop.

I can see the IOPS and CPU going up together, however, they don't go up more than 50%, which makes me imagine that I'm underusing the database and that there's some config that could be changed or that's causing it.

This can be seen/illustrated in the graph below:


I'm using the Memory Optimized Azure MySQL with 32vCPU and I have around 900 active connections and here is some important configs I have set currently:

innodb_buffer_pool_size : 80% of avail memory
io_threads (read/write) : 64
innodb_log_buffer_size: 1Gb
innodb_io_capacity: 5000
innodb_io_capacity_max: 9000
query_cache_size: 0
innodb_change_buffer_max_size: 50
table_open_cache: 40000
table_open_cache_instances: 8
innodb_lru_scan_depth: 8192
innodb_max_dirty_pages_pct: 65

Here's the INNOB Status for a high throughput period : SHOW ENGINE INNODB STATUS

I'm not sure what other configs I should be looking into to use the max of my CPU/IOPS and would love some tips on what to look into.

  • 1
    innodb_lru_scan_depth is normally increased with io_capacity. With large about of writes innodb_log_file_size/innodb_log_files_in_group are normally increased (needs nonfast -innodb_fast_shutdown=0 shutdown - see manual for changing these). Could flush more often by decreasing innodb_max_dirty_pages_pct too. – danblack Nov 28 '19 at 20:47
  • 1
    Hi @danblack thanks for responding. I'm sorry I'm not sure if I follow. You're saying this might be caused because the DB is not flushing much? my txn_commit=1 (I need it to be this way) so I thought it could be the opposite (I'm doing too many flushs) – Guilherme Rodriguez Nov 28 '19 at 21:05
  • 1
    SHOW ENGINE INNODB STATUS would be useful. – danblack Nov 28 '19 at 21:50
  • The more dirty pages there are there is a longer history on each database row. The queries need to go through the history to get the entry that corresponds to when the transaction was started (repeatable-read isolation). Are they all insert/update queries? You can't tune your way out of every problem. Can you show the table structures, queries and query plan (EXPLAIN {query})? – danblack Nov 28 '19 at 22:29
  • 1
    @GuilhermeRodriguez Your SHOW ENGINE INNODB STATUS indicates you have innodb_buffer_pool_instances of 8 configured. Use 100 for INNODB_LRU_SCAN_DEPTH rather than 8192 to conserve 90% of CPU cycles used for the function every SECOND. – Wilson Hauck Dec 8 '19 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.