如何使用自己開發(fā)的MYMON工具監(jiān)控MYSQL運行狀態(tài)

這篇文章主要介紹如何使用自己開發(fā)的MYMON工具監(jiān)控MYSQL運行狀態(tài)，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

一、此工具展示方式： 
    工具將show global status中一些重要的值按照用戶輸入的描述和次數(shù)不斷的打印到前臺或者打印到
指定文件中，用于幫助DBA進(jìn)行性能診斷或者瓶頸判斷。
二、此工具運行原理：
   將show global status的值根據(jù)用戶輸入的秒數(shù)記錄，前一次的值為oldvalue,新的值為newvalue，存儲
在內(nèi)存中，然后判斷是否為累計值還是當(dāng)前值，如果是累計值則newvalue-oldvalue，如果為當(dāng)前值則
輸出，這樣則保證了輸出的正確性
三、工具特點：
1、監(jiān)控賬號低權(quán)限只需要useage權(quán)限
2、遠(yuǎn)程連接功能
3、根據(jù)用戶需要將數(shù)據(jù)寫入到指定文件
4、運行日志記錄，檢測出錯輸入日志
5、5.6 5.7支持、沒有測試5.5
6、核心源碼開源
7、可以根據(jù)需求隨時定制輸出，當(dāng)然需要聯(lián)系本人

注意：
WINDOWS不支持，編譯版本只有LINUX 64BIT.
并且此工具依賴MYSQL連接動態(tài)庫文件


四、、工具使用幫助
[root@testmy 6]# ./mymon 
Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com 
Info:This tool collect data from 'show global status' and show interval (-t) seconds data change
help DBA to diagnose performance problem,when error or (-d) run log is at current dir name 
is mymon.log.
WIN32 platform is not support
Useage:./mymon [-u username] [-p password] -S socketfile/{-h hosname -P port} -t seconds -n times [-l logfile] [-d]
[-?]           :help info;
[-u username]  :connect to mysqld username,when no username requisite is optional;
[-p password]  :connect to mysqld password,when no password requisite is optional;
[-S socketfile]:connect to mysqld socket file;
[-h hostname]  :connect to mysqld host;
[-P port]      :connect to mysqld port;
                socketfile or [hostname port]  must;
[-t seconds]   :seconds of interval,this is necessary;
[-n times]     :how many times you watch,this is necessary;
[-l logfile]   :when you want to write information to a logfile provide a logfile name
                when a logfile provide no information at foreground,is optional;
[-d]           :more running log at logfile,otherwise error log in mymon.log;


   注意本地連接用-S連接指定socketfile即可，遠(yuǎn)程使用 -h -P連接 使用IP和端口連接 ,-l 可以直接把輸出寫入到指定的文件，
-d就是debug模式一般不用本工具會記錄一個日志在當(dāng)前目錄下名字為：mymon.log，這是運行日志如果有錯誤會記錄到日志，
開啟-d 會計入更多的信息，但是一般不需要用，主要是我調(diào)試代碼的時候用的.當(dāng)然-t 和 -n就像幫助說的是間隔的秒數(shù)和
持續(xù)的次數(shù)，使用中如果輸出有值為-1則代表值沒有采集到.
   一般只需要在線上一臺服務(wù)器上放上本工具，使用遠(yuǎn)程連接連接到如何服務(wù)器即可，如：
   ./mymon -h 192.168.190.91 -P3306 -t 5 -n 100 -ummon2 -pgelc123 -l /root/mysqlog/20170123mysql.log
   表示連接到服務(wù)器192.168.190.91 3306端口 5秒輸出一次 輸出100次，輸出保留到文件/root/mysqlog/20170123mysql.log
   但是注意SysTime: Sun Jan 22 16:13:30 2017 輸出是運行程序系統(tǒng)的當(dāng)前時間，但是Uptime當(dāng)然就是MYSQLD服務(wù)器的時間


四、如果獲得和使用
獲取：
我一共編譯好了4個版本
5.7 社區(qū)版本      mymon5.7c
5.7 percona版本  mymon5.7p
5.6 社區(qū)版本       mymon5.6c
5.6 perconna版本 mymon5.6p
百度云盤如下：
http://pan.baidu.com/s/1jIa6OfG


如果要使用更老的版本，請聯(lián)系我。


使用：
下載后上傳到 LINUX操作系統(tǒng),
mkdir mon
cp mymon5.7p ./mon/
chmod 755 mymon5.7p
然后必須找到相應(yīng)的庫文件位置一般在安裝位置的lib目錄下，如：
[root@testmy 9]# ./mymon5.7c
./mymon5.7c: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
[root@testmy 9]# export LD_LIBRARY_PATH=/mysqldata/mysql5.7/lib
[root@testmy 9]# ./mymon5.7c
Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com 
就可以正常運行，順便說一下5.6為 so.18 5.7為so.20跟老的為5.6以前版本


關(guān)于MYSQL監(jiān)控用戶權(quán)限：監(jiān)控部需要什么權(quán)限，只要建立一個用戶即可
mysql> create user moni@'%' identified by 'gelc123';
Query OK, 0 rows affected (0.22 sec)
mysql> show grants for  moni@'%' ;
+----------------------------------+
| Grants for moni@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'moni'@'%' |
+----------------------------------+
1 row in set (0.06 sec)


這也最小化了顧慮，因為這個賬戶基本沒有什么權(quán)限，如果不放心程序，可以將程序放到遠(yuǎn)程連接到線上數(shù)據(jù)庫如：
./mymon5.6c -umoni -pgelc123 -h292.168.190.93 -P3307 -n 1 -t 1 -l /tmp/mysqlmon.log


這樣這個程序既不會有權(quán)限而且也不在線上數(shù)據(jù)庫服務(wù)器，而且可以集中管理，但是一點注意：
SysTime: Sun Jan 22 16:13:30 2017 輸出是運行程序系統(tǒng)的當(dāng)前時間，但是Uptime當(dāng)然就是MYSQLD服務(wù)器的時間




