Mysql
Innodb 從 MyISAM 轉換後的慢查詢
幾天前，我們將一些寫入密集型表從 MyISAM 轉換為 InnoDB，希望由於 InnoDB 更好的鎖定係統而獲得更好的性能，但是我們開始看到慢日誌上的簡單查詢，而不是獲得性能。
例如，下面的查詢需要 3.6 秒才能執行：
# Time: 130402 7:24:07 # User@Host: iron[iron] @ localhost [] # Query_time: 3.596235 Lock_time: 0.000033 Rows_sent: 0 Rows_examined: 1 SET timestamp=1364883847; UPDATE `cookies` SET `lastSelectedLanguage`="english" WHERE `cookieID`="27276286";見下表結構：
mysql> describe cookies; +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | cookieID | bigint(20) | NO | PRI | NULL | auto_increment | | containerID | int(10) unsigned | NO | MUL | NULL | | | dtCreated | datetime | NO | | NULL | | | lastSelectedLanguage | varchar(31) | YES | | NULL | | +----------------------+------------------+------+-----+---------+----------------+使用它在 0 秒內執行的相同 WHERE 子句執行選擇（不使用記憶體）
mysql> SELECT SQL_NO_CACHE * FROM `cookies` WHERE `cookieID`="27276286"; +----------+-------------+---------------------+----------------------+ | cookieID | containerID | dtCreated | lastSelectedLanguage | +----------+-------------+---------------------+----------------------+ | 27276286 | 6 | 2013-04-02 06:23:52 | english | +----------+-------------+---------------------+----------------------+ 1 row in set (0.00 sec)伺服器是一個 16 核 cpu 的：
... processor : 15 vendor_id : GenuineIntel cpu family : 6 model : 44 model name : Intel(R) Xeon(R) CPU E5620 @ 2.40GHz stepping : 2 cpu MHz : 2393.931 cache size : 12288 KB ...並有 SSD 驅動程序。
伺服器上 InnoDB 表的總大小為 1.79 GB：
mysql> SELECT (SUM(DATA_LENGTH)+SUM(INDEX_LENGTH)) /1024/1024/1024 AS Total_InnoDB_in_GB FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB'; +--------------------+ | Total_InnoDB_in_GB | +--------------------+ | 1.781707763672 | +--------------------+ 1 row in set (0.12 sec)正如你在下面看到的，我們有 2.5 GB 的 innodb_buffer_pool_size，幾乎比索引 + 數據的總和多 1GB
mysql> SHOW VARIABLES LIKE '%innodb%'; +-----------------------------------------+------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------+ | have_innodb | YES | | ignore_builtin_innodb | OFF | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 2684354560 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 536870912 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | innodb_use_legacy_cardinality_algorithm | ON | +-----------------------------------------+------------------------+ 38 rows in set (0.00 sec)我正在執行 5.1.66 版（因為 5.1 版是 debian 擠壓的非後向埠儲存庫中包含的最新版本，不幸的是更新到 5.5/5.6 不是一個選項）
mysql> SHOW VARIABLES LIKE 'version%'; +-------------------------+-----------------------+ | Variable_name | Value | +-------------------------+-----------------------+ | version | 5.1.66-0+squeeze1-log | | version_comment | (Debian) | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | +-------------------------+-----------------------+ 4 rows in set (0.00 sec)更新1：
剛剛在 16:46:02 發生了一個新事件，請參閱 14:46:00 的 iostat（2 秒間隔）
Tue Apr 2 16:46:00 IST 2013 Linux 2.6.32-5-amd64 (hemlock) 02/04/13 _x86_64_ (16 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.54 0.00 0.44 0.22 0.00 96.79 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 67.88 1667.89 1369.53 801352236 658003944 Tue Apr 2 16:46:02 IST 2013 Linux 2.6.32-5-amd64 (hemlock) 02/04/13 _x86_64_ (16 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.54 0.00 0.44 0.22 0.00 96.79 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 67.88 1667.91 1369.53 801366876 658004640 Tue Apr 2 16:46:04 IST 2013 Linux 2.6.32-5-amd64 (hemlock) 02/04/13 _x86_64_ (16 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.54 0.00 0.44 0.22 0.00 96.79 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 67.89 1667.95 1369.52 801389476 658005912 Tue Apr 2 16:46:06 IST 2013 Linux 2.6.32-5-amd64 (hemlock) 02/04/13 _x86_64_ (16 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.54 0.00 0.44 0.22 0.00 96.79 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 67.89 1667.95 1369.53 801389628 658012616 Tue Apr 2 16:46:08 IST 2013 Linux 2.6.32-5-amd64 (hemlock) 02/04/13 _x86_64_ (16 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.54 0.00 0.44 0.22 0.00 96.79 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 67.89 1667.94 1369.53 801389652 658014192 Tue Apr 2 16:46:10 IST 2013 Linux 2.6.32-5-amd64 (hemlock) 02/04/13 _x86_64_ (16 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.54 0.00 0.44 0.22 0.00 96.79 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 67.89 1667.93 1369.53 801389668 658015880還有
\s那個時期（16:46:00 16:46:02 16:46:04 16:46:06）：Tue Apr 2 16:46:00 IST 2013 -------------- mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1 Connection id: 370306 Current database: Current user: readonly@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.66-0+squeeze1-log (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 9 hours 13 min 25 sec Threads: 2 Questions: 5265901 Slow queries: 109 Opens: 9852 Flush tables: 1 Open tables: 2048 Queries per second avg: 158.587 -------------- Tue Apr 2 16:46:02 IST 2013 -------------- mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1 Connection id: 370336 Current database: Current user: readonly@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.66-0+squeeze1-log (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 9 hours 13 min 27 sec Threads: 11 Questions: 5266200 Slow queries: 109 Opens: 9853 Flush tables: 1 Open tables: 2048 Queries per second avg: 158.587 -------------- Tue Apr 2 16:46:04 IST 2013 -------------- mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1 Connection id: 370361 Current database: Current user: readonly@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.66-0+squeeze1-log (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 9 hours 13 min 29 sec Threads: 27 Questions: 5266361 Slow queries: 110 Opens: 9858 Flush tables: 1 Open tables: 2048 Queries per second avg: 158.582 -------------- Tue Apr 2 16:46:06 IST 2013 -------------- mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1 Connection id: 370402 Current database: Current user: readonly@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.66-0+squeeze1-log (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 9 hours 13 min 31 sec Threads: 2 Questions: 5267006 Slow queries: 117 Opens: 9864 Flush tables: 1 Open tables: 2048 Queries per second avg: 158.592 --------------有沒有人知道為什麼這個更新（它恰好插入和刪除）這麼慢？
我懷疑您的緩慢更新是由於您的高
innodb_max_dirty_pages_pct. 這是一篇關於 InnoDB 如何處理檢查點和臟頁刷新的非常好的文章，但我建議的要點是降低innodb_max_dirty_pages_pct到 60 或 70 看看是否有幫助。不幸的是，我懷疑您在 5.1 中執行本機 InnoDB 而不是 InnoDB 外掛。這將限制您調整檢查點的能力。