用status信息對MySQL服務器進行優化(一)

網上有很多的文章教怎麼配置MySQL服務器,但考慮到服務器硬件配置的不同,具體應用的差別,那些文章的做法隻能作為初步設置參考,我們需要根據自己的情況進行配置優化,好的做法是MySQL服務器穩定運行瞭一段時間後運行,根據服務器的”狀態”進行優化。


mysql> show global status;


可以列出MySQL服務器運行各種狀態值,另外,查詢MySQL服務器配置信息語句:


mysql> show variables;


一、慢查詢


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 |
+———————+——-+
配置中打開瞭記錄慢查詢,執行時間超過2秒的即為慢查詢,系統顯示有4148個慢查詢,你可以分析慢查詢日志,找出有問題的SQL語句,慢查詢時間不宜設置過長,否則意義不大,最好在5秒以內,如果你需要微秒級別的慢查詢,可以考慮給MySQL打補丁:http://www.percona.com/docs/wiki/release:start,記得找對應的版本。


打開慢查詢日志可能會對系統性能有一點點影響,如果你的MySQL是主-從結構,可以考慮打開其中一臺從服務器的慢查詢日志,這樣既可以監控慢查詢,對系統性能影響又小。


二、連接數


經常會遇見”MySQL: ERROR 1040: Too many connections”的情況,一種是訪問量確實很高,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服務器連接數上限設置的過高瞭。


三、Key_buffer_size


key_buffer_size是對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%


四、臨時表


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以下的臨時表才能全部放內存,超過的就會用到硬盤臨時表。


五、Open Table情況


mysql> show global status like open%tables%;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables   | 919   |
| Opened_tables | 1951  |
+—————+——-+
Open_tables表示打開表的數量,Opened_tables表示打開過的表數量,如果Opened_tables數量過大,說明配置中table_cache(5.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%

發佈留言