mysql slow-log series of COMMIT only entries


This is a section from a mysql-slow-query log for a host. I haven’t trimmed any entries out of the logs, so, you can see it’s a bunch of commit entries:

Currently the innodb_io_capacity is set to 200 (default). From looking at other stackexchange messages it looks like upping this value could be the way to go. However, looking at the datadog IOPS graph below, it doesn’t look like we’re getting above ~40, so, I’m not sure it’ll help at all (if not even make things worse) to up the number.

Any suggestions welcome

mysql IOPS graph


# Time: 2020-09-10T14:14:02.076528Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35591
# Query_time: 1.230601  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747242;
COMMIT;
# Time: 2020-09-10T14:14:02.076538Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 1.062418  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747242;
COMMIT;
# Time: 2020-09-10T14:14:28.808115Z
# User@Host: datadog(datadog) @  (127.0.0.1)  Id: 35595
# Query_time: 1.617290  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747268;
SHOW BINARY LOGS;
# Time: 2020-09-10T14:14:28.862131Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 2.068354  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747268;
COMMIT;
# Time: 2020-09-10T14:14:29.126253Z
# User@Host: foohost(foohost) @  (fooip)  Id: 34724
# Query_time: 2.014779  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747269;
COMMIT;
# Time: 2020-09-10T14:14:29.126305Z
# User@Host: foohost(foohost) @  (fooip)  Id: 34988
# Query_time: 1.574517  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747269;
COMMIT;
# Time: 2020-09-10T14:14:29.126256Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33512
# Query_time: 2.094602  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747269;
COMMIT;
# Time: 2020-09-10T14:14:29.126277Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35577
# Query_time: 1.005327  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747269;
COMMIT;
# Time: 2020-09-10T14:14:30.683645Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35579
# Query_time: 1.157522  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747270;
COMMIT;
# Time: 2020-09-10T14:14:30.683701Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35577
# Query_time: 1.152817  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747270;
COMMIT;
# Time: 2020-09-10T14:14:30.683648Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35588
# Query_time: 1.144674  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747270;
COMMIT;
# Time: 2020-09-10T14:14:30.683646Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35578
# Query_time: 1.150778  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747270;
COMMIT;
# Time: 2020-09-10T14:14:30.683737Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33512
# Query_time: 1.150129  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747270;
COMMIT;
# Time: 2020-09-10T14:14:31.565181Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 1.707623  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747271;
COMMIT;
# Time: 2020-09-10T14:14:31.565187Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35589
# Query_time: 1.229553  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747271;
COMMIT;
# Time: 2020-09-10T14:14:31.821701Z
# User@Host: foohost(foohost) @  (fooip)  Id: 35579
# Query_time: 1.130455  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747271;
COMMIT;
# Time: 2020-09-10T14:15:06.274071Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 1.247492  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747306;
COMMIT;
# Time: 2020-09-10T14:15:08.715511Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 1.601739  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747308;
COMMIT;
# Time: 2020-09-10T14:15:38.025337Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 1.597416  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747338;
COMMIT;
# Time: 2020-09-10T14:18:36.861812Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33511
# Query_time: 2.154160  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747516;
COMMIT;
# Time: 2020-09-10T14:18:37.200459Z
# User@Host: foohost(foohost) @  (fooip)  Id: 34988
# Query_time: 1.100469  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747517;
COMMIT;
# Time: 2020-09-10T14:18:37.200477Z
# User@Host: foohost(foohost) @  (fooip)  Id: 34724
# Query_time: 1.390703  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747517;
COMMIT;
# Time: 2020-09-10T14:18:37.200561Z
# User@Host: foohost(foohost) @  (fooip)  Id: 33512
# Query_time: 1.742150  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1599747517;