Python实现监控MySQL性能指标.docx

1、Python实现监控MySQL性能指标Python实现监控MySQL性能指标 华仔目录1、 QPS计算(每秒查询数) 22、 TPS计算(每秒事务数) 33、线程连接数和命中率 34、表缓存 35、最大连接数 46、 Innodb 缓存 47、 MyISAM Key Buffer缓存 48、临时表使用情况 59、 binlog cache使用情况 510、 Innodb日志设置 511、表扫描情况判断 612、 innodb行等其他参数 613、 join操作信息 614、慢查询 715、表锁/从数据库等其他 716、源代码： 817、结束语 12本文旨在详细了解MYSQL主要性能指标的计算原

2、理，深入了解各项参数设置，以及通过Python实现各项性能指标的计算，监控MySQL运行情况，达到优化MySQL的目的。Python运行结果如下（）：1、QPS计算(每秒查询数)针对MyISAM引擎为主的DBshow GLOBAL status like questions;show global status like uptime;QPS=questions/uptime mysql自启动以来的平均QPS，如果要计算某一时间段内的QPS，可在高峰期间获取间隔时间t2-t1，然后分别计算出t2和t1时刻的q值，QPS=(q2-q1)/(t2-t1)针对InnnoDB引擎为主的DBshow g

3、lobal status like com_update;show global status like com_select;show global status like com_delete;show global status like uptime;QPS=(com_update+com_insert+com_delete+com_select)/uptimeCom_select/s：平均每秒select语句执行次数Com_insert/s：平均每秒insert语句执行次数Com_update/s：平均每秒update语句执行次数Com_delete/s：平均每秒delete语句执行

4、次数2、 TPS计算(每秒事务数)show global status like com_commit;show global status like com_rollback;show global status like uptime;TPS=(com_commit+com_rollback)/uptime3、线程连接数和命中率show global status like threads_%;| Threads_cached | 480 | /代表当前此时此刻线程缓存中有多少空闲线程| Threads_connected | 153 | /代表当前已建立连接的数量，因为一个连接就需要一个

5、线程，所以也可以看成当前被使用的线程数| Threads_created | 20344 | /代表从最近一次服务启动，已创建线程的数量| Threads_running | 2 | /代表当前激活的（非睡眠状态）线程数show global status like Connections;线程缓存命中率=1-Threads_created/Connections = 99.994%show variables like %thread_cache_size%;根据Threads_connected可预估thread_cache_size值应该设置多大，一般来说250是一个不错的上限值，如果内

6、存足够大，也可以设置成thread_cache_size值和threaads_connected值相同；或者通过观察threads_created值，如果该值很大或一直在增长，可以适当增加thread_cache_size的值；在休眠状态下每个线程大概占用256KB左右的内存，所以当内存足够时，设置太小也不会节约太多内存，除非该值已经超过几千。4、表缓存show global status like open_tables%;我们设置的打开表的缓存和表定义缓存show variables like table_open_cache;show variables like table_defi%

7、;针对MyISAM：mysql每打开一个表，都会读入一些数据到table_open_cache缓存中，当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取，所以该值要设置得足够大以避免需要重新打开和重新解析表的定义，一般设置为max_connections的10倍，但最好保持在10000以内。还有种依据就是根据状态open_tables的值进行设置，如果发现open_tables的值每秒变化很大，那么可能需要增大table_open_cache的值。table_definition_cache通常简单设置为服务器中存在的表的数量，除非有上万张表。针对InnoDB：与MyISAM不同

8、，InnoDB的open table和open file并无直接联系，即打开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、最大连接数show global status like Max_used_connect

9、ions;我们设置的max_connections大小show variables like max_connections%;通常max_connections的大小应该设置为比Max_used_connections状态值大，Max_used_connections状态值反映服务器连接在某个时间段是否有尖峰，如果该值大于max_connections值，代表客户端至少被拒绝了一次，可以简单地设置为符合以下条件：Max_used_connections/max_connections=0.86、Innodb缓存show global status like innodb_buffer_poo

10、l_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_rea

11、d_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100Innodb缓冲池的利用率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

12、+ Innodb_buffer_pool_reads)=99.994%如果该值小于99.9%，建议就应该增大innodb_buffer_pool_size的值了，该值一般设置为内存总大小的75%-85%，或者计算出操作系统所需缓存+mysql每个连接所需的内存（例如排序缓冲和临时表）+MyISAM键缓存，剩下的内存都给innodb_buffer_pool_size，不过也不宜设置太大，会造成内存的频繁交换，预热和关闭时间长等问题。7、MyISAM Key Buffer缓存show global status like key_%;show variables like %key_cache_b

13、lock_size%;show variables like %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%的命中率下就工作良好，

14、有些则需要99.99%，所以从经验上看，每秒的缓存未命中次数更重要，假设一个独立磁盘每秒能做100个随机读，那么每秒有5个缓冲未命中可能不会导致I/O繁忙，但每秒80个就可能出现问题。每秒缓存未命中=Key_reads/uptime=0.33key_read_requests: MyISAM平均每秒钟从缓冲池中的读取次数Key_write_requests: MyISAM平均每秒钟从缓冲池中的写入次数key_reads : MyISAM平均每秒钟从硬盘上读取的次数key_writes : MyISAM平均每秒钟从硬盘上写入的次数8、临时表使用情况show global status like

