6.1.3 MySQL上線后根據status狀態進行適當優化
MySQL數據庫上線后，可以等其穩定運行一段時間后再根據服務器的“status”狀態進行適當優化，我們可以用如下命令列出MySQL服務器運行的各種狀態值：
mysql> show global status;
筆者個人較喜歡的用法是show status like ’查詢值%'。
1. 慢查詢
有時為了定位系統中效率比較低下的Query語名，需要打開慢查詢日志，也就是Slow
Query Log，查詢慢查詢日志的相關命令如下：
mysql> show variables like '%slow%';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+---------------------+---------+
mysql> show global status like '%slow%';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+---------------------+---------+
打開慢查詢日志可能會對系統性能有一點影響，如果你的MySQL是主-從結構，可以考慮打開其中一臺從服務器的慢查詢日志，這樣既可以監控慢查詢，對系統性能影響也會很小。另外，可用MySQL自帶的命令mysqldumpslow進行查詢，比如，下面的命令可以查出訪問次數最多的20個sql語句：
mysqldumpslow -s c -t 20 host-slow.log
2. 連接數
如果經常遇見“MySQL: ERROR 1040: Too manyconnections”的問題，一種情況是訪問量確實很高，MySQL服務器抗不住，這個時候需要考慮增加從服務器分散讀壓力；另外一種情況是MySQL配置文件中max_connections的值過小。舉例說明：
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
這臺MySQL服務器的最大連接數是256，然后查詢一下該服務器響應的最大連接數：
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+
MySQL服務器過去的最大連接數是245，沒有達到服務器連接數的上限256，應該不會出現1040錯誤，比較理想的設置是：
Max_used_connections / max_connections ＊ 100% ≈ 85%
最大連接數占上限連接數的85%左右，如果發現比例在10%以下，則說明MySQL服務器連接數的上限設置過高。
3. Key_buffer_size
key_buffer_size是設置MyISAM表索引擎緩存空間的大小，此參數對MyISAM表性能影響最大，下面是一臺以MyISAM為主要存儲引擎服務器的配置：
mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 536870912 |
+-----------------+-----------+
從上面的配置可以看出，系統分配了512MB內存給key_buffer_size，我們再查看一下key_buffer_size的使用情況：
mysql> show global status like 'key_read%';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+---------------------+-------------+
一共有27813678764個索引讀取請求，有6798830個請求在內存中沒有找到，直接從硬盤讀取索引，計算索引未命中緩存的概率：key_cache_miss_rate=Key_reads/Key_read_requests * 100%。
比如上面的數據，key_cache_miss_rate為0.0244%,4000個索引讀取請求才有一個直接讀硬盤，已經很BT了，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬盤），如果key_cache_miss_rate在0.01%以下，說明key_buffer_size分配過多，可以適當減少。
MySQL服務器還提供了key_blocks_*參數，如下所示：
mysql> show global status like 'key_blocks_u%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+--------------------+----------+
Key_blocks_unused表示未使用的緩存簇（blocks）數，Key_blocks_used表示曾經用到的最大的blocks數，比如這臺服務器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引，把緩存占滿了。比較理想的設置是：Key_blocks_used/（Key_blocks_unused + Key_blocks_used)*100%≈80%。
4. 臨時表
當執行語句時，關于已經被創造的隱含臨時表的數量，我們可以用如下命令查知其具體情況：
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+-------------------------+---------+
每次創建臨時表時，Created_tmp_tables都會增加，如果是在磁盤上創建臨時表，Created_tmp_disk_tables也會增加。Created_tmp_files表示MySQL服務創建的臨時文件數，比較理想的配置是：Created_tmp_disk_tables/Created_tmp_tables*100%<=25%。
比如上面的服務器Created_tmp_disk_tables/Created_tmp_tables*100%=1.20%，應該說已經相當不錯了。我們再看一下MySQL服務器對臨時表的配置：
mysql> show variables where Variable_name in（'tmp_table_size', 'max_heap_table_
size');
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+---------------------+-----------+
只有256MB以下的臨時表才能全部放在內存中，超過的就會放到硬盤臨時表。
5. Open Table的情況
Open_tables表示打開表的數量，Opened_tables表示打開過的表數量，可以用如下命令查看其具體情況：
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
+---------------+-------+
如果Opened_tables數量過大，說明配置中table_cache（MySQL5.1.3之后這個值叫做table_open_cache）的值可能太小，我們查詢一下服務器table_cache值：
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+
比較合適的值為：
Open_tables / Opened_tables ＊ 100% >= 85%
Open_tables / table_cache ＊ 100% <= 95%
6. 進程使用情況
如果我們在MySQL服務器的配置文件中設置了thread_cache_size，當客戶端斷開之時，服務器處理此客戶請求的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數未達上限）。Threads_created表示創建過的線程數，可以用如下命令查看：
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+
如果發現Threads_created的值過大，表明MySQL服務器一直在創建線程，這也是比較耗費資源的，可以適當增大配置文件中thread_cache_size的值，查詢服務器thread_cache_size配置，如下所示：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
示例中的MySQL服務器還是比較健康的。
7. 查詢緩存（query cache）
它涉及的主要有兩個參數，qrery_cache_size和query_cache_type。其中query_cache_size設置MySQL的Query Cache大小，query_cache_type設置使用查詢緩存的類型，可以用如下命令查看其具體情況：
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+-------------------------+-----------+
MySQL查詢緩存變量的相關解釋如下所示：
? Qcache_free_blocks：緩存中相鄰內存塊的個數，數目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。
? Qcache_free_memory：緩存中的空閑內存。
? Qcache_hits：表示有多少次命中。通過這個參數可以查看到Query Cache的基本效果。
? Qcache_inserts：每插入一個查詢時就會增大。命中次數除以插入次數就是不中比率。
? Qcache_lowmem_prunes：表示有多少條Query因為內存不足而被清除出Query Cache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結合，能夠更清楚地了解系統中Query Cache的內存大小是否真的足夠，是否頻繁出現因為內存不足而有Query被換出的情況。
? Qcache_not_cached：不適合進行緩存的查詢數量，通常是由于這些查詢不是SELECT語句或用了now()之類的函數。
? Qcache_queries_in_cache：當前緩存的查詢（和響應）數量。
? Qcache_total_blocks：緩存中塊的數量。
我們再查詢一下服務器上關于query_cache的配置：
mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
各字段的解釋如下所示：
? query_cache_limit：超過此大小的查詢將不緩存。
? query_cache_min_res_unit：緩存塊的最小值。
? query_cache_size：查詢緩存大小。
? query_cache_type：緩存類型，決定緩存什么樣的查詢，示例中表示不緩存select sql_no_cache查詢。
? query_cache_wlock_invalidate：表示當有其他客戶端正在對MyISAM表進行寫操作時，讀請求是要等WRITE LOCK釋放資源后再查詢還是允許直接從Query Cache中讀取結果，默認為FALSE（可以直接從Query Cache中取得結果）。
query_cache_min_res_unit的配置是一柄“雙刃劍”，默認是4KB，設置值大對大數據查詢有好處，但如果都是小數據查詢，就容易造成內存碎片和浪費。
查詢緩存碎片率=Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者如果你的查詢都是小數據量，嘗試減小query_cache_min_res_unit。
查詢緩存利用率=（query_cache_size-Qcache_free_memory）/query_cache_size*100%
查詢緩存利用率在25%以下說明query_cache_size設置過大，可適當減小；查詢緩存利用率在80%以上且Qcache_lowmem_prunes＞50則說明query_cache_size可能過小，或是碎片太多。
查詢緩存命中率=（Qcache_hits-Qcache_inserts）/Qcache_hits*100%
示例服務器中的查詢緩存碎片率等于20.46%，查詢緩存利用率等于62.26%，查詢緩存命中率等于1.94%，說明命中率很差，可能寫操作比較頻繁，而且可能存在碎片。
8. 排序使用情況
表示系統中對數據進行排序時使用的Buffer，我們可以用如下命令查看：
mysql> show global status like 'sort%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Sort_merge_passes | 29 |
| Sort_range | 37432840 |
| Sort_rows | 9178691532 |
| Sort_scan | 1860569 |
+-------------------+------------+
Sort_merge_passes包括如下步驟：MySQL首先會嘗試在內存中排序，使用的內存大小由系統變量sort_buffer_size決定，如果它不夠大，則把所有的記錄都讀到內存中，而MySQL則會把每次在內存中排序的結果存到臨時文件中，等MySQL找到所有記錄之后，再把臨時文件中的記錄做一次排序，這次再排序就會增加sort_merge_passes。實際上，MySQL會用另一個臨時文件來存儲再次排序的結果，所以我們通常會看到sort_merge_passes增加的數值是創建臨時文件數的兩倍。因為用到了臨時文件，所以速度可能會較慢，增大sort_buffer_size會減少sort_merge_passes和創建臨時文件的次數，但盲目地增大sort_buffer_size并不一定能提高速度。
9. 文件打開數（open_files）
我們在處理MySQL故障時發現，當open_files大于open_files_limit值時，MySQL數據庫就會發生卡住的現象，導致Apache服務器也打不開相應頁面，大家在工作中要注意這個問題，我們可以用如下命令查看其具體情況：
mysql> show global status like 'open_files';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Open_files | 1410 |
+------------------+-------+
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+
比較合適的設置是：Open_files/open_files_limit*100%<=75%。
10. Innodb_buffer_pool_size的合理設置
InnoDB存儲引擎的緩存機制和MyISAM的最大區別在于，InnoDB不僅僅緩存索引，同時還會緩存實際的數據。此參數用于設置InnoDB最主要的buffer（InnoDB buffer pool）大小，也就是用戶表及索引數據的最主要緩存空間，對InnoDB整體性能的影響也最大。
無論是MySQL官方手冊還是網絡上許多人分享的InnoDB優化建議，都是建議簡單地將此值設置為整個系統物理內存的50%～80%之間。這種做法其實是不妥的，我們應根據實際的運行場景來正確設置此項參數。以筆者的生產數據庫（因為歷史遺留問題，表引擎有InnoDB和MyISAM兩種）為例，物理服務器總內存為8 GB，配置Innodb_buffer_pool_size為2048 MB，網站穩定上線后，通過以下命令觀察：
mysql> show status like 'Innodb_buffer_pool_%';
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Innodb_buffer_pool_pages_data | 118505 |
| Innodb_buffer_pool_pages_dirty | 30 |
| Innodb_buffer_pool_pages_flushed | 4061659|
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 12567 |
| Innodb_buffer_pool_pages_total | 131072 |
| Innodb_buffer_pool_read_ahead_rnd | 18293 |
| Innodb_buffer_pool_read_ahead_seq | 19019 |
| Innodb_buffer_pool_read_requests | 3533588224 |
| Innodb_buffer_pool_reads | 1138442 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 58802802 |
+-----------------------------------+------------+
12 rows in set (0.00 sec)
通過此命令得出的結果可以計算出InnoDB buffer pool的read命中率大約為：（3533588224-1138442）/3533588224=99.96%。
write命令中率大約為：118505/131072=90.41%。
我們發現這個值設置過小，后期考慮將其增加到4GB左右（這個值的設定具體要求也要根據服務器的物理內存而定）。
注意
32位CentOS因為系統方面的制約，此值最大也只能配置為2.7GB左右，所以建議大家的數據庫系統選擇為64位的系統。
6.1.4 利用tuning-primer腳本進行數據庫調優
在工作中，等MySQL在線上穩定運行一段時間后，可以調用MySQL調優腳本tuning-primer.sh來檢查我們的參數是否合理，它的下載地址為：http://launchpad.net/mysql-tuning-primer/trunk/1.5-r5/+download/tuning-primer.sh。
該腳本使用“SHOW STATUS LIKE…”和“SHOW VARIABLES LIKE…”命令獲得MySQL的相關變量和運行狀態。然后根據推薦的調優參數對當前的MySQL數據庫進行測試。最后根據不同顏色的標識來提醒用戶需要注意的各個參數設置。
當前版本會處理如下這些推薦的參數：
? Slow Query Log（慢查詢日志）
? Max Connections（最大連接數）
? Worker Threads（工作線程）
? Key Buffer（Key緩沖）
? Query Cache（查詢緩存）
? Sort Buffer（排序緩存）
? Joins（連接）
? Temp Tables（臨時表）
? Table（Open & Definition）Cache（表緩存）
? Table Locking（表鎖定）
? Table Scans（read_buffer）（表掃描，讀緩沖）
? InnoDB Status（InnoDB狀態）
筆者用tuning-primer.sh腳本掃描新接手的一臺MySQL數據庫服務器后發現還是有很多問題的，比如：
1）MySQL有時連接非常慢，嚴重時會被拖死。
通過Show processlist發現大量的unauthenticated user連接，數據庫肯定每次都要響應，所以速度越來越慢。解決方法其實很簡單，在mysql.cnf里添加skip-name-resolve即可，也就是不啟用DNS反應解析。
發生這種情況的原因也很簡單，MySQL的認證實際上是user+host的形式（也就是說user可以相同），所以MySQL在處理新連接時會嘗試解析客戶端連接的IP，啟用參數skip-name-resolve后MySQL授權的時候就只能用純IP的形式了。
2）數據庫在繁忙期間負載很大，長期達到了13，遠遠超過了系統平均負載4。
通過腳本掃描，發現沒有建立thread_cache_size，所以加上thread_cache_size=256后，重啟數據庫，數據庫的平均負載一下子降到了5～6。
3）數據庫中有張new_cheat_id表被讀取頻繁，而且長期處于Sending data狀態。
初步懷疑為磁盤I/O壓力過大所致，所以操作如下：
explain SELECT count(new_cheat_id) FROM new_cheat WHERE account_id = '14348612'
AND offer_id = '689'\G;
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
id: 1
select_type: SIMPLE
table: new_cheat
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2529529
Extra: Using where
1 row in set (0.00 sec)
這個問題很嚴重，后來跟研發團隊確認，此表忘記建立索引了，導致每次都是全表掃描2529529行記錄，嚴重消耗服務器的I/O資源，所以立即建好索引，并用show index命令查看表索引：
mysql> show index from new_cheat;
+-------------+------------+------------+-------------+--------------+----------
+-------------+------------+-----------+--------------+-------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation|
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------+-------------+--------------+----------
+-------------+------------+-----------+--------------+-------------+---------+
| new_cheat | 0 | PRIMARY | 1 | new_cheat_id | A |
2577704 | NULL | NULL | | BTREE | |
| new_cheat | 1 | ip | 1 | ip | A |
1288852 | NULL | NULL | | BTREE | |
| new_cheat | 1 | account_id | 1 | account_id | A |
1288852 | NULL | NULL | | BTREE | |
+-------------+------------+------------+-------------+--------------+----------
+-------------+------------+-----------+--------------+-------------+---------+
3 rows in set (0.01 sec)
我們再看explain結果：
mysql> explain SELECT count(new_cheat_id) FROM new_cheat WHERE account_id =
'14348612' AND offer_id = '689'\G;
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
id: 1
select_type: SIMPLE
table: new_cheat
type: ref
possible_keys: account_id
key: account_id
key_len: 4
ref: const
rows: 6
Extra: Using where
1 row in set (0.00 sec)
大家可以發現，建立索引后，此SQL通過account_id索引直接讀取了6條記錄就獲得了查詢結果，系統負載由5～6直接降到了3.07～3.66。
附上我們的電子訂單系統MySQL數據庫（服務器為DELL R710,16G內存，RAID 10，表引擎為MyISAM）調整后myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
long_query_time = 2
slow_query_log
slow_query_log_file = /data/mysql/slow.log
skip-name-resolve
skip-locking
skip-networking
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
Remove the next comment character if you are not familiar with SQL
safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
