I'm trying to understand why InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it.

I have a table with nearly 5M rows, about 9GB in size. I also have a queue with chunks of data to be inserted into that table. On average there is about one insert every 2-3 seconds, but the database seems to sit at around 20% CPU usage. What's interesting, if I stop the worker processing the queue and wait until there's, say, 10-20k jobs to process and I restart the worker so that they get processed one after another, the CPU usage seems to be a lot lower! Only after the worker caught up with the queue and it's back to around 1 write every 2-3 seconds, the CPU usage goes up.

I've also noticed it when running the insert query by hand, with null values, if I insert them with a few seconds intervals, there seems to be a CPU spike about 1 second after every insert (which makes sense), but if run a lot of inserts quickly one after another, there's just one CPU spike after I stop the inserts.

It's a bit of a problem for me because the database runs on AWS, on t2.small instance and 20% CPU is the baseline performance level, which means I'm running out of CPU credits.

More technical details:

Ubuntu 16.04
Percona Server 5.7.19-17-log
AWS t2.small (1 CPU, 2GB RAM)

MySQL config (based on Percona's blog post):

innodb_buffer_pool_instances = 4 innodb_buffer_pool_chunk_size = 128M innodb_buffer_pool_size = 1500M innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT

I've tried deleting all indexes, no changes.

  • most interaction gets triggered after commit time. – danblack Sep 19 '18 at 22:44
  • See what effect lowering innodb_max_dirty_pages_pct to 50% or so makes. Is your worker processing lots in transaction. Note long connections in a repeatable_read isolation mode means a lot of history needs to be maintained. Limit the connection time length of workers in queues. – danblack Sep 19 '18 at 23:39

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.