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