MYSQL性能調(diào)優(yōu)工具介紹

1、MySQL 性能調(diào)優(yōu)工具介紹2013.03.27DBA 王洪權(quán) 內(nèi)容概要內(nèi)容概要 一 mysql awr報(bào)告（簡(jiǎn)單的AWR,mysqltuner.pl,tuning-primer.sh pt-query-digest,pt-variable-advisor） 二二 性能分析工具介紹性能分析工具介紹（mytop,innotop,orzdba,tcpdump,pt-query-digest,tbdba-slow-picker.pl,iotop,io-profile,nicstat,mpstat,iostat,vmstat,tcprstat,dstat,oprofile,vmtouch） 三三 主從

2、復(fù)制延時(shí)解決（主從復(fù)制延時(shí)解決（relay-fetch 介紹） 問答問答工具化的啟發(fā) 工具化的重要性（熟練運(yùn)用已有的工具，可以快速對(duì)數(shù)據(jù)庫(kù)進(jìn)行診斷，時(shí)刻了解你的數(shù)據(jù)庫(kù)的運(yùn)行狀況）mysqltuner.pl 給mysql做個(gè)簡(jiǎn)單報(bào)告mysqltuner.pl 給mysql做個(gè)簡(jiǎn)單報(bào)告tuning-primer.sh tuning-primer.sh pt-variable-advisor 診斷你的參數(shù)設(shè)置是否合理pt-qury-digest給mysql做個(gè)簡(jiǎn)單SQL報(bào)告mytop 查看你的mysql在做什么mytop 查看你的mysql在做什么innotop 綜合了解你的mysql（mytop加

3、強(qiáng)版）innotop -m Qinnotop 綜合了解你的mysql（mytop加強(qiáng)版）orzdba 時(shí)刻了解你的數(shù)據(jù)庫(kù)orzdba 時(shí)刻了解你的數(shù)據(jù)庫(kù)tcpdump抓包tcpdump + pt-query-digest抓包捕捉瞬時(shí)sqltcpdump + pt-query-digest抓包捕捉瞬時(shí)sqltcpdump + pt-query-digest抓包捕捉瞬時(shí)sql./dba-slow-picker.pl（診斷異常時(shí)刻SQL）找尋哪個(gè)進(jìn)程在磨你的磁盤 iotop ioprofile 查看IO情況的利器ioprofile 查看IO情況的利器ioprofile 查看IO情況的利器ioprof

4、ile 查看IO情況的利器ioprofile 透過進(jìn)程發(fā)現(xiàn)寫入狀況ioprofile 產(chǎn)看IO情況的利器ioprofile 產(chǎn)看IO情況的利器Tcprstat(通過響應(yīng)時(shí)間判斷數(shù)據(jù)庫(kù)運(yùn)行狀況)每個(gè)請(qǐng)求時(shí)間在0.9ms25.934ms網(wǎng)絡(luò)利器nicstat(充分了解你的網(wǎng)卡運(yùn)行狀況) wKB/s,OutKB #每秒寫的千字節(jié)數(shù)每秒寫的千字節(jié)數(shù)(transmitted) rMbps,RdMbps #每秒讀的百萬字節(jié)數(shù)每秒讀的百萬字節(jié)數(shù)K(received) %Util #接口的利用率百分比 Sat #每秒的錯(cuò)誤數(shù)，接口接近飽和的一個(gè)指標(biāo)dstat 充分了解你服務(wù)器的狀況（vmstat）加強(qiáng)版roo

5、tbbs # dstat -tclmdny 1 mpstat 定位你的cpuQPS在在1.1W到到1.5W之間波動(dòng)之間波動(dòng)mpstat 定位你的cpu但通過但通過tcprstat觀察到，響應(yīng)時(shí)間不是非常穩(wěn)定，會(huì)從觀察到，響應(yīng)時(shí)間不是非常穩(wěn)定，會(huì)從0.3ms波動(dòng)到波動(dòng)到1.9msmpstat 定位你的cpu軟中斷taskset -p 03 700 pid為為700的進(jìn)程綁定到第四顆的進(jìn)程綁定到第四顆CPU上面上面vmtouch (is cool tools) 一 你是不是發(fā)現(xiàn)你的cache 在瘋狂吃掉，明明很大的內(nèi)存cache了很多，然而卻很少free，很可能你備份的數(shù)據(jù)被cache住了，如何把

6、這部分內(nèi)存從cache中釋放出來，那么萬能工具vmtouch出現(xiàn)了。 二 試圖從用戶空間管理OS 的Paging Space 三 還支持把具體的文件，目錄cache到pagecache中，加速訪問速度vmtouch (is cool tools)vmtouch (is cool tools)vmtouch (is cool tools)vmtouch (is cool tools)oprofile 介紹 = oprofile 是什么 = oprofile也是一個(gè)開源的profiling工具，它使用硬件調(diào)試寄存器來統(tǒng)計(jì)信息，進(jìn)行profiling的開銷比較小，而且可以對(duì)內(nèi)核進(jìn)行profiling

