Percona监控MySQL模板详解

难道是刚上班时向日志表的插入量高?

InnoDB Memory Allocation

Total memory allocated 8824815616; in additional pool allocated 0
  • Total Mem Alloc
    InnoDB申请的总内存量,单位字节
  • Additional Pool Alloc
    分配给额外内存的总量,单位字节

项目上线后,有些用户反映登录变慢了。而且,只有上午八九点钟左右的时候慢,过了这一段时间就没有用户反映有问题。经过对比
AWR,发现变慢是不定时的,从 8 点开始,到 9
点左右为止,在半小时一次的报告中,偶尔会有那么一两份 AWR 会显示
BufferBusy Waits 比较高,然后就正常了。

InnoDB Semaphores Wait Time

  • InnoDB Sem Wait Time Ms
    显示当前正在等待互斥量的InnoDB线程的等待时间的总耗时(毫秒).
    分析:
    正常情况下,InnoDB Semaphores Wait Time和 InnoDB Semaphores
    Waits应该是空的.除非服务器运行着高并发的工作负载,它促使InnoDB采取让操作系统等待的措施.信息位于SHOW
    ENGINE INNODB STATUS的SEMAPHORES片段.
    相关php脚本代码部分如下:

    elseif (strpos($line, 'seconds the semaphore:') > 0) {
    # --Thread 907205 has waited at handler/ha_innodb.cc line 7156 for 1.00 seconds the semaphore:
    increment($results, 'innodb_sem_waits', 1);
    increment($results, 'innodb_sem_wait_time_ms', to_int($row[9]) * 1000);
    }
    

    其中innodb_sem_waits的值是多少,表示有多少线程在等待,而innodb_sem_wait_time_ms表示所有线程等待的时间,默认单位是秒,在脚本中乘以1000,所以监控图中的单位是毫秒.

  • 应对这个问题

    InnoDB采取多阶段等待策略.首先,尝试对锁进行循环等待.如果经过了一个预设的循环等待周期(innodb_sync_spin_loops

    30,当前配置文件默认为30次)之后还没有成功,就会退到更昂贵更复杂的等待阵列里,如果并发量太大的话,则导致系统负载突增.
    循环等待的成本相对比较低,但是需要不停地检查一个资源是否被锁定,消耗CPU周期,也就是说,当另外一条线程能处理事情时,循环等待也会独占处理器.
    循环等待的替换方案就是让操作系统做上下文切换(等待阵列),每秒钟几千次的切换会引发大量的系统开销.

  • 解决办法
    根据具体的应用,调整参数;或者优化应用,减少并发.

但无论如何, Buffer Busy Waits
是产生了,有可能以主机的硬件来论,现在已经是并发插入量的极限了。但奇怪的是,这种情况每天只会在刚上班后不久出现,其他时段正常。

InnoDB Transactions

显示了InnoDB事务相关的信息

  • InnoDB Transactions
    InnoDB内部的事务总数.由以下数值计算出:
    Trx id counter 89F56195 # 当前事务ID,每创建一个新事务就会累加

    Purge done for trx's n:o < 89F5609C undo n:o < 0 -- InnoDB清除旧版本MVCC时所用的事务ID.这个ID之前的老版本数据已经清除.
    

    该数值就是由当前事务ID减去清除旧数据的事务ID再由十六进制转成十进制的值.(参考ss_get_mysql_stats.php脚本902行)

  • History List
    历史记录的长度.位于InnoDB数据文件的撤销空间里的未清除事务的数目.当一个事务执行了更新并提交后,这个数字就会累加,当清除进程移除一个旧版本数据时,它就会递减.

看到这个情况,很容易让人认为是某个时间段有很多人一起在访问同一张表,其他时间又不一起访问了。究竟是不是这么回事呢?

InnoDB Tables In Use

# mysql tables in use 2, locked 2
  • InnoDB Tables In Use
    所有事务用到的表的数量
  • InnoDB Locked Tables
    所有事务锁定的表的数量

根据文件号、块号查找的结果来看,绝大多数的 Buffer Busy Waits
都出现在日志表上。

MySQL Transaction Handler

  • Handler Commit 提交一个事务的请求的个数
  • Handler Rollback 回滚一个事务的请求的个数
  • Handler Savepoint 创建一个事务保存点的请求的个数
  • Handler Savepoint Rollback 回滚到一个事务保存点的请求的个数.

