【分享】mysql 數(shù)據(jù)庫性能追蹤與分析

billicon

前提，由于數(shù)據(jù)庫最近性能不穩(wěn)定，經(jīng)常內(nèi)存吃緊，有爆庫的危險呀。因為我受命去調(diào)查下原因，以下為我的工作報告，拋磚引玉。在和大家分享下的同時，希望也能聽聽大家的經(jīng)驗,那我就獻丑了，呵呵。
      這是公司內(nèi)部使用的數(shù)據(jù)庫，但是受不住兩三百的連接，真的很丟臉呀，居然讓我搞得這么差勁。

１.mysql服務(wù)器訪問速度慢，查看服務(wù)器的進程異常情況。top



2.服務(wù)器讀寫正�！�iostat


3.內(nèi)存(vmstat)，硬盤讀寫(iostat)都正常，就是CPU(top)超高,系統(tǒng)CPU日志追蹤(sar),系統(tǒng)在下午
三點幾，CPU負(fù)載急劇上升。



4.由上可知，知道是mysql進程問題，連接超多呀。


５.連接mysql服務(wù)器客戶端，show processlist,可以看到大量的連接。連接分析如下
說明現(xiàn)在服務(wù)器有238個連接，其實空的連接有188個，188中又有172 個是在打醬油的。
有56個線程正在處理，不過在wait for table.


6.排除NULL,現(xiàn)在了解服務(wù)器中忙碌的數(shù)據(jù)庫，數(shù)據(jù)庫networkresourcemanager很忙呀,waiting,checking,sending.操作集中



7.我們?yōu)榉治鱿聎ait for table的線程的操作內(nèi)容。這個很重要，實際影響到服務(wù)器性能的緣由。
說明線程正在對int_networkresourcemanager的figureline表進程操作，很忙呀，瓶頸在這里(wait for)。


８.以下為figureline的表結(jié)構(gòu)情況，可以知道它是innodb存儲引擎支持的表結(jié)構(gòu)

#######################################################################
#######################################################################
#######################################################################
#######################################################################
my.cnf參數(shù)，讀取參數(shù)值需要加大一倍,read_buffer_size/read_rnd_buffer_size是讀取參數(shù)值，加大點。

說明連接失敗情況，客戶端非法中斷連接次數(shù)，我們有必要查看錯誤日志，錯誤挺多。
到時再整理提交一份錯誤日志表單。
cat show\ status.txt  | grep -i aborted
| Aborted_clients                   | 2944        |
| Aborted_connects                  | 2252        |

前面都說了系統(tǒng)卡在查詢，我們有必要啟動慢查詢?nèi)罩菊业狡款i，有必要調(diào)試。
mysql> show variables like "%slow%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /data/mysql/testmysql1-slow.log |
+---------------------+---------------------------------+

說明我們已經(jīng)他們了14598個線程，現(xiàn)在有238個線程，正在運行的為67個。和前面對應(yīng)，問題他們是查詢類的，我們沒有緩存呀。。
cat show\ status.txt  | grep  "Threads"
| Threads_cached                    | 0           |
| Threads_connected                 | 238         |
| Threads_created                   | 14598       |
| Threads_running                   | 67          |  

我們承諾有886個連接，現(xiàn)在最大用了391，說明服務(wù)器性能慢不是連接限制。
cat show\ status.txt  | grep -i connections
| Connections                       | 14599       |
| Max_used_connections              | 391         |
mysql> show variables like "%max_connection%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 886   |
+-----------------+-------+

這是表鎖情況分析，居然存在5個表鎖，要開慢查詢調(diào)優(yōu)。
cat show\ status.txt  | grep -i table_lock
| Table_locks_immediate             | 2568624     |
| Table_locks_waited                | 5           |

已經(jīng)啟動查詢緩存，但是居然沒有query_cache_size值，主要用于
查詢，居然都沒有，導(dǎo)致狀態(tài)信息都沒有查詢信息。
cat show\ status.txt  | grep -i qcache
| 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           |
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     |
+------------------------------+---------+

通過狀態(tài)發(fā)現(xiàn)磁盤和內(nèi)存對innodb的緩存根本就沒有起作用，沒數(shù)據(jù)呀。
通過變量信息，可以知道二進制緩存僅為32KB,混合類型日志，異步讀入，
每個二進制日志文件可以達到１Ｇ多。
有必要將binlog_cache_size設(shè)置為32M.
mysql> show global status like "%binlog%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Binlog_cache_disk_use  | 0     |
| Binlog_cache_use       | 0     |
| Com_binlog             | 0     |
| Com_show_binlog_events | 0     |
| Com_show_binlogs       | 2     |
+------------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like "%binlog%";
+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| binlog_cache_size              | 32768                |
| binlog_format                  | MIXED                |
| innodb_locks_unsafe_for_binlog | OFF                  |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size                | 1073741824           |
| sync_binlog                    | 0                    |
+--------------------------------+----------------------+
6 rows in set (0.00 sec)

原因：主要客戶端不停地向服務(wù)器發(fā)出查詢語句，而這些查詢語句都是對整個數(shù)據(jù)表進行遍歷處理，完成一次需要很多資源，而且數(shù)據(jù)庫居然沒有開啟緩存，使得相同的工作重復(fù)無意義的執(zhí)行，浪費系統(tǒng)資源。而服務(wù)器沒有能力處理的請求則wait for隊列中，用戶體驗極差。
最終的處理結(jié)果：
       根據(jù)我目前的了解和知識水平，我會將mysql的慢查詢?nèi)罩鹃_啟，找出相應(yīng)的語句，再進行優(yōu)化處理。
       主要是innodb類型數(shù)據(jù)庫，但是查詢緩存呀，二進制日志緩存呀，我都沒有啟動呀，通過增加緩存大小，減少對數(shù)據(jù)庫頻繁的讀操作。
       連接數(shù)足矣，不過要添加超時機制，讓超時的連接斷開，不浪費系統(tǒng)資源。
請多多指教，謝謝。

cenalulu

感謝分享，很棒的一個完整問題定位過程。
不過有幾點可以改進下：
當(dāng)問題已經(jīng)定位到CPU瓶頸時，第一件事情應(yīng)該是定位慢查詢 ，而不是增加緩存。
因為CPU消耗高，幾乎是一定可以斷定是有慢查詢。而緩存并不一定能解決所有慢查詢。
通過tcpdump + pt-query-digest 基本上就可以追蹤到問題SQL。
直接改寫SQL，優(yōu)化效果會更明顯

wenhq

不錯啊，LZ支持�。�！