MySQL数据库监控

　　MYSQL常用的性能指标
　　（1）QPS（每秒Query量）
　　QPSQuestions（orQueries）seconds
　　mysqlshowglobalstatuslikeQuestion；
　　（2）TPS（每秒事务量）
　　TPS（ComcommitComrollback）seconds
　　mysqlshowglobalstatuslikeComcommit；
　　mysqlshowglobalstatuslikeComrollback；
　　（3）keyBuffer命中率
　　mysqlshowglobalstatuslikekey；
　　keybufferreadhits（1keyreadskeyreadrequests）100
　　keybufferwritehits（1keywriteskeywriterequests）100
　　（4）InnoDBBuffer命中率
　　mysqlshowstatuslikeinnodbbufferpoolread；
　　innodbbufferreadhits（1innodbbufferpoolreadsinnodbbufferpoolreadrequests）100
　　（5）QueryCache命中率
　　mysqlshowstatuslikeQcache；
　　Querycachehits（Qcahcehits（QcachehitsQcacheinserts））100；
　　（6）TableCache状态量
　　mysqlshowglobalstatuslikeopen；
　　比较opentables与opendtables值
　　（7）ThreadCache命中率
　　mysqlshowglobalstatuslikeThread；
　　mysqlshowglobalstatuslikeConnections；
　　Threadcachehits（1Threadscreatedconnections）100
　　（8）锁定状态
　　mysqlshowglobalstatuslikelock；
　　TablelockswaitedTablelocksimmediate0。3如果这个比值比较大的话，说明表锁造成的阻塞比较严重
　　Innodbrowlockwaitsinnodb行锁，太大可能是间隙锁造成的
　　（9）复制延时量
　　mysqlshowslavestatus
　　查看延时时间
　　（10）TmpTable状况（临时表状况）
　　mysqlshowstatuslikeCreatetmp；
　　CreatedtmpdisktablesCreatedtmptables比值最好不要超过10，如果Createdtmptables值比较大，
　　可能是排序句子过多或者是连接句子不够优化
　　（11）BinlogCache使用状况
　　mysqlshowstatuslikeBinlogcache；
　　如果Binlogcachediskuse值不为0，可能需要调大binlogcachesize大小
　　（12）Innodblogwaits量
　　mysqlshowstatuslikeinnodblogwaits；
　　Innodblogwaits值不等于0的话，表明innodblogbuffer因为空间不足而等待
　　（13）openfileandtable
　　mysqlmysqleshowglobalstatuslikeOpenfiles；
　　mysqlmysqleshowglobalstatuslikeOpentables；
　　（14）慢查询
　　开启慢查询：
　　编辑etcmy。cnf，在〔mysqld〕域中添加：
　　slowquerylog1开启慢查询
　　slowquerylogfiledatamysqlslow。log慢查询日志路径
　　longquerytime1慢查询的时长
　　查看慢查询数量：
　　执行mysqladminstatus
　　（15）全日志
　　查看全日志：
　　showglobalvariableslikegenerallog；
　　开启全日志：
　　setglobalgenerallogon；
　　注意开启全日志会消耗服务器性能，一般只有在排查问题时才会短暂打开。
　　分割线
　　我们需要根据自己的情况进行配置优化，好的做法是MySQL服务器稳定运行了一段时间后，根据服务器的状态再进行优化。
　　数据库性能状态QueryCache命中率
　　Querycachehits（Qcahcehits（QcachehitsQcacheinserts））100；
　　showstatuslikeQcache；
　　QPS（每秒Query量）
　　QPSQuestions（orQueries）seconds
　　showglobalstatuslikeQuestion；
　　TPS（每秒事务量）
　　TPS（ComcommitComrollback）seconds
　　showstatuslikeComcommit；
　　showstatuslikeComrollback；
　　（3）keyBuffer命中率
　　keybufferreadhits（1keyreadskeyreadrequests）100
　　keybufferwritehits（1keywriteskeywriterequests）100
　　showstatuslikekey；
　　（4）InnoDBBuffer命中率
　　innodbbufferreadhits（1innodbbufferpoolreadsinnodbbufferpoolreadrequests）100
　　showstatuslikeinnodbbufferpoolread；
　　（5）QueryCache命中率
　　Querycachehits（Qcahcehits（QcachehitsQcacheinserts））100；
　　showstatuslikeQcache；
　　（6）TableCache状态量
　　showstatuslikeopen；
　　（7）ThreadCache命中率
　　Threadcachehits（1Threadscreatedconnections）100
　　showstatuslikeThread；
　　showstatuslikeConnections；
　　（8）锁定状态
　　showstatuslikelock；
　　（9）复制延时量
　　showslavestatus；
　　（10）TmpTable状况（临时表状况）
　　showstatuslikeCreatedtmp；
　　（11）BinlogCache使用状况
　　showstatuslikeBinlogcache；
　　（12）Innodblogwaits量
　　showstatuslikeinnodblogwaits；
　　MySQL5。7的新特性之一就是sys库，sys库完成MySQL信息的收集一、用户、连接类
　　1、查看每个客户端IP过来的连接消耗资源情况。
　　selectfromsys。hostsummary；
　　totalconnections：
　　currentconnections：当前连接数
　　statementlatency：声明延迟
　　statementavglatency：声明平均延迟
　　tablescans：扫描查询的总次数
　　fileios：
　　fileiolatency：
　　currentmemory：
　　totalmemoryallocated：
　　uniqueusers：
　　2、查看每个用户消耗资源情况
　　selectfromsys。usersummary；
　　user：
　　statements：
　　statementlatency：
　　statementavglatency：
　　tablescans：
　　fileios：
　　fileiolatency：
　　currentconnections：
　　totalconnections：
　　uniquehosts：
　　currentmemory：
　　totalmemoryallocated：
　　3、查看当前连接情况（有多少连接就应该有多少行）
　　selecthost，currentconnections，statementsfromsys。hostsummary；
　　4、查看当前正在执行的SQL
　　selectconnid，pid，user，db，command，currentstatement，laststatement，time，locklatencyfromsys。session
　　执行showfullprocesslist的结果
　　二、SQL和io类
　　1、查看发生IO请求前5名的文件。
　　selectfromsys。ioglobalbyfilebybytesorderbytotaldesclimit5；
　　三、bufferpool、内存
　　1、查看总共分配了多少内存
　　selectfromsys。memoryglobaltotal；
　　selectfromsys。memoryglobalbycurrentbytes；
　　2、每个库（database）占用多少bufferpool
　　selectfromsys。innodbbufferstatsbyschemaorderbyallocateddesc；
　　pages是指在bufferpool中的page数量；
　　pagesold指在LUR列表中处于后37位置的page。
　　当出现bufferpage不够用时，就会征用这些page所占的空间。
　　37是默认位置，具体可以自定义。
　　3、统计每张表具体在InnoDB中具体的情况，比如占多少页？
　　注意和前面的pages的总数都是相等的，也可以借用sum（pages）运算验证一下。
　　selectfromsys。innodbbufferstatsbytable；
　　4、查询每个连接分配了多少内存
　　利用session表和memorybythreadbycurrentbytes分配表进行关联查询。
　　SELECTb。USER，currentcountused，currentallocated，currentavgalloc，currentmaxalloc，totalallocated，currentstatementFROMsys。memorybythreadbycurrentbytesa，sys。SESSIONbWHEREa。threadidb。thdid；
　　四、字段、索引、锁
　　1、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考。
　　selectfromsys。schemaautoincrementcolumns；
　　2、MySQL索引使用情况统计
　　selectfromsys。schemaindexstatisticsorderbyrowsselecteddesc；
　　3、MySQL中有哪些冗余索引和无用索引
　　若库中展示没有冗余索引，则没有数据；当有联合索引idxabc（a，b，c）和idxa（a），那么idxa就算冗余索引了。
　　selectfromsys。schemaredundantindexes；
　　4、查看INNODB锁信息
　　在未来的版本将被移除，可以采用其他方式
　　selectfromsys。innodblockwaits
　　5、查看库级别的锁信息，这个需要先打开MDL锁的监控：
　　打开MDL锁监控
　　updateperformanceschema。setupinstrumentssetenabledYES，TIMEDYESwherenamewaitlockmetadatasqlmdl；
　　selectfromsys。schematablelockwaits；五、线程类
　　1、MySQL内部有多个线程在运行，线程类型及数量
　　selectuser，count（）fromsys。processlistgroupbyuser；
　　六、主键自增
　　查看MySQL自增id的使用情况
　　SELECTtableschema，tablename，ENGINE，AutoincrementFROMinformationschema。TABLESWHERETABLESCHEMANOTIN（INFORMATIONSCHEMA，PERFORMANCESCHEMA，MYSQL，SYS）
　　一、对数据库服务可用性进行监控
　　思路：
　　1。1通过测试账号ping命令返回的信息判断数据库可以通过网络连接
　　usrbinmysqladminurootp123456ping
　　mysqldisalive
　　1。2确认数据库是否可读写
　　a。检查数据库的readonly参数是否为off
　　mysqlurootp123456P3306eshowglobalvariableslikereadonlygrepreadonly
　　readonlyOFF
　　b。执行简单的数据库查询，如：selectversion；
　　mysqlurootp123456P3306eselectversion
　　二、对数据库性能进行监控
　　2。1监控数据库连接数可用性
　　a。数据库最大连接数
　　mysqlurootp123456eshowvariableslikemaxconnections
　　b。数据库当前打开的连接数
　　mysqladminurootp123456extendedstatusgrepwThreadsconnected
　　注：如何计算当前打开的连接数占用最大连接数的比例呢？
　　resultThreadsconnectedmaxconnections，在做监控报警或可视化监控时能够很好的根据这个比例及时调整最大连接数。
　　2。2数据库性能监控
　　a。QPS：每秒的查询数
　　QPS计算方法
　　QuestionsSHOWGLOBALSTATUSLIKEQuestions；
　　UptimeSHOWGLOBALSTATUSLIKEUptime；
　　QPSQuestionsUptime
　　b。TPS：每秒的事物量（commit与rollback的之和）
　　TPS计算方法
　　ComcommitSHOWGLOBALSTATUSLIKEComcommit；
　　ComrollbackSHOWGLOBALSTATUSLIKEComrollback；
　　UptimeSHOWGLOBALSTATUSLIKEUptime；
　　TPS（ComcommitComrollback）Uptime
　　2。3数据库并发请求数量
　　SHOWGLOBALSTATUSLIKEThreadsrunning；
　　注：并发请求数量通常会远小于同一时间内连接到数据库的连接数量。
　　2。4监控innodb阻塞情况
　　a。innodb
　　三、对主从复制进行监控
　　3。1主从复制链路状态的监控
　　3。2主从复制延迟时间的监控
　　3。3定期确认主从复制的数据是否一致