如何避免鎖等待超時超出並提高 MySQL InnoDB 寫入速度

我執行了一個產生 25 個執行緒的多執行緒客戶端，以進行並發 API 呼叫並將數據插入 AWS Aurora 伺服器。

一段時間後，我開始看到超時錯誤：lock wait timeout exceeded try restarting transaction。我們對執行 MySQL 5.6.10 的伺服器執行相同的測試，並且沒有發生鎖定等待超時。

有沒有辦法避免這種超時？

在 AWS Aurora 伺服器上，SHOW ENGINE INNODB STATUS顯示：

---TRANSACTION 8530565676, ACTIVE 81 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 405, OS thread handle 0x2ae270b03700, query id 11045 10.50.101.56 app_migration
INSERT INTO contacts_contactaudit (action,
   contact_id,
   date_created,
   date_updated,
   external_contact_id,
   entity_name,
   first_name,
   last_name,
   middle_name,
   actor_created_id,
   actor_updated_id,
   email,
   phone_number_id,
   external_contact_guid,
   external_shared_contact_id,
   active_timezone, audit_date)
SELECT 'I' as action, new.id,
   new.date_created,
   new.date_updated,
   new.external_contact_id,
   new.entity_name,
   new.first_name,
   new.last_name,
   new.middle_name,
   new.actor_created_id,
   new.actor_updated_id,
   new.email,
   new.phone_number_id,
   new.external_contact_guid,
   new.external_shared_contact_id,
   new.active_timezone, now();

這是我們為 INSERTs 語句創建的觸發器：

CREATE TRIGGER contacts_contact_insert_audit
AFTER INSERT ON contacts_contact
FOR EACH ROW
   INSERT INTO contacts_contactaudit (action,
   ...
   audit_date)
SELECT 'I' as action, new.id,
   ... 
now();

這是審計表模式：

 CREATE TABLE `contacts_contactaudit` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date_created` datetime(6) DEFAULT NULL,
 `date_updated` datetime(6) DEFAULT NULL,
 `action` varchar(1) NOT NULL,
 `audit_date` datetime(6) NOT NULL,
 `contact_id` int(11) DEFAULT NULL,
 `external_contact_id` bigint(20) DEFAULT NULL,
 `entity_name` varchar(128) DEFAULT NULL,
 `first_name` varchar(128) DEFAULT NULL,
 `last_name` varchar(128) DEFAULT NULL,
 `middle_name` varchar(128) DEFAULT NULL,
 `actor_created_id` int(11) DEFAULT NULL,
 `actor_updated_id` int(11) DEFAULT NULL,
 `email` varchar(256) DEFAULT NULL,
 `phone_number_id` int(11) DEFAULT NULL,
 `external_contact_guid` varchar(128) DEFAULT NULL,
 `external_shared_contact_id` bigint(20) DEFAULT NULL,
 `active_timezone` varchar(128),
 PRIMARY KEY (`id`),
 KEY `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` (`actor_created_id`),
 KEY `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` (`actor_updated_id`),
 KEY `contacts_contactaudit_contact_id_9b809fe7_uniq` (`contact_id`),
 CONSTRAINT `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` FOREIGN KEY (`actor_created_id`) REFERENCES `actors_actor` (`id`),
 CONSTRAINT `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` FOREIGN KEY (`actor_updated_id`) REFERENCES `actors_actor` (`id`)
) 
ENGINE=InnoDB 
AUTO_INCREMENT=21577 
DEFAULT CHARSET=utf8;

我們已經確定了根本原因：它是innodb_autoinc_lock_mode = 1.

以下是官方文件的摘要：

• 0: traditional lock mode，用於向後兼容性、性能測試和解決“混合模式插入”問題，因為語義上可能存在差異。
• 1: consecutive lock mode: 在這種模式下，“批量插入”使用特殊的 AUTO-INC 表級鎖並持有它直到語句結束。這適用於所有 INSERT … SELECT、REPLACE … SELECT 和 LOAD DATA 語句。一次只能執行一個持有 AUTO-INC 鎖的語句
• 2: interleaved lock mode: 在這種鎖模式下，沒有“INSERT-like”語句使用表級AUTO-INC鎖，多個語句可以同時執行。這是最快且最具可擴展性的鎖定模式，但在從二進制日誌重放 SQL 語句時使用基於語句的複製或恢復場景時，它並不安全。

在我們的例子中，當多個 API 呼叫 INSERT 語句時，如果一個 API 呼叫被延長，該 INSERT 語句將在目標表上持有一個 TABLE 鎖，從而導致超時。

我們將其切換到innodb_autoinc_lock_mode = 2，重新啟動伺服器，瞧。現在我們可以呼叫多個 API 來插入同一個表而不會超時。

也許可能有一些“主”API，每個都執行 250-300 次插入的某個子集？

對於 ACID，每個事務都需要寫入磁碟——這就是限制您的速度的原因。不知道您的 SQL 的詳細資訊，我只能猜測有幫助的事情：

• 批處理INSERTs到表中（單個事務中的多行）。
• 25 個執行緒中的每一個都只為數百個INSERTs. （如果涉及多個表，這很好用。） 注意：雖然它（極大地）減少了超時的機會，但它確實增加了死鎖的機會。準備好重放回滾的事務。
• 重新考慮TRIGGERs. 由於您有一個 API，它可以在單獨的查詢中進行審計，從而在建構事務時提供更大的靈活性。
• 考慮使用儲存過程來處理數據塊。（我通常更喜歡在應用程式碼中做同樣的事情，但 SP 很好。）

需要檢查的其他事項…主表和審計表中有多少行？的價值是innodb_buffer_pool_size多少？多少記憶體？

引用自：https://dba.stackexchange.com/questions/189804