本脚本主要用于查看mysql的各项性能参数,以及推荐性能指标,脚本在附件中

运行结果:

[root@cacti ~]# python mysqlpulse.py
Host[localhost]:
User[root]:
Password root@localhost:
database[mysql]:
mysqlpulse.py:323: Warning: The foreign data source you are trying to reference does not exist. Data source error:  error: 1054  'Unknown column 'alias' in 'field list''
  cursor.execute(sql)
mysqlpulse.py:323: Warning: The foreign data source you are trying to reference does not exist. Data source error:  error: 1146  'Table 'center_app.middleware' doesn't exist'
  cursor.execute(sql)
=============MySQL info=============
Connection id        : 207464
Current database     : mysql
Current user         : root@localhost
SSL                  : DISABLED
MySQL VERSION        : 5.1.59-log MySQL Community Server (GPL)
MySQL client info    : 5.1.59
Protocol version     : 10
Connection           : Localhost via UNIX socket
Server characterset  : latin1
Db     characterset  : latin1
Client characterset  : latin1
Conn.  characterset  : latin1
collation_connection : latin1_swedish_ci
collation_database   : latin1_swedish_ci
collation_server     : latin1_swedish_ci
Uptime               : 7240897s
============processlist=============
processlist rowcount =27
status time>0 threads list:
Id User  Host   db  Command  Time  State Info 
status time>0 threads count=0
=========MySQL status pulse=========
-----------------------------------------------------------------------------------------------------------------------------
Reads/Writes status                            total                        current                        proposal         
-----------------------------------------------------------------------------------------------------------------------------
Reads:Writes ratio(Reads/Writes)          : 1.28:1 (652.14M/511.02M)       1:22.68 (182.00/4.03k)        
QPS(Questions/Uptime)                     : 173.07/s (1.17G/6.91M)         73.87/s (4.33k/60.00)         
TPS(Questions/Uptime)                     : 2.27/s (15.69M/6.91M)          0.98/s (59.00/60.00)          
Table locks waited ratio(waited/immediate): 1:5.22k (111.33k/580.78M)      0:1 (0.00/4.23k)               0:1
select per second(select/Uptime)          : 94.44/s (652.14M/6.91M)        3.03/s (182.00/60.00)         
insert per second(insert/Uptime)          : 2.75/s (19.00M/6.91M)          0.90/s (54.00/60.00)          
update per second(update/Uptime)          : 70.10/s (484.09M/6.91M)        67.90/s (3.98k/60.00)         
delete per second(delete/Uptime)          : 1.13/s (7.81M/6.91M)           0.00/s (0.00/60.00)           
replace per second(replace/Uptime)        : 0.02/s (121.14k/6.91M)         0.00/s (0.00/60.00)           
Bytes sent per second(sent/Uptime)        : 191.95k/s (1.29T/6.91M)        13.66k/s (819.64k/60.00)      
Bytes received per second(re/Uptime)      : 45.39k/s (313.44G/6.91M)       16.67k/s (1000.48k/60.00)     
------------------------------------
Slow and Sort queries status
------------------------------------
Slow queries Ratio(Slow/Questions)        : 0.67% (8.01M/1.17G)            0.25% (11.00/4.33k)            Lower than 0
Slow queries PS(Slow/Uptime)              : 1.16/s (8.01M/6.91M)           0.18/s (11.00/60.00)           Lower than 0
Full join PS(full/Uptime)                 : 0.00/s (260.00/6.91M)          0.00/s (0.00/60.00)            Lower than 0
Sort merge passes PS(merge/Uptime)        : 0.00/s (1.00/6.91M)            0.00/s (0.00/60.00)            Lower than 0
Sort range PS(range/Uptime)               : 0.17/s (1.16M/6.91M)           0.05/s (3.00/60.00)            Lower than 0
Sort rows PS(rows/Uptime)                 : 20.56/s (142.01M/6.91M)        12.38/s (743.00/60.00)         Lower than 0
Sort scan PS(scan/Uptime)                 : 0.38/s (2.64M/6.91M)           0.28/s (17.00/60.00)           Lower than 0
------------------------------------
connections status
------------------------------------
Thread cache hits(created/Total)          : 99.88% (249.00/202.60k)        100.00% (0.00/1.00)            Higher than 0
Connections used ratio(Max used/Max)      : 25.17% (38.00/151.00)          0.00% (0.00/151.00)            Lower than 90
Aborted connects ratio(Aborted/Max)       : 168.21% (254.00/151.00)        0.00% (0.00/151.00)            Lower than 0
Threads running                           : 1.00                                                         
------------------------------------
temp table and Open tables/files status
------------------------------------
Temp tables to Disk ratio(disk/tmp)       : 3.21% (90.65k/2.75M)           0.00% (0.00/18.00)             Lower than 0
Open tables/table open cache/Opened tables: (246.00/512.00/41.50k)         (0.00/512.00/0.00)             1:1:1
Opened files PS(Opened/Uptime)            : 0.07/s (506.48k/6.91M)         0.00/s (0.00/60.00)            Lower than 0
------------------------------------
InnoDB Status
------------------------------------
Innodb buffer read hits(Disk/total)       : 99.91% (2.65M/2.74G)           100.00% (0.00/157.28k)         Higher than 99.99
Innodb dblwr pages written:dblwr writes   : 73.42:1 (144.62M/1.97M)                                       Lower than 64:1
Innodb buffer pages used ratio(free/total): 100.00% (0.00/24.00k)                                         Lower than 99.99
Innodb buffer pages dirty ratio(dirty)    : 3.78% (928.00/24.00k)                                        
Innodb buffer pages flushed PS(flushed)   : 20.94/s (144.62M/6.91M)        11.90/s (714.00/60.00)        
Innodb buffer pool pages misc             : 325.00                                                       
Innodb row lock waits PS(waits/Uptime)    : 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)           
Innodb row lock current waits             : 0.00                           0.00                          
Innodb row lock time avg                  : 443.00                         0.00                          
Innodb row lock time max                  : 50.09k                         0.00                          
Innodb row lock time total                : 10.86M                         0.00                          
Innodb rows read PS(read/Uptime)          : 466.70/s (3.15G/6.91M)         626.40/s (36.70k/60.00)       
Innodb rows inserted PS(inserted/Uptime)  : 246.39/s (1.66G/6.91M)         135.67/s (7.95k/60.00)        
Innodb rows updated PS(updated/Uptime)    : 69.96/s (483.08M/6.91M)        67.90/s (3.98k/60.00)         
Innodb data reads PS(reads/Uptime)        : 0.50/s (3.46M/6.91M)           0.00/s (0.00/60.00)           
Innodb data writes PS(writes/Uptime)      : 9.54/s (65.89M/6.91M)          6.12/s (367.00/60.00)         
Innodb data fsyncs PS(fsyncs/Uptime)      : 1.15/s (7.91M/6.91M)           0.87/s (52.00/60.00)          
Innodb data pending reads PS(reads/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)           
Innodb data pending writes PS(write/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)           
Innodb data pending fsyncs PS(fsync/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)           
------------------------------------
key buffer Status
------------------------------------
key buffer used ratio(used/size)          : 0.05% (185.00k/384.00M)                                       Lower than 99.99
key buffer read hit ratio(reads/request)  : 100.00% (183.00/208.97M)       100.00% (0.00/0.00)            Higher than 99.99
key buffer write hit ratio(writes/request): 81.37% (8.95M/48.03M)          100.00% (0.00/0.00)            Higher than 99.99
------------------------------------
Qcache Status
------------------------------------
Qcache queries hits ratio(hits/reads)     : 93.45% (609.41M/652.14M)       42.31% (77.00/182.00)          Higher than 30.00
Qcache hits inserts ratio(hits/inserts)   : 1.59k% (609.41M/37.40M)        101.32% (77.00/76.00)          Higher than 300.00
Qcache memory used ratio(free/total)      : 54.62% (7.26M/16.00M)                                        
Qcache prune ratio(prunes/inserts)        : 5.31% (1.99M/37.40M)           0.00% (0.00/76.00)            
Qcache block Fragmnt ratio(free/total)    : 23.65% (2.75k/11.63k)                                        
------------------------------------
Slave Status
------------------------------------
Slave running status                      : OFF                            OFF                           
Slave open temp tables                    : 0.00                           0.00                          
Slave transactions PS(transactions/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)           
Slave received PS(heartbeats/Uptime)      : 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)