Mysql
高負載表上的持續死鎖
我有一個
files大約 73m 行的表，總計大約 45GB。一天插入的行數約為 100k。但是我們每天執行 IODKU 的總行數可以達到數千萬（因為許多文件是從不同的客戶端一遍又一遍地發送的）
我們主要更新
lastSeen專欄。這是表格：
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我可以看到很多鎖：
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;
我們分批插入 200 個，大約有 20 個並行插入執行緒（此表上沒有其他並發更新）。
我正在考慮可能的解決方案：
- 在應用程序級別記憶體數據並使用 1 個執行緒每 x 分鐘插入一次
- 與解決方案 1 相同，只是在 DB 中有一個記憶體表
- 也許這是一個架構問題，我們不應該多次同步同一個文件？
編輯
這是插入批處理的範例：
INSERT INTO indicators.files (sha256, MD5, SHA1, riskLevel, lastRA, lastSeen, CommonFileName, CommonPath, CommonExtension, numComps, numClients, maxRiskLevel) VALUES (....) ON DUPLICATE KEY UPDATE lastSeen = NOW()無法添加引擎狀態，因為它太大了，但是從引擎狀態來看這很有趣，我看到很多：
---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 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 ;;
以下是一些可以嘗試的事情：
預分類
sha256對於您插入的每個批次，按列按升序對它們進行預排序。這將有助於防止最基本的死鎖情況，對於任何一對具有共同行的事務。
sha256您有 2 個唯一索引 (和)的事實id可能會使這項工作徒勞無功，但這至少是一個廉價的初步嘗試。小批量
批次越小，它們包含相同行的可能性就越小，因此死鎖的可能性就越小。
擺脫
id柱子。為什麼有
id專欄？如果沒有必要，去掉它並用sha256作主鍵。這個站點上有很多問題與您的情況相似：死鎖和多個唯一索引。
我敢肯定還有很多。
ALTER TABLE也就是說，無論是在更改應用程序的工作方式還是在執行語句的實際時間方面，此選項都可能非常昂貴。我建議先在測試環境中嘗試一下，看看是否可以先用目前結構複製問題，然後觀察sha256主鍵表是否遇到相同的死鎖。提供更多資訊
的輸出是
SHOW ENGINE INNODB STATUS什麼？其中包含最新的死鎖資訊。它可能會提供有關究竟是什麼導致您的死鎖的額外線索。您的查詢的確切結構是什麼？這也可能提供一些有用的資訊。
緊迫的！檢查最大值（id）。IODKU（和所有其他插入），“刻錄”ID。也就是說，他們分配他們可能需要的 id，但不返回它們。
“幾千萬” –>
INT UNSIGNED不到一年就可能溢出。計劃A：更改為
BIGINT方案 B：插入前檢查。
最好的辦法是收集行，然後只批量插入新行。這樣可以加快處理速度並避免刻錄 id。
如果行已被收集到表中
t。在 100 行或 1 秒後停止，以先到者為準。INSERT INTO real (...) SELECT ... FROM t LEFT JOIN real WHERE real.id IS NULL; -- to limit to only new rows這是我的高速攝取部落格中的一個子集，即處理“標準化”的部分（有很多錯誤）。
上述查詢的乒乓部分解釋了各種客戶端如何將行插入到單個表中。然後一個單獨的任務可以交換錶，允許插入在一個空表中繼續，同時批處理另一個表。行的重複數據刪除（部落格中未提及）可以首先使用此表完成，然後再使用真實表完成。
部分問題是
UNIQUE(sha256)- 這是非常隨機的，必須在完成INSERT.在 sha256 上進行預排序：徒勞，因為太小比例的行會碰巧在同一個塊中。
擺脫
id柱子？有很多優點和缺點；您沒有提供足夠的資訊來做出判斷。
- 其他表是否在使用
idnow？注意：BINARY(32)大於INT（4 字節）或`BIGINT（8 字節）。- 二級索引會更大。
- 通過散列檢查唯一性可能會更慢，因為 PK 索引比二級索引更龐大。
- 如果這很重要，該表將失去“參考位置”。
請向我們展示“最新死鎖”中的競爭查詢。