五、此工具展示內(nèi)容：
展示的內(nèi)容并不是固定，后期根據(jù)需求可以進(jìn)行簡單的加入指標(biāo)即可，當(dāng)前輸出為如下：
------------------------------------------MYMON--------------------------------------------
SysTime: Sun Jan 22 16:13:30 2017
Uptime:6963226    Connections:1       Aborted_clients:0       Max_used_connections:1319    
--------------------------------------MYSQLINFO-------------------------------------------
[###Threads info and qc  and :]
Threads_connected:252         Threads_running:4             Qcache_free_memory:0            
Qcache_hits:0                 Qcache_not_cached:0           Qcache_inserts:0                
[###Table read info:]
Handler_read_first:211        Handler_read_key:8804         Handler_read_next:42346         
Handler_read_last:0           Handler_read_rnd:73           Handler_read_rnd_next:717694
[###Sorts and temp tables info:]
Sort_rows:0                   Sort_merge_passes:0
Created_tmp_tables:163        Created_tmp_disk_tables:152
[###Table join info:]
Select_full_join:1            Select_scan:207
[###Open table cache and binlog cache info:]
Table_open_cache_hits:4938    Table_open_cache_misses:0
Binlog_cache_use:820          Binlog_cache_disk_use:0
[###Hanler_xxx and Com_xxx info:]
Handler_delete:34574          Handler_update:675            Handler_write:1364
Handler_commit:5833           Handler_rollback:10
Com_commit:156                Com_rollback:5
U(counts):631     D(counts):159     I(counts):47      S(counts):4955    
--------------------------------------INNODBINFO------------------------------------------
[###Innodb_lock info:]
Innodb_row_lock_time:0                Innodb_row_lock_waits:0
Innodb_row_lock_current_waits:38
[###Innodb_buffer_pool info:]
Innodb_buf_pool_pages_total:1965840   Innodb_buf_pool_pages_dirty:19154
Innodb_buf_pages_data:1940433         Innodb_buf_pages_free:8116
Innodb_buffer_pool_wait_free:0        Innodb_buffer_pool_pages_misc:17291
Innodb_dblwr_pages_written:1188       Innodb_dblwr_writes:80
Innodb_buffer_pool_pages_flushed:2249
read_ahead:0          ahead_evict:0          ahead_rnd:0   
[###Innodb_log info:]
Innodb_log_writes:796                 Innodb_log_waits:0
Innodb_os_log_pending_fsyncs:0        Innodb_os_log_pending_writes:0
Innodb_os_log_written:44073472
[###Innodb_data read/writes info:]
Innodb_data_reads:684                 Innodb_data_read:11206656
Innodb_data_writes:2067               Innodb_data_written:63603200
Innodb_data_fsyncs:1017               Innodb_data_pending_fsyncs:0
Innodb_buffer_pool_reads(physics reads):423
Innodb_buffer_pool_read_requests(logic reads):2869823
[###Innodb_data rows info:]
U(rows):675     D(rows):34575   I(rows):194     S(rows):767094  
---------------------------------------TOTALINFO------------------------------------------
Bytes_received:911280         Bytes_sent:8653956


六、監(jiān)控值說明：
(本部分，帶有作者自己的理解，如果有誤請指出)


文中
當(dāng)前值輸出：保持show global status的當(dāng)前值輸出
累計值相減輸出：用上一次的值減去下一次的值輸出，間隔為用戶指定的秒數(shù)


第一部分：
SysTime: Sun Jan 22 16:13:30 2017
Uptime:6963226    Connections:1       Aborted_clients:0       Max_used_connections:1319    


Uptime:The number of seconds the MySQL server has been running.(當(dāng)前值輸出 單位秒)
Connections:The number of connection attempts (successful or not) to the MySQL server.(累計值相減輸出 單位次數(shù))
Aborted_clients:The number of connections that were aborted because the client died without closing the connection
                properly.(累計值相減輸出，單位連接數(shù))
Max_used_connections:The maximum number of connections that have been in use simultaneously since the server started.
                     (當(dāng)前值輸出，單位連接數(shù))
第二部分：
[###Threads info and qc  and :]
Threads_connected:252         Threads_running:4             Qcache_free_memory:0            
Qcache_hits:0                 Qcache_not_cached:0           Qcache_inserts:0        


Threads_connected：The number of currently open connections.(當(dāng)前值輸出 單位連接數(shù))
Threads_running：The number of threads that are not sleeping.(當(dāng)前值輸出 單位連接數(shù))
Qcache_free_memory:The amount of free memory for the query cache.(當(dāng)前值輸出 單位字節(jié)數(shù))
Qcache_hits:The number of query cache hits.(累計值相減輸出 單位次數(shù))
Qcache_not_cached:The number of noncached queries.(累計值相減輸出 單位次數(shù))
Qcache_inserts:The number of queries added to the query cache.(累計值相減輸出 單位次數(shù))


第三部分：
本部分全部是(累計值相減輸出 單位次數(shù))
[###Table read info:]
Handler_read_first:211        Handler_read_key:8804         Handler_read_next:42346         
Handler_read_last:0           Handler_read_rnd:73           Handler_read_rnd_next:717694


Handler_read_first:
         /*索引中第一條記錄被讀的次數(shù)。如果較高，它表明服務(wù)器正執(zhí)行大量全索引掃描；例如，SELECT * order by id，假定id列有索引。
         * eg:
         * +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
         * | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
         * +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
         * |  1 | SIMPLE      | testj | NULL       | index | NULL          | PRIMARY | 276     | NULL |   19 |   100.00 | NULL  |
         * +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
         */
Handler_read_key:
         /*根據(jù)索引讀一行的請求數(shù)。如果較高，說明查詢和表的索引正確。本值可能是ref或者const也可能是range，只是代表索引KEY命中使用了索引
         * eg:
         * mysql> explain select * from testshared3 where id>1;
         * +----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
         * | id | select_type | table       | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
         * +----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
         * |  1 | SIMPLE      | testshared3 | NULL       | range | id            | id   | 5       | NULL |    1 |   100.00 | Using index condition |
         * +----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
         */
Handler_read_next:
         /*按照索引順序讀下一行的請求數(shù),如果索引不唯一或者使用唯一索引的前綴或者范圍都會觸發(fā)這個值,簡單的說只有唯一命中不觸發(fā)這個值
         * 這個值如果比較大說明需要連續(xù)訪問下一個key的次數(shù)非常多
         *
         *eg:
         *mysql> explain select * from testshared3 where id=1;
         *+----+-------------+-------------+------------+------+---------------+------+---------+-------+--------+----------+-------+
         *| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref   | rows   | filtered | Extra |
         *+----+-------------+-------------+------------+------+---------------+------+---------+-------+--------+----------+-------+
         *|  1 | SIMPLE      | testshared3 | NULL       | ref  | id            | id   | 5       | const | 523260 |   100.00 | NULL  |
         *+----+-------------+-------------+------------+------+---------------+------+---------+-------+--------+----------+-------+
         *
         *| Handler_read_key      | 49      |
         *| Handler_read_next     | 897698  |
         */
Handler_read_last:
        /*
         * 查詢讀索引最后一個索引鍵的請求數(shù)。當(dāng)使用ORDER BY時，服務(wù)器優(yōu)先發(fā)出使用第一個索引的請求，之后順序往后掃描索引。當(dāng)使用ORDER BY DESC時，
         * 服務(wù)器優(yōu)先發(fā)出使用最后一個索引的請求， 之后向前掃描索引。
         * 例如：select … order by id desc limit 10;
         */


Handler_read_rnd,Handler_read_rnd_next:這兩個值較大則可能遇到了全表掃描及type=ALL


這一部分則可以大體掌握當(dāng)前數(shù)據(jù)庫全表掃描和索引使用的大概情況  
第四部分：
[###Sorts and temp tables info:]
Sort_rows:0                   Sort_merge_passes:0
Created_tmp_tables:163        Created_tmp_disk_tables:152


Sort_rows:The number of sorted rows.(累計值相減輸出 單位行數(shù))
Sort_merge_passes:The number of merge passes that the sort algorithm has had to do.(累計值相減輸出 單位次數(shù))
Created_tmp_tables:The number of internal temporary tables created by the server while executing statements.(累計值相減輸出 單位次數(shù))
Created_tmp_disk_tables:The number of internal on-disk temporary tables created by the server while executing statements.(累計值相減輸出 單位次數(shù))


很明顯這一部分說明了排序和臨時表使用情況，可以幫助DBA進(jìn)行判斷


第五部分：
[###Table join info:]
Select_full_join:1            Select_scan:207

Select_full_join:The number of joins that perform table scans because they do not use indexes. 
                 If this value is not 0, you should carefully check the indexes of your tables
                 (累計值相減輸出 單位次數(shù))
Select_scan:The number of joins that did a full scan of the first table. (累計值相減輸出 單位次數(shù))

這兩個值則說明了jion的時候是否使用到了索引，特別是Select_full_join，應(yīng)該比較低，否則說明被驅(qū)動表沒有使用到索引
應(yīng)該引起重視，優(yōu)化語句，一般來講Select_scan比較高是正常的因為驅(qū)動表如果沒有謂詞條件使用全表掃描是正常的方式


第六部分：
[###Open table cache and binlog cache info:]
Table_open_cache_hits:4938    Table_open_cache_misses:0
Binlog_cache_use:820          Binlog_cache_disk_use:0


Table_open_cache_hits:The number of hits for open tables cache lookups.(累計值相減輸出 單位次數(shù))
Table_open_cache_misses:The number of misses for open tables cache lookups. (累計值相減輸出 單位次數(shù))
Binlog_cache_use: The number of transactions that used the binary log cache. (累計值相減輸出 單位事物數(shù))
Binlog_cache_disk_use:The number of transactions that used the temporary binary log cache but that exceeded the value 
                                 of binlog_cache_size and used a temporary file to store statements from the transaction.(累計值相減輸出 單位事物數(shù))
明顯這部分說明了binlog_cache_size和 table_open_cache是否合適


第七部分：
本部分全部是(累計值相減輸出 單位次數(shù))
[###Hanler_xxx and Com_xxx info:]
Handler_delete:34574          Handler_update:675            Handler_write:1364
Handler_commit:5833           Handler_rollback:10
Com_commit:156                Com_rollback:5
U(counts):631     D(counts):159     I(counts):47      S(counts):4955    

U(counts):631     D(counts):159     I(counts):47      S(counts):4955  
這一行是Com_update\Com_delete\Com_insert\Com_select的簡寫

Handler_xxx:這一批值說明的是相應(yīng)操作處理的次數(shù)
Com_xxx:這一批值說明是相應(yīng)操作對應(yīng)語句發(fā)出的次數(shù)

比如Handler_commit 和 Com_commit這不同，如果是隱含提交和自動提交不用發(fā)起commit，但是
commit是處理了的，那么Handler_commit增加而Com_rollback不變，又比如手動執(zhí)行一個commit
Com_commit增加Handler_commit不變，因為雖然發(fā)起了commit命令并不需要處理實驗如下：


mysql> show status like '%commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Com_commit     | 1     |
| Com_xa_commit  | 0     |
| Handler_commit | 4     |
+----------------+-------+
3 rows in set (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> show status like '%commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Com_commit     | 2     |
| Com_xa_commit  | 0     |
| Handler_commit | 4     |
+----------------+-------+
3 rows in set (0.00 sec)


第八部分：
[###Innodb_lock info:]
Innodb_row_lock_time:0                Innodb_row_lock_waits:0
Innodb_row_lock_current_waits:38


Innodb_row_lock_time:The total time spent in acquiring row locks for InnoDBtables, in milliseconds.(累計值相減輸出 單位毫秒)
Innodb_row_lock_waits:The number of times operations on InnoDB tables had to wait for a row lock.(累計值相減輸出 單位次數(shù))
Innodb_row_lock_current_waits:The number of row lockscurrently being waited for by operations on InnoDB tables.(當(dāng)前值輸出 單位個數(shù))


明顯這一部分是對innodb 鎖的狀態(tài)的監(jiān)控


第九部分：


[###Innodb_buffer_pool info:]
Innodb_buf_pool_pages_total:1965840   Innodb_buf_pool_pages_dirty:19154
Innodb_buf_pages_data:1940433         Innodb_buf_pages_free:8116
Innodb_buffer_pool_wait_free:0        Innodb_buffer_pool_pages_misc:17291
Innodb_dblwr_pages_written:1188       Innodb_dblwr_writes:80
Innodb_buffer_pool_pages_flushed:2249
read_ahead:0          ahead_evict:0          ahead_rnd:0       


Innodb_buf_pool_pages_total:及Innodb_buffer_pool_pages_data The total size of the InnoDB buffer pool, in pages.(當(dāng)前值輸出 單位頁)
Innodb_buf_pool_pages_dirty:及Innodb_buffer_pool_pages_dirty The current number of dirty pagesin the InnoDB buffer pool.(當(dāng)前值輸出 單位頁)
Innodb_buf_pages_data:及Innodb_buffer_pool_pages_data The number of pagesin the InnoDB buffer poolcontaining data. The number includes both dirtyand
                      clean pages.(當(dāng)前值輸出 單位頁)
Innodb_buf_pages_free:及Innodb_buffer_pool_pages_free The number of free pagesin the InnoDB buffer pool.(當(dāng)前值輸出 單位頁)
Innodb_buffer_pool_wait_free:Normally, writes to the InnoDB buffer poolhappen in the background. When InnoDBneeds to read or
                             create a pageand no clean pages are available, InnoDBflushes some dirty pagesfirst and waits for that
                             operation to finish. This counter counts instances of these waits.(累計值相減輸出 單位次數(shù))


Innodb_buffer_pool_pages_misc:The number of pages in the InnoDB buffer pool that are busy because they have
                              been allocated for administrative overhead, such as row locks or the adaptive hash
                              index. This value can also be calculated as Innodb_buffer_pool_pages_total ?
                              Innodb_buffer_pool_pages_free ? Innodb_buffer_pool_pages_data.(當(dāng)前值輸出 單位頁)


Innodb_dblwr_pages_written:The number of pages that have been written to the doublewrite buffer.(累計值相減輸出 單位頁)
Innodb_dblwr_writes:The number of doublewrite operations that have been performed. (累計值相減輸出 單位次數(shù))
Innodb_buffer_pool_pages_flushed:The number of requests to flush pages from the InnoDB buffer pool.(累計值相減輸出 單位次數(shù))


這部分是innodb_buffer_pool的重點包含了臟頁,數(shù)據(jù)頁,空閑頁等信息,以及刷磁盤的次數(shù)


第十部分：
[###Innodb_log info:]
Innodb_log_writes:796                 Innodb_log_waits:0
Innodb_os_log_pending_fsyncs:0        Innodb_os_log_pending_writes:0
Innodb_os_log_written:44073472




Innodb_log_writes:The number of physical writes to the InnoDB redo logfile.(累計值相減輸出 單位次數(shù))
Innodb_log_waits:The number of times that the log buffer was too small and a wait was required for it to be flushed before
                               continuing.(累計值相減輸出 單位次數(shù))
Innodb_os_log_pending_fsyncs:The number of pending fsync() operations for the InnoDB redo logfiles.(累計值相減輸出 單位次數(shù))
Innodb_os_log_pending_writes:The number of pending writes to the InnoDB redo logfiles.(累計值相減輸出 單位次數(shù))
Innodb_os_log_written:The number of bytes written to the InnoDB redo logfiles.(累計值相減輸出 單位字節(jié))


這部分說明了 innodb redo 寫的信息，如寫了多少數(shù)據(jù)，多少次等待，如果Innodb_os_log_pending_writes和Innodb_os_log_pending_fsyncs
不為0則說明遭遇了I/O等待。而Innodb_log_waits不為0則考慮innodb_log_buffer_size 是否過小。
注意：不管innodb_flush_method設(shè)置如何 redo始終使用kernel buffer。


第十一部分：
[###Innodb_data read/writes info:]
Innodb_data_reads:684                 Innodb_data_read:11206656
Innodb_data_writes:2067               Innodb_data_written:63603200
Innodb_data_fsyncs:1017               Innodb_data_pending_fsyncs:0
Innodb_buffer_pool_reads(physics reads):423
Innodb_buffer_pool_read_requests(logic reads):2869823


Innodb_data_reads:The total number of data reads.(累計值相減輸出 單位次數(shù))
Innodb_data_read:The amount of data read since the server was started(累計值相減輸出 單位字節(jié)數(shù))
Innodb_data_writes:The total number of data writes.(累計值相減輸出 單位次數(shù))
Innodb_data_written:The amount of data written so far, in bytes.(累計值相減輸出 單位字節(jié)數(shù))
Innodb_data_fsyncs:The number of fsync()operations so far. The frequency of fsync()calls is influenced by the setting
                            of the innodb_flush_method configuration option.(累計值相減輸出 單位次數(shù))
Innodb_data_pending_fsyncs:The current number of pending fsync()operations. The frequency of fsync()calls is influenced by
                           the setting of the innodb_flush_method configuration option.(累計值相減輸出 單位次數(shù))
Innodb_buffer_pool_reads(physics reads):The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read 
                                                        directly from disk.(累計值相減輸出 單位次數(shù))
Innodb_buffer_pool_read_requests(logic reads):The number of logical read requests.(累計值相減輸出 單位次數(shù))
read_ahead:及Innodb_buffer_pool_read_ahead:The number of pages read into the InnoDB buffer pool by the read-ahead background thread.(累計值相減輸出 單位頁)
ahead_evict:及Innodb_buffer_pool_read_ahead_evicted:The number of pagesread into the InnoDB buffer poolby the read-aheadbackground thread that were
                                                                                subsequently evictedwithout having been accessed by queries.(累計值相減輸出 單位頁)
ahead_rnd:及Innodb_buffer_pool_read_ahead_rnd:The number of “random”read-aheads initiated by InnoDB. This happens when a query scans a large
                                                                        portion of a table but in random order.(累計值相減輸出 單位次數(shù))


這一部分可以看出邏輯讀取的命中率，預(yù)讀效率等，注意Innodb_data_pending_fsyncs和Innodb_data_fsyncs如果開啟了O_DIRECT,那么innodb_buffer的內(nèi)存
數(shù)據(jù)不會出現(xiàn)在kernel buffer，那么fsyncs pending的情況會少得多。如果這兩個值較大注意磁盤I/O


第十二部分：
本部分全部是(累計值相減輸出 單位行數(shù))
U(rows):675     D(rows):34575   I(rows):194     S(rows):767094  


U:Innodb_rows_updated
D:Innodb_rows_deleted
I:Innodb_rows_inserted
S:Innodb_rows_read


這部分真正的反應(yīng)了DML和SELECT的行數(shù)，注意和Com_xxx和Handler_xxx的區(qū)別


第十三部分：
Bytes_received:911280         Bytes_sent:8653956


沒什么好說的自己查文檔


七、內(nèi)存溢出檢測
    使用：
valgrind --tool=memcheck --leak-check=full  ./mymon5.7c -S/mysqldata/mysql5.7sla/mysqld3307.sock -t 1 -n 10
    
==5120== LEAK SUMMARY:
==5120==      definitely lost: 0 bytes in 0 blocks
==5120==      indirectly lost: 0 bytes in 0 blocks
==5120==      possibly lost: 232 bytes in 2 blocks
==5120==      still reachable: 8,176 bytes in 2 blocks
==5120==      suppressed: 0 bytes in 0 blocks
==5120== Reachable blocks (those to which a pointer was found) are not shown.
==5120== To see them, rerun with: --leak-check=full --show-reachable=yes
==5120== 
==5120== For counts of detected and suppressed errors, rerun with: -v
==5120== ERROR SUMMARY: 2 errors from 2 contexts (suppressed: 6 from 6)
可以看到?jīng)]有內(nèi)存溢出


八、核心源碼

點擊(此處)折疊或打開

1. main主調(diào)函數(shù)：
   

2. /*************************************************************************
   

3.   > File Name: main.c
   

4.   > Author: gaopeng QQ:22389860 all right reserved
   

5.   > Mail: gaopp_200217@163.com
   

6.   > Created Time: Wed 08 Feb 2017 08:01:58 AM CST
   

7.  ************************************************************************/
   

8. 
   

9. #include"type.h"
   

10. 
    

11. int useage(void)
    

12. {
    

13.         printf(
    

14.             "Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com \n"
    

15.                         "Info:This tool collect data from 'show global status' and show interval (-t) seconds data change\n"
    

16.                         "help DBA to diagnose performance problem,when error or (-d) run log is at current dir name \n"
    

17.                         "is mymon.log.\n"
    

18.                         "WIN32 platform is not support\n"
    

19.                         "Useage:./mymon [-u username] [-p password] -S socketfile/{-h hosname -P port} -t seconds -n times [-l logfile] [-d]\n"
    

20.                         "[-?]           :help info;\n"
    

21.                         "[-u username]  :connect to mysqld username,when no username requisite is optional;\n"
    

22.                         "[-p password]  :connect to mysqld password,when no password requisite is optional;\n"
    

23.                         "[-S socketfile]:connect to mysqld socket file;\n"
    

24.                         "[-h hostname]  :connect to mysqld host;\n"
    

25.                         "[-P port]      :connect to mysqld port;\n"
    

26.                         "                socketfile or [hostname port]  must;\n"
    

27.                         "[-t seconds]   :seconds of interval,this is necessary;\n"
    

28.                         "[-n times]     :how many times you watch,this is necessary;\n"
    

29.                         "[-l logfile]   :when you want to write information to a logfile provide a logfile name\n"
    

30.                         "                when a logfile provide no information at foreground,is optional;\n"
    

31.                         "[-d]           :more running log at logfile,otherwise error log in mymon.log;\n"
    

32.                 );
    

33.         return 0;
    

34. }
    

35. 
    

36. 
    

37. int wel(void)
    

38. {
    

39.         printf(
    

40.             "welcome used this mysql monitor tool\n"
    

41.                   "Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com \n"
    

42.                         "Info:This tool collect data from 'show global status' and show interval (-t) seconds data change\n"
    

43.                         "help DBA to diagnose performance problem,when error or (-d) run log is at current dir name \n"
    

44.                         "is mymon.log.\n"
    

45.                         "WIN32 platform is not support\n"
    

46.                         "Useage:./mymon [-u username] [-p password] -S socketfile/{-h hosname -P port} -t seconds -n times [-l logfile] [-d]\n"
    

47.                         "more help info in ./mymon -h\n"
    

48.                         );
    

49.         return 0;
    

50. }
    

51. 
    

52. int getpar( char (*par)[500],int n)
    

53. {
    

54.         int i;
    

55.         for(i=0;i<4;i++)
    

56.         {
    

57.                 printf("your parameter is %s\n",*(par+i));
    

58.         }
    

59. }
    

60. 
    

61. int checkpar(char (*par)[500],int t,int n)
    

62. {
    

63.         if(strlen(*(par+2)) == 0 && (strlen(*(par+4)) == 0 || strlen(*(par+5)) == 0 ))
    

64.         {
    

65. 
    

66.                 useage();
    

67.                 return -1;
    

68.         }
    

69. 
    

70.         if(t==0||n==0)
    

71.         {
    

72.                 useage();
    

73.                 return -1;
    

74.         }
    

75.         return 0;
    

76. }
    

77. 
    

78. int main(int argc,char* argv[])
    

79. {
    

80.         int ch;
    

81.         int t = 0; //t how long
    

82.         int n = 0;//n count
    

83.         FILE* fd = 0;//log file fs
    

84.         unsigned int mport = 0;//mysql connect port
    

85. 
    

86.         debug = 0;
    

87.         write_file = 0;
    

88.         PAR par;
    

89.         setbuf(stdout,NULL);//stdio buffer disable
    

90.         memset(par,0,sizeof(PAR));
    

91.         if(argc<2)
    

92.         {
    

93.                 useage();
    

94.                 return -1;
    

95.         }
    

96.         while ((ch = getopt(argc, argv, "u:p:S:t:n:l:h:P:d?")) != -1)
    

97.         {
    

98.                 switch (ch)
    

99.                 {
    

100.                         case 'u':
     

101.                                 if(strlen(optarg)>MAX_PAR_LEN)
     

102.                                 {
     

103.                                         printf("MYMON:username max length more than 500 bytes\n");
     

104.                                         return -1;
     

105.                                 }
     

106.                                 strcpy(par[0],optarg);
     

107.                                 break;
     

108.                         case 'h':
     

109.                                 if(strlen(optarg)>MAX_PAR_LEN)
     

110.                                 {
     

111.                                         printf("MYMON:hostname max length more than 500 bytes\n");
     

112.                                         return -1;
     

113.                                 }
     

114.                                 strcpy(par[4],optarg);
     

115.                                 break;
     

116.                         case 'P':
     

117.                                 if(strlen(optarg)>MAX_PAR_LEN)
     

118.                                 {
     

119.                                         printf("MYMON:port max length more than 500 bytes\n");
     

120.                                         return -1;
     

121.                                 }
     

122.                                 strcpy(par[5],optarg);
     

123.                                 sscanf (par[5],"%u",&mport);
     

124.                                 break;
     

125.                         case 'p':
     

126.                                 if(strlen(optarg)>MAX_PAR_LEN)
     

127.                                 {
     

128.                                         printf("MYMON:password max length more than 500 bytes\n");
     

129.                                         return -1;
     

130.                                 }
     

131.                                 strcpy(par[1],optarg);
     

132.                                 break;
     

133.                         case 'S':
     

134.                                 if(strlen(optarg)>MAX_PAR_LEN)
     

135.                                 {
     

136.                                         printf("MYMON:socket max length more than 500 bytes\n");
     

137.                                         return -1;
     

138.                                 }
     

139.                                 strcpy(par[2],optarg);
     

140.                                 break;
     

141.                         case 't':
     

142.                                 sscanf (optarg,"%d",&t);
     

143.                                 break;
     

144.                         case 'n':
     

145.                                 sscanf (optarg,"%d",&n);
     

146.                                 break;
     

147.                         case 'l':
     

148.                                 if(strlen(optarg)>MAX_PAR_LEN)
     

149.                                 {
     

150.                                         printf("MYMON:logfile name max length more than 500 bytes\n");
     

151.                                         return -1;
     

152.                                 }
     

153.                                 strcpy(par[3],optarg);
     

154.                                 if((fd = fopen(par[3],OPENFLAG)) == NULL)
     

155.                                 {
     

156.                                         perror("MYMON:");
     

157.                                         return -1;
     

158.                                 }
     

159.                                 write_file = 1;
     

160.                                 setbuf(fd,NULL);//stdio buffer disable
     

161.                                 break;
     

162.                         case 'd':
     

163.                                 debug = 1;
     

164.                                 break;
     

165.                         default:
     

166.                         case '?':
     

167.                                 useage();
     

168.                                 return -1;
     

169.                 }
     

170.         }
     

171. 
     

172.         if(checkpar(par,t,n) == -1)
     

173.         {
     

174.                 printf("MYMON:must parameter error\n");
     

175.                 return -1;
     

176.         }
     

177.         wel();
     

178.         if(conn(par,t,n+1,fd,mport) !=0)
     

179.                 {
     

180.                         printf("MYMON:error pelase check log\n");
     

181.                         return -1;
     

182.                 }
     

183.         if(write_file == 1)
     

184.                 {
     

185.                         fclose(fd);
     

186.                 }
     

187.         return 0;
     

188. }

點擊(此處)折疊或打開

1. fun.c功能實現(xiàn)接口函數(shù)
   

2. /*************************************************************************
   

3.   > File Name: fun.c
   

4.   > Author: gaopeng QQ:22389860 all right reserved
   

5.   > Mail: gaopp_200217@163.com
   

6.   > Created Time: Wed 08 Feb 2017 10:08:56 AM CST
   

7.  ************************************************************************/
   

8.  /*
   

9.         This file frist store new and old data to one data struct
   

10.         **struct->*struct->struct
    

11.         struct have three pointer
    

12.         statname
    

13.         olddata
    

14.         newdata
    

15. 
    

16.         Then sub new and old data to one struct array
    

17.         Last use array to print data
    

18. */
    

19. 
    

20.  
    

21. 
    

22. #include"type.h"
    

23. #include"fun.h"
    

24. 
    

25. /*
    

26.  * field    pointer to status name
    

27.  * olddata  pointer to prev data
    

28.  * newdata  pointer to now data
    

29.  */
    

30. 
    

31. 
    

32. typedef struct MDATA
    

33. {
    

34.         char* statname;
    

35.         char* olddata;
    

36.         char* newdata;
    

37. } MDATA;
    

38. 
    

39. typedef struct D_STROE
    

40. {
    

41.         char statname[MAXSTATNAME];
    

42.         int64_t data;
    

43. } D_STROE;
    

44. 
    

45. 
    

46. /* ret=0 is sucss  ret<0 is false*/
    

47. int handle_data(MYSQL_RES *result/*in*/,uint64_t row_count,unsigned int row_field,FILE* fd,MDATA* *datap/*in*/,int i);
    

48. /* ret=0 is sucss  ret<0 is false*/
    

49. int memaloc(MDATA** *datap/*out*/,uint64_t row_count);
    

50. /* ret=0 is sucss  ret<0 is false*/
    

51. int sub_store_data(MDATA* *datap,D_STROE* *last_data/*out*/,uint64_t row_count,int* lenth/*in*/);
    

52. /* ret=0 is sucss  ret<0 is false*/
    

53. int print_data(D_STROE* last_data,int lenth,FILE* fd) ;
    

54. /* ret=-1 is no data return >=0 and -65535 is data return */
    

55. int64_t findva(const char* ins,D_STROE* last_data,int lenth);
    

56. /* no retrun value*/
    

57. int freedatap(MDATA* *datap,uint64_t row_count);
    

58. 
    

59. static freecouti=0;
    

60. 
    

61. void test(void)
    

62. {
    

63.         ;
    

64. }
    

65. 
    

66. 
    

67. int mprintf(FILE* outfile,const char *format,...)
    

68. {
    

69.     va_list args;
    

70.     va_start(args,format);
    

71.     if( vfprintf(outfile,format,args) <0 )
    

72.         {
    

73.                 GET_LOG(LogLevel[4],ERROR,"ERROR:mprintf() error:%d\n",ERROR);
    

74.                         printf("MYMON: mprintf() error:%d\n",ERROR);
    

75.                         exit(-1);
    

76.         }
    

77.     va_end(args);
    

78.         return 0;
    

79. }
    

80. 
    

81. 
    

82. 
    

83. int xfree(void* p)
    

84. {
    

85.         if(p==NULL)
    

86.         {
    

87.                 freecouti++;
    

88.                 if(debug == 3)
    

89.                 {
    

90.                         GET_LOG(LogLevel[1],DEBUG,"DEBUG:xfree(NULL) debug:%d\n",freecouti);
    

91.                 }
    

92.                 return 0;
    

93.         }
    

94.         free(p);
    

95.         p=NULL;
    

96.         return 0;
    

97. }
    

98. 
    

99. int conn(char (*par)[500]/*in*/,int t,int n,FILE* fd,unsigned int myport)
    

100. {
     

101.         MYSQL conn_ptr;
     

102.         MYSQL_RES *query_res =NULL;
     

103.         MYSQL_ROW sqlrow_data;
     

104.         int ret = 0;
     

105.         uint64_t  row_count = 0;
     

106.         unsigned int row_field = 0;
     

107.         MDATA* *datap =NULL;
     

108.         int i=0;//used to check frist time no old data
     

109. 
     

110. 
     

111.         /*
     

112.          *MYSQL *mysql_init(MYSQL *mysql)
     

113.          *An initialized MYSQL*handle. NULLif there was insufficient memory to allocate a new object.
     

114.          */
     

115.         if(mysql_init(&conn_ptr) == NULL)
     

116.         {
     

117.                 ret = -1;
     

118.                 GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_init() error:%d\n",ERROR);
     

119.                 printf("MYMON: mysql_init() error\n");
     

120.                 return ret;
     

121.         }
     

122.         /*
     

123.          *MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user,
     

124.          *const char *passwd, const char *db, unsigned int port, const char *unix_socket,
     

125.          *unsigned long client_flag)
     

126.          *A MYSQL*connection handle if the connection was successful, NULLif the connection was unsuccessful.
     

127.          *For a successful connection, the return value is the same as the value of the first parameter
     

128.          */
     

129.         if(mysql_real_connect(&conn_ptr,*(par+4),*par,*(par+1),NULL,myport,*(par+2),0)==NULL)
     

130.         {
     

131.                 ret = -2;
     

132.                 GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_real_connect() error:%d\n",ERROR);
     

133.                 printf("MYMON: mysql_real_connect() error\n");
     

134.                 if(mysql_errno(&conn_ptr))
     

135.                 {
     

136.                         GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_real_connect() error:%s\n",mysql_error(&conn_ptr));
     

137.                         printf("MYMON: mysql_real_connect() error:%s\n",mysql_error(&conn_ptr));
     

138.                 }
     

139.                 return ret;
     

140.         }
     

141. 
     

142.         while(n--)
     

143.         {
     

144.                 /*
     

145.                  *int mysql_query(MYSQL *mysql, const char *stmt_str)
     

146.                  *Zero for success. Nonzero if an error occurred.
     

147.                  */
     

148.                 if((mysql_query(&conn_ptr,"show global status")) != 0)
     

149.                 {
     

150.                         ret = -3;
     

151.                         GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_query() error:%s\n",mysql_error(&conn_ptr));
     

152.                         printf("MYMON: mysql_query() error:%s\n",mysql_error(&conn_ptr));
     

153.                         return ret;
     

154.                 }
     

155.                 /*
     

156.                  * MYSQL give memory out
     

157.                  * MYSQL_RES *mysql_store_result(MYSQL *mysql)
     

158.                  * A MYSQL_RESresult structure with the results. NULL(0) if an error occurred
     

159.                  */
     

160. 
     

161.                 if((query_res=mysql_store_result(&conn_ptr)) == NULL)
     

162.                 {
     

163.                         ret = -4;
     

164.                         GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_store_result() error:%s\n",mysql_error(&conn_ptr));
     

165.                         printf("MYMON: mysql_store_result() error:%s\n",mysql_error(&conn_ptr));
     

166.                         return ret;
     

167.                 }
     

168.                 /*
     

169.                  * my_ulonglong mysql_num_rows(MYSQL_RES *result)
     

170.                  * The number of rows in the result set.
     

171.                  * unsigned int mysql_num_fields(MYSQL_RES *result)
     

172.                  * An unsigned integer representing the number of columns in a result set
     

173.                  */
     

174.                 row_count = mysql_num_rows(query_res);
     

175.                 row_field = mysql_field_count(&conn_ptr);
     

176.                 if(debug == 1)
     

177.                 {
     

178.                         GET_LOG(LogLevel[2],INFO,"INFO:mysql_num_rows() rows is %llu\n",row_count);
     

179.                         GET_LOG(LogLevel[2],INFO,"INFO:mysql_field_count() fileds is %ld\n",row_field);
     

180.                         printf("MYMON: mysql_num_rows() rows is %llu\n",row_count);
     

181.                         printf("MYMON: mysql_field_count() fileds is %ld\n",row_field);
     

182.                 }
     

183.                 if(i == 0)
     

184.                 {
     

185.                         if((memaloc(&datap,row_count)) !=0)
     

186.                         {
     

187.                                 ret = -6;
     

188.                                 GET_LOG(LogLevel[4],ERROR,"ERROR:memaloc() error:%s\n","ERROR");
     

189.                                 printf("MYMON: memaloc() error:%s\n","ERROR");
     

190.                                 return ret;
     

191.                         }
     

192.                 }
     

193.                 if(handle_data(query_res,row_count,row_field,fd,datap,i)!=0)
     

194.                 {
     

195.                         ret = -5;
     

196.                         GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() error:%s\n","ERROR");
     

197.                         printf("MYMON: handle_data() error:%s\n","ERROR");
     

198.                         return ret;
     

199.                 }
     

200. 
     

201.                 /*
     

202.                  * void mysql_free_result(MYSQL_RES *result)
     

203.                  * no result
     

204.                  */
     

205.                 mysql_free_result(query_res);
     

206.                 sleep(t);
     

207.                 i++;
     

208.         }
     

209.         /*
     

210.          * void mysql_close(MYSQL *mysql)
     

211.          * no result
     

212.          */
     

213. 
     

214.         /*hear free datap*/
     

215.                 {
     

216.                         freedatap(datap,row_count);
     

217.                 }
     

218.         mysql_close(&conn_ptr);
     

219.         return ret;
     

220. }
     

221. 
     

222. 
     

223. int freedatap(MDATA* *datap,uint64_t row_count)
     

224. {
     

225.         int ret = 0;
     

226.         uint64_t i = 0;
     

227.         if(datap == NULL)
     

228.                 {
     

229.                         return ret;
     

230.                 }
     

231.         GET_LOG(LogLevel[3],INFO,"INFO:freedatap()  check previous line is error?:%d\n",ret);
     

232.         for(i=0;i<row_count;i++)
     

233.                 {
     

234.                         xfree ((*(datap+i))->statname);
     

235.                         xfree((*(datap+i))->newdata);
     

236.                         xfree((*(datap+i))->olddata);
     

237.                         xfree(*(datap+i));
     

238.                 }
     

239.         xfree(datap);
     

240.         return ret;
     

241. }
     

242. int memaloc(MDATA** *datap,uint64_t row_count)
     

243. {
     

244.         int ret=0;
     

245.         MDATA* *mp=NULL;
     

246.         uint64_t i=0;
     

247. 
     

248.         if(datap == NULL)
     

249.         {
     

250.                 ret = -5;
     

251.                 GET_LOG(LogLevel[4],ERROR,"ERROR:memaloc()  datap=NULL?:%d\n",ret);
     

252.                 printf("MYMON: memaloc()  datap=NULL?:%d\n",ret);
     

253.                 return ret;
     

254.         }
     

255.         if((mp = (MDATA**)calloc(1,row_count*sizeof(MDATA*))) == NULL)
     

256.         {
     

257.                 ret = -3;
     

258.                 GET_LOG(LogLevel[4],ERROR,"ERROR:memaloc() memory malloc **p error:%d\n",ret);
     

259.                 printf("MYMON: memaloc() memory malloc **p error:%d\n",ret);
     

260.                 return ret;
     

261.         }
     

262.         for(i=0;i<row_count;i++)
     

263.         {
     

264.                 if((*(mp+i)=(MDATA*)calloc(1,sizeof(MDATA))) == NULL)
     

265.                 {
     

266.                         ret=-4;
     

267.                         GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() memory malloc *p error:%d\n",ret);
     

268.                         printf("MYMON: MYMON:handle_data() memory malloc *p error:%d\n",ret);
     

269.                         freedatap(mp,row_count);
     

270.                         return ret;
     

271.                 }
     

272.         }
     

273.         *datap = mp;
     

274.         return ret;
     

275. 
     

276. }
     

277. 
     

278. 
     

279. int handle_data(MYSQL_RES *result,uint64_t row_count,unsigned int row_field,FILE* fd,MDATA* *datap,int seq)
     

280. {
     

281.         uint64_t i = 0;
     

282.         MYSQL_ROW sqlrow_data;
     

283.         int ret = 0;
     

284.         D_STROE* last_data=NULL;//used last data store
     

285.         int lenth=0;//last data lenth
     

286. 
     

287.         //tmp
     

288.         int tmp = 0;
     

289.     D_STROE* tmp_last_data = NULL;
     

290. 
     

291.         if(result == NULL||datap == NULL)
     

292.         {
     

293.                 ret = -2;
     

294.                 GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() result == NULL datap == NULL :%s\n","ERROR");
     

295.                 printf("MYMON:handle_data() result == NULL datap == NULL :%s\n","ERROR");
     

296.                 return ret;
     

297.         }
     

298. 
     

299.         for(i=0;i<row_count;i++)//control true result return
     

300.         {
     

301.                 /*
     

302.                  * MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
     

303.                  * A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred
     

304.                  */
     

305.                 if((sqlrow_data=mysql_fetch_row(result)) == NULL) //control mysql_fetch_row error
     

306.                 {
     

307.                         ret = -1;
     

308.                         GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_fetch_row() error:%s\n","ERROR");
     

309.                         printf("MYMON: mysql_fetch_row() error:%s\n","ERROR");
     

310.                         return ret;
     

311.                 }
     

312.                 {
     

313.                         unsigned long *lengths;
     

314.                         lengths = mysql_fetch_lengths(result);
     

315.                         xfree((*(datap+i))->statname);
     

316.                         if(((*(datap+i))->statname = (char*)calloc(1,lengths[0]+10)) == NULL)
     

317.                         {
     

318.                                 ret = -4;
     

319.                                 GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() statname loop mem alloc error:%s\n","ERROR");
     

320.                                 printf("MYMON: handle_data() statname loop mem alloc error:%s\n","ERROR");
     

321.                                 return ret;
     

322. 
     

323.                         }
     

324.                         strcpy((*(datap+i))->statname,sqlrow_data[0] ? sqlrow_data[0] : "NULL");
     

325.                         xfree((*(datap+i))->olddata);
     

326.                         (*(datap+i))->olddata = (*(datap+i))->newdata;
     

327.                         //no free newdata pointer
     

328.                         if(((*(datap+i))->newdata = (char*)calloc(1,lengths[1]+10)) == NULL)
     

329.                         {
     

330.                                 ret = -5;
     

331.                                 GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() newdata loop mem alloc error:%s\n","ERROR");
     

332.                                 printf("MYMON: handle_data() newdata loop mem alloc error:%s\n","ERROR");
     

333.                                 return ret;
     

334.                         }
     

335.                         strcpy((*(datap+i))->newdata,sqlrow_data[1] ? sqlrow_data[1] : "NULL");
     

336.                         if(debug == 1)
     

337.                                 {
     

338.                                if((!strcmp((*(datap+i))->statname,"NULL")) || (!strcmp((*(datap+i))->newdata,"NULL")) )
     

339.                                 {
     

340.                                         GET_LOG(LogLevel[1],DEBUG,"DEBUG:handle_data() statname or data IS NULL:[%s]\n",(*(datap+i))->statname);
     

341.                                                 GET_LOG(LogLevel[1],DEBUG,"DEBUG:handle_data() statname or data IS NULL:[%s]\n",(*(datap+i))->newdata);
     

342.                                 }
     

343.                                 }
     

344.                 }
     

345. 
     

346.         }
     

347.         if(seq == 0)//olddata have no data
     

348.                 {
     

349.                         if(debug == 1)
     

350.                                 {
     

351.                         GET_LOG(LogLevel[1],DEBUG,"DEBUG:handle_data() first time is info %s\n","no old data");
     

352.                         printf("MYMON: handle_data() first time is info %s\n","no old data");
     

353.                                 }
     

354.                 }
     

355.         if(seq != 0 ) //newdata and olddata have data
     

356.                 {
     

357.                         if(sub_store_data(datap,&last_data,row_count,&lenth) != 0 ) //store data to array
     

358.                                 {
     

359.                                         ret = -6;
     

360.                                         GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() sub_store_data() error:%s\n","ERROR");
     

361.                                         printf("MYMON: handle_data() sub_store_data() error %s\n","ERROR");
     

362.                                         return ret;
     

363.                                 }
     

364.                 if(debug == 1)
     

365.                         {
     

366.                         tmp_last_data = last_data;
     

367.                         for(tmp =0;tmp<lenth;tmp++)
     

368.                                 {
     

369.                                         GET_LOG(LogLevel[1],DEBUG,"DEBUG:handle_data() find data is [%s]",(tmp_last_data)->statname);
     

370.                                         GET_LOG(LogLevel[1],DEBUG,"[%lld] \n",(tmp_last_data)->data);
     

371.                                         tmp_last_data++;
     

372.                                 }
     

373.                         }
     

374. 
     

375. 
     

376. 
     

377.                         if(print_data(last_data,lenth,fd) != 0) //store data from array
     

378.                                 {
     

379.                                         ret = -7;
     

380.                                         GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() print_data() error:%s\n","ERROR");
     

381.                                         printf("MYMON: handle_data() print_data() error %s\n","ERROR");
     

382.                                         return ret;
     

383.                                 }
     

384. 
     

385.                 }
     

386.         //used end free last data array
     

387.         xfree(last_data);
     

388.         return ret;
     

389. }
     

390. 
     

391. 
     

392. int sub_store_data(MDATA* *datap,D_STROE* *last_data,uint64_t row_count,int* lenth)
     

393. {
     

394.         D_STROE* tmp_p = NULL;
     

395.         int ret = 0;
     

396.         uint64_t i = 0;
     

397. 
     

398.         char* *dyd_d = NULL;
     

399.         char* *std_d = NULL;
     

400.         int64_t a = 0;
     

401.         int64_t b = 0;
     

402. 
     

403.         if(datap == NULL || last_data == NULL )
     

404.                 {
     

405.                     ret = -1;
     

406.                         GET_LOG(LogLevel[4],ERROR,"ERROR:sub_store_data() datap == NULL || fail_data == NULL %s\n","ERROR");
     

407.                         printf("MYMON: sub_store_data() datap == NULL || fail_data == NULL %s\n","ERROR");
     

408.                         return ret;
     

409.                 }
     

410. 
     

411.         if((tmp_p = (D_STROE*)calloc(1,(sizeof(stdata)+sizeof(dydata))*sizeof(D_STROE))) == NULL)
     

412.                 {
     

413.                         ret = -2;
     

414.                         GET_LOG(LogLevel[4],ERROR,"ERROR:sub_store_data()  mem alloc error:%s\n","ERROR");
     

415.                         printf("MYMON: sub_store_data()  mem alloc error:%s\n","ERROR");
     

416.                         return ret;
     

417.                 }
     

418.         *last_data = tmp_p;
     

419. 
     

420.         for(i=0;i<row_count;i++)
     

421.                 {
     

422.                         dyd_d = dydata; //re pointer to dydata
     

423.                 std_d = stdata; //re pointer to dydata
     

424.                         if(strlen((*(datap+i))->statname)>MAXSTATNAME)
     

425.                                 {
     

426.                                         ret = -3;
     

427.                                         GET_LOG(LogLevel[4],ERROR,"ERROR:sub_store_data()  statname>MAXSTATNAME:%s\n","ERROR");
     

428.                                         printf("MYMON: sub_store_data()  statname>MAXSTATNAME:%s\n","ERROR");
     

429.                                         goto er;
     

430.                                 }
     

431.                         while(*dyd_d)
     

432.                                 {
     

433.                                         if(debug == 3)
     

434.                                                 {
     

435.                                                         printf("dyd [%s] [%s] [%d] [%d]\n",*dyd_d,(*(datap+i))->statname,strlen(*dyd_d),strlen((*(datap+i))->statname));
     

436.                                                 }
     

437.                                         if(!strcmp(*dyd_d,(*(datap+i))->statname))
     

438.                                                 {
     

439. 
     

440.                                                         if(debug == 1)
     

441.                                                                 {
     

442.                                                                         GET_LOG(LogLevel[1],DEBUG,"DEBUG:sub_store_data() find data is %s  \n",*dyd_d);
     

443.                                                                 }
     

444.                                                         strcpy(tmp_p->statname,*dyd_d);
     

445.                                                         if( !strcmp((*(datap+i))->olddata,"NULL") || !strcmp((*(datap+i))->newdata,"NULL")) //check "NULL" DATA ?
     

446.                                                                 {
     

447.                                                                         GET_LOG(LogLevel[2],INFO,"INFO:sub_store_data() data %s is [NULL] \n",*dyd_d);
     

448.                                                                         tmp_p->data = ERRORDATA;
     

449.                                                                 }
     

450.                                                         if(sscanf((*(datap+i))->olddata,"%lld",&a) == EOF || sscanf((*(datap+i))->newdata,"%lld",&b) == EOF)
     

451.                                                                 {
     

452.                                                                         ret = -4;
     

453.                                                                         GET_LOG(LogLevel[4],ERROR,"ERROR:sub_store_data() sscanf() error %s\n","ERROR");
     

454.                                                                         printf("MYMON: sub_store_data() sscanf() error:%s\n","ERROR");
     

455.                                                                         goto er;
     

456.                                                                 }
     

457.                                                         tmp_p->data = b-a; //new - old
     

458.                                                         tmp_p++;
     

459.                                                         (*lenth)++;
     

460.                                                         break;
     

461.                                                 }
     

462.                                         dyd_d++;
     

463.                                 }
     

464.                         while(*std_d)
     

465.                                 {
     

466.                                         if(debug == 3)
     

467.                                                 {
     

468.                                                         printf("std [%s] [%s] [%d] [%d]\n",*std_d,(*(datap+i))->statname,strlen(*std_d),strlen((*(datap+i))->statname));
     

469.                                                 }
     

470.                                         if(!strcmp(*std_d,(*(datap+i))->statname))
     

471.                                                 {
     

472. 
     

473.                                                      if(debug == 1)
     

474.                                                                 {
     

475.                                                                         GET_LOG(LogLevel[1],DEBUG,"DEBUG:sub_store_data() find data is %s \n",*std_d);
     

476.                                                                 }
     

477.                                                         strcpy(tmp_p->statname,*std_d);
     

478.                                                         if(!strcmp((*(datap+i))->newdata,"NULL")) //check "NULL" DATA ?
     

479.                                                                 {
     

480.                                                                         GET_LOG(LogLevel[2],INFO,"INFO:sub_store_data() data %s is [NULL] \n",*std_d);
     

481.                                                                         tmp_p->data = ERRORDATA;
     

482.                                                                 }
     

483.                                                         if(sscanf((*(datap+i))->olddata,"%lld",&tmp_p->data) == EOF)
     

484.                                                                 {
     

485.                                                                         ret = -4;
     

486.                                                                         GET_LOG(LogLevel[4],ERROR,"ERROR:sub_store_data() sscanf() error %s\n","ERROR");
     

487.                                                                         printf("MYMON: sub_store_data() sscanf() error:%s\n","ERROR");
     

488.                                                                         goto er;
     

489.                                                                 }
     

490.                                                         tmp_p++;
     

491.                                                         (*lenth)++;
     

492.                                                         break;
     

493.                                                 }
     

494.                                 std_d++;
     

495.                                 }
     

496.                 }
     

497.         return ret;
     

498. 
     

499.         er:
     

500.                 xfree(tmp_p);
     

501.                 *last_data= NULL;
     

502.                 return ret;
     

503. }
     

504. 
     

505. int print_data(D_STROE* last_data,int lenth,FILE* fd)
     

506. {
     

507.         time_t rawtime;
     

508.         struct tm * timeinfo = NULL;
     

509.         int ret = 0;
     

510.         FILE* outfile = NULL;
     

511.         if(write_file == 1)
     

512.                 {
     

513.                         outfile=fd;
     

514.                 }
     

515.         else
     

516.                 {
     

517.                         outfile=stdout;
     

518.                 }
     

519. 
     

520.         if(time (&rawtime) == -1)
     

521.                 {
     

522.                         ret = -2;
     

523.                         GET_LOG(LogLevel[4],ERROR,"ERROR:print_data() time() error %s\n","ERROR");
     

524.             printf("MYMON: ERROR:print_data() time() error:%s\n","ERROR");
     

525.                         return ret;
     

526.                 }
     

527.         timeinfo = localtime ( &rawtime );
     

528.         if (timeinfo == NULL )
     

529.                 {
     

530.                         ret = -1;
     

531.                         GET_LOG(LogLevel[4],ERROR,"ERROR:print_data() localtime () error %s\n","ERROR");
     

532.                         printf("MYMON: ERROR:print_data() localtime () error:%s\n","ERROR");
     

533.                         return ret;
     

534.                 }
     

535.         mprintf(outfile,"------------------------------------------MYMON--------------------------------------------\n");
     

536.         mprintf(outfile,"SysTime: %s",asctime (timeinfo));
     

537.         mprintf(outfile,
     

538.                     "%s%-11ld%s%-8ld%s%-8ld%s%-8ld\n",
     

539.                         "Uptime:",findva("Uptime",last_data,lenth),
     

540.                         "Connections:",findva("Connections",last_data,lenth) ,
     

541.                         "Aborted_clients:",findva("Aborted_clients",last_data,lenth),
     

542.                         "Max_used_connections:",findva("Max_used_connections",last_data,lenth));
     

543.         mprintf(outfile,"--------------------------------------MYSQLINFO-------------------------------------------\n");
     

544.         mprintf(outfile,"[###Threads info and qc info:]\n");
     

545.         mprintf(outfile,
     

546.                     "%s%-12ld%s%-14ld%s%-13ld\n",
     

547.                    "Threads_connected:",findva("Threads_connected",last_data,lenth),
     

548.            "Threads_running:",findva("Threads_running",last_data,lenth),
     

549.            "Qcache_free_memory:",findva("Qcache_free_memory",last_data,lenth)
     

550.                         );
     

551.         mprintf(outfile,
     

552.                     "%s%-18ld%s%-12ld%s%-17ld\n",
     

553.                    "Qcache_hits:",findva("Qcache_hits",last_data,lenth),
     

554.                    "Qcache_not_cached:",findva("Qcache_not_cached",last_data,lenth),
     

555.                    "Qcache_inserts:",findva("Qcache_inserts",last_data,lenth)
     

556.                   );
     

557.         mprintf(outfile,"[###Table read info:]\n");
     

558.         mprintf(outfile,
     

559.                     "%s%-11ld%s%-13ld%s%-14ld\n",
     

560.                         "Handler_read_first:",findva("Handler_read_first",last_data,lenth),
     

561.                         "Handler_read_key:",findva("Handler_read_key",last_data,lenth),
     

562.                         "Handler_read_next:",findva("Handler_read_next",last_data,lenth)
     

563.                         );
     

564. 
     

565.         mprintf(outfile,
     

566.                     "%s%-12ld%s%-13ld%s%ld\n",
     

567.                         "Handler_read_last:",findva("Handler_read_last",last_data,lenth),
     

568.                         "Handler_read_rnd:",findva("Handler_read_rnd",last_data,lenth),
     

569.                         "Handler_read_rnd_next:",findva("Handler_read_rnd_next",last_data,lenth)
     

570.                         );
     

571.         mprintf(outfile,"[###Sorts and temp tables info:]\n");
     

572.         mprintf(outfile,
     

573.                     "%s%-20ld%s%ld\n",
     

574.                     "Sort_rows:",findva("Sort_rows",last_data,lenth),
     

575.                         "Sort_merge_passes:",findva("Sort_merge_passes",last_data,lenth)
     

576.                         );
     

577.         mprintf(outfile,
     

578.                     "%s%-11ld%s%ld\n",
     

579.                         "Created_tmp_tables:",findva("Created_tmp_tables",last_data,lenth),
     

580.                         "Created_tmp_disk_tables:",findva("Created_tmp_disk_tables",last_data,lenth)
     

581.                         );
     

582.         mprintf(outfile,"[###Table join info:]\n");
     

583.     mprintf(outfile,
     

584.                     "%s%-13ld%s%ld\n",
     

585.                         "Select_full_join:",findva("Select_full_join",last_data,lenth),
     

586.                         "Select_scan:",findva("Select_scan",last_data,lenth)
     

587.                         );
     

588.          
     

589.           mprintf(outfile,"[###Open table cache and binlog cache info:]\n");
     

590.           mprintf(outfile,
     

591.                     "%s%-8ld%s%ld\n",
     

592.                         "Table_open_cache_hits:",findva("Table_open_cache_hits",last_data,lenth),
     

593.                         "Table_open_cache_misses:",findva("Table_open_cache_misses",last_data,lenth)
     

594.                         );
     

595.           mprintf(outfile,
     

596.                     "%s%-13ld%s%ld\n",
     

597.                         "Binlog_cache_use:",findva("Binlog_cache_use",last_data,lenth),
     

598.                         "Binlog_cache_disk_use:",findva("Binlog_cache_disk_use",last_data,lenth)
     

599.                         );
     

600.          mprintf(outfile,"[###Hanler_xxx and Com_xxx info:]\n");
     

601. 
     

602.          mprintf(outfile,
     

603.                     "%s%-15ld%s%-15ld%s%ld\n",
     

604.                         "Handler_delete:",findva("Handler_delete",last_data,lenth),
     

605.                         "Handler_update:",findva("Handler_update",last_data,lenth),
     

606.                         "Handler_write:",findva("Handler_write",last_data,lenth)
     

607.                         );
     

608.          mprintf(outfile,
     

609.                     "%s%-15ld%s%-ld\n",
     

610.                         "Handler_commit:",findva("Handler_commit",last_data,lenth),
     

611.                         "Handler_rollback:",findva("Handler_rollback",last_data,lenth)
     

612.                         );
     

613.           mprintf(outfile,
     

614.                          "%s%-19ld%s%ld\n",
     

615.                          "Com_commit:",findva("Com_commit",last_data,lenth),
     

616.                          "Com_rollback:",findva("Com_rollback",last_data,lenth)
     

617.                          );
     

618.         mprintf(outfile,
     

619.                     "%s%-8ld%s%-8ld%s%-8ld%s%-8ld\n",
     

620.                         "U(counts):",findva("Com_update",last_data,lenth),
     

621.                         "D(counts):",findva("Com_delete",last_data,lenth),
     

622.                         "I(counts):",findva("Com_insert",last_data,lenth),
     

623.                         "S(counts):",findva("Com_select",last_data,lenth)
     

624.                         );
     

625.     mprintf(outfile,
     

626.                    "--------------------------------------INNODBINFO------------------------------------------\n");
     

627.     mprintf(outfile,"[###Innodb_lock info:]\n");
     

628.         mprintf(outfile,
     

629.                     "%s%-17ld%s%ld\n",
     

630.                         "Innodb_row_lock_time:",findva("Innodb_row_lock_time",last_data,lenth),
     

631.                         "Innodb_row_lock_waits:",findva("Innodb_row_lock_waits",last_data,lenth)
     

632.                         );
     

633.         mprintf(outfile,
     

634.                     "%s%ld\n",
     

635.                         "Innodb_row_lock_current_waits:",findva("Innodb_row_lock_current_waits",last_data,lenth)
     

636.                         );
     

637.     mprintf(outfile,"[###Innodb_buffer_pool info:]\n");
     

638. 
     

639.         mprintf(outfile,"%s%-10ld%s%ld\n","Innodb_buf_pool_pages_total:",findva("Innodb_buffer_pool_pages_total",last_data,lenth)
     

640.                     ,"Innodb_buf_pool_pages_dirty:",findva("Innodb_buffer_pool_pages_dirty",last_data,lenth));
     

641. 
     

642.         mprintf(outfile,"%s%-16ld%s%ld\n","Innodb_buf_pages_data:",findva("Innodb_buffer_pool_pages_data",last_data,lenth)
     

643.                     ,"Innodb_buf_pages_free:",findva("Innodb_buffer_pool_pages_free",last_data,lenth));
     

644. 
     

645.         mprintf(outfile,
     

646.                     "%s%-9ld%s%ld\n",
     

647.                         "Innodb_buffer_pool_wait_free:",findva("Innodb_buffer_pool_wait_free",last_data,lenth),
     

648.                         "Innodb_buffer_pool_pages_misc:",findva("Innodb_buffer_pool_pages_misc",last_data,lenth)
     

649.                         );
     

650.         mprintf(outfile,
     

651.                     "%s%-11ld%s%ld\n",
     

652.                         "Innodb_dblwr_pages_written:",findva("Innodb_dblwr_pages_written",last_data,lenth),
     

653.                         "Innodb_dblwr_writes:",findva("Innodb_dblwr_writes",last_data,lenth)
     

654.                         );
     

655. 
     

656.         mprintf(outfile,
     

657.                     "%s%ld\n",
     

658.                         "Innodb_buffer_pool_pages_flushed:",findva("Innodb_buffer_pool_pages_flushed",last_data,lenth)
     

659.                         );
     

660.         mprintf(outfile,
     

661.                      "%s%-10ld %s%-10ld %s%-10ld\n",
     

662.                      "read_ahead:",findva("Innodb_buffer_pool_read_ahead",last_data,lenth),
     

663.                      "ahead_evict:",findva("Innodb_buffer_pool_read_ahead_evicted",last_data,lenth),
     

664.                      "ahead_rnd:",findva("Innodb_buffer_pool_read_ahead_rnd",last_data,lenth));
     

665.         mprintf(outfile,"[###Innodb_log info:]\n");
     

666.         mprintf(outfile,
     

667.                      "%s%-20ld%s%ld\n",
     

668.                         "Innodb_log_writes:",findva("Innodb_log_writes",last_data,lenth),
     

669.                         "Innodb_log_waits:",findva("Innodb_log_waits",last_data,lenth)
     

670.                         );
     

671.         mprintf(outfile,
     

672.                     "%s%-9ld%s%ld\n",
     

673.                         "Innodb_os_log_pending_fsyncs:",findva("Innodb_os_log_pending_fsyncs",last_data,lenth),
     

674.                         "Innodb_os_log_pending_writes:",findva("Innodb_os_log_pending_writes",last_data,lenth)
     

675.                         );
     

676.         mprintf(outfile,
     

677.                     "%s%ld\n",
     

678.                         "Innodb_os_log_written:",findva("Innodb_os_log_written",last_data,lenth)
     

679.                    );
     

680.         mprintf(outfile,"[###Innodb_data read/writes info:]\n");
     

681.     mprintf(outfile,
     

682.                     "%s%-20ld%s%ld\n",
     

683.                         "Innodb_data_reads:",findva("Innodb_data_reads",last_data,lenth),
     

684.                         "Innodb_data_read:",findva("Innodb_data_read",last_data,lenth)
     

685.                         );
     

686.     mprintf(outfile,
     

687.                     "%s%-19ld%s%ld\n",
     

688.                         "Innodb_data_writes:",findva("Innodb_data_writes",last_data,lenth),
     

689.                         "Innodb_data_written:",findva("Innodb_data_written",last_data,lenth)
     

690.                         );
     

691.         mprintf(outfile,
     

692.                     "%s%-19ld%s%ld\n",
     

693.                     "Innodb_data_fsyncs:",findva("Innodb_data_fsyncs",last_data,lenth),
     

694.                     "Innodb_data_pending_fsyncs:",findva("Innodb_data_pending_fsyncs",last_data,lenth)
     

695.                     );
     

696.         mprintf(outfile,
     

697.                     "%s%ld\n",
     

698.                         "Innodb_buffer_pool_reads(physics reads):",findva("Innodb_buffer_pool_reads",last_data,lenth)
     

699.                    );
     

700.         mprintf(outfile,
     

701.                     "%s%ld\n",
     

702.                         "Innodb_buffer_pool_read_requests(logic reads):",findva("Innodb_buffer_pool_read_requests",last_data,lenth)
     

703.                    );
     

704. 
     

705.         mprintf(outfile,"[###Innodb_data rows info:]\n");
     

706.         mprintf(outfile,
     

707.                     "%s%-8ld%s%-8ld%s%-8ld%s%-8ld\n",
     

708.                         "U(rows):",findva("Innodb_rows_updated",last_data,lenth),
     

709.                         "D(rows):",findva("Innodb_rows_deleted",last_data,lenth),
     

710.                         "I(rows):",findva("Innodb_rows_inserted",last_data,lenth),
     

711.                         "S(rows):",findva("Innodb_rows_read",last_data,lenth)
     

712.                         );
     

713. 
     

714.         mprintf(outfile,"---------------------------------------TOTALINFO------------------------------------------\n");
     

715.         mprintf(outfile,
     

716.                     "%s%-15ld%s%ld\n",
     

717.                         "Bytes_received:",findva("Bytes_received",last_data,lenth),
     

718.                         "Bytes_sent:",findva("Bytes_sent",last_data,lenth)
     

719.                         );
     

720.         return ret;
     

721. }
     

722. 
     

723. int64_t findva(const char* ins,D_STROE* last_data,int lenth)
     

724. {
     

725.         int i=0;
     

726.         for(i=0;i<lenth;i++)
     

727.                 {
     

728.                         if(!strcmp(last_data->statname,ins))
     

729.                                 {
     

730.                                         return last_data->data;
     

731.                                         break;
     

732.                                 }
     

733.                         last_data++;
     

734.                 }
     

735.         return (int64_t)(-1);
     

736. }

以上是“如何使用自己開發(fā)的MYMON工具監(jiān)控MYSQL運行狀態(tài)”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！