Mysql 效能最佳化--基礎引數

dbasdk發表於2014-09-25
Mysql 效能最佳化--基礎引數

先檢視mysql基本資訊
[root@sznagios libexec]# mysql -V
mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id:          1423970
Current database:
Current user:           centreon@172.25.128.42
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.66 Source distribution
Protocol version:       10
Connection:             172.25.128.35 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 3 days 17 hours 22 min 53 sec

Threads: 39  Questions: 104521030  Slow queries: 46  Opens: 189450  Flush tables: 1  Open tables: 64  Queries per second avg: 324.828
--------------


1, 檢視資料庫當前的各種執行狀態值

mysql> show global status;
+-----------------------------------+--------------+
| Variable_name                     | Value        |
+-----------------------------------+--------------+
| Aborted_clients                   | 93           |
| Aborted_connects                  | 918009       |
| Binlog_cache_disk_use             | 0            |
| Binlog_cache_use                  | 0            |
| Bytes_received                    | 69209087317  |

2,檢視資料庫配置引數語句
mysql> show variables;
| wait_timeout                            | 28800                                                                                     |
| warning_count                           | 0                                                                                         |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
277 rows in set (0.00 sec)

檢視慢查詢  
mysql> show variables like '%slow%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+
4 rows in set (0.00 sec)

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 46    |
+---------------------+-------+
2 rows in set (0.00 sec)

系統顯示慢查詢46條。執行時間超過2秒,系統定義為慢查詢。
mysql主從結構時,在從伺服器分析慢查詢日誌,對系統效能影響小

最大連線數
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show global status like '%Max_used_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 115   |
+----------------------+-------+
1 row in set (0.00 sec)

Max_used_connections / max_connections * 100% ≈ 85%
最大連線數占上限連線數的85%左右,如果發現比例在10%以下,MySQL伺服器連線數上限設定的過高。

MyISAMysql表效能影響最大引數
mysql> show variables like '%key_buffer_size%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 417655437 |
| Key_reads         | 25985259  |
+-------------------+-----------+
2 rows in set (0.00 sec)

計算索引未命中快取的機率:
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_block_u%';
   
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 'key_block_u%';
Empty set (0.00 sec)

臨時表
mysql> show global status like '%create_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%

我們再看一下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  |
+------------------------+--------+
| Com_show_open_tables   | 0      |
| Open_tables            | 64     |
| Opened_tables          | 189374 |
| Slave_open_temp_tables | 0      |
+------------------------+--------+
4 rows in set (0.00 sec)
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%

程式使用情況
mysql> show global status like 'Thread%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Threads_cached    | 0       |
| Threads_connected | 25      |
| Threads_created   | 1426023 |
| Threads_running   | 2       |
+-------------------+---------+
4 rows in set (0.00 sec)
在MySQL伺服器配置檔案中設定了thread_cache_size,當客戶端斷開之後,伺服器處理此客戶的執行緒將會快取起來以響應下一個客戶而不是銷燬(前提是快取數未達上限)。Threads_created表示建立過的執行緒數,如果發現Threads_created值過大的話,表明MySQL伺服器一直在建立執行緒,這也是比較耗資源,可以適當增加配置檔案中thread_cache_size值
查詢伺服器thread_cache_size配置:

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     |
+-------------------+-------+
1 row in set (0.00 sec)


查詢快取
mysql> show global status like '%qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)
MySQL查詢快取變數解釋:
Qcache_free_blocks:快取中相鄰記憶體塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE會對快取中的碎片進行整理,從而得到一個空閒塊。
Qcache_free_memory:快取中的空閒記憶體。
Qcache_hits:每次查詢在快取中命中時就增大
Qcache_inserts:每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率。
Qcache_lowmem_prunes:快取出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看;如果這個數字在不斷增長,就表示可能碎片非常嚴重,或者記憶體很少。(上面的 free_blocks和free_memory可以告訴您屬於哪種情況)
Qcache_not_cached:不適合進行快取的查詢的數量,通常是由於這些查詢不是 SELECT 語句或者用了now()之類的函式。
Qcache_queries_in_cache:當前快取的查詢(和響應)的數量。
Qcache_total_blocks:快取中塊的數量。

再查詢一下伺服器關於query_cache的配置:
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)
各欄位的解釋:
query_cache_limit:超過此大小的查詢將不快取
query_cache_min_res_unit:快取塊的最小大小
query_cache_size:查詢快取大小
query_cache_type:快取型別,決定快取什麼樣的查詢,示例中表示不快取 select sql_no_cache 查詢
query_cache_wlock_invalidate:當有其他客戶端正在對MyISAM表進行寫操作時,如果查詢在query cache中,是否返回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%


排序使用情況
mysql> show global status like 'sort%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Sort_merge_passes | 7        |
| Sort_range        | 81766    |
| Sort_rows         | 19196256 |
| Sort_scan         | 645901   |
+-------------------+----------+
4 rows in set (0.00 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 並不一定能提高速度,見 How fast can you sort data with MySQL?(另外,增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處

檔案開啟數
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 33    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 25000 |
+------------------+-------+
1 row in set (0.00 sec)

比較合適的設定:Open_files / open_files_limit * 100% <= 75%

表鎖情況

mysql> show global status like 'table_locks%';
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Table_locks_immediate | 106767354 |
| Table_locks_waited    | 11783     |
+-----------------------+-----------+
2 rows in set (0.01 sec)

mysql> select 106767354/11783;
+-----------------+
| 106767354/11783 |
+-----------------+
|       9061.1350 |
+-----------------+
1 row in set (0.00 sec)
Table_locks_immediate表示立即釋放表鎖數,Table_locks_waited表示需要等待的表鎖數,如果Table_locks_immediate / Table_locks_waited > 5000,最好採用InnoDB引擎,因為InnoDB是行鎖而MyISAM是表鎖,對於高併發寫入的應用InnoDB效果會好些。

表掃描
mysql> show global status like 'handler_read%';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Handler_read_first    | 20336636     |
| Handler_read_key      | 589222889    |
| Handler_read_next     | 251390653    |
| Handler_read_prev     | 221          |
| Handler_read_rnd      | 4330823      |
| Handler_read_rnd_next | 202640993647 |
+-----------------------+--------------+
6 rows in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_select    | 16108125 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> select 202640993647/16108125;
+-----------------------+
| 202640993647/16108125 |
+-----------------------+
|            12580.0485 |
+-----------------------+
1 row in set (0.00 sec)

計算表掃描率:
表掃描率 = Handler_read_rnd_next / Com_select
表掃描率超過4000,說明進行了太多表掃描,很有可能索引沒有建好,增加read_buffer_size值會有一些好處,但最好不要超過8MB。

mysql> show variables like '%read_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)



















來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29734436/viewspace-1280676/,如需轉載,請註明出處,否則將追究法律責任。

相關文章