我一直在優化我的 MySQL 伺服器，在調整了一些查詢之後，我最終在 pt-query-digest 輸出頂部看到了 COMMIT 項，如下面的摘錄所示：

# 322s user time, 770ms system time, 71.75M rss, 223.13M vsz
# Current date: Sat Oct  1 11:12:58 2016
# Hostname: XXXX
# Files: /home/tools/Slow10.log
# Overall: 1.08M total, 1.47k unique, 61.74 QPS, 0.17x concurrency _______
# Time range: 2016-10-01 06:15:09 to 11:06:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          2913s     1us     14s     3ms    15ms    27ms   194us
# Lock time           123s       0      2s   113us   167us     3ms    42us
# Rows sent        1019.81k       0   8.58k    0.97    0.99   26.59       0
# Rows examine     314.86M       0   2.13M  305.82  234.30   7.13k       0
# Query size       321.03M       6  14.64k  311.81   1.14k  743.10   88.31

# Profile
# Rank Query ID           Response time   Calls  R/Call V/M   Item
# ==== ================== =============== ====== ====== ===== ============
#    1 0x813031B8BBC3B329 1676.7355 57.6%  70006 0.0240  0.02 COMMIT
#    2 0xBC10C51A724ECD57   61.8928  2.1%    211 0.2933  0.08 SELECT users

查詢顯示為：

# Query 1: 4.00 QPS, 0.10x concurrency, ID 0x813031B8BBC3B329 at byte 462955973
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2016-10-01 06:15:09 to 11:06:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   70006
# Exec time     57   1677s     9us      2s    24ms    56ms    24ms    16ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0 410.19k       6       6       6       6       0       6
# String:
# Databases    XXX_XX... (9419/13%)... 40 more
# Hosts        localhost
# Query_time distribution
#   1us  #
#  10us  #
# 100us  #
#   1ms  ####
#  10ms  ################################################################
# 100ms  #
#    1s  #
#  10s+
commit\G

關於如何優化它的任何消息？

如果需要更多資訊，請告訴我！

提前致謝！

編輯 2016-02-10：任何查詢優化之前的第一次分析如下：

# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================ ======= ====== ===== ==========
# 1 0x62057CDB69C17D23 18377.2125 46.3% 139328 0.1319 0.15 SELECT AAAA
# 2 0xEC77079791A0E274 6992.0443 17.6% 127446 0.0549 0.09 SELECT BBBB
# 3 0xB8A934DECC36D811 6283.1217 15.8% 247595 0.0254 0.03 UPDATE users_sessions
# 4 0x813031B8BBC3B329 2042.6094 5.1% 89046 0.0229 0.04 COMMIT

伺服器有 8Gb RAM，innodb_buffer_pool_size=5G大約有 70 個數據庫，每個數據庫有 140 個表。

編輯 2016-10-06：

# grep innodb /etc/my.cnf
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=5G
innodb_log_buffer_size=4M

取證…

• 您的中位時間COMMITs：16 毫秒。
• 旋轉磁碟寫入的典型時間：10 毫秒。
• 預設設置innodb_flush_log_at_trx_commit：1，表示每次送出時刷新到磁碟。

結論：兇手使用圖書館的燭台。或者..您正在做的大部分事情都是非常快速地送出事務。

可能的加速方法：

• innodb_flush_log_at_trx_commit = 2– 這每秒刷新一次，而不是每個事務一次。這不太“安全”，但對於某些使用者來說，權衡是值得的。
• 批量操作。而不是一個一個INSERT，COMMIT做很多INSERTs。主要成本COMMIT是將一次寫入刷新到回滾日誌，無論事務中進行了多少。
• 硬體——SSD——將 10 毫秒降低到 1 毫秒（或類似的時間）；具有電池支持的寫入記憶體的 RAID - 使寫入幾乎需要 0 毫秒，即使不損失安全性。
• 由於有幾個COMMITs佔用了 1 秒以上，您可以尋找它們，看看它們的事務是否可以改進。

簡而言之

用於SHOW ENGINE INNODB STATUS獲取更多資訊。

長版

這意味著你COMMIT要花很多時間。有幾個問題可能會導致它：

• CPU 限製或 I/O 限制。

在這種情況下，你可以找出哪個事務慢，然後進行優化。

• InnoDB 鎖。

在這種情況下，您可以找出獲取了什麼樣的鎖，以及可能的死鎖（InnoDB 可能無法檢測到），然後對其進行優化。

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