MySQL instance stalling "doing SYNC index"

Problem

An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, UPDATE and DELETE queries remaining in "Query end" state. This obviously is most unfortunate. The MySQL slow query log is logging even the most trivial queries with insane query times, hundreds of them with the same timestamp corresponding to the point in time where the stall has been resolved:

# Query_time: 101.743589  Lock_time: 0.000437 Rows_sent: 0  Rows_examined: 0
SET timestamp=1409573952;
INSERT INTO sessions (redirect_login2, data, hostname, fk_users_primary, fk_users, id_sessions, timestamp) VALUES (NULL, NULL, '192.168.10.151', NULL, 'anonymous', '64ef367018099de4d4183ffa3bc0848a', '1409573850');

And the device statistics are showing increased, although not excessive I/O load in this time frame (in this case updates were stalling 14:17:30 - 14:19:12 according to the timestamps from the statement above):

```
# sar -d
[...]
02:15:01 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
02:16:01 PM dev8-0 41.53 207.43 1227.51 34.55 0.34 8.28 3.89 16.15
02:17:01 PM dev8-0 59.41 137.71 2240.32 40.02 0.39 6.53 4.04 24.00
02:18:01 PM dev8-0 122.08 2816.99 1633.44 36.45 3.84 31.46 1.21 2.88
02:19:01 PM dev8-0 253.29 5559.84 3888.03 37.30 6.61 26.08 1.85 6.73
02:20:01 PM dev8-0 101.74 1391.92 2786.41 41.07 1.69 16.57 3.55 36.17
[...]
# sar
[...]
02:15:01 PM CPU %user %nice %system %iowait %steal %idle
02:16:01 PM all 15.99 0.00 12.49 2.08 0.00 69.44
02:17:01 PM all 13.67 0.00 9.45 3.15 0.00 73.73
02:18:01 PM all 10.64 0.00 6.26 11.65 0.00 71.45

We were seeing the same issue on two servers on versions 5.6.12 and 5.6.16 running on Windows, with a pair of slaves each. We were stumped, like you, for almost two months.

Solution:

set global binlog_order_commits = 0;

See
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_order_commits for details of the variable.

Explanation:

InnoDB full-text uses a cache (by default 8M in size) containing changes that need to be applied to the actual full-text index on disk.

Once the cache fills up, a couple of transactions are created to perform the work of merging the data that was contained in the cache - this tends to be a large amount of random IO, so unless your entire full-text index can be loaded into the buffer pool, it's a long and slow transaction.

With binlog_order_commits set to true, all transactions containing inserts and updates, started after the long-running fts_sync_index transaction, must wait until it has completed before they can commit.

This is only an issue if binary logging is enabled

StackExchange Database Administrators Q#75439, answer score: 6