工具用于檢視指定時間内show global status名額的變化,能夠幫助運維人員了解系統負載的走勢
本工具在5.5 5.6 5.7中測試通過
源碼我放在百度雲盤了
http://pan.baidu.com/s/1mhIeKp6
源碼一共5個檔案
conmysql.c main.c findv.c other.c type.h
如果源碼編譯使用如下方法
1、建立一個MYSQL使用者用于監控,不需要什麼權限隻要能夠show global status 即可
mysql> create user mmon@'localhost' identified by 'test123';
Query OK, 0 rows affected (0.30 sec)
這樣就可以了
2、找到你的MYSQL庫檔案
一般在你的安裝目錄lib 目錄下
如/mysqldata/mysql5.7/lib
export LD_LIBRARY_PATH=/mysqldata/mysql5.7/lib
3、編譯源檔案
原版
gcc conmysql.c main.c findv.c other.c -L/mysqldata/mysql5.7/lib -L/mysqldata/mysql5.7/lib -lmysqlclient -I/mysqldata/mysql5.7/include -o mmon -W -fPIC
percona
gcc conmysql.c main.c findv.c other.c -L/dbdata/mysql3307/lib -lperconaserverclient -I/dbdata/mysql3307/include -o mmon -W -fPIC
4、幫助
[root@testmy mysqlmon]# ./mmon -help
Welcome use this mon tool!
This mon tool is calc status delta!
Author:gaopeng QQ:22389860
This mon tool is calc status delta, interval time is paramter -l define default is 10 seconds
but cant's less than 3 seconds
-t define how many times defualt is 1440 times
If -10 appear means is that no values find!
Usage::./mmon -h -u -p -P/-s [-l] [-t]
-h:host
-u:username
-p:password
-P:port
-s:socket
-l:interval time of seconds
-t:how many times
-l 是時間間隔預設是10秒,指定時間最小為3秒,小于3秒會報錯。
-t 是你想采樣的此時預設是1440次,不能小于1.
5、使用
./mmon -h=localhost -u=test11 -p=gelc123 -s=/mysqldata/mysql5.7/mysqld3307.sock -l=5 -t=3
按ctrl+c 即可以退出
注意-p=lc1\$23 這裡的特殊字元用一下轉義\
輸出為:
Your input paramter is: -h,localhost
Your input paramter is: -u,mmontest
Your input paramter is: -p,Lc123
Your input paramter is: -s,/dbdata/mysql5800/mysql.sock
Your input paramter is: -l,5
Your input paramter is: -t,3
Parameter counter is: 5
Mmonitor:host is:localhost,user is:mmontest,passwd is:gelc123,socket is:/dbdata/mysql5800/mysql.sock,port is 0
Mmonitor:Connect mysql server success
Mmonitor:status rows : 402,field : 2
------------------------------------------MMON--------------------------------------------
SysTime: Mon Sep 12 12:25:28 2016
Uptime:2312561 Connections:1 Aborted_clients:0 Max_used_connections:44
--------------------------------------MYSQLINFO-------------------------------------------
Threads_connected:12 Threads_running:1 Qcache_free_memory:1031328
Qcache_hits:0 Qcache_not_cached:0 Qcache_inserts:0
Handler_read_first:0 Handler_read_key71 Handler_read_next0
Handler_read_last:0 Handler_read_prev:0 Handler_read_rnd:71
Handler_read_rnd_next:403
Sort_rows:0 Select_full_join:0 Select_scan:1
Com_commit:177 Com_rollback:0
Handler_commit:455 Handler_rollback:0
U:70 D:1 I:207 S:0 S/(U+D+I+S)(%):0.00
TIM(temp table in mem lager)(%):100.00 SOD(sort on disk small)(%):0.00
TOH(table open hint lager)(%):100.00 BOD(binglog on disk samll)(%):0.00
--------------------------------------INNODBINFO------------------------------------------
Innodb_row_lock_time:0 Innodb_row_lock_waits:0
Innodb_log_writes:181 Innodb_log_waits:0
Innodb_buffer_pool_wait_free:0 Innodb_buffer_pool_pages_misc:380
Innodb_buffer_pool_pages_flushed:84
Innodb_dblwr_pages_written:84 Innodb_dblwr_writes:8
Innodb_os_log_pending_fsyncs:0 Innodb_os_log_pending_writes:0
Innodb_os_log_written:384512
Innodb_data_reads:21 Innodb_data_read:344064
Innodb_data_writes:273 Innodb_data_written:3137024
Innodb_buffer_pool_bytes_data:32071745536
U(rows):70 D(rows):1 I(rows):273 S(rows):71
BPH(buffer cache hint)(%):100.00 BFP(free buffer pct)(%):0.42
BDP(dirty buffer pct)(%):0.68
---------------------------------------TOTALINFO------------------------------------------
Bytes_received:283146 Bytes_sent:11968
QPS:55 TPS:55 IOPS:96
---------------------------------------ENDL-----------------------------------------------
大部分保留了原有的status名字而沒有使用簡寫,目的在于大家能夠更快在文檔中找到相應的解釋
明白其中的含義。
關于名額問題,任何show global status中的都可以非常簡單加入到工具中,可以聯系我加入。
部分錯誤碼
Mmonitor(16):connect mysql server error
Mmonitor(15):mysql port is number type!" ??
Mmonitor:(14)parameters must -h -u -p -P -s [-l] "
Mmonitor:(13)parameter len must>=4"
Mmonitor(12):miss -parameter= ="
Mmonitor(11):use only -u -h -p -P -s [-l]"
Mmonitor(10):miss -parameter= -"
Mmonitor(17):data global status failed no pri?
Mmonitor(18):fetch data failed!"
Mmonitor(21): cycle is number type!"
Mmonitor(22): Mmonitor cycle must lager than 3 sec!
關于記憶體洩露:
通過工具 valgrind 檢測
==5999== HEAP SUMMARY:
==5999== in use at exit: 51,863 bytes in 718 blocks
==5999== total heap usage: 18,489 allocs, 17,771 frees, 2,250,709 bytes allocated
==5999==
==5999== LEAK SUMMARY:
==5999== definitely lost: 0 bytes in 0 blocks
==5999== indirectly lost: 0 bytes in 0 blocks
==5999== possibly lost: 34,651 bytes in 17 blocks
==5999== still reachable: 17,212 bytes in 701 blocks
==5999== suppressed: 0 bytes in 0 blocks
==5999== Rerun with --leak-check=full to see details of leaked memory
==5999== For counts of detected and suppressed errors, rerun with: -v
可以看到沒有記憶體洩露
可以寫shell腳本
是我固定非配的一點記憶體CTRL+C後釋放
如果寫腳本可以如下:
export LD_LIBRARY_PATH=/dbdata/mysql3307/lib
DATE_TAG=`date +%Y%m%d`
int=1
while(( $int<=288 ))
do
/root/mmon/mmon -h=localhost -u=mmontest -p=gelc123 -s=/dbdata/mysql5800/mysql.sock -l=60 -t=5 >>/root/mmon/mmon_${DATE_TAG}.log
let "int++"
done
代表每次抽取為1分鐘 會采樣288*5=1440 次就是 剛好一天的。
1 00 * * * /root/mmon.sh
然後這樣挂載腳本,就可以每天1分鐘采樣一次記錄下所有的資訊變化。