MySqL線上status做出适當優化

 優化的一些指令：

mysql> show global staus;   //這個是顯示所有的狀态的指令；

1、慢查詢

mysql> show variables like '%slow%';

+---------------------+-----------------------------------+

| Variable_name       | Value                             |

| log_slow_queries    | ON                                |

| slow_launch_time    | 2                                 |

| slow_query_log      | ON                                |

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

4 rows in set (0.00 sec)

顯示出慢查詢限制時間為2秒，慢查詢日志檔案所在的目錄

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name       | Value |

| Slow_launch_threads | 0     |

| Slow_queries        | 38    |

2 rows in set (0.00 sec)

打開慢查詢日志可能會對系統有一點點的影響，如果你的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 | 800   |

1 row in set (0.00 sec)

這台MySQL伺服器的最大連接配接數是800，然後再查詢一下該伺服器響應的最大連數：

mysql> show global status like 'Max_used_connections';

+----------------------+-------+

| Variable_name        | Value |

| Max_used_connections | 88    |

MySQL伺服器的最大連接配接數是88，沒有達到伺服器連接配接數的上限800，應該不會出現1040錯誤。比較理想的設定是：

Max_used_connections / max_connections * 100% 這個數值在85%左右

最大連接配接數占上限連接配接數和85%左右，如果發現比例在10%以下，則說明MySQL伺服器連接配接數的上限設定的過高了。

3、key_buffer_size

key_buffer_size是設定MyISAM表緩存空間的大小，此參數對MyISAM表性能影響最大。

mysql> show variables like 'key_buffer_size';

+-----------------+-----------+

| Variable_name   | Value     |

| key_buffer_size | 268435456 |

從上面的配置可以看出，配置設定了256MB記憶體給key_buffer_size.下面再來看一下它的使用情況：

mysql> show global status like 'key_read%';

+-------------------+-------+

| Variable_name     | Value |

| Key_read_requests | 15319 |

| Key_reads         | 3     |

一共有15319個索引讀取請求，有3個請求在記憶體中沒找到，直接從硬碟讀取索引，計算索引未命中緩存的的機率：

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%

比如上面的資料，Key_cache_miss_rate 為0.01958%，這個未命中的機率很小，效果上已經很好了，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 | 6      |

| Key_blocks_used   | 231957 |

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 | 135      |

| Created_tmp_files       | 5        |

| Created_tmp_tables      | 37526111 |

3 rows in set (0.00 sec)

每次建立臨時表時，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% = 0.00035%，應該說是相當好了。我們再看一下MySQL伺服器對臨時表的配置：

mysql> show variables where Variable_name in ('tmp_table_size','max_heap_table_size');

+---------------------+-----------+

| Variable_name       | Value     |

| max_heap_table_size | 67108864  |

| tmp_table_size      | 268435456 |

隻有64MB以下的臨時表才能放在記憶體中，超過的就會用到硬碟臨時表。

5、打開表的情況

Open_tables 表示打開表的數量，Open_tables表示打開過的表數量，我們可以用如下指令檢視其具體情況：

mysql> show global status like 'open%tables%';

+---------------+-------+

| Variable_name | Value |

| Open_tables   | 646   |

| Opened_tables | 653   |

如果Opened_tables數量過大，說明配置中table_cache（MySQL5.1.3之後這個值叫做table_open_cache)的值可能太小。我們查詢一下伺服器table_cache值：

mysql> show variables like 'table_open_cache';

| table_cache   | 1024  |

比較合适的值為：

Open_tables / Opened_tables * 100% >=85%

Open_tables / table_cache * 100% <=95%

mysql> show variables like 'table_open_cache';

+------------------+-------+

| Variable_name    | Value |

| table_open_cache | 1024  |

6 程序使用情況

如果我們在MySQL伺服器的配置檔案中設定了thread_cache_size，當用戶端斷開之時，伺服器處理此客戶請求的線程将會緩存起來以響應下一個客戶而不是銷毀（前提是緩存未達到上限）。Threads_created表示建立過的線程數，我們可以用如下指令檢視：

mysql> show global status like 'Thread%';

| Threads_cached    | 59    |

| Threads_connected | 44    |

| Threads_created   | 138   |

| Threads_running   | 1     |

如果發現Threads_created的值過大的話，表明MySQL伺服器一直在建立線程，這也是比較耗費資源的，可以适當的增大配置檔案中thread_cache_size的值。查詢伺服器thread_cache_size配置，如下所示：

mysql> show variables like 'thread_cache_size';

| thread_cache_size | 64    |

示例中的MySQL伺服器還是挺健康的。

如果運用指令：mysql> show full processlist;

顯示出大量的sending data 而且時間很長那就有可能是資料庫一直在建立程序，此時要增大thread_cache_size的值。

7、查詢緩存（query cache)

它主要涉及兩個參數，query_cache_size是設定MySQL的Query Cache大小，query_cache_type是設定使用查詢緩存的類型，我們可以用如下指令檢視其具體情況：

mysql>show global status like 'qcache%';

| Qcache_free_blocks      | 452      |

| Qcache_free_memory      | 83214448 |

| Qcache_hits             | 52902869 |

| Qcache_inserts          | 1856039  |

| Qcache_lowmem_prunes    | 305804   |

| Qcache_not_cached       | 42944    |

| Qcache_queries_in_cache | 80812    |

| Qcache_total_blocks     | 162634   |

8 rows in set (0.01 sec)

MySQL查詢緩存變量的相關解釋如下。

