Innodb 更新性能

我們最近將我們的一張表切換到了 innodb，現在我們正在經歷非常緩慢的 UPDATE 執行時間。過去需要 0.010-0.030 秒的更新現在可能需要超過 70 秒。一些查詢被丟棄是因為它們無法在預設的 50 秒限制內獲得鎖（我意識到我們可以提高這個限制）。

有問題的表只有一個索引，主鍵本身是一個 mediumint 值。該表有大約 100 萬行。此上下文中的所有 UPDATE 都涉及單行。通常，該行中的 4-5 列在每個查詢中都會受到影響。

目前的 my.cnf 粘貼在下面。您是否看到任何可能特別導致 innodb 更新性能不佳的內容？

[mysqld]
set-variable=local-infile=0

datadir=/db/mysql/data
socket=/var/lib/mysql/mysql.sock
#log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


skip-locking
key_buffer = 1G
query_cache_size = 256M
thread_cache_size = 128
table_cache = 2048
max_connections = 400
query_cache_limit = 1024M
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
skip-bdb
skip-locking
skip-name-resolve

innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

更新：

innodb_log_file_size: 5242880

have_innodb: YES

“WHERE” 子句總是只查找一列，這是主鍵。

更新 - 2012 年 7 月 26 日：

我們將數據庫升級到 mysql 5.5。現在 innodb 更新非常快，在我們的特定情況下不到 0.010 秒。而且，變異數非常低。我對此的看法是：在 mysql 5.0 上應謹慎使用 innodb。

觀點#1

當您僅在 InnoDB 中按主鍵更新時，聚集索引（又名gen_clust_index）可能會被鎖定，這是一種罕見但可能的情況。

我曾經回答過一個人關於這個主題的三個文章

• 無法破譯 innodb 狀態日誌中的死鎖
• 偶爾慢查詢的原因？
• 這兩個查詢如果按順序執行會導致死鎖嗎？

請仔細閱讀這些內容。這些問題的發布者根據看到的 InnoDB Clustered Index Locking 行為找到了自己的解決方法。可悲的是，他沒有發布解決方法是什麼。

此外，當您看到查詢執行緩慢時，登錄 mysql 並執行SHOW ENGINE INNODB STATUS\G並開始在聚集索引中查找鎖。

觀點 #2

我看到你註釋掉了innodb_log_file_size。預設為 5MB。由於innodb_buffer_pool_size設置為 1G，因此 innodb_log_file_size 需要為 256M。點擊此處將 innodb_log_file_size 設置為 256M。

觀點#3

我看到您沒有使用innodb_file_per_table。您可能希望使用它來專門為具有一百萬行的一個表完成表更新。點擊此處查看如何清理 InnoDB 基礎架構以使用 innodb_file_per_table。

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