Python实现监控MySQL性能指标.docx
《Python实现监控MySQL性能指标.docx》由会员分享,可在线阅读,更多相关《Python实现监控MySQL性能指标.docx(15页珍藏版)》请在冰豆网上搜索。
![Python实现监控MySQL性能指标.docx](https://file1.bdocx.com/fileroot1/2022-11/23/d3822d5b-50a5-4a82-be5a-b8f8028781f1/d3822d5b-50a5-4a82-be5a-b8f8028781f11.gif)
Python实现监控MySQL性能指标
Python实现监控MySQL性能指标
—华仔
目录
1、QPS计算(每秒查询数)2
2、TPS计算(每秒事务数)3
3、线程连接数和命中率3
4、表缓存3
5、最大连接数4
6、Innodb缓存4
7、MyISAMKeyBuffer缓存4
8、临时表使用情况5
9、binlogcache使用情况5
10、Innodb日志设置5
11、表扫描情况判断6
12、innodb行等其他参数6
13、join操作信息6
14、慢查询7
15、表锁/从数据库等其他7
16、源代码:
8
17、结束语12
本文旨在详细了解MYSQL主要性能指标的计算原理,深入了解各项参数设置,以及通过Python实现各项性能指标的计算,监控MySQL运行情况,达到优化MySQL的目的。
Python运行结果如下():
1、QPS计算(每秒查询数)
针对MyISAM引擎为主的DB
showGLOBALstatuslike'questions';
showglobalstatuslike'uptime';
QPS=questions/uptime
mysql自启动以来的平均QPS,如果要计算某一时间段内的QPS,可在高峰期间获取间隔时间t2-t1,然后分别计算出t2和t1时刻的q值,QPS=(q2-q1)/(t2-t1)
针对InnnoDB引擎为主的DB
showglobalstatuslike'com_update';
showglobalstatuslike'com_select';
showglobalstatuslike'com_delete';
showglobalstatuslike'uptime';
QPS=(com_update+com_insert+com_delete+com_select)/uptime
Com_select/s:
平均每秒select语句执行次数
Com_insert/s:
平均每秒insert语句执行次数
Com_update/s:
平均每秒update语句执行次数
Com_delete/s:
平均每秒delete语句执行次数
2、TPS计算(每秒事务数)
showglobalstatuslike'com_commit';
showglobalstatuslike'com_rollback';
showglobalstatuslike'uptime';
TPS=(com_commit+com_rollback)/uptime
3、线程连接数和命中率
showglobalstatuslike'threads_%';
|Threads_cached|480|//代表当前此时此刻线程缓存中有多少空闲线程
|Threads_connected|153|//代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数
|Threads_created|20344|//代表从最近一次服务启动,已创建线程的数量
|Threads_running|2|//代表当前激活的(非睡眠状态)线程数
showglobalstatuslike'Connections';
线程缓存命中率=1-Threads_created/Connections=99.994%
showvariableslike'%thread_cache_size%';
根据Threads_connected可预估thread_cache_size值应该设置多大,一般来说250是一个不错的上限值,如果内存足够大,也可以设置成thread_cache_size值和threaads_connected值相同;或者通过观察threads_created值,如果该值很大或一直在增长,可以适当增加thread_cache_size的值;在休眠状态下每个线程大概占用256KB左右的内存,所以当内存足够时,设置太小也不会节约太多内存,除非该值已经超过几千。
4、表缓存
showglobalstatuslike'open_tables%';
我们设置的打开表的缓存和表定义缓存
showvariableslike'table_open_cache';
showvariableslike'table_defi%';
针对MyISAM:
mysql每打开一个表,都会读入一些数据到table_open_cache缓存中,当mysql在这个缓存中找不到相应的信息时,才会去磁盘上直接读取,所以该值要设置得足够大以避免需要重新打开和重新解析表的定义,一般设置为max_connections的10倍,但最好保持在10000以内。
还有种依据就是根据状态open_tables的值进行设置,如果发现open_tables的值每秒变化很大,那么可能需要增大table_open_cache的值。
table_definition_cache通常简单设置为服务器中存在的表的数量,除非有上万张表。
针对InnoDB:
与MyISAM不同,InnoDB的opentable和openfile并无直接联系,即打开frm表时其相应的ibd文件可能处于关闭状态;故InnoDB只会用到table_definiton_cache,不会使用table_open_cache;其frm文件保存于table_definition_cache中,而idb则由innodb_open_files决定(前提是开启了innodb_file_per_table),最好将innodb_open_files设置得足够大,使得服务器可以保持所有的.ibd文件同时打开。
5、最大连接数
showglobalstatuslike'Max_used_connections';
我们设置的max_connections大小
showvariableslike'max_connections%';
通常max_connections的大小应该设置为比Max_used_connections状态值大,Max_used_connections状态值反映服务器连接在某个时间段是否有尖峰,如果该值大于max_connections值,代表客户端至少被拒绝了一次,可以简单地设置为符合以下条件:
Max_used_connections/max_connections=0.8
6、Innodb缓存
showglobalstatuslike'innodb_buffer_pool_read%';
innodb_buffer_pool_reads:
平均每秒从物理磁盘读取页的次数
innodb_buffer_pool_read_requests:
平均每秒从innodb缓冲池的读次数(逻辑读请求数)
innodb_buffer_pool_write_requests:
平均每秒向innodb缓冲池的写次数
innodb_buffer_pool_pages_dirty:
平均每秒innodb缓存池中脏页的数目
innodb_buffer_pool_pages_flushed:
平均每秒innodb缓存池中刷新页请求的数目
innodb缓冲池的读命中率
innodb_buffer_read_hit_ratio=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100
Innodb缓冲池的利用率
Innodb_buffer_usage=(1-Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total)*10
缓冲池命中率:
(Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_reads)=99.994%
如果该值小于99.9%,建议就应该增大innodb_buffer_pool_size的值了,该值一般设置为内存总大小的75%-85%,或者计算出操作系统所需缓存+mysql每个连接所需的内存(例如排序缓冲和临时表)+MyISAM键缓存,剩下的内存都给innodb_buffer_pool_size,不过也不宜设置太大,会造成内存的频繁交换,预热和关闭时间长等问题。
7、MyISAMKeyBuffer缓存
showglobalstatuslike'key_%';
showvariableslike'%key_cache_block_size%';
showvariableslike'%key_buffer_size%';
缓冲区的使用率=1-(Key_blocks_unused*key_cache_block_size/key_buffer_size)=18.6%
读命中率=1-Key_reads/Key_read_requests=99.98%
写命中率=1-Key_writes/Key_write_requests=99.05%
可看到缓冲区的使用率并不高,如果很长一段时间后还没有使用完所有的键缓冲,可以考虑把缓冲区调小一点。
键缓存命中率可能意义不大,因为它和应用相关,有些应用在95%的命中率下就工作良好,有些则需要99.99%,所以从经验上看,每秒的缓存未命中次数更重要,假设一个独立磁盘每秒能做100个随机读,那么每秒有5个缓冲未命中可能不会导致I/O繁忙,但每秒80个就可能出现问题。
每秒缓存未命中=Key_reads/uptime=0.33
key_read_requests:
MyISAM平均每秒钟从缓冲池中的读取次数
Key_write_requests:
MyISAM平均每秒钟从缓冲池中的写入次数
key_reads:
MyISAM平均每秒钟从硬盘上读取的次数
key_writes:
MyISAM平均每秒钟从硬盘上写入的次数
8、临时表使用情况
showglobalstatuslike'Created_tmp%';
showvariableslike'%tmp_table_size%';
Created_tmp_disk_tables:
服务器执行语句时在硬盘上自动创建的临时表的数量
Created_tmp_tables:
服务器执行语句时自动创建的内存中的临时表的数量
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%,如果Created_tmp_tables值比较大,可能是排序句子过多或者连接句子不够优化
比例占到0.34,证明数据库应用中排序,join语句涉及的数据量太大,需要优化SQL或者增大tmp_table_size的值,我设的是64M。
该比值应该控制在0.2以内。
9、binlogcache使用情况
showstatuslike'Binlog_cache%';
showvariableslike'binlog_cache_size';
Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数
Binlog_cache_use表示用binlog_cache_size缓存的次数
当对应的Binlog_cache_disk_use值比较大的时候我们可以考虑适当的调高binlog_cache_size对应的值
10、Innodb日志设置
showvariableslike'%innodb_log_buffer_size%';
showstatuslike'innodb_log_waits';
innodb_os_log_fsyncs:
平均每秒向日志文件完成的fsync()写数量
innodb_os_log_written:
平均每秒写入日志文件的字节数
innodb_log_writes:
平均每秒向日志文件的物理写次数
innodb_log_write_requests:
平均每秒日志写请求数
innodb_log_buffer_size我设置了8M,应该足够大了;Innodb_log_waits表示因logbuffer不足导致等待的次数,如果该值不为0,可以适当增大innodb_log_buffer_size的值。
11、表扫描情况判断
showglobalstatuslike'Handler_read%';
Handler_read_first:
使用索引扫描的次数,该值大小说不清系统性能是好是坏
Handler_read_key:
通过key进行查询的次数,该值越大证明系统性能越好
Handler_read_next:
使用索引进行排序的次数
Handler_read_prev:
此选项表明在进行索引扫描时,按照索引倒序从数据文件里取数据的次数,一般就是ORDERBY...DESC
Handler_read_rnd:
该值越大证明系统中有大量的没有使用索引进行排序的操作,或者join时没有使用到index
Handler_read_rnd_next:
使用数据文件进行扫描的次数,该值越大证明有大量的全表扫描,或者合理地创建索引,没有很好地利用已经建立好的索引
12、innodb行等其他参数
showglobalstatuslike'Innodb_buffer_pool_wait_free';
该值不为0表示bufferpool没有空闲的空间了,可能原因是innodb_buffer_pool_size设置太大,可以适当减少该值。
innodb_rows_deleted:
平均每秒从innodb表删除的行数
innodb_rows_inserted:
平均每秒从innodb表插入的行数
innodb_rows_read:
平均每秒从innodb表读取的行数
innodb_rows_updated:
平均每秒从innodb表更新的行数
innodb_row_lock_waits:
一行锁定必须等待的时间数
innodb_row_lock_time:
行锁定花费的总时间,单位毫秒
innodb_row_lock_time_avg:
行锁定的平均时间,单位毫秒
innodb_data_reads:
innodb平均每秒从文件中读取的次数
innodb_data_writes:
innodb平均每秒从文件中写入的次数
innodb_data_fsyncs:
innodb平均每秒进行fsync()操作的次
innodb_data_read:
innodb平均每秒钟读取的数据量,单位为KB
innodb_data_written:
innodb平均每秒钟写入的数据量,单位为KB
13、join操作信息
showglobalstatuslike'select_full_join';
该值表示在join操作中没有使用到索引的次数,值很大说明join语句写得很有问题
showglobalstatuslike'select_range';
该值表示第一个表使用ranges的join数量,该值很大说明join写得没有问题,通常可查看select_full_join和select_range的比值来判断系统中join语句的性能情况
showglobalstatuslike'Select_range_check';
如果该值不为0需要检查表的索引是否合理
14、慢查询
showglobalstatuslike'Slow_queries';
该值表示mysql启动以来的慢查询个数,即执行时间超过long_query_time的次数,可根据Slow_queries/uptime的比值判断单位时间内的慢查询个数,进而判断系统的性能。
15、表锁/从数据库等其他
1、表锁
showglobalstatuslike'table_lock%';
这两个值的比值:
Table_locks_waited/Table_locks_immediate趋向于0,如果值比较大则表示系统的锁阻塞情况比较严重
2、从数据库
可以在slave节点上执行showslavestatus\G命令,其中Seconds_Behind_Master项的值即为slave当前的延时量
16、源代码:
#-*-encoding:
utf8-*-
#from__future__importdivision
importsys
importpymysql
#数据库配置参数
host='192.168.0.20'
user='lepus_user'
password='lepus_user'
db='mca_crm'
#----------------------------------------------------------------------
defgetConn(host,user,passwd,db='mysql',port=3306,charset=''):
try:
conn=pymysql.connect(host=host,user=user,passwd=passwd,db=db,port=port,charset=charset)
returnconn
exceptpymysql.Error,e:
print"Error%d:
%s"%(e.args[0],e.args[1])
sys.exit
(1)
#----------------------------------------------------------------------
defcloseConn(conn):
"""关闭mysqlconnection"""
conn.close()
#----------------------------------------------------------------------
defgetValue(conn,query):
"""查询相关参数返回一个值"""
cursor=conn.cursor()
getNum=cursor.execute(query)
ifgetNum>0:
result=cursor.fetchone()
else:
result=['0']
returnint(result[1])
defgetQuery(conn,query):
"""查询相关参数返回多个值"""
cursor=conn.cursor()
cursor.execute(query)
result=cursor.fetchall()
returnresult
#执行查询的总次数
Questions="showglobalstatuslike'Questions'"
#服务器已经运行的时间(以秒为单位)
Uptime="showglobalstatuslike'Uptime'"
Com_commit="showglobalstatuslike'Com_commit'"
Com_rollback="showglobalstatuslike'Com_rollback'"
#从硬盘读取键的数据块的次数。
如果Key_reads较大,则Key_buffer_size值可能太小。
#可以用Key_reads/Key_read_requests计算缓存损失率
Key_reads="showglobalstatuslike'Key_reads'"
#从缓存读键的数据块的请求数
Key_read_requests="showglobalstatuslike'Key_read_requests'"
#向硬盘写入将键的数据块的物理写操作的次数
Key_writes="showglobalstatuslike'Key_writes'"
#将键的数据块写入缓存的请求数
Key_write_requests="showglobalstatuslike'Key_write_requests'"
#是否有innodb引擎,5.5版本后没有了该参数。
所以有特殊处理
Have_innodb="showglobalvariableslike'have_innodb'"
#不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。
Innodb_buffer_pool_reads="showglobalstatuslike'Innodb_buffer_pool_reads'"
#InnoDB已经完成的逻辑读请求数
Innodb_buffer_pool_read_requests="showglobalstatuslike'Innodb_buffer_pool_read_requests'"
#查询缓存被访问的次数
Qcache_hits="showglobalstatuslike'Qcache_hits'"
#加入到缓存的查询数量,缓存没有用到
Qcache_inserts="showglobalstatuslike'Qcache_inserts'"
#当前打开的表的数量
Open_tables="showglobalstatuslike'Open_tables'"
#已经打开的表的数量。
如果Opened_tables较大,table_cache值可能太小
Opened_tables="showglobalstatuslike'Opened_tables'"
#创建用来处理连接的线程数。
如果Threads_created较大,你可能要
#增加thread_cache_size值。
缓存访问率的计算方法Threads_created/Connections
Threads_created="showglobalstatuslike'Threads_created'"
#试图连接到(不管是否成功)MySQL服务器的连接数。
缓存访问率的计算方法Threads_created/Connections
Connections="showglobalstatuslike'Connections'"
#Com_select/s:
平均每秒select语句执行次数
#Com_insert/s:
平均每秒insert语句执行次数
#Com_update/s:
平均每秒update语句执行次数
#Com_delete/s:
平均每秒delete语句执行次数
Com_select="showglobalstatuslike'Com_select'"
Com_insert="showglobalstatuslike'Co