Re: innodb_autoinc_lock_mode=2

[Rick James]

Smells like a bug. Please post it at bugs.mysql.com

Meanwhile... file_scan is a large table (and presumably high insert rate) with a large number of secondary indexes. You may be pushing I/O limits of the system.

Current keys:
PRIMARY KEY (`file_scan_id`),
UNIQUE KEY `iall` (`file_id`,`scanner_id`),
KEY `idate` (`date_scanned`),
KEY `iscanner_id_result` (`scanner_id`,`result`),
KEY `iall_result` (`file_id`,`scanner_id`,`result`(1)) USING BTREE

Proposed keys:
PRIMARY KEY (`file_id`,`scanner_id`),
KEY `idate` (`date_scanned`),
KEY `iscanner_id_result` (`scanner_id`,`result`),

Notes:
* file_scan_id seems to otherwise be unused.
* By having file_id+scanner_id as the PK, and the way InnoDB clusters the PK, `iall_result` becomes unnecessary.
* Each UNIQUE KEY (including the PK) must be checked before approving an INSERT; this cuts that in half.
* The PK is appended to each secondary key. Currently it is 4 bytes; my proposal changes it to 12 bytes, but with overlap in one case. Still, the net effect will be a much smaller "Index_length".
* Is scanner_id only "1" or "2"? If so, change it to TINYINT UNSIGNED, thereby saving 3 bytes in each row. Plus 3 bytes per secondary key in my proposal.
* The I/O load may be cut significantly: The old PK (auto_increment) and date_scanned (I assume it is mostly NOW()) are mostly I/O-free. The rest (3 in your schema; 1 in mine) probably require I/O on each INSERT.