但统计的结果显示,白天有好几个时段,日志表的插入量都很大,并不是早上上班时段特别大,有时下午还会比上午插入的稍多些,但没有发现下午日志表上有
Buffer
BusyWaits,下午也从来没人反映过慢,而且整库的压力上下午基本差不多。如果全天都有
Buffer Busy
Waits,我想我也会放弃进一步调查。但有时下午的插入量多,反而没有等待。那说明
ASSM
是足以支撑这个量级的并发插入的。想解决问题的话,第一步是定位问题,这我们都知道。可如何定位这个问题呢?

MySQL Connections

  • Max Connections 允许同时保持在打开状态的客户连接的最大个数
  • Max Used Connections 此前曾同时打开处于打开状态的连接的最大个数
  • Aborted Clients 因客户端没有正确地关闭而被丢弃的连接的个数
  • Aborted Connects 试图连接MySQL服务器但没有成功的次数
  • Threads Connectd 现在正处于打开状态的连接的个数
  • Connections 试图连接MySQL服务器的尝试次数

曾经遇到过这样的应用,要求对用户的登录、退出行为做记录。此部分的逻辑很简单,用户每登录一次应用,向数据库中一个日志表中插入一行,退出应用的时候再向日志表中插入一行。

InnoDB Checkpoint Age

  • Uncheckpointed Bytes
    显示了未写入磁盘的数据量.如果该值的大小接近innodb_log_file_size *澳门新葡亰3522平台游戏,
    n
    的总大小,则需要增加innodb_log_file_size的值,但是要注意,如果发生宕机,则需要更长的回复时间.(从redolog恢复)

先来确定一下等待是针对哪个对象。通过 V$SEGMENT_STATISTICS,查找
STATISTIC_NAME列为 buffer busy waits 的,或者,查看
V$ACTIVE_SESSION_HISTORY 中的历史等待事件,根据 P1、 P2
列的值,就可以定位争用是针对哪个对象的。

MySQL Binary/Relay Logs

  • Binlog Cache Use
    保存在二进制日志缓存里的事务的个数
  • Binlog Cache Disk Use
    超过binlog_cache_size设置的缓存大小,使用磁盘临时文件的事务的个数
  • Binlog Log Space
    二进制日志的大小
  • Relay Log Space
    中继日志的大小
    如果Binlog Cache Disk Use/Binlog Cache
    Use的值较大,那么应该尝试增加binlog_cache_size的大小.但是,也不要期望改善过多,如果写临时文件的数量从每秒1个减少到每分钟一个,这已经证明优化的足够好了.没必要耗费大量的内存,来处理binlog_cache_size中的事务.

此日志表是个日分区表,每天一个分区。每天大约会插入千万行,除了插入并发很高以外,就没有其他的大并发操作。另外,每天晚上会将当天的数据推送到数据仓库,在数据仓库中再进行分析、对比。

InnoDB Insert Buffer Usage

  • Ibuf Cell Count
    分段大小
  • Ibuf Used Cells
    插入缓冲区的大小
  • Ibuf Free Cells
    “自由列表”的长度

日志表每天分区的数据量最高接近千万行,就按每天 1000 万行算,除以 3600×
24,平均每秒 116
个并发插入。当然,还要考虑高低峰的问题,晚上应用基本上没什么人用的,这几百万行大部分都是白天插入的。所以,再乘个
2,每秒 232 的插入量,这是最高的了。也并不是很多,这点量和 Oracle 宣称的
ASSM 支持的高并发插入相比,应该不会有 Buffer Busy Waits。

MySQL Command Counts

命令计数器,显示了MySQL(在过去1秒内)执行各种命令的次数

  • Questions
    记录了服务器收到的查询和命令的总数.(Com_*变量的总数不一定相等.)
  • Com Select
  • Com Delete
  • Com Insert
  • Com Update
  • Com Replace
  • Com Load
  • Com Delete Multi
  • Com Insert Select
  • Com Update Multi
  • Com Replace Select

这个案例很有代表性,如果不深入到细节中,很容易在中途得出错误的结论。下面详细描述思考过程,希望能给大家带来些启发。

MySQL Query Cache Memory

MySQL Query Cache

MySQL Threads

  • Thread Cache Size
    线程缓存所能容纳的线程的最大个数.断开的mysql连接会放到这个缓存里,新建立的连接就会重复使用它们而不创建新的线程.
    如果缓存中有自由的线程,MySQL就能很快的响应连接请求,不必为每个连接都创建新的线程.每个在缓存中的线程通常消耗256KB内存.
  • Thread Created 为处理连接创建的线程总数

