转 zabbix+percona监控MySQL之各个监控项
### sample 1
感谢Adley_zk 写的中文翻译 非常棒
https://blog.csdn.net/Adley_zk/article/details/107027968
最新数据监控项
Aborted Clients
因客户端没有正确地关闭而被丢弃的连接的个数,数字增大意味着有客户端成功建立连接但很快就断开或者被终止,一般发生在网络不稳当的环境中,主要可能性又:1、客户端没有主动关闭mysql连接mysql_close() 2、wait_timeout设置时间短被mysql干掉 3、客户端由于某些原因被干掉
Aborted Connects
试图连接MySQL服务器但没有成功的次数,当有大量链接连接不上mysql的时候数值激增,主要原因:没有授权或者密码不对,一般错误日志会有改提示(access denied for ‘user’@‘host’) 2、连接数满了,一般错误包含(too many cononection) 3、超过了链接时间限制,主要有这个参数控制(connect_timeout),mysql默认是10s,基本除非网络极端不好,一般不会超时
Active Transactions
正在执行的事务数量
Adaptive Hash Memory
自适应哈希索引占用的内存大小
Additional Pool Alloc
分配给额外内存的总量,单位字节
Binary Log Space
二进制日志的大小
Binlog Cache Disk Use
超过binlog_cache_size设置的缓存大小,使用磁盘临时文件的事务的个数
Binlog Cache Use
保存在二进制日志缓存里的事务的个数
Bytes Received
收到字节数
Bytes Sent
发送字节数
Com Delete
过去的一秒内执行删除命令的次数
Com Delete Multi
过去的一秒内执行多重删除命令的次数
Com Insert
过去的一秒内执行插入语句的命令次数
Com Insert Select
过去的一秒内执行查询插入命令的次数
Com Load
过去的一秒内执行load命令的次数
Com Replace
过去的一秒内执行replace命令的次数
Com Replace Select
过去的一秒内执行replace select的次数
Com Select
过去的一秒内执行select命令的次数
Com Update
过去的一秒内执行update命令的次数
Com Update Multi
过去的一秒内执行多重更新命令的次数
Connections
所有尝试连接到mysql server的连接数,关键时不管成功还是失败。所以这个数值的增量并不等于show processlist的数值,这点需要注意。
Created Tmp Disk Tables
MySQL服务器在对SQL查询语句进行处理时在磁盘上创建的临时数据表的个数,如果这个值比较高,可能的原因:
a.查询在选择BLOB或者TEXT列的时候创建了临时表
b.tmp_table_size和max_heap_table_size的值也许太小
Created Tmp Files
MySQL服务器所创建的临时文件的个数
Created Tmp Tables
MySQL服务器在对SQL查询语句进行处理时在内存里创建的临时数据表的个数.
如果该值太高,唯一的解决办法是:优化查询语句.
Current Transactions
当前的事务数量(包括not started,ACTIVE,…等各种状态)
Database Pages
数据页大小
Dictionary Cache Memory
File Fsyncs
调用fsync()函数的次数.与innodb_flush_log_at_trx_commit值的设置有关.
File Reads
显示每秒文件的读次数
File System Memory
文件系统剩余的内存
File Writes
显示每秒文件的写次数
Free Pages
空闲页大小
Handler Commit
提交一个事务的请求的个数
Handler Delete
从数据表删除一个数据行的请求的个数
Handler Read First
读取索引中第一个索引项的请求的个数
Handler Read Key
根据一个索引值而读取一个数据行的请求的个数
Handler Read Next
按索引顺序读取下一个数据行的请求的个数
Handler Read Prev
按索引逆序读取前一个数据行的请求的个数
Handler Read Rnd
根据某个数据行的位置而读取该数据行的请求的个数
Handler Read Rnd Next
读取下一个数据行的请求的个数.如果这个数字很高,就说明有很多语句需要通过全表扫描才能完成或有很多查询没有使用适当的索引
Handler Rollback
回滚一个事务的请求的个数
Handler Savepoint
创建一个事务保存点的请求的个数
Handler Savepoint Rollback
回滚到一个事务保存点的请求的个数.
Handler Update
对数据表里的一个数据行进行修改的请求的个数
Handler Write
向数据表里插入一个数据行的请求的个数
Hash Index Cells Total
散列索引单元总数
Hash Index Cells Used
散列索引单元已用数
History List
历史记录的长度.位于InnoDB数据文件的撤销空间里的未清除事务的数目.当一个事务执行了更新并提交后,这个数字就会累加,当清除进程移除一个旧版本数据时,它就会递减.
Ibuf Cell Count
分段大小
Ibuf Free Cells
"自由列表"的长度
Ibuf Inserts
插入的记录数
Ibuf Merged
合并的页的数量
Ibuf Merges
合并的次数
Ibuf Used Cells
插入缓冲区的大小
Innodb Locked Tables
所有事务锁定的表的数量
Innodb Lock Structs
该图形显示了在innodb内部有多少锁结构(不是死锁).这大致与当前事务锁住的行数有关系.可以用来判断是否存在锁争用.
Innodb Lock Wait Secs
显示每秒处于锁等待的innodb事务总数.如果有一个非常大的值,应该检查LOCK WAIT transactions,
Innodb Log Buffer Size
Innodb Row Lock Time
该模板读取的Innodb_row_lock_time状态变量,表示InnoDB引擎在每次申请数据行锁定时等待的总时间(以毫秒为单位).
Innodb Row Lock Waits
读取的Innodb_row_lock_waits状态变量,表示InnoDB经过这么长时间才获得一个行锁.(毫秒)
Innodb Sem Waits
显示当前正在等待互斥量的InnoDB线程的数量.
Innodb Sem Wait Time Ms
显示当前正在等待互斥量的InnoDB线程的等待时间的总耗时(毫秒).
正常情况下,InnoDB Semaphores Wait Time和 InnoDB Semaphores Waits应该是空的.除非服务器运行着高并发的工作负载,它促使InnoDB采取让操作系统等待的措施.信息位于SHOW ENGINE INNODB STATUS的SEMAPHORES片段.
其中innodb_sem_waits的值是多少,表示有多少线程在等待,而innodb_sem_wait_time_ms表示所有线程等待的时间,默认单位是秒,在脚本中乘以1000,所以监控图中的单位是毫秒.
Innodb Tables In Use
所有事务用到的表的数量
Innodb Transactions
显示了InnoDB事务相关的信息
Key Buf Bytes Unflushed
同Key_blocks_unused 键缓存里尚未被使用过的缓存块的个数
Key Buf Bytes Used
同Key_blocks_used变量 键缓存里已经被使用的缓存块的个数
Key Buffer Size
键缓存大小
Key Read Requests
从键缓存读出索引块的读操作的次数
Key Reads
从磁盘读出索引块的读操作次数
Key Write Requests
向键缓存写一个索引块的请求的个数
Key Writes
把索引块写入磁盘的写操作的次数
Locked Transactions
锁住的事务数量
Lock System Memory
锁系统的内存
Log Bytes Flushed
刷新日志字节数
Log Bytes Written
写入日志字节数
Log Writes
日志写入数量
Max Connections
允许同时保持在打开状态的客户连接的最大个数
Max Used Connections
此前曾同时打开处于打开状态的连接的最大个数
Modified Pages
"脏"数据页.如果脏数据页太多,则需要检查磁盘IO状态.
MySQL running slave
slave运行状态
Opened Tables
MySQL服务器已打开的数据表总数(包括显式定义的临时表).如果这个值很高,应该慎重考虑,是否加大数据表缓存
Open Files
当前处于打开状态的文件的个数,如果与open_files_limit接近,则应该加大open_files_limit的值.
Open Tables
当前处于打开状态的数据表的个数.不包括TEMPORARY
Os Waits
操作系统等待时间
Page Hash Memory
每页散列内存
Pages Created
页面创建数
Pages Read
页面读取数
Pages Written
页面写入数
Pending Aio Log Ios
Pending Aio Sync Ios
Pending Buf Pool Flushes
缓冲池刷新等待数量
Pending Chkp Writes
Pending Ibuf Aio Reads
Pending Log Flushes
日志刷新等待数量
Pending Log Writes 日志写入等待数量
Pending Normal Aio Reads
Pending Normal Aio Writes
Pool Read Requests
连接池请求书
Pool Reads
连接池读取数
Pool Size
连接池大小
Qcache Free Blocks
Query Cache 中目前还有多少剩余的blocks。如果该值显示较大,则说明Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理
Qcache Free Memory
Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了;
Qcache Hits
多少次命中。通过这个参数我们可以查看到Query Cache 的基本效果;表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;
Qcache Inserts
多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数我们就可以算出Query Cache 的命中率,hits/inserts
Qcache Lowmem Prunes 多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换出
Qcache Not Cached 因为query_cache_type 的设置或者不能被cache 的Query 的数量;
Qcache Queries In Cache
当前Query Cache 中cache 的Query 数量;
Qcache Total Blocks
当前Query Cache 中的block 数量
Query Cache Size
Qcache的大小
Query Time Count 00
Query Time Count 01
Query Time Count 02
Query Time Count 03
Query Time Count 04
Query Time Count 05
Query Time Count 06
Query Time Count 07
Query Time Count 08
Query Time Count 09
Query Time Count 10
Query Time Count 11
Query Time Count 12
Query Time Count 13
Query Time Total 00
Query Time Total 01
Query Time Total 02
Query Time Total 03
Query Time Total 04
Query Time Total 05
Query Time Total 06
Query Time Total 07
Query Time Total 08
Query Time Total 09
Query Time Total 10
Query Time Total 11
Query Time Total 12
Query Time Total 13
Questions
记录了服务器收到的查询和命令的总数.(Com_*变量的总数不一定相等.)
Read Views
读取的视图数
Recovery System Memory
系统回收内存数
Relay Log Space
中继日志的大小
如果Binlog Cache Disk Use/Binlog Cache Use的值较大,那么应该尝试增加binlog_cache_size的大小.但是,也不要期望改善过多,如果写临时文件的数量从每秒1个减少到每分钟一个,这已经证明优化的足够好了.没必要耗费大量的内存,来处理binlog_cache_size中的事务.
Rows Deleted
删除的行数
Rows Inserted
插入的函数
Rows Read
读取的行数
Rows Updated
更新的行数
Select Full Join
没有使用索引而完成的多表联接操作的次数.这种情况是性能杀手,最好去优化sql.
Select Full Range Join
利用一个辅助性的参照表(reference table)上的区间搜索(range search)操作而完成的多数据表联接操作的次数.
该值表示使用了范围查询联接表的次数.
Select Range
利用第一个数据表上的某个区间而完成的多数据表联接操作的次数.
Select Range Check
该变量记录了在联接时,对每一行数据重新检查索引的查询计划的数量,它的开销很大.
如果该值较高或正在增加,说明一些查询没有找到好索引.
Select Scan
通过对第一个数据表进行全表扫描而完成的多数据表联接操作的次数.
Slave Lag
复制延迟
Slave Open Temp Tables
从服务器中的SQL线程曾经打开的临时文件的个数
Slave Retried Transactions
从服务器中的SQL线程重新尝试执行一个事务的次数
Slave Running
从服务器的I/O线程和SQL线程是否在运行
Slave Stopped
从服务器的I/O线程和SQL线程是否在停止
Slow Queries
慢查询的次数(执行时间超过long_query_time值)
Sort Merge Passes
查询导致了文件排序的次数.可以优化sql或者适当增加sort_buffer_size变量
Sort Range
利用一个区间进行的排序操作的次数
Sort Rows
对多少行排序
Sort Scan
利用一次全表扫作而完成的排序操作的次数
Spin Rounds
InnoDB内部预设的互斥量信号数量
Spin Waits
InnoDB内部对锁进行循环等待的数量(与innodb_sync_spin_loops参数有关)
State Closing Tables
线程正在将已更改的表数据刷新到磁盘并关闭所使用的表。这应该是一个快速的操作。如果没有,请确认您没有已满的磁盘,并且磁盘没有被大量使用。
State Copying To Tmp Table:
服务将表复制到内存的的表
State End:
This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements
State Freeing Items:线程执行命令。在这种状态下完成的某些项目的释放涉及查询缓存
State Init:
这发生在初始化表、删除、插入、选择或更新语句之前。服务器在这种状态下进行的行动包括冲洗的二进制日志,Innodb日志,和一些查询缓存清理操作。
State Locked:
被锁状态
State Login:
连接线程的初始状态,直到成功地对客户端进行身份验证为止。
State None
什么都没有的,空的,注意不是NULL状态
State Other
其他
State Preparing:
是否在查询执行optimize命令期间
State Reading From Net:
网络连接读取状态
State Sending Data:
线程正在读取和处理SELECT语句的行,并向客户机发送数据。因为在这种状态下发生的操作往往会执行大量磁盘访问(读取),因此它通常是给定查询生命周期中最长的运行状态。
State Sorting Result:
For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables
State Statistics:
服务器正在计算统计数据以开发查询执行计划。如果线程在这种状态下很长时间,服务器可能是磁盘绑定执行其他工作。
State Updating:
表示线程正在更新的行
State Writing To Net:
网络连接写入状态
Table Cache
表存储量
Table Locks Immediate
无需等待就能够立刻得到满足的数据表锁定请求的个数
Table Locks Waited
显示了有多少表被锁住了并且导致服务器级的锁等待(存储引擎级的锁,如InnoDB行级锁,不会使该变量增加).
如果这个值比较高或者正在增加,那么表明存在严重的并发瓶颈.
Thread Cache Size
线程缓存所能容纳的线程的最大个数.断开的mysql连接会放到这个缓存里,新建立的连接就会重复使用它们而不创建新的线程.
Thread Hash Memory
Threads Cached
mysql管理的线程池中还有多少可以被复用的资源
Threads Connected
我们经常使用show processlist看见那个数值,现在正处于打开状态的连接的个数
Threads Created
为处理连接创建的线程总数(新创建的线程数)
Threads Running
正在运行的线程数
Total Mem Alloc
总的内存分配
Total number of mysqld processes
mysql总进程数
Uncheckpointed Bytes
显示了未写入磁盘的数据量.如果该值的大小接近innodb_log_file_size * n 的总大小,则需要增加innodb_log_file_size的值,但是要注意,如果发生宕机,则需要更长的回复时间.(从redolog恢复)
Unflushed Log
是log_bytes_written与log_bytes_flushed的差值,表示日志缓存区里还有多少数据没被刷新到日志文件里.
如果这个差值超过了innodb_log_buffer_size设置的30%,则需要考虑是否加大该参数值.
图形监控项:
InnoDB Adaptive Hash Index
显示了“自适应哈希索引”的使用情况,哈希索引只能用来搜索等值的查询.
1、Hash Index Cells Total
自适应哈希表的槽数=innodb_buffer_pool_size/256
2、Hash Index Cells Used
用到自适应哈希表的查询个数
InnoDB Buffer Pool Activity
显示Innodb缓冲池的内部活动,页的创建,读取,写入.如果有突然的增加或者减少,需要检查应用
1、Pages Created
2、Pages Read
3、Pages Written
InnoDB Buffer Pool
1、Pool Size
InnoDB缓冲池的页数量,每页大小16K
2、Database Pages
数据页大小
3、Free Pages
空闲页大小
4、Modified Pages
"脏"数据页.如果脏数据页太多,则需要检查磁盘IO状态.
InnoDB Checkpoint Age
1、Uncheckpointed Bytes
显示了未写入磁盘的数据量.如果该值的大小接近innodb_log_file_size * n 的总大小,则需要增加innodb_log_file_size的值,但是要注意,如果发生宕机,则需要更长的回复时间.(从redolog恢复)
InnoDB Current Lock Waits
1、InnoDB Lock Wait Secs
显示每秒处于锁等待的innodb事务总数.如果有一个非常大的值,应该检查LOCK WAIT transactions,请看下面的模板
InnoDB I/O
1、File Reads
显示每秒文件的读次数
2、File Writes
显示每秒文件的写次数
3、Log Writes
写日志的次数
4、File Fsyncs
调用fsync()函数的次数.与innodb_flush_log_at_trx_commit值的设置有关.
InnoDB I/O Pending
显示了InnoDB挂起的同步或异步IO操作.如果挂起的操作太多,则需要更大的RAM,更大的缓冲池,更快的磁盘.
1、Pending Aio Log Ios
2、Pending Aio Sync Ios
3、Pending Buf Pool Flushes
4、Pending Chkp Writes
5、Pending Ibuf Aio Reads
6、Pending Log Flushe
7、Pending Log Writes
8、Pending Normal Aio Reads
9、Pending Normal Aio Writes
InnoDB Insert Buffer
插入缓冲,并不是缓存的一部分,而是物理页,对于非聚集索引的插入或更新操作,不是每一次直接插入索引页.而是先判断插入的非聚集索引页是否在缓冲池中.如果在,则直接插入,如果不再,则先放入一个插入缓冲区中.然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作.
使用条件:非聚集索引,非唯一
1、Ibuf Inserts
插入的记录数
2、Ibuf Merged
合并的页的数量
3、Ibuf Merges
合并的次数
如果merges/merged的值等于3/1,则代表插入缓冲对于非聚集索引页的IO请求大约降低了3倍
InnoDB Insert Buffer Usage
1、Ibuf Cell Count
分段大小
2、Ibuf Used Cells
插入缓冲区的大小
3、Ibuf Free Cells
"自由列表"的长度
InnoDB Internal Hash Memory Usage
显示了InnoDB内部各种哈希结构(不可人工干预),占用的内存大小.
1、Adaptive Hash Memory
自适应哈希索引占用的内存大小
2、Page Hash Memory
3、Dictionary Cache Memory
4、File System Memory
5、Lock System Memory
6、Recovery System Memory
7、Thread Hash Memory
InnoDB Lock Structures
1、InnoDB Lock Structs
该图形显示了在innodb内部有多少锁结构(不是死锁).这大致与当前事务锁住的行数有关系.可以用来判断是否存在锁争用.
对于数量多少算好或者算坏,没有硬性的规定.实际情况下,大量的事务在等待锁,很明显,该值越小越好.
这个数据来源是SHOW ENGINE INNODB STATUS;
InnoDB Log
相关变量:innodb_log_buffer_size
InnoDB Log Buffer Size
1、Log Bytes Written
2、Log sequence number
当前日志的位置
3、Log Bytes Flushed
4、Log flushed up to
日志已经刷新的位置
5、Unflushed Log
是log_bytes_written与log_bytes_flushed的差值,表示日志缓存区里还有多少数据没被刷新到日志文件里.
如果这个差值超过了innodb_log_buffer_size设置的30%,则需要考虑是否加大该参数值.
InnoDB Memory Allocation
Total memory allocated 8824815616; in additional pool allocated 0
1、Total Mem Alloc
2、InnoDB申请的总内存量,单位字节
3、Additional Pool Alloc
分配给额外内存的总量,单位字节
InnoDB Row Lock Time
1、InnoDB Row Lock Time
该模板读取的Innodb_row_lock_time状态变量,表示InnoDB引擎在每次申请数据行锁定时等待的总时间(以毫秒为单位).
InnoDB Row Lock Waits
1、InnoDB Row Lock Waits
读取的Innodb_row_lock_waits状态变量,表示InnoDB经过这么长时间才获得一个行锁.(毫秒)
InnoDB Row Operations
Number of rows inserted 50678311, updated 66425915, deleted 20605903, read 454561562
大致能表现InnoDB内部的操作
1、Row Read
2、Row Deleted
3、Row Updated
4、Row Inserted
InnoDB Semaphores Wait Time
1、InnoDB Sem Wait Time Ms
显示当前正在等待互斥量的InnoDB线程的等待时间的总耗时(毫秒).
应对这个问题
InnoDB采取多阶段等待策略.首先,尝试对锁进行循环等待.如果经过了一个预设的循环等待周期(innodb_sync_spin_loops = 30,当前配置文件默认为30次)之后还没有成功,就会退到更昂贵更复杂的等待阵列里,如果并发量太大的话,则导致系统负载突增.
循环等待的成本相对比较低,但是需要不停地检查一个资源是否被锁定,消耗CPU周期,也就是说,当另外一条线程能处理事情时,循环等待也会独占处理器.
循环等待的替换方案就是让操作系统做上下文切换(等待阵列),每秒钟几千次的切换会引发大量的系统开销.
解决办法
根据具体的应用,调整参数;或者优化应用,减少并发.
InnoDB Semaphores Waits
1、InnoDB Sem Waits
显示当前正在等待互斥量的InnoDB线程的数量.
InnoDB Semaphores
显示innodb内部的信号活动状态.
包括Mutex spin waits,RW-shared spins,RW-excl spins等各种信号量的数量总和.
1、Spin Rounds
InnoDB内部预设的互斥量信号数量
2、Spin Waits
InnoDB内部对锁进行循环等待的数量(与innodb_sync_spin_loops参数有关)
3、Os Wait系统等待
事务退到操作系统的等待阵列的数量
在高并发的情况,会发现这个值有尖峰状,不稳定.图像主要显示了,不合理的设计情况下,不同的连接类型之间的行锁或互斥锁的争用.
InnoDB Tables In Use
mysql tables in use 2, locked 2
1、InnoDB Tables In Use
所有事务用到的表的数量
2、InnoDB Locked Tables
所有事务锁定的表的数量
InnoDB Transactions Active/Locked
该图显示了InnoDB事务的状态数量.
1、Active Transactions
正在执行的事务数量
2、Locked Transactions
锁住的事务数量
3、Current Transactions
当前的事务数量(包括not started,ACTIVE,…等各种状态)
not started # 事务已经提交到磁盘
ACTIVE # 正在执行的事务
4、Read Views(待更新)
read views open inside InnoDB
InnoDB Transactions
显示了InnoDB事务相关的信息
1、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行)
2、History List
历史记录的长度.位于InnoDB数据文件的撤销空间里的未清除事务的数目.当一个事务执行了更新并提交后,这个数字就会累加,当清除进程移除一个旧版本数据时,它就会递减.
MyISAM Indexs
显示了在MyISAM索引上的读写情况
1、Key Reads Requests
从键缓存读出索引块的读操作的次数
2、Key Reads
从磁盘读出索引块的读操作次数
3、Key Write Requests
向键缓存写一个索引块的请求的个数
4、Key Writes
把索引块写入磁盘的写操作的次数
MyISAM Key Cache
1、Key Buffer Size
键缓存大小
2、Key Buf Bytes Used
同Key_blocks_used变量
键缓存里已经被使用的缓存块的个数
3、Key Buf Bytes Unused
同Key_blocks_unused
键缓存里尚未被使用过的缓存块的个数
MySQL Binary/Relay Logs
1、Binlog Cache Use
保存在二进制日志缓存里的事务的个数
2、Binlog Cache Disk Use
超过binlog_cache_size设置的缓存大小,使用磁盘临时文件的事务的个数
3、Binlog Log Space
二进制日志的大小
4、Relay Log Space
中继日志的大小
如果Binlog Cache Disk Use/Binlog Cache Use的值较大,那么应该尝试增加binlog_cache_size的大小.但是,也不要期望改善过多,如果写临时文件的数量从每秒1个减少到每分钟一个,这已经证明优化的足够好了.没必要耗费大量的内存,来处理binlog_cache_size中的事务.
MySQL Command Counts
命令计数器,显示了MySQL(在过去1秒内)执行各种命令的次数
1、Questions
记录了服务器收到的查询和命令的总数.(Com_*变量的总数不一定相等.)
2、Com Select
3、Com Delete
4、Com Insert
5、Com Update
6、Com Replace
7、Com Load
8、Com Delete Multi
9、Com Insert Select
10、Com Update Multi
11、Com Replace Select
MySQL Connections
1、Max Connections
允许同时保持在打开状态的客户连接的最大个数
2、Max Used Connections
此前曾同时打开处于打开状态的连接的最大个数
3、Aborted Clients
因客户端没有正确地关闭而被丢弃的连接的个数
4、Aborted Connects
试图连接MySQL服务器但没有成功的次数
5、Threads Connectd
现在正处于打开状态的连接的个数
6、Connections
试图连接MySQL服务器的尝试次数
MySQL Files and Tables
1、Table Cache
2、Open Tables
当前处于打开状态的数据表的个数.不包括TEMPORARY
3、Open Files
当前处于打开状态的文件的个数,如果与open_files_limit接近,则应该加大open_files_limit的值.
4、Opened Tables
MySQL服务器已打开的数据表总数(包括显式定义的临时表).如果这个值很高,应该慎重考虑,是否加大数据表缓存(table_open_cache).
MySQL Handler
1、Handler_writer 向数据表里插入一个数据行的请求的个数
2、Handler_update 对数据表里的一个数据行进行修改的请求的个数
3、Handler_delete 从数据表删除一个数据行的请求的个数
4、Handler_read_first 读取索引中第一个索引项的请求的个数
5、Handler_read_key 根据一个索引值而读取一个数据行的请求的个数
6、Handler_read_next 按索引顺序读取下一个数据行的请求的个数
7、Handler_read_prev 按索引逆序读取前一个数据行的请求的个数
8、Handler_read_rnd 根据某个数据行的位置而读取该数据行的请求的个数
9、Handler_read_rnd_next 读取下一个数据行的请求的个数.如果这个数字很高,就说明有很多语句需要通过全表扫描才能完成或有很多查询没有使用适当的索引
MySQL Network Traffic
1、Bytes Send
发送字节数
2、Bytes Received
收到字节数
MySQL Replication
默认用SHOW SLAVE STATUS命令获取各状态值
1、Slave Running
从服务器的I/O线程和SQL线程是否在运行
2、Slave Stopped
3、Slave Lag
复制延迟
4、Slave Open Tmp Tables
从服务器中的SQL线程曾经打开的临时文件的个数
5、Slave Retried Transactions
从服务器中的SQL线程重新尝试执行一个事务的次数
MySQL Select Types
1、Select Full Join
没有使用索引而完成的多表联接操作的次数.这种情况是性能杀手,最好去优化sql.
2、Select Full Range Join
利用一个辅助性的参照表(reference table)上的区间搜索(range search)操作而完成的多数据表联接操作的次数.
该值表示使用了范围查询联接表的次数.
3、Select Range
利用第一个数据表上的某个区间而完成的多数据表联接操作的次数.
4、Select Range Check
该变量记录了在联接时,对每一行数据重新检查索引的查询计划的数量,它的开销很大.
如果该值较高或正在增加,说明一些查询没有找到好索引.
5、Select Scan
通过对第一个数据表进行全表扫描而完成的多数据表联接操作的次数.
MySQL Sorts
1、Sort Rows
对多少行排序
2、Sort Range
利用一个区间进行的排序操作的次数
3、Sort Merge Passes
查询导致了文件排序的次数.可以优化sql或者适当增加sort_buffer_size变量
4、Sort Scan
利用一次全表扫作而完成的排序操作的次数
MySQL Table Locks
1、Table Locks Immediate
无需等待就能够立刻得到满足的数据表锁定请求的个数
2、Table Locks Waited
显示了有多少表被锁住了并且导致服务器级的锁等待(存储引擎级的锁,如InnoDB行级锁,不会使该变量增加).
如果这个值比较高或者正在增加,那么表明存在严重的并发瓶颈.
3、Slow Queries
慢查询的次数(执行时间超过long_query_time值)
MySQL Temporary Objects
1、Created_tmp_tables
MySQL服务器在对SQL查询语句进行处理时在内存里创建的临时数据表的个数.
如果该值太高,唯一的解决办法是:优化查询语句.
2、Created_tmp_disk_tables
MySQL服务器在对SQL查询语句进行处理时在磁盘上创建的临时数据表的个数,如果这个值比较高,可能的原因:
a.查询在选择BLOB或者TEXT列的时候创建了临时表
b.tmp_table_size和max_heap_table_size的值也许太小
3、Created_tmp_files
MySQL服务器所创建的临时文件的个数
MySQL Threads
1、Thread Cache Size
线程缓存所能容纳的线程的最大个数.断开的mysql连接会放到这个缓存里,新建立的连接就会重复使用它们而不创建新的线程.
如果缓存中有自由的线程,MySQL就能很快的响应连接请求,不必为每个连接都创建新的线程.每个在缓存中的线程通常消耗256KB内存.
2、Thread Created
为处理连接创建的线程总数
MySQL Transaction Handler
1、Handler Commit
提交一个事务的请求的个数
2、Handler Rollback
回滚一个事务的请求的个数
3、Handler Savepoint
创建一个事务保存点的请求的个数
4、Handler Savepoint Rollback
回滚到一个事务保存点的请求的个数.
###########sample 2
感谢-编译-人生-, 英文资料也提供非常详细
https://blog.csdn.net/forest_boy/article/details/45517225
转自:http://blog.itpub.net/12608397/viewspace-747884/
This page gives installation instructions specific to the MySQL graph templates, shows examples of graphs in the MySQL template collection, and shows what they do. You might want to look at http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html to learn the meaning of the status variables contained in the graphs.
This document should be correct and complete as of version 1.1.8 of the graphs. Please use the issue tracker or the mailing list to report any errors or omissions. If you have any sample graphs that are better than those shown, please contribute!
- Installation Notes
- InnoDB Active/Locked Transactions
- InnoDB Adaptive Hash Index
- InnoDB Buffer Pool
- InnoDB Buffer Pool Activity
- InnoDB Buffer Pool Status
- InnoDB Checkpoint Age
- InnoDB Current Lock Waits
- InnoDB Insert Buffer
- InnoDB Insert Buffer Usage
- InnoDB Internal Hash Memory Usage
- InnoDB I/O Activity
- InnoDB I/O Pending
- InnoDB Lock Structures
- InnoDB Log Activity
- InnoDB Memory Allocation
- InnoDB Row Lock Time
- InnoDB Row Lock Waits
- InnoDB Row Operations
- InnoDB Semaphores
- InnoDB Tables In Use
- InnoDB Transaction Activity
- InnoDB Transactions
- MyISAM Indexes
- MyISAM Key Cache
- MySQL Binary/Relay logs
- MySQL Command Counters
- MySQL Connections
- MySQL Files and Tables
- MySQL Handlers
- MySQL Network Traffic
- MySQL Processlist
- MySQL Query Cache
- MySQL Query Cache Memory
- MySQL Query Response Time (Microseconds)
- MySQL Query Time Histogram (Count)
- MySQL Replication Status
- MySQL Select Types
- MySQL Sorts
- MySQL Table Locks
- MySQL Temporary Objects
- MySQL Transaction Handler
Installation Notes
The MySQL templates work by executing a PHP script. that gathers information from MySQL servers and returns it to Cacti. The script. makes an ordinary MySQL connection to gather its input.
It is highly recommended that you use the same MySQL username and password for all servers you want to graph, to ease the installation and configuration. If you don’t, you will need to follow the instructions in InstallingTemplates andCustomizingTemplates to accommodate your installation.
The script. requires that you be able to connect to MySQL from your Cacti server. You can test this with the mysql command-line program. Debugging MySQL connection problems is beyond the scope of this documentation; refer to the MySQL manual if you have trouble.
To install,
- Create a MySQL user with the SUPER and PROCESS privileges on each server you want to monitor. Assuming you use “cacti” and “s3cret” as your username and password, execute the following command on each server: GRANT SUPER, PROCESS ON *.* TO 'cacti'@'%' IDENTIFIED BY "s3cret";
- If you want to monitor replication with mk-heartbeat from the Maatkit toolkit (recommended), you must grant SELECT on the heartbeat table also. Assuming the mk-heartbeat table is maatkit.heartbeat, execute GRANT SELECT ON maatkit.heartbeat TO 'cacti'@'%';
- See below for detailed information on the privileges.
- Copy ss_get_mysql_stats.php into your Cacti installation’s scripts/ directory.
- All other steps are the same as mentioned in InstallingTemplates.
If you want to specify a different MySQL port for various servers, see how to accept input in each data source.
User Privileges
The suggested user privileges mentioned above are sufficient for the common case. In some cases you might not want or have such access. The following list explains the queries that the data-gathering script. executes, the functionality, and how to disable if it’s unwanted:
- SHOW /*!50002 GLOBAL */ STATUS
- This query requires no special privileges and is necessary for core functionality.
- SHOW VARIABLES
- This query requires no special privileges and is necessary for core functionality.
- SHOW SLAVE STATUS
- This query requires either SUPER or REPLICATION CLIENT. It is necessary for tracking replication lag on replication slaves, which is enabled by default. To disable, edit the $chk_options array in the configuration.
- Alternatively, use mk-heartbeat and grant SELECT on the heartbeat table.
- If disabled, parts of the the MySQL Replication Status and MySQL Binary/Relay logs graphs will be empty.
- SHOW MASTER LOGS
- This query is used to count up the size of binary logs. It requires the SUPER privilege.
- If disabled in the $chk_options array, then part of the MySQL Binary/Relay logs graph will be empty.
- SHOW PROCESSLIST
- This query requires the PROCESS privilege to generate the MySQL Processlist graph.
- You can disable this query by editing the $chk_options array in the configuration.
- SHOW /*!50000 ENGINE*/ INNODB STATUS
- This query requires the SUPER privilege. It is required for all of the InnoDB graphs.
- You can disable this query by editing the $chk_options array in the configuration.
- In MySQL 5.1.24 and greater, the required privilege is PROCESS, not SUPER. See MySQL bug 32710.
If you disable significant portions of the functionality, such as the InnoDB graphs, then you might want to edit the Host Template to remove unwanted graphs.
InnoDB Active/Locked Transactions
This template shows InnoDB transaction counts:
- An active transaction is a transaction that’s currently open. It’s possible for transactions to be in “not started” status, which really means that this connection to MySQL doesn’t actually have a transaction open. A transaction is active between BEGIN and COMMIT. It’s also active whilst a query is running, although it might commit immediately due to auto-commit, if applicable. This graph really just shows how much transactional activity is happening on the database.
- A locked transaction is in LOCK WAIT status. This usually means it’s waiting for a row lock, but in some cases could be a table lock or an auto-increment lock. If you start to see lock waits, you need to check SHOW INNODB STATUS and search for the string “LOCK WAIT” to examine what’s waiting. Lock waits can come from several sources, including too much contention on busy tables, queries accessing data through scans on different indexes, or bad query patterns such as SELECT .. FOR UPDATE.
InnoDB Adaptive Hash Index
This graph shows the hash index’s cells total and cells used. There isn’t really anything actionable about this graph: the adaptive hash index isn’t designed to be user-tunable, although you can disable it. However, should something go wrong with performance, this graph might provide diagnostic information.
InnoDB Buffer Pool
This graph shows the current status of the InnoDB buffer pool: the size, free pages, used (database) pages, and dirty (modified) pages. Keep an eye on the dirty pages. If too much of the buffer pool fills with dirty pages and InnoDB starts to flush aggressively to reduce that number, you could see cyclical behavior. This might be correlated with intense disk activity and/or periods of reduced throughput. Recent versions of the InnoDB plugin, the Percona patches, and XtraDB have various solutions for this problem, should you experience it.
The example graph shows what happens when InnoDB restarts: the buffer pool empties and then fills again. You can see the corresponding activity in the InnoDB Checkpoint Age graph example below.
InnoDB Buffer Pool Activity
This graph shows activity inside the buffer pool: pages created, read, and written. You can consider it roughly equivalent to the Handler graphs. If you see a sudden change in the graph, you should try to trace it to some change in your application.
InnoDB Buffer Pool Status
This graph is from an old version of the templates, and is replaced by InnoDB Buffer Pool.
InnoDB Checkpoint Age
This graph shows the InnoDB checkpoint age, which is the same thing as the number of uncheckpointed bytes, and thus the amount of recovery that’ll have to be performed if there’s a crash. If the uncheckpointed bytes begin to approach the combined size of the InnoDB log files, your system might need larger log files. In addition, a lot of un-checkpointed data might indicate that you’ll have a long and painful recovery if there’s a crash. If you are writing a tremendous amount of data to the log files, and thus need large log files for performance, you might want to take a look at the Percona enhancements to recovery performance, to reduce downtime in the event of a crash.
The example is from the same server and time period as the InnoDB Buffer Pool graph.
InnoDB Current Lock Waits
This graph shows the total number of seconds that InnoDB transactions have been waiting for locks. This is related to the InnoDB Locked Transactions graph above, except that it’s the sum of the lock wait time. You might have only one transaction in LOCK WAIT status, but it might be waiting a very long time if innodb_lock_wait_timeout is set to a large value. So if you see a large value on this graph, you should investigate for LOCK WAIT transactions, just as described above.
InnoDB Insert Buffer
This graph shows information about InnoDB’s insert buffer: inserts, merge operations, and merged records. This is not generally actionable, because the insert buffer is not user-configurable. However, you can use it to diagnose certain kinds of performance problems, such as furious disk activity after you stop the server from processing queries, or during particular types of queries that force the insert buffer to be merged into the indexes. (The insert buffer is sort of a delayed way of updating non-unique secondary indexes.) If the insert buffer is causing problems, the Percona version of MySQL might help, because it has some configuration parameters for the buffer.
InnoDB Insert Buffer Usage
This graph shows the total cells in the insert buffer, and the used and free cells. This is diagnostic only, as in the previous graph. You can use it to see the buffer usage, and thus correlate with server activity that might be hard to explain otherwise.
InnoDB Internal Hash Memory Usage
This graph shows how much memory InnoDB uses for various internal hash structures: the adaptive hash index, page hash, dictionary cache, filesystem, locks, recovery system, and thread hash. This is available only in Percona-enhanced versions of InnoDB, and these structures are generally not configurable. However, you might use it to diagnose some kinds of performance problems, such as much greater than expected memory usage. In standard InnoDB, the internal data dictionary tends to consume large amounts of memory when you have many tables, for example. The Percona version of InnoDB lets you control that with some features that are similar to MySQL’s table cache.
InnoDB I/O Activity
This graph shows InnoDB’s I/O activity: file reads and writes, log writes, and fsync() calls. This might help diagnose the source of I/O activity on the system. Some of this can be influenced with InnoDB settings, especially innodb_flush_log_at_trx_commit. There are more tunables in the Percona version of InnoDB, XtraDB, and the InnoDB plugin.
InnoDB I/O Pending
This graph shows InnoDB’s pending synchronous and asynchronous I/O operations in various parts of the engine. Pending I/O is not ideal; ideally you’d like InnoDB’s background thread(s) to keep up with writes, and you’d like the buffer pool large enough that reads are not an issue. If you see a lot of pending I/O, you might need more RAM, a bigger buffer pool (or use O_DIRECT to avoid double-buffering), or a faster disk subsystem.
InnoDB Lock Structures
This graph shows how many lock structures InnoDB has internally. This should correlate roughly to the number of row locks transactions are currently holding, and might be useful to help diagnose increased lock contention. There is no hard rule about what’s a good or bad number of locks, but in case many transactions are waiting for locks, obviously fewer is better.
The data comes from lines in SHOW INNODB STATUS such as the following:
# 23 lock struct(s), heap size 3024, undo log entries 27 # LOCK WAIT 12 lock struct(s), heap size 3024, undo log entries 5 # LOCK WAIT 2 lock struct(s), heap size 368
It is the sum of all of the N lock struct(s) values.
InnoDB Log Activity
This graph shows InnoDB log activity: the log buffer size, bytes written, flushed, and unflushed. The example graph is illustrating issue 10 , which really needs to be fixed. After that, you might be able to use this graph to find problems with your log buffer size, or with a disk system that’s not fast enough. If transactions need to write to the log buffer and it’s either not big enough or is currently being flushed, they’ll stall. You don’t want that. You can also influence this buffer’s activity through innodb_flush_logs_at_trx_commit.
InnoDB Memory Allocation
This graph shows InnoDB’s total memory allocation, and how much of that is in the additional pool (as opposed to the buffer pool). If a lot of memory is in the additional memory pool, you might suspect problems with the internal data dictionary cache; see above for more on this. Unfortunately, in standard InnoDB it’s a bit hard to know where the memory really goes.
InnoDB Row Lock Time
This graph is new in version 1.1.7 of the graphs. It shows the amount of time, in milliseconds, that InnoDB has waited to grant row locks. This comes from the Innodb_row_lock_time status variable, which is only available in MySQL 5 and newer.
InnoDB Row Lock Waits
This graph is new in version 1.1.7 of the graphs. It shows the number of times that InnoDB has waited to grant row locks. This comes from the Innodb_row_lock_waits status variable, which is only available in MySQL 5 and newer.
InnoDB Row Operations
This graph shows row operations InnoDB has performed: reads, deletes, inserts, and updates. These should be roughly equivalent to Handler statistics, with the exception that they can show internal operations not reflected in the Handler statistics. These might include foreign key operations, for example.
InnoDB Semaphores
This graph shows information on InnoDB semaphore activity: the number of spin rounds, spin waits, and OS waits. You might see these graphs spike during times of high concurrency or contention. These graphs basically indicate different types of activity involved in obtaining row locks or mutexes, both of which are causes of poor scaling in standard InnoDB. XtraDB, the InnoDB plugin, and the Percona-patched MySQL are much improved in this regard.
InnoDB Tables In Use
This graph shows how many tables InnoDB has in use and how many are locked. If there are spikes in these graphs, you’ll probably also see spikes in LOCK WAIT and other signs of contention amongst queries.
InnoDB Transaction Activity
This graph shows information about transactions within InnoDB: transactions created, open, active time, lock wait time, MVCC read views, and the length of the history list. This graph has been deprecated, and its information is moved to other graphs, such as InnoDB Active/Locked Transactions, and InnoDB Transactions.
InnoDB Transactions
This graph shows information about transactions within InnoDB.
- Total transactions ever created is the internal transaction counter.
- The current transactions are all transactions, no matter what status (ACTIVE, LOCK WAIT, not started, etc).
- The length of the history list shows how old the oldest unpurged transaction is. If this grows large, you might have transactions that are staying open a very long time. This means InnoDB can’t purge old row versions. In PostgreSQL terms, it can’t VACUUM. It will get bloated and slow as a result. Commit your transactions as quickly as you can.
- The number of read views open shows how many transactions have a consistent snapshot of the database’s contents, which is achieved by MVCC.
Some of the things on this graph really belong on the Active/Locked graph, where they would make more sense, but I don’t want to break backwards compatibility by doing that. The current transactions and the number of transactions with read views open would be more sensible on that graph. Cross reference that graph to make sense of these metrics.
MyISAM Indexes
This graph shows information about how many logical and physical reads and writes took place to MyISAM indexes. Probably the most important one is the physical reads. Contrary to the popular wisdom, the ratio between logical and physical reads is utterly meaningless and is a red herring that wastes a lot of time and money. Instead, you should look at the absolute number of physical reads per second, and compare it to what your disks are capable of. (RRDTool normalizes everything to units of seconds, so this graph’s absolute value is the number you’re looking for.)
MyISAM Key Cache
This graph is new in release 1.1.7. It shows the size of the key buffer, how much of it is used, and how much is unflushed. Memory that isn’t used might not really be allocated; the key buffer isn’t allocated to its full size.
MySQL Binary/Relay logs
This graph shows information about the space used by the server binary and relay logs. The variations in the sizes are when the logs are purged, probably due to expire_logs_days being set. If this suddenly grows large, look for problems in purging, which might be caused by a configuration change, or by someone manually deleting a file and causing the automatic purge to stop working.
MySQL Command Counters
This graph shows counters for various MySQL commands. These are derived from the Com counters from SHOW STATUS. If there is a change in the graph, it indicates that something changed in the application.
MySQL Connections
This graph shows information about the connection parameters and counters inside MySQL: connections permitted, connections used, connections aborted, clients aborted, current connections, and connections created. Probably the most interesting are the aborted clients and connections, which might indicate a malfunctioning application that disconnects ungracefully, an idle connection timing out, network problems, bad authentication attempts, or similar.
MySQL Files and Tables
This graph shows status of MySQL’s table cache and file handles: the size of the cache, and how many open files and tables there are. This graph is not likely to be exciting.
MySQL Handlers
This graph shows the various Handler counters, which record how many operations MySQL has done through the storage engine API. Changes in indexing will probably show up clearly here: a query that used to do a table scan but now has a good index to use will cause different Handler calls to be used, for example. If you see sudden changes, it probably correlates with schema changes or a different mixture of queries. The example graph shows a large spike of Handler_read_rnd_next, which probably means something was doing a lot of table scans. (I generated that graph synthetically by running a table-scan query in an infinite loop for a while.)
Here is another graph, generated from an active production server.
MySQL Network Traffic
This graph shows network traffic to and from the MySQL Server, in bytes.
MySQL Processlist
The MySQL Processlist shows the number (count) of queries from SHOW PROCESSLIST in given statuses. Some of the statuses are lumped together into the “other” category. This is basically a “scoreboard” type of graph. In most cases, you should see mostly Other, or a few of the statuses like “Sending data”. Queries in Locked status are the hallmark of a lot of MyISAM table locking. Any mixture of statuses is possible, and you should investigate sudden and systemic changes.
MySQL Query Cache
This graph shows information about the query cache inside MySQL: the number of queries in the cache, inserted, queries not cached, queries pruned due to low memory, and cache hits.
MySQL Query Cache Memory
This graph shows information on the query cache’s memory usage: total size, free memory, total blocks and free blocks. Blocks are not of a uniform. size, despite the name.
MySQL Query Response Time (Microseconds)
This graph displays a histogram of the query response time distribution available in Percona Server. Because the time units are user-configurable, exact unit labels are not displayed; rather, the graph simply shows the values. There are 14 time units by default in Percona Server, so there are 13 entries on the graph (the 14th is non-numeric, so we omit it).
The graph actually displays the amount of response time spent by the server on queries of various lengths. See the Percona documentation for more. The units are in microseconds on the graph, because RRDtool cannot store floating-point values.
MySQL Query Time Histogram (Count)
This graph displays a histogram of the query response time distribution available in Percona Server. Because the time units are user-configurable, exact unit labels are not displayed; rather, the graph simply shows the values. There are 14 time units by default in Percona Server, so there are 13 entries on the graph (the 14th is non-numeric, so we omit it).
The graph displays the number of queries that fell into each time division. See the Percona documentation for more.
MySQL Replication Status
This graph displays the status of the replication thread. There are two ways to measure the replication delay:
- By looking at SHOW SLAVE STATUS’s Seconds_behind_master column, which is shown as Secs Behind Master
- By looking at a heartbeat table such as those supported by mk-heartbeat. You must configure the ss_get_mysql_stats.php file to do this. Examine the comments in the header of the file (TODO: get that documentation online.)
When replication is running, there is an AREA of the same size as the replication delay, colored green. When it’s stopped, there’s an AREA of the same size as the replication delay, colored red. What this means is that you’ll see a graph of replication delay, colored in with the appropriate color (green or red) to indicate whether replication was stopped at that moment. If replication isn’t delayed, you won’t see any green or red. If you’re using Seconds_behind_master instead of mk-heartbeat to measure delay, it’s impossible to measure delay when the slave is stopped, so you won’t see any red. This is one of the many reasons Seconds_behind_master from SHOW SLAVE STATUS is not terribly useful.
The graph also shows open temporary tables and retried transactions.
MySQL Select Types
This graph shows information on how many of each type of select the MySQL server has performed: full join, full range join, range, range check, and scan. Like the Handler graphs, these show different types of execution plans, so any changes should be investigated. You should strive to have zero Select_full_join queries! The graph shows some of those.
MySQL Sorts
This graph shows information about MySQL sort operations: rows sorted, merge passes, and number of sorts triggered by range and scan queries. Over-analyzing this data and trying to tweak sort buffer sizes is the hallmark of an inexperienced database administrator who will soon be wondering why the server is slow. However, the graphs may react favorably (get smaller) in response to query optimization, increasing your feeling of satisfaction and giving you something to show your boss. If the graphs react unfavorably (grow), check your EXPLAIN plans.
MySQL Table Locks
This graph shows information about table-level lock operations inside MySQL: locks waited, locks granted without waiting, and slow queries. Locks that have to wait are generally caused by MyISAM tables. Even InnoDB tables will cause locks to be acquired, but they will generally be released right away and no waiting will occur.
MySQL Temporary Objects
This graph shows information about temporary objects created by the MySQL server: temporary tables, temporary files, and temporary tables created on disk instead of in memory. Like sort data, this is easy to over-analyze. The most serious one is the temp tables created on disk. Dealing with these is complex, but is covered well in High Performance MySQL.
MySQL Transaction Handler
This graph shows the transactional operations that took place at the MySQL server level. If you see a lot of BEGIN and COMMIT, that might be an indication that you’re using a dumb ORM or connection software that inserts spurious queries that you don’t know about, and adds latency to your application.