7、。 Oprofile 是一個(gè)全局的抽樣統(tǒng)計(jì)工具 Oprofile是一種細(xì)粒度的工具，可以為指令集或者為函數(shù)、系統(tǒng)調(diào)用或中斷處理例程收集采樣。Oprofile 通過取樣來工作。使用收集到的評(píng)測(cè)數(shù)據(jù)，用戶可以很容易地找出性能問題。oprofile 使用場(chǎng)景 cpu無端占用高？應(yīng)用程序響應(yīng)慢？苦于沒有分析的工具？ oprofile利用cpu硬件層面提供的性能計(jì)數(shù)器(performance counter)，通過計(jì)數(shù)采樣，幫助我們從進(jìn)程、函數(shù)、代碼層面找出占用cpu的罪魁禍?zhǔn)?。Oprofile系統(tǒng)工作流圖oprofile使用# 加載oprofile內(nèi)核模塊opcontrol -init #我們對(duì)內(nèi)核的

8、取樣沒興趣opcontrol -setup -no-vmlinux#在開始收集采樣數(shù)據(jù)前回顧下我們的設(shè)置opcontrol -status#清除上一次采樣到的數(shù)據(jù)opcontrol -reset #啟動(dòng)oprofiled守護(hù)程序,從內(nèi)核中拉出采樣數(shù)據(jù) opcontrol -start #運(yùn)行我們的程序 #收集采樣數(shù)據(jù)opcontrol -dump#關(guān)閉守護(hù)程序, 同時(shí)準(zhǔn)備好采樣的數(shù)據(jù)opcontrol -shutdownoprofile獲取采樣信息 #系統(tǒng)級(jí)別的opreport -long-filenames#模塊級(jí)別的opreport image:foo -l#源碼級(jí)別的opannotate

9、 image:foo -soprofile使用opcontrol -deinitmodprobe oprofile timer=1$dmesg|grep oprofile|tail -n 1(oprofile: using timer interrupt.)opcontrol -resetpcontrol -separate=lib -no-vmlinux-start -image=/home/mysql_user/mysqlhome/bin/mysqldopcontrol -dumpopcontrol -shutdownopreport -l /home/mysql_user/mysqlho

10、me/bin/mysqldoprofile 診斷你的cpu異常relay-fetch 加速主從同步 relayfetch是一個(gè)開源的mysql備庫(kù)預(yù)熱工具，通過解析本地relay log文件，在SQL線程執(zhí)行到相應(yīng)的位置之前預(yù)先將其需要的數(shù)據(jù)塊加載到內(nèi)存中，達(dá)到為備庫(kù)slave線程進(jìn)行預(yù)熱的目的。在測(cè)試中，relayfetch為備庫(kù)SQL復(fù)制線程帶來了2-3倍的復(fù)制速度提升。目前已經(jīng)在淘寶的一些備庫(kù)延遲較大的線上備庫(kù)上部署，效果非常明顯。 目前relayfetch主要支持ROW模式，被預(yù)熱的表需要有主鍵或唯一鍵。 relay-fetch 工作原理圖relayfetch使用多線程，包括1個(gè)rea

11、der線程和N個(gè)worker線程 relay-fetch 工作流程1.reader線程的執(zhí)行流程 1). 讀取本地的relay log文件，獲取日志中的事件； 2). 將row數(shù)據(jù)解析出來，獲得其中的Primary key值； 3). 根據(jù)Primary key轉(zhuǎn)換成對(duì)應(yīng)庫(kù)表的select語句，依次分發(fā)給N個(gè)隊(duì)列（N可配置，也表示worker線程數(shù)）； 4). 每從relay log中讀取count個(gè)事件（5000<count<10000，動(dòng)態(tài)調(diào)整）后，會(huì)做如下判斷： a)當(dāng)前備庫(kù)延時(shí)secounds_behind_master低于設(shè)定值(使用a指定，默認(rèn)為1秒)時(shí)

12、，進(jìn)入sleep； b)當(dāng)前reader線程讀relay log的位置超過SQL線程執(zhí)行Position大于設(shè)定值時(shí)（使用-s指定，默認(rèn)為1M），進(jìn)入sleep 5). 當(dāng)讀到文件尾時(shí)（rotate事件），等待sql線程; 6). goto 1)。 relay-fetch 測(cè)試 1. 測(cè)試環(huán)境 Mysql版本：5.1.48； b) 內(nèi)存8G；CPU：4*2493.749 c) 操作系統(tǒng)：Red Hat Enterprise Linux Server release 5.4； d) 使用sysbench,2000W數(shù)據(jù)。文件大小4.7G； 2. 測(cè)試方案 測(cè)試relayfetch在不同buffer pool size/data size比例值下，對(duì)sql線程執(zhí)行速度有多大的提升 正常情況 a) stop slave sql_thread b) 運(yùn)行sysbench(max-requests:1W、2W、4W、6W、8W、10W、12W) c) 完成sysbench后start slave sql_thread d