InnoDB Buffer Pool

  • Pool Size InnoDB缓冲池的页数量,每页大小16K
  • Database Pages 数据页大小
  • Free Pages 空闲页大小
  • Modified Pages “脏”数据页.如果脏数据页太多,则需要检查磁盘IO状态.

InnoDB Row Lock Time

  • InnoDB Row Lock Time
    该模板读取的Innodb_row_lock_time状态变量,表示InnoDB引擎在每次申请数据行锁定时等待的总时间(以毫秒为单位).

MySQL Processlist

  • State Closing Tables
    该线程将已更改的表数据刷新到磁盘,并关闭已使用的表。这应该是一个快速的操作。如果没有,请确认您没有一个完整的磁盘,并且磁盘没有非常重的使用。

  • State Copying To Tmp Table
    服务器正在复制到内存中的临时表

  • State End
    这发生创建视图、删除、插入、查询或更新语句之最后,但是在修改ALTER
    TABLE之前

  • State Freeing Items
    线程执行了一个命令。在此状态下完成的一些项目将涉及查询缓存。这个状态通常会被清理干净

  • State Init
    这发生在ALTER
    TABLE、DELETE、INSERT、SELECT或UPDATE语句的初始化之前。在这个状态下,服务器所采取的操作包括刷新二进制日志、InnoDB日志和一些查询缓存清理操作。
    对于最终状态,以下操作可能会发生:
    删除表中的数据之后删除查询缓存条目
    将事件写入二进制日志
    释放内存缓冲区,包括blob

  • State Locked
    查询被另一个查询锁定。
    在MySQL
    5.5.3中,这个状态被删除了,因为它相当于表锁状态,不再出现在SHOW
    PROCESSLIST输出中。

  • State Login
    登录
    连接线程的初始状态,直到客户机成功地进行了身份验证。

  • State Preparing
    这个状态发生在查询优化期间。

  • State Reading From Net
    服务器正在从网络读取一个数据包。

  • State Sengding Data
    线程正在读取和处理SELECT语句的行,并将数据发送给客户机。因为在这个状态期间发生的操作往往会执行大量的磁盘访问(读),所以在给定查询的生命周期中,它通常是运行时间最长的状态。

  • State Sorting Result
    对于SELECT语句,这类似于创建排序索引,但对于非临时表

  • State Statistics
    服务器正在计算统计数据,以开发查询执行计划。如果一个线程在这个状态中存在很长时间,那么服务器可能会执行其他的工作。

  • State Updating
    线程正在寻找更新的行,并正在更新它们

  • State Writing To Net
    服务器正在向网络写入一个包。

  • State None
    什么都没有的,空的,注意不是NULL状态

  • State Other
    其他

MySQL Select Types

  • Select Full
    Join没有使用索引而完成的多表联接操作的次数.这种情况是性能杀手,最好去优化sql.
  • Select Full Range Join利用一个辅助性的参照表(reference
    table)上的区间搜索(range
    search)操作而完成的多数据表联接操作的次数.
    该值表示使用了范围查询联接表的次数.
  • Select
    Range利用第一个数据表上的某个区间而完成的多数据表联接操作的次数.
  • Select Range
    Check该变量记录了在联接时,对每一行数据重新检查索引的查询计划的数量,它的开销很大.
    如果该值较高或正在增加,说明一些查询没有找到好索引.
  • Select
    Scan通过对第一个数据表进行全表扫描而完成的多数据表联接操作的次数.

InnoDB I/O

  • File Reads 显示每秒文件的读次数
  • File Writes 显示每秒文件的写次数
  • Log Writes 写日志的次数
  • File Fsyncs
    调用fsync()函数的次数.与innodb_flush_log_at_trx_commit值的设置有关.

MySQL Query Response Time (Microseconds)

Percona文档

MyISAM Key Cache

  • Key Buffer Size
    键缓存大小
  • Key Buf Bytes Used
    同Key_blocks_used变量
    键缓存里已经被使用的缓存块的个数
  • Key Buf Bytes Unused
    同Key_blocks_unused
    键缓存里尚未被使用过的缓存块的个数

MyISAM Indexs

显示了在MyISAM索引上的读写情况

  • Key Reads Requests
    从键缓存读出索引块的读操作的次数
  • Key Reads
    从磁盘读出索引块的读操作次数
  • Key Write Requests
    向键缓存写一个索引块的请求的个数
  • Key Writes
    把索引块写入磁盘的写操作的次数