8.5.2 Optimizing InnoDB Transaction Management 優化InnoDB事務管理

8.5.2 Optimizing InnoDB Transaction Management 優化InnoDB事務管理

優化InnoDB 事務處理, 找到完美的平衡在性能開銷和伺服器負載之間。比如,

一個應用可能會遇到性能問題,如果它送出每秒數千次, 不同的性能問題如果它每2-3個小時送出一次。

預設MySQL 設定 AUTOCOMMIT=1 能利用性能局限性。在實踐中,幾個相關的DML 操作到一個事務,通過

設定AUTOCOMMIT=0 或者一個START TRANSACTION 語句,在做出改變後 執行COMMIT語句。

InnoDB 必須flush log 到磁盤在每次事務送出的時候,如果transaction 模式更改為database.

當每個改變後跟着commit(預設自動送出), 儲存設備的I/O吞吐量.

另外的,對于事務隻有單個的SELECT 語句組成,打開自動送出有助于InnoDB 來識别隻讀的事務,優化它們。

避免在insert，update或者delete大量記錄後執行復原,如果一個大的事務會放緩伺服器的性能,

復原會讓問題變得更糟,復原可能比正常的DML操作時間時間花費多幾倍。 殺死資料庫程序沒有幫助,

因為伺服器啟動會再次復原。

To minimize the chance of this issue occurring:

1.增加buffer pool的大小,讓所有的DML改變都能被cache 相比立即寫到磁盤

2.設定innodb_change_buffering=all(預設就是all) ,更新和删除操作會被buffered ，除inserts之外

考慮在大的DML操作,周期性的執行COMMIT，可能把一個單獨的delete或者update 打散成多個語句來操作較少的資料行。

一旦失控的回顧發生,增加buffer_pool ，復原變的CPU-bound and runs fast,

或者kill the server ,重新開機設定 innodb_force_recovery=3

這個問題在MySQL 5.5和更高的版本是不突出的, 因為預設的innodb_change_buffering=all,

允許更新和删除操作被cache在記憶體, 使它們更快的運作在第一個地方,復原也變得更快。

確定使用這個參數時,長時間運作事務得程序有很多的inserts，updates或者deletes.

如果你承受當crash 發生時 最新送出的事務丢失,可以設定innodb_flush_log_at_trx_commit =0.

InnoDB 會flush日志1秒一次, 盡管flush 不是強制的,或者set the value of innodb_support_xa to 0

當記錄被修改或者删除, 該記錄和相關的undo logs 不是實體的立即删除,

甚至在事務送出後。 舊的undo資料是儲存的直到事務開始的事務或者并發完成,

是以這些事務可以通路先前修改或者删除的記錄。是以,長時間運作的事務可以防止InnoDB

清楚資料,被一個不同的事務修改的。

當長事務修改或者删除記錄, 其他的事務使用READ COMMITED和 REPEATABLE READ 隔離級别

需要做更多的工作來重構老的資料(undo),如果讀取相同的行。