[zz]通過show status 來優(yōu)化MySQL數(shù)據(jù)庫

huixiangtao

來源：
http://lxneng.javaeye.com/blog/451985
1, 查看MySQL服務器配置信息
Java代碼

mysql> show variables;  mysql> show variables;
2, 查看MySQL服務器運行的各種狀態(tài)值
Java代碼

mysql> show global status;  mysql> show global status;
3, 慢查詢
Java代碼

mysql> show variables like '%slow%';   

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

| Variable_name    | Value |   

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

| log_slow_queries | OFF   |   

| slow_launch_time | 2     |   

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

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

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

| Variable_name       | Value |   

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

| Slow_launch_threads | 0     |   

| Slow_queries        | 279   |   

+---------------------+-------+  mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
| slow_launch_time | 2     |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 279   |
+---------------------+-------+
配置中關(guān)閉了記錄慢查詢（最好是打開，方便優(yōu)化），超過2秒即為慢查詢，一共有279條慢查詢
4, 連接數(shù)
Java代碼

mysql> show variables like 'max_connections';   

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

| Variable_name   | Value |   

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

| max_connections | 500   |   

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

  

mysql> show global status like 'max_used_connections';   

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

| Variable_name        | Value |   

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

| Max_used_connections | 498   |   

+----------------------+-------+  mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+
設(shè)置的最大連接數(shù)是500，而響應的連接數(shù)是498
max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）
5, key_buffer_size
key_buffer_size是對MyISAM表性能影響最大的一個參數(shù), 不過數(shù)據(jù)庫中多為Innodb
Java代碼

mysql> show variables like 'key_buffer_size';   

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

| Variable_name   | Value    |   

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

| key_buffer_size | 67108864 |   

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

  

mysql> show global status like 'key_read%';   

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

| Variable_name     | Value    |   

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

| Key_read_requests | 25629497 |   

| Key_reads         | 66071    |   

+-------------------+----------+  mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+
mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads         | 66071    |
+-------------------+----------+
一共有25629497個索引讀取請求，有66071個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27%
需要適當加大key_buffer_size
Java代碼

mysql> show global status like 'key_blocks_u%';   

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

| Variable_name     | Value |   

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

| Key_blocks_unused | 10285 |   

| Key_blocks_used   | 47705 |   

+-------------------+-------+  mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used   | 47705 |
+-------------------+-------+
Key_blocks_unused表示未使用的緩存簇(blocks)數(shù)，Key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）
6， 臨時表
Java代碼

mysql> show global status like 'created_tmp%';   

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

| Variable_name           | Value   |   

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

| Created_tmp_disk_tables | 4184337 |   

| Created_tmp_files       | 4124    |   

| Created_tmp_tables      | 4215028 |   

+-------------------------+---------+  mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 4184337 |
| Created_tmp_files       | 4124    |
| Created_tmp_tables      | 4215028 |
+-------------------------+---------+
每次創(chuàng)建臨時表，Created_tmp_tables增加，如果是在磁盤上創(chuàng)建臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務創(chuàng)建的臨時文件文件數(shù)：
Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 99% （理想值
Java代碼

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

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

| Variable_name       | Value     |   

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

| max_heap_table_size | 134217728 |   

| tmp_table_size      | 134217728 |   

+---------------------+-----------+  mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size      | 134217728 |
+---------------------+-----------+
需要增加tmp_table_size
7,open table 的情況
Java代碼

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

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

| Variable_name | Value |   

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

| Open_tables   | 1024  |   

| Opened_tables | 1465  |   

+---------------+-------+  mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1024  |
| Opened_tables | 1465  |
+---------------+-------+
Open_tables 表示打開表的數(shù)量，Opened_tables表示打開過的表數(shù)量，如果Opened_tables數(shù)量過大，說明配置中 table_cache(5.1.3之后這個值叫做table_open_cache)值可能太小，我們查詢一下服務器table_cache值
Java代碼

mysql> show variables like 'table_cache';   

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

| Variable_name | Value |   

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

| table_cache   | 1024  |   

+---------------+-------+  mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 1024  |
+---------------+-------+
Open_tables / Opened_tables * 100% =69% 理想值 （>= 85%）
Open_tables / table_cache * 100% = 100% 理想值 (
Java代碼

mysql> show global status like 'Thread%';   

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

| Variable_name     | Value |   

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

| Threads_cached    | 31    |   

| Threads_connected | 239   |   

| Threads_created   | 2914  |   

| Threads_running   | 4     |   

+-------------------+-------+  mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 31    |
| Threads_connected | 239   |
| Threads_created   | 2914  |
| Threads_running   | 4     |
+-------------------+-------+
如果我們在MySQL服務器配置文件中設(shè)置了thread_cache_size，當客戶端斷開之后，服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數(shù)未達上限）。Threads_created表示創(chuàng)建過的線程數(shù)，如果發(fā)現(xiàn)Threads_created值過大的話，表明 MySQL服務器一直在創(chuàng)建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢服務器 thread_cache_size配置：
Java代碼

mysql> show variables like 'thread_cache_size';   

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

| Variable_name     | Value |   

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

| thread_cache_size | 32    |   

+-------------------+-------+  mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 32    |
+-------------------+-------+
9, 查詢緩存(query cache)
Java代碼

mysql> show global status like 'qcache%';   

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

| Variable_name           | Value    |   

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

| Qcache_free_blocks      | 2226     |   

| Qcache_free_memory      | 10794944 |   

| Qcache_hits             | 5385458  |   

| Qcache_inserts          | 1806301  |   

| Qcache_lowmem_prunes    | 433101   |   

| Qcache_not_cached       | 4429464  |   

| Qcache_queries_in_cache | 7168     |   

| Qcache_total_blocks     | 16820    |   

+-------------------------+----------+  mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2226     |
| Qcache_free_memory      | 10794944 |
| Qcache_hits             | 5385458  |
| Qcache_inserts          | 1806301  |
| Qcache_lowmem_prunes    | 433101   |
| Qcache_not_cached       | 4429464  |
| Qcache_queries_in_cache | 7168     |
| Qcache_total_blocks     | 16820    |
+-------------------------+----------+
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。
Qcache_free_memory：緩存中的空閑內(nèi)存。
Qcache_hits：每次查詢在緩存中命中時就增大
Qcache_inserts：每次插入一個查詢時就增大。命中次數(shù)除以插入次數(shù)就是不中比率。
Qcache_lowmem_prunes：緩存出現(xiàn)內(nèi)存不足并且必須要進行清理以便為更多查詢提供空間的次數(shù)。這個數(shù)字最好長時間來看；如果這個數(shù)字在不斷增長，就表示可能碎片非常嚴重，或者內(nèi)存很少。（上面的          free_blocks和free_memory可以告訴您屬于哪種情況）
Qcache_not_cached：不適合進行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。
Qcache_queries_in_cache：當前緩存的查詢（和響應）的數(shù)量。
Qcache_total_blocks：緩存中塊的數(shù)量。
我們再查詢一下服務器關(guān)于query_cache的配置：
Java代碼



本文來自ChinaUnix博客，如果查看原文請點：http://blog.chinaunix.net/u3/107145/showart_2186166.html