MySql優化——觀察伺服器狀态

通常在對MySQL進行優化的時候，不要一上來就去優化MySQL的語句。MySQL的優化是一個系統話的過程，我們要自上而下來對MySQL進行優化。MySQL優化的第一步是觀察伺服器狀态，找出出問題的地方在哪裡，然後才能有的放矢。

我們可以使用show status和show processlist指令來觀察MySQL的伺服器狀态。

show status傳回了伺服器很多狀态名額，但是通常我們隻需要關注以下幾項：Queries,Threads_connected,Threads_running。這三項分别代表資料庫的查詢數量，已經連接配接的線程數和正在執行的線程數。注意這些名額項都是一個累計的值，是以如果我們要計算出這些統計項的內插補點才能觀察出伺服器的狀态。為了計算出這些統計項的內插補點，我們可以有兩種方案。為了能夠使用linux的awk工具來實作資料統計，我們使用mysqladmin ext來代替MySQL指令行下面的show status指令。

第一種：

我們首先建立一個start.sh腳本檔案，腳本内容為

#!/bin/bash

while true

do

mysqladmin -uroot ext|awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt

sleep 

done
           

這個腳本的意思其實很簡單，每隔一秒查詢出MySQL的查詢數目，線程連接配接數目，線程運作數目，并将結果追加到status.txt檔案中。我們可以将這個腳本執行24個小時或者一定的時間，然後對status的内容進行分析。（關于awk，可以參考http://man.linuxde.net/awk，這裡不再贅述）。

然後使用awk統計出status檔案中的上下兩行的內插補點，使用下面的shell指令：

awk 'BEGIN{print Queries,Threads_connected,Threads_running}NR==1{last=$1;next;}{q=$1-last;last=$1}{print q,$2,$3}' status.txt
           

該指令行可以統計出MySQL伺服器的狀态內插補點，同樣使用了linux的awk工具。

然後我們可以将統計出的資料導入成為excel表格，生成圖表，就可以清楚的看出來伺服器的狀态走勢。

第二種

我們可以直接使用mysqladmin ext的參數來統計出來MySQL的伺服器狀态。通過-r和-i參數我們可以達到同樣的效果。-r可以看到各個名額的內插補點，-i可以設定重新整理頻率。

mysqladmin -uroot -r -i -pxxx ext
           

結合awk我們就可以統計出來MySQL伺服器的狀态。

mysqladmin -uroot -pxxx -r -i 1 ext |awk -F"|" '{if( ~ /Variable_name/){print "<---------"strftime("%HH:%mm:%ss")"---------->"}if( ~ /Queries|Threads_connected|Threads_running/){print ,}}' >>status.txt
           

通過觀察伺服器的連接配接狀态和查詢數量，我們可以繪制出伺服器的狀态折線圖。通常如果狀态折線圖中總是周期性的出現查詢高峰，可能就是緩存的失效時間固定在某個時間點。針對這樣的情況，我們可以增加緩存時間，每天夜裡重新整理緩存。通常情況下夜間的通路量不會太大，在夜間緩存失效不會造成查詢洪峰。等到白天查詢變多的時候，其實我們已經建立起了一部分索引。避免出現查詢高峰。

或者我們可以把緩存時間設為一個區間值，讓緩存逐漸失效，而不是在某一個點一起失效。這樣也能有效的防止查詢高峰。