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.