Return to Question

The time of the stall in those cases is roughly the same, too.

The time of the stall in those cases is roughly the same, too. I have opened a bug on bugs.mysql.com so the devs could look into this.

###Peculiarity More often than not, I notice in the mysql slow log that the oldest query stalling is an INSERT into a large-ish (~10 M rows) table with a VARCHAR primary key:

With primary keys being clustered indexes in an InnoDB table it might be that a page split is the trigger for the stall.

###Edit Further investigation (i.e. SHOW ENGINE INNODB STATUS) has shown that it indeed always is an update to the files table which is causing the stall. And "SHOW ENGINE INNODB STATUS" is saying in the TRANSACTIONS section:

The FTS size is quite impressive:

yet, it looks like a bug in MySQL 5.6.20 causing index operations to block all further updates.

More often than not, I notice in the mysql slow log that the oldest query stalling is an INSERT into a large-ish (~10 M rows) table with a VARCHAR primary key and a full-text search index:

Further investigation (i.e. SHOW ENGINE INNODB STATUS) has shown that it indeed always is an update to a table using full-text indexes which is causing the stall. The respective TRANSACTIONS section of "SHOW ENGINE INNODB STATUS" has entries like these two for the oldest running transactions:

From the logs it seems a bit like the undo log entries number for doing SYNC index is advancing at ~150/s until it reaches 20,000, at which point the operation is done.

The FTS size of this specific table is quite impressive:

although the issue is also triggered by tables with significantly less massive FTS data size like this one:

# du -c FTS_0000000000002467_0000000000003a21_INDEX* 49156 FTS_0000000000002467_0000000000003a21_INDEX_1.ibd 225284 FTS_0000000000002467_0000000000003a21_INDEX_2.ibd 147460 FTS_0000000000002467_0000000000003a21_INDEX_3.ibd 135172 FTS_0000000000002467_0000000000003a21_INDEX_4.ibd 155652 FTS_0000000000002467_0000000000003a21_INDEX_5.ibd 106500 FTS_0000000000002467_0000000000003a21_INDEX_6.ibd 819224 total 

The time of the stall in those cases is roughly the same, too.

###Peculiarity More often than not, I notice in the mysql slow log that the oldest query stalling is an INSERT into a large-ish (~10 M rows) table with a VARCHAR primary key:

CREATE TABLE `files` ( `id_files` varchar(32) NOT NULL DEFAULT '', `filename` varchar(100) NOT NULL DEFAULT '', `content` text, PRIMARY KEY (`id_files`), KEY `filename` (`filename`), FULLTEXT KEY `content` (`content`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 

With primary keys being clustered indexes in an InnoDB table it might be that a page split is the trigger for the stall.

###Edit Further investigation (i.e. SHOW ENGINE INNODB STATUS) has shown that it indeed always is an update to the files table which is causing the stall. And "SHOW ENGINE INNODB STATUS" is saying in the TRANSACTIONS section:

---TRANSACTION 162269409, ACTIVE 122 sec doing SYNC index 6 lock struct(s), heap size 1184, 0 row lock(s), undo log entries 19942 TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_1" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_2" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_3" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_4" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_5" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_6" trx id 162269409 lock mode IX ---TRANSACTION 162269408, ACTIVE (PREPARED) 122 sec committing mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1 MySQL thread id 165998, OS thread handle 0x7fe0e239c700, query id 91208956 192.168.10.153 root query end INSERT INTO files (id_files, filename, content) VALUES ('f19e63340fad44841580c0371bc51434', '1237716_File_70380a686effd6b66592bb5eeb3d9b06.doc', '[...] TABLE LOCK table `vw`.`files` trx id 162269408 lock mode IX 

So there is some heavy full text index action going on there (doing SYNC index) stopping ALL SUBSEQUENT updates to ANY table.

The FTS size is quite impressive:

# du -c FTS_000000000000224a_00000000000036b9_* 614404 FTS_000000000000224a_00000000000036b9_INDEX_1.ibd 2478084 FTS_000000000000224a_00000000000036b9_INDEX_2.ibd 1576964 FTS_000000000000224a_00000000000036b9_INDEX_3.ibd 1630212 FTS_000000000000224a_00000000000036b9_INDEX_4.ibd 1978372 FTS_000000000000224a_00000000000036b9_INDEX_5.ibd 1159172 FTS_000000000000224a_00000000000036b9_INDEX_6.ibd 9437208 total 

yet, it looks like a bug in MySQL 5.6.20 causing index operations to block all further updates.

###Peculiarity More often than not, I notice in the mysql slow log that the oldest query stalling is an INSERT into a large-ish (~10 M rows) table with a VARCHAR primary key:

CREATE TABLE `files` ( `id_files` varchar(32) NOT NULL DEFAULT '', `filename` varchar(100) NOT NULL DEFAULT '', `content` text, PRIMARY KEY (`id_files`), KEY `filename` (`filename`), FULLTEXT KEY `content` (`content`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 

With primary keys being clustered indexes in an InnoDB table it might be that a page split is the trigger for the stall.

###Edit Further investigation (i.e. SHOW ENGINE INNODB STATUS) has shown that it indeed always is an update to the files table which is causing the stall. And "SHOW ENGINE INNODB STATUS" is saying in the TRANSACTIONS section:

---TRANSACTION 162269409, ACTIVE 122 sec doing SYNC index 6 lock struct(s), heap size 1184, 0 row lock(s), undo log entries 19942 TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_1" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_2" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_3" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_4" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_5" trx id 162269409 lock mode IX TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_6" trx id 162269409 lock mode IX ---TRANSACTION 162269408, ACTIVE (PREPARED) 122 sec committing mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1 MySQL thread id 165998, OS thread handle 0x7fe0e239c700, query id 91208956 192.168.10.153 root query end INSERT INTO files (id_files, filename, content) VALUES ('f19e63340fad44841580c0371bc51434', '1237716_File_70380a686effd6b66592bb5eeb3d9b06.doc', '[...] TABLE LOCK table `vw`.`files` trx id 162269408 lock mode IX 

So there is some heavy full text index action going on there (doing SYNC index) stopping ALL SUBSEQUENT updates to ANY table.

The FTS size is quite impressive:

# du -c FTS_000000000000224a_00000000000036b9_* 614404 FTS_000000000000224a_00000000000036b9_INDEX_1.ibd 2478084 FTS_000000000000224a_00000000000036b9_INDEX_2.ibd 1576964 FTS_000000000000224a_00000000000036b9_INDEX_3.ibd 1630212 FTS_000000000000224a_00000000000036b9_INDEX_4.ibd 1978372 FTS_000000000000224a_00000000000036b9_INDEX_5.ibd 1159172 FTS_000000000000224a_00000000000036b9_INDEX_6.ibd 9437208 total 

yet, it looks like a bug in MySQL 5.6.20 causing index operations to block all further updates.