Qcache_free_blocks：緩存中相鄰記憶體塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE 會對緩存中的碎片進行處理，進而得到一個空閑塊。

Qcache_free_memory：緩存中的空閑記憶體。

Qcache_hits：多少次命中。通過這個參數可以檢視到Query Cache的基本效果。

Qcache_inserts:插入次數，每次插入一個查詢時就增加1.命中次數除以插入次數就是命中比率。

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            | 3145728   |

| query_cache_min_res_unit     | 4096      |

| query_cache_size             | 268435456 |

| query_cache_type             | ON        |

| query_cache_wlock_invalidate | OFF       |

5 rows in set (0.00 sec)

各字段的解釋如下：

query_cache_limit:超過此大小的查詢将不緩存。

query_cache_min_res_unit:緩存塊的最小值。

query_cache_type:緩存類型，決定緩存什麼樣的查詢，示例中表示不緩存select sql_no_cache查詢

query_cache_wlock_invalidate:表示當有其他用戶端正在對MyISAM表示進行寫操作時，讀請求是要等 WRITE LOCK釋放資源後再查詢還是允許直接從Query Cache中讀取結果，預設為OFF（可以直接從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%

示例伺服器中的查詢緩存碎片率等于0.2779%，查詢緩存使用率等于61.5%，查詢緩存命中率等于96.49%，說明命中率還是挺高的，而且碎片很少。

8、排序使用情況

它表示系統中對資料進行排序時所使用的Buffer，我們可以用如下指令檢視：

mysql> show global status like 'sort%';

+-------------------+----------+

| Variable_name     | Value    |

| Sort_merge_passes | 23       |

| Sort_range        | 35536    |

| Sort_rows         | 19732031 |

| Sort_scan         | 46755    |

4 rows in set (0.06 sec)

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';

| Open_files    | 25    |

mysql> show variables like 'open_files_limit';

| open_files_limit | 8192  |

比較合适的配置：Open_files / open_files_limit * 100% <= 75%。

很多時候我們會發現，通過參數設定進行性能優化所帶來的性能提升，并不如許多人的想象的那樣會産生質的飛躍，除非是之前的設定存在嚴重不合理的情況。我們不能将性能調優完全依托于通過DBA在資料庫上線後進行參數調整，而應該在系統設計和開發階段就盡可能減少性能問題。

10、Innodb_buffer_pool_size的合理設定

InnoDB存儲引擎的緩存機制和MyISAM的最大差別就在于，InnoDB不僅僅緩存索引，同時還會緩存實際的資料。此參數用來設定InnoDB最主要的buffer（InnoDB buffer pool)的大小，也就是緩存使用者表及索引資料的最主要緩存空間，對InnoDB整體性能影響也最大。

無論是MySQL官方手冊還是網絡上許多人分享的InnoDB優化建議，都是簡單地建議将此值設定為整個系統實體記憶體的50%~80%。這麼做法其實不妥，我們應要根據實際的運作場景來正确設定此項參數。以我的生産資料庫（表的引擎有InnoDB和MyISAM兩種）為例，實體伺服器總記憶體為8GB，配置Innodb_buffer_pool_size=2048MB,網站穩定上線後，通過以下指令觀察：

mysql> show status like 'Innodb_buffer_pool_%';

+-----------------------------------+------------+

| Variable_name                     | Value      |

| Innodb_buffer_pool_pages_data     | 63285      |

| Innodb_buffer_pool_pages_dirty    | 3          |

| Innodb_buffer_pool_pages_flushed  | 37081      |

| Innodb_buffer_pool_pages_free     | 0          |

| Innodb_buffer_pool_pages_misc     | 2251       |

| Innodb_buffer_pool_pages_total    | 65536      |

| Innodb_buffer_pool_read_ahead_rnd | 19214      |

| Innodb_buffer_pool_read_ahead_seq | 16193      |

| Innodb_buffer_pool_read_requests  | 3274048071 |

| Innodb_buffer_pool_reads          | 562959     |

| Innodb_buffer_pool_wait_free      | 0          |

| Innodb_buffer_pool_write_requests | 1159654    |

12 rows in set (0.00 sec)

通過此指令得出的結果可以計算出InnoDB buffer pool的read命中率大約為：

（3274048071 - 63285） / 3274048071 = 99.99%

write命中率大約為：

63285 / 65536 * 100% = 96.56%

我們發現這個值設定得過小，後期考慮将其增加到3072MB左右，另外需要注意的是，32位系統因為系統方面的制約，此值隻能設定為2.2GB~2.7GB，是以建議大家的資料庫系統為64位。

另外，等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資料庫進行測試。最後根據不同顔色的辨別來提醒使用者需要注意的各個參數設定。該版本目前相容MySQL3.23和更高的版本（包含5.1），但是尚不支援MySQL5.5版本。

目前版本會處理如下這些推薦的參數：

Slow Query Log （慢查詢日志）

Max Connections （最大連接配接數）

Worker Threads （工作線程）

Key Buffer （Key 緩沖）

Query Cache （查詢緩存）

Sort Buffer (排序緩存）

Joins （連接配接）

Temp Tables（臨時表）

Table （Open &amp；Definition）Cache（表緩存）

Table Locking（表鎖定）

Tables Scans(read_buffer)(表掃描，讀緩沖）

InnoDB Status（InnoDB 狀态）

整個mysql的簡單優化就這樣，這些參數設定隻是作為參考，實際需要還要看自己的伺服器。

還有sql查詢語句的優化應該放在重中之重。

本文轉自 ZhouLS 51CTO部落格，原文連結:http://blog.51cto.com/zhou123/1155375