Constant deadlocks on a high load table

Question

I have a table files with about 73m rows, which totals to about 45GB.

The rows being inserted in a day are about 100k. But the total rows which we run IODKU can reach tens of millions a day (since many of the files are sent over and over again from different clients)

We are updating mainly the lastSeen column.

This is the table:

CREATE TABLE `files` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `sha256` binary(32) NOT NULL,
    `md5` binary(16) DEFAULT NULL,
    `sha1` binary(20) DEFAULT NULL ,
    `riskLevel` smallint(6) NOT NULL DEFAULT '0' ,
    `lastRA` timestamp NULL DEFAULT NULL ,
    `dateIn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `lastSeen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `CommonFileName` varchar(150) DEFAULT NULL ,
    `CommonPath` varchar(350) DEFAULT NULL ,
    `CommonExtension` char(5) DEFAULT NULL,
    `state2` tinyint(3) unsigned NOT NULL DEFAULT '0',
    `numComps` smallint(5) unsigned NOT NULL DEFAULT '0',
    `numClients` smallint(5) unsigned NOT NULL DEFAULT '0',
    `underAnalysis` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `sha256` (`sha256`),
    KEY `lastSeen` (`lastSeen`),
    KEY `dateIn` (`dateIn`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

I can see many locks:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

We are inserting in batches of 200, with around 20 parallel insert threads (no other concurrent updates on this table).

I am thinking of possible solutions:

1. Caching the data in application level and inserting every x minutes using 1 thread
2. Same as solution 1 just with a cache table in DB
3. Maybe this is an architectural issue and we shouldn't sync the same file so many times?

EDIT

This is an example of an insert batch:

INSERT INTO indicators.files (sha256, MD5, SHA1, riskLevel, lastRA, lastSeen, CommonFileName, CommonPath, CommonExtension, numComps, numClients, maxRiskLevel) 
VALUES (....) ON DUPLICATE KEY
UPDATE lastSeen = NOW()

Can't add the engine status because it's too big, but this is interesting from the engine status, I see many:

---TRANSACTION 84423352921, ACTIVE 11 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 5925505, OS thread handle 0x2b7adef8d700, query id 15526252234 ec2-54-217-206-23.eu-west-1.compute.amazonaws.com 54.217.206.23 root Sending data
INSERT INTO indicators.files
                        (sha256,md5, sha1, riskLevel, lastRA, lastSeen, CommonFileName, CommonPath, CommonExtension, numComps, numClients, maxRiskLevel)
                        SELECT sha256, md5, sha1, riskLevel, lastRA, lastSeen, CommonFileName, CommonPath, CommonExtension, 1 ,1, riskLevel
                        FROM sync6501.files as sync_files
                        WHERE sha256 IN (0x838A492EBAAA55C3492194BB63DDC31C5151B884CA94546DE59DEF034ECF2C45, 0xF75A9E9421F4736E91BFFB251BFB3797C104FAF67084698C4316A82FC2D86CD9, 0xC7F88106C79ABB3FE49DDBDAC15AFD4B05E67
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `indicators`.`files` trx id 84423352921 lock mode AUTO-INC waiting

Latest Deadlock:

LATEST DETECTED DEADLOCK
------------------------
2019-11-21 07:26:21 2b7ad8585700
*** (1) TRANSACTION:
TRANSACTION 84484300076, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 1488 lock struct(s), heap size 177704, 1596 row lock(s), undo log entries 878
MySQL thread id 6031119, OS thread handle 0x2b7aa0fce700, query id 15698217319 ec2-54-217-206-23.eu-west-1.compute.amazonaws.com 54.217.206.23 root update
INSERT INTO indicators.Domains (Domain, DomainCRC, last_seen) VALUES ('www2.unimed.coop.br',CRC32('www2.unimed.coop.br'),'2019-11-19 16:59:32'),('www.boticario.com.br',CRC32('www.boticario.com.br'),'2019-11-19 16:59:32'),('13.111.45.227',CRC32('13.111.45.227'),'2019-11-19 17:00:00'),('static.mrosupply.com',CRC32('static.mrosupply.com'),'2019-11-19 17:00:05'),('www.motionindustries.com',CRC32('www.motionindustries.com'),'2019-11-19 17:00:05'),('www.mrosupply.com',CRC32('www.mrosupply.com'),'2019-11-19 17:00:05'),('cic.ironmountain.com',CRC32('cic.ironmountain.com'),'2019-11-19 17:00:12'),('www.albionhotel.net',CRC32('www.albionhotel.net'),'2019-11-19 17:01:42'),('quimicaevestibular.com.br',CRC32('quimicaevestibular.com.br'),'2019-11-19 17:02:00'),('www.megawatsoft.com',CRC32('www.megawatsoft.com'),'2019-11-19 17:02:00'),('corinto.pucp.edu.pe',CRC32('corinto
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6220123 page no 2413929 n bits 648 index `Domain` of table `indicators`.`Domains` trx id 84484300076 lock_mode X waiting
Record lock, heap no 568 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 35322e37322e382e313634; asc 52.72.8.164;;
 1: len 4; hex 7ffe4a1d; asc   J ;;

*** (2) TRANSACTION:
TRANSACTION 84484300765, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
1428 lock struct(s), heap size 177704, 1775 row lock(s), undo log entries 715
MySQL thread id 5880464, OS thread handle 0x2b7ad8585700, query id 15698219237 ec2-54-217-206-23.eu-west-1.compute.amazonaws.com 54.217.206.23 root update
INSERT INTO indicators.Domains (Domain, DomainCRC, last_seen) VALUES ('r3.res.outlook.com',CRC32('r3.res.outlook.com'),'2019-11-19 16:10:17'),('dynamosoftware.zendesk.com',CRC32('dynamosoftware.zendesk.com'),'2019-11-19 16:10:17'),('secure.gravatar.com',CRC32('secure.gravatar.com'),'2019-11-19 16:10:17'),('imap.gmail.com',CRC32('imap.gmail.com'),'2019-11-19 16:10:17'),('40.97.28.114',CRC32('40.97.28.114'),'2019-11-19 16:10:17'),('40.97.199.114',CRC32('40.97.199.114'),'2019-11-19 16:10:17'),('40.91.91.94',CRC32('40.91.91.94'),'2019-11-19 16:10:17'),('52.114.77.34',CRC32('52.114.77.34'),'2019-11-19 16:10:17'),('40.97.154.242',CRC32('40.97.154.242'),'2019-11-19 16:10:17'),('107.180.41.168',CRC32('107.180.41.168'),'2019-11-19 16:10:17'),('23.221.210.216',CRC32('23.221.210.216'),'2019-11-19 16:10:17'),('52.96.16.162',CRC32('52.96.16.162'),'2019-11-19 16:10:17')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6220123 page no 2413929 n bits 648 index `Domain` of table `indicators`.`Domains` trx id 84484300765 lock_mode X
Record lock, heap no 477 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 12; hex 35322e37322e36312e323031; asc 52.72.61.201;;
 1: len 4; hex 4e6229a1; asc Nb) ;;

Record lock, heap no 568 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 35322e37322e382e313634; asc 52.72.8.164;;
 1: len 4; hex 7ffe4a1d; asc   J ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6220123 page no 336162 n bits 744 index `Domain` of table `indicators`.`Domains` trx id 84484300765 lock_mode X waiting
Record lock, heap no 586 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 13; hex 36362e38352e3133362e313031; asc 66.85.136.101;;
 1: len 4; hex 6d1dada2; asc m   ;;

Answer 1

Here are some things to try:

Pre-sort

For each batch you insert, pre-sort them by the sha256 column in increasing order. This will help prevent the most basic deadlock situation, for any pair of transactions that have a pair of rows in common.

The fact that you have 2 unique indexes (sha256 and id) may make this effort futile, but it is at least a cheap initial attempt.

Smaller batches

The smaller the batch the less likely they will contain the same rows, and so less likely to deadlock.

Get rid of the id column.

Why do you have an id column? If it isn't necessary, get rid of it and use sha256 as the primary key.

There are lots of questions on this site with a similar situation as you: deadlocks and multiple unique indexes.

Example 1

Example 2

I'm sure there are many more.

That said, this option is likely to be very expensive, both in changing how your application works and in the actual time to execute the ALTER TABLE statements. I suggest trying this in a test environment first, and see if you can first duplicate the problem with your current structure, and then observe whether a sha256 primary key table experiences the same deadlocks.

Provide more information

What is the output of SHOW ENGINE INNODB STATUS? That contains the latest deadlock information. It may provide additional clues on what exactly is causing your deadlocks.

What is the exact structure of your query? That may provide some useful information as well.

Answer 2

URGENT! Check max(id). IODKU (and all other INSERTs), "burn" ids. That is, they allocate the ids that they might need, but don't return them.

"tens of millions" --> INT UNSIGNED may overflow in less than a year.

Plan A: change to BIGINT

Plan B: check before inserting.

The best is to gather rows, then batch insert only the new ones. This speeds up the processing and avoids burning ids.

If rows have been gathered into table t. Stop after, say, 100 rows or 1 second, whichever comes first.

INSERT INTO real (...)
    SELECT ...
        FROM t
        LEFT JOIN real
        WHERE real.id IS NULL;   -- to limit to only new rows

That is a subset of what if found in my high-speed ingestion blog, namely the part dealing with "normalization" (which has lots of dupss).

The ping-ponging part of the above query explains how various clients can insert rows into a single table. Then a separate task could swap tables, allowing the inserts to continue in an empty table, while batch processing the other table. The dedupping of rows (not mentioned in the blog) could be done first with this table, then secondarily with the real table.

Part of the issue is UNIQUE(sha256) -- This is very random, and it must be checked before finishing the INSERT.

Pre-sorting on sha256: Futile, because too small a percentage of rows will happen to be in the same blocks.

Get rid of the id column? There are many pros and cons; you have not provided enough info to make a judgement.

• Are other tables using the id now? Note: BINARY(32) is bigger than INT (4 bytes) or `BIGINT (8 bytes).
• The secondary indexes would be bulkier.
• The check for uniqueness via the hash might be slower because the PK index is bulkier than a secondary index.
• The table would lose "locality of reference", if that is important.

Please show us the competing query in the "latest deadlock".

Answer 3

I want to add one more possible answer which seems to do the job:

How do I create a sequence in MySQL?

Basically doing the Auto-Inc in application level instead of letting the DB handle it.