15、Created_tmp%;show variables like %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的

16、值，我设的是64M。该比值应该控制在0.2以内。9、binlog cache使用情况show status like Binlog_cache%; show variables like 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日志设置

17、show variables like %innodb_log_buffer_size%;show status like 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表示因log buffer不足导致

18、等待的次数，如果该值不为0，可以适当增大innodb_log_buffer_size的值。11、表扫描情况判断show global status like Handler_read%;Handler_read_first：使用索引扫描的次数，该值大小说不清系统性能是好是坏Handler_read_key：通过key进行查询的次数，该值越大证明系统性能越好Handler_read_next：使用索引进行排序的次数Handler_read_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY . DESCHandler_read_rnd：该值越大证

19、明系统中有大量的没有使用索引进行排序的操作，或者join时没有使用到indexHandler_read_rnd_next：使用数据文件进行扫描的次数，该值越大证明有大量的全表扫描，或者合理地创建索引，没有很好地利用已经建立好的索引12、innodb行等其他参数show global status like Innodb_buffer_pool_wait_free;该值不为0表示buffer pool没有空闲的空间了，可能原因是innodb_buffer_pool_size设置太大，可以适当减少该值。innodb_rows_deleted: 平均每秒从innodb表删除的行数innodb_row

20、s_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_f

21、syncs：innodb平均每秒进行fsync()操作的次innodb_data_read：innodb平均每秒钟读取的数据量，单位为KBinnodb_data_written：innodb平均每秒钟写入的数据量，单位为KB13、 join操作信息show global status like select_full_join;该值表示在join操作中没有使用到索引的次数，值很大说明join语句写得很有问题show global status like select_range;该值表示第一个表使用ranges的join数量，该值很大说明join写得没有问题，通常可查看select_full_

22、join和select_range的比值来判断系统中join语句的性能情况show global status like Select_range_check;如果该值不为0需要检查表的索引是否合理14、慢查询show global status like Slow_queries;该值表示mysql启动以来的慢查询个数，即执行时间超过long_query_time的次数，可根据Slow_queries/uptime的比值判断单位时间内的慢查询个数，进而判断系统的性能。15、表锁/从数据库等其他1、表锁show global status like table_lock%;这两个值的比值：Ta

23、ble_locks_waited /Table_locks_immediate趋向于0，如果值比较大则表示系统的锁阻塞情况比较严重 2、从数据库可以在slave节点上执行show slave statusG命令,其中Seconds_Behind_Master项的值即为slave当前的延时量16、源代码：# -*- encoding: utf8 -*-#from _future_ import divisionimport sysimportpymysql# 数据库配置参数host = 192.168.0.20user = lepus_userpassword = lepus_userdb =

24、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)return connexceptpymysql.Error, e:print Error %d: %s % (e.args0, e.args1)sys.exit(1)#-defcloseConn(conn): 关闭mysql connectionconn.cl

25、ose()#-defgetValue(conn, query): 查询相关参数返回一个值 cursor = conn.cursor()getNum=cursor.execute(query)ifgetNum0:result = cursor.fetchone()else:result=0returnint(result1)defgetQuery(conn, query): 查询相关参数返回多个值 cursor = conn.cursor()cursor.execute(query)result = cursor.fetchall()return result#执行查询的总次数Questions

26、 = show global status like Questions#服务器已经运行的时间（以秒为单位）Uptime = show global status like UptimeCom_commit = show global status like Com_commitCom_rollback = show global status like Com_rollback#从硬盘读取键的数据块的次数。如果Key_reads较大，则Key_buffer_size值可能太小。#可以用Key_reads/Key_read_requests计算缓存损失率Key_reads = show glo

27、bal status like Key_reads#从缓存读键的数据块的请求数Key_read_requests = show global status like Key_read_requests#向硬盘写入将键的数据块的物理写操作的次数Key_writes = show global status like Key_writes#将键的数据块写入缓存的请求数Key_write_requests = show global status like Key_write_requests#是否有innodb引擎，5.5版本后没有了该参数。所以有特殊处理Have_innodb = show gl

28、obal variables like have_innodb#不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。Innodb_buffer_pool_reads = show global status like Innodb_buffer_pool_reads#InnoDB已经完成的逻辑读请求数Innodb_buffer_pool_read_requests = show global status like Innodb_buffer_pool_read_requests#查询缓存被访问的次数Qcache_hits = show global status like Qcache_

29、hits#加入到缓存的查询数量，缓存没有用到Qcache_inserts = show global status like Qcache_inserts#当前打开的表的数量Open_tables = show global status like Open_tables#已经打开的表的数量。如果Opened_tables较大，table_cache值可能太小Opened_tables = show global status like Opened_tables#创建用来处理连接的线程数。如果Threads_created较大，你可能要#增加thread_cache_size值。缓存访问率的

30、计算方法Threads_created/ConnectionsThreads_created = show global status like Threads_created#试图连接到(不管是否成功)MySQL服务器的连接数。缓存访问率的计算方法Threads_created/ConnectionsConnections = show global status like Connections#Com_select/s：平均每秒select语句执行次数#Com_insert/s：平均每秒insert语句执行次数#Com_update/s：平均每秒update语句执行次数#Com_delete/s：平均每秒delete语句执行次数Com_select = show global status like Com_selectCom_insert = show global status like Co