怎么用status信息對(duì)MySQL服務(wù)器進(jìn)行優(yōu)化

這篇文章給大家分享的是有關(guān)怎么用status信息對(duì)MySQL服務(wù)器進(jìn)行優(yōu)化的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

網(wǎng)上有很多的文章教怎么配置MySQL服務(wù)器，但考慮到服務(wù)器硬件配置的不同，具體應(yīng)用的差別，那些文章的做法只能作為初步設(shè)置參考，我們需要根據(jù)自己的情況進(jìn)行配置優(yōu)化，好的做法是MySQL服務(wù)器穩(wěn)定運(yùn)行了一段時(shí)間后運(yùn)行，根據(jù)服務(wù)器的”狀態(tài)”進(jìn)行優(yōu)化。

> show global status;

可以列出MySQL服務(wù)器運(yùn)行各種狀態(tài)值，另外，查詢MySQL服務(wù)器配置信息語句：

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 |
+---------------------+-------+
配置中打開了記錄慢查詢，執(zhí)行時(shí)間超過2秒的即為慢查詢，系統(tǒng)顯示有4148個(gè)慢查詢，你可以分析慢查詢?nèi)罩?，找出有問題的SQL語句，慢查詢時(shí)間不宜設(shè)置過長(zhǎng)，否則意義不大，最好在5秒以內(nèi)，如果你需要微秒級(jí)別的慢查詢，可以考慮給MySQL打補(bǔ)?。海浀谜覍?duì)應(yīng)的版本。

打開慢查詢?nèi)罩究赡軙?huì)對(duì)系統(tǒng)性能有一點(diǎn)點(diǎn)影響，如果你的MySQL是主－從結(jié)構(gòu)，可以考慮打開其中一臺(tái)從服務(wù)器的慢查詢?nèi)罩荆@樣既可以監(jiān)控慢查詢，對(duì)系統(tǒng)性能影響又小。

二、連接數(shù)

經(jīng)常會(huì)遇見”MySQL: ERROR 1040: Too many connections”的情況，一種是訪問量確實(shí)很高，MySQL服務(wù)器抗不住，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過小：

mysql> show variables like max_connections;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 256   |
+-----------------+-------+
這臺(tái)MySQL服務(wù)器最大連接數(shù)是256，然后查詢一下服務(wù)器響應(yīng)的最大連接數(shù)：

mysql> show global status like Max_used_connections;
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 245   |
+----------------------+-------+
MySQL服務(wù)器過去的最大連接數(shù)是245，沒有達(dá)到服務(wù)器連接數(shù)上限256，應(yīng)該沒有出現(xiàn)1040錯(cuò)誤，比較理想的設(shè)置是：

Max_used_connections / max_connections  * 100% ≈ 85%

最大連接數(shù)占上限連接數(shù)的85％左右，如果發(fā)現(xiàn)比例在10%以下，MySQL服務(wù)器連接數(shù)上限設(shè)置的過高了。

三、Key_buffer_size

key_buffer_size是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)，下面一臺(tái)以MyISAM為主要存儲(chǔ)引擎服務(wù)器的配置：

mysql> show variables like key_buffer_size;
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+
分配了512MB內(nèi)存給key_buffer_size，我們?cè)倏匆幌耴ey_buffer_size的使用情況：

mysql> show global status like key_read%;
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_read_requests      | 27813678764 |
| Key_reads              | 6798830     |
+------------------------+-------------+
一共有27813678764個(gè)索引讀取請(qǐng)求，有6798830個(gè)請(qǐng)求在內(nèi)存中沒有找到直接從硬盤讀取索引，計(jì)算索引未命中緩存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000個(gè)索引讀取請(qǐng)求才有一個(gè)直接讀硬盤，已經(jīng)很BT了，key_cache_miss_rate在0.1%以下都很好（每1000個(gè)請(qǐng)求有一個(gè)直接讀硬盤），如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當(dāng)減少。

MySQL服務(wù)器還提供了key_blocks_*參數(shù)：

mysql> show global status like key_blocks_u%;
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_blocks_unused      | 0           |
| Key_blocks_used        | 413543      |
+------------------------+-------------+
Key_blocks_unused表示未使用的緩存簇(blocks)數(shù)，Key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)，比如這臺(tái)服務(wù)器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置：

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

四、臨時(shí)表

mysql> show global status like created_tmp%;
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197   |
| Created_tmp_files       | 58      |
| Created_tmp_tables      | 1771587 |
+-------------------------+---------+
每次創(chuàng)建臨時(shí)表，Created_tmp_tables增加，如果是在磁盤上創(chuàng)建臨時(shí)表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件文件數(shù)，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，應(yīng)該相當(dāng)好了。我們?cè)倏匆幌翸ySQL服務(wù)器對(duì)臨時(shí)表的配置：

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以下的臨時(shí)表才能全部放內(nèi)存，超過的就會(huì)用到硬盤臨時(shí)表。

五、Open Table情況

mysql> show global status like open%tables%;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 919   |
| Opened_tables | 1951  |
+---------------+-------+
Open_tables表示打開表的數(shù)量，Opened_tables表示打開過的表數(shù)量，如果Opened_tables數(shù)量過大，說明配置中table_cache(5.1.3之后這個(gè)值叫做table_open_cache)值可能太小，我們查詢一下服務(wù)器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%

感謝各位的閱讀！關(guān)于“怎么用status信息對(duì)MySQL服務(wù)器進(jìn)行優(yōu)化”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！