8

We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at some instance the commits with exact same timestamp gets slow.

This is extracted from MySQL slow query logs enter image description here

There isn't anyother query in the slow query log at the same time. Any pointers on how it can be further investigated?

We are using MySQL 5.7.16 on Amazon RDS.

2
  • 1
    Usually that means that something else was running and it finished just before the flood of things happened. Meanwhile, what is the "commit" that you want to do?
    – Rick James
    May 3 '17 at 15:08
  • @RickJames unable to find anything else except these commits in the slow query logs, any suggestions how to find it? We are using JPA + Hibernate and these commits are coming from the BE app.
    – Ahmed
    May 4 '17 at 4:48
8

Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to innodb_flush_log_at_trx_commit=0, however that may not be the right choice for you - see the previous link.

In any case, COMMITs don't usualy stall due to other activity on the server (like queries), unless that other activity locks some kind of mutex for far too long.

If I had to guess without any further evidence, I would say that your writes are so intensive, that MySQL is unable to flush fast enough using adaptive checkpointing, so it exceeds 75% of the log capacity and starts sharp checkpointing (which blocks server activity for a short period).

There are few quick ways to make MySQL handle writes better (besides one already mentioned above):

  1. Increase innodb log files (innodb_log_file_size) - you can go up to 4GB or so for your logs (esimating based on zero information given on the amount of writes),
  2. Increase pIOPS capacity for the data drive
  3. Increase innodb_io_capacity (up to ~70-80% of pIOPS capacity)

Otherwise it would be really useful to see disk activity from AWS, amount of data server currently writes (you can calculate it using Log sequence number delta between two consecutive show engine innodb status\G runs)

There are other ways too, but for that, I'd need a lot more details on the actual workload.

6
  • Actually not all of the commits are stalling, there are some instances that commits stall for a very short time. The pIOPS are set to 6000 but they remain below 1000. Our write IOPS count is around 700 ops/second.
    – Ahmed
    May 4 '17 at 12:08
  • innodb_flush_log_at_trx_commit=1 we faced an issue when we set it to 0, on automatic failover the replication was broken. So we had to make it 1 again.
    – Ahmed
    May 4 '17 at 12:10
  • 261.99 inserts/s, 184.78 updates/s, 0.32 deletes/s, 188287.95 reads/s
    – Ahmed
    May 4 '17 at 12:21
  • @Ahmed, what is the current size of your transaction logs ? Also could you measure the amount of writes with the LSN delta from two show engine innodb status\G runs as described here ? The resulting iops could be limited by your configuration, hence the importance of actual amount of data written. BTW, do you have small spikes in IO activity (around the time of the stall?) Maybe you could try to catch this issue with pt-stalk ? May 5 '17 at 5:21
  • the problem is not appearing so frequently these days, it looks like it appears mostly when the writes are high (some script doing mass insertions)
    – Ahmed
    May 17 '17 at 6:31

Your Answer

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

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