Mysql

1.1.1监控指标说明

主要针对SQL耗时、吞吐量（QPS TPS）命中率 锁等待等指标进行监控。

• 本来运维工具产品有以下参数：(global status里面的状态量)
  TPS/QPS
  连接数
  每秒SQL执行次数
  全表扫描数
  InnoDB缓冲池命中率
  InnoDB缓冲池使用率/脏块率
  InnoDB逻辑读
  排序记录数
  InnoDB锁等待次数
  InnoDB脏页数量
  InnoDB读写量
  InnoDB buffer pool读写次数
  InnoDB日志文件写次数
  打开文件/表数量
  慢SQL
  MyISAM读写次数
  MyISAM key Buffer 读/写/利用率(%)
  MySQL执行语句时在硬盘上自动创建的临时表的数量

指标	解释
TPS	是Transactions Per Second的缩写，也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。
QPS	是Queries Per Second的缩写，意思是每秒查询率，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
连接数	当前总连接数The number of connection attempts (successful or not) to the MySQL server. Connections
每秒SQL执行次数	insert delete update select语句 ROWDML：InnoDB每秒钟操作数据行数的统计，根据操作的不同，分为平均每秒向日志文件的物理写次数、平均每秒从InnoDB表“删除/更新/读取/插入”的行数。
全表扫描数	平均每秒全表扫描次数 show global status like “handler_read%”
InnoDB缓冲池命中率	InnoDB buffer pool hit 不低于95%
InnoDB缓冲池使用率/脏块率	InnoDB缓冲池的读命中率、利用率以及缓冲池脏块的百分率(InnoDB缓冲池)
InnoDB物理读	innodb_buffer_pool_reads: 平均每秒从物理磁盘读取页的次数
InnoDB逻辑读	innodb_buffer_pool_read_requests: 平均每秒从innodb缓冲池的读次数
排序记录数	Sort_rows
InnoDB锁等待次数	Innodb_row_lock_current_waits
InnoDB脏页数量	innodb_buffer_pool_pages_dirty
InnoDB读写量	InnoDB每秒钟的读取和写入次数。/innodb_data_read innodb_data_written
InnoDB buffer pool读写次数	innodb_buffer_pool_read_requests/ innodb_buffer_pool_write_requests
InnoDB日志文件写次数	InnoDB日志：InnoDB的日志写入情况/ Innodb_log_writes
打开文件/表数量	Innodb_num_open_files/Com_show_open_tables
慢SQL	Slow_queries
MyISAM读写次数	MyISAM平均每秒的读写次数。 key_read_requests/ key_write_requests
MyISAM key Buffer 读/写/利用率(%)	MyISAM平均每秒的Key Buffer使用状况。Key_usage_ratio =Key_blocks_used/(Key_blocks_used+Key_blocks_unused)*100 —- Key_read_hit_ratio=(1-Key_reads/Key_read_requests)*100 — Key_write_hit_ratio =(1-Key_writes/Key_write_requests)*100
MySQL执行语句时在硬盘上自动创建的临时表的数量	执行语句时在硬盘上自动创建的临时表的数量（临时表）Created_tmp_disk_tables
IOPS	RDS实例的IOPS（每秒IO请求次数）

系统吞吐量要素：
一个系统的吞吐量（承压能力）与request对CPU的消耗、外部接口、IO等等紧密关联。单个request 对CPU消耗越高，外部系统接口、IO速度越慢，系统吞吐能力越低，反之越高。

系统吞吐量几个重要参数：QPS（TPS）、并发数、响应时间

        QPS（TPS）：（Query Per Second）每秒钟request/事务 数量

        并发数： 系统同时处理的request/事务数

        响应时间：  一般取平均响应时间

（很多人经常会把并发数和TPS理解混淆）

理解了上面三个要素的意义之后，就能推算出它们之间的关系：
QPS（TPS）= 并发数/平均响应时间    或者   并发数 = QPS*平均响应时间

TPS/QPS区别及理解：

1、TPS即每秒处理事务数，包括：”用户请求服务器”、”服务器自己的内部处理”、”服务器返回给用户”，这三个过程，每秒能够完成N个这三个过程，TPS也就是3；

2、QPS基本类似于TPS，但是不同的是，对于一个页面的一次访问，形成一个TPS；但一次页面请求，可能产生多次对服务器的请求，服务器对这些请求，就可计入QPS之中。

3、一般的，评价系统性能均以每秒钟完成的技术交易的数量来衡量。系统整体处理能力取决于处理能力最低模块的TPS值。

4、QPS对应fetches/sec，即每秒的响应请求数，也即是最大吞吐能力。

MySQL RDS磁盘占用包括日志文件（binlog文件、错误日志等），数据文件（数据、索引文件），和一些其他文件（ibdata，logfile_0,临时文件等）
造成 MySQL 实例空间使用率过高，主要有如下四种原因：
Binlog 文件占用高。
数据文件占用高。
临时文件占用高。
系统文件占用高。

对应解决方法：
1、定期删除binlog，假如当前dml造成大量的binlog，可以通过RDS控制台即使清理binlog
2、通过truncate或者drop及时清除不需要的表
3、终止对应的回话
4、ibdata中undo占用高可以进行undo分离，或者进行数据转移；增加redo log file的大小和组数

磁盘空间、磁盘空间详情：

这段时间的数据是一条直线，空间状态都很稳定，没有性能问题。

MySQL RDS磁盘占用包括日志文件（binlog文件、错误日志等），数据文件（数据、索引文件），和一些其他文件（ibdata，logfile_0,临时文件等）

造成 MySQL 实例空间使用率过高，主要有如下四种原因：

Binlog 文件占用高。

数据文件占用高。

临时文件占用高。

系统文件占用高。

对应解决方法：

1、定期删除binlog，假如当前dml造成大量的binlog，可以通过RDS控制台即使清理binlog

2、通过truncate或者drop及时清除不需要的表

3、终止对应的回话

4、ibdata中undo占用高可以进行undo分离，或者进行数据转移；增加redo log file的大小和组数

MySQL内存使用率：

基本上是一条直线，没有变化。因为MySQL有innodb_buffer_pool，大约为物理内存的50%-80%，内存使用率高一些，相对的性能也会提高

cpu/mem的使用率：

现在cpu的使用率在30%左右，不算高。内存的使用率基本平稳在30%左右，正常

CPU的使用率高的原因：

• 系统执行应用提交查询（包括数据修改操作）时需要大量的逻 辑读，（逻辑 IO，执行查询所需访问的表的数据行数），需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。造成逻辑读高的原因，很可能是异常SQL，扫描的数据行数过多导致。

物理内存：

直线保持基本无变化，物理内存就是实际的内存条的内存大小

连接数：show processlist

连接数是指用户能够创建多少个连接，也就是MySQL show processlist命令输出结果中运行着的线程个数。
当前总连接数： show processlist结果中的总线程个数
当前活跃连接数：show processlist结果中的活跃线程数(Command列状态为sleep的不计入在内)

当前连接数在1500左右，后来增高至6000左右。但是活跃连接数一直在个位数，说明现在的空闲连接数过多。总连接数超过参考值2000。出现严重问题。

数据库的连接一般是使用长连接，可能是应用侧的连接池初始连接数设置过高，应用启动后建立多个到RDS的空闲连接

解决方法：

• 1、长连接建议启用连接池的复用连接功能。

• 2、对于交互式连接和非交互式连接，建议修改相应的wait_timeout和interactive_timeout参数。（空闲时间超过指定的时间后，RDS的连接会主动关闭）。通过DT，RDS控制台，性能优化，参数设置中修改。

• 3、kill当前的空闲会话。

网络流量：

Bytes_received/s：平均每秒的输入流量，单位byte/s
Bytes_sent/s：平均每秒的输出流量，单位byte/s

IOPS：

每秒读写的次数。现在是比较小的。在0-0.2之间。

如果IOPS比较高的话，有可能是以下原因：

1、实例内存满足不了缓存数据或排序等需要，导致产生大量的物理 IO。

2、查询执行效率低，扫描过多数据行。

解决方法：

1、查询是否有慢SQL，优化慢SQL，可以参考杜康的实例卡慢诊断的优化建议，或者登录DMS，通过诊断报告、优化来进行SQL优化
2、终止查询语句
3、通过show processlist，或者DMS控制台、杜康等来kill查询回话id

QPS/TPS：

TPS= Com_insert/s + Com_update/s + Com_delete/s
QPS=Com_select/s + Com_insert/s + Com_update/s + Com_delete/s
官方文档入口com
QPS比较高，在90000左右，最高到达110000 。每秒的事务数在10000以上。正常，业务量比较高

原因分析：

• QPS比较高，每秒SQL的语句执行次数高，业务量上来，处于业务的高峰期，用户连接数增加，访问量增加。

• 如果QPS比较高，逻辑读不高，慢SQL也不是系统的瓶颈，QPS和cpu使用率的变化曲线吻合，这时候优化的余地就不高了，可以从实例规格、应用架构方面进行考虑。

• 如果QPS不高，查询执行效率低、执行时需要扫描大量表中数据、优化余地大，并且出现慢查询问题，QPS和CPU的变化曲线不吻合

• 如果QPS比较高，并且逻辑读也比较高，CPU的使用率增加，这时候可以优化优化相应的慢SQL，添加主实例的只读实例来缓解压力。

COMDML：

Com_select/s：平均每秒select语句执行次数

Com_insert/s：平均每秒insert语句执行次数

Com_update/s：平均每秒update语句执行次数

Com_delete/s：平均每秒delete语句执行次数

ROWDML：

innodb_rows_deleted: 平均每秒从innodb表删除的行数

innodb_rows_inserted: 平均每秒从innodb表插入的行数

innodb_rows_read: 平均每秒从innodb表读取的行数

innodb_rows_updated: 平均每秒从innodb表更新的行数

Innodb缓冲池：

缓冲池(innodb buffer pool)简单来说就是一块内存区域。缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。不能简单认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。

在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲池中。若在，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘中的页。

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_buffer_read_hit_ratio = ( 1 – innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100

缓冲池的利用率

innodb_buffer_usage = ( 1 – innodb_buffer_pool_pages_free / innodb_buffer_pool_pages_total) * 100

缓冲池的脏块的百分率

innodb_buffer_pool_pages_dirty / innodb_buffer_pool_pages_total

Innodb读写量：

平均每秒读取的数据量：innodb_data_read

平均每秒写入的数据量：innodb_data_written

Innodb读写次数：

平均每秒Innodb从文件中读取的次数：innodb_data_reads

平均每秒Innodb从文件中写入的次数：innodb_data_writes

Innodb日志：

平均每秒向日志文件的物理写次数：innodb_log_writes

平均每秒日志写请求次数：innodb_log_write_requests

平均每秒向日志文件完成fsync()写数量：innodb_os_log_fsyncs

临时表：

Created_tmp_disk_tables：MySQL执行语句时在磁盘上自动创建的临时表的数量

在某些情况下，MySQL服务器会自动创建内部临时表。用explain查看select语句的执行计划，如果extra列显示“using temporary”,即使用了内部临时表。一般情况下，MySQL会先创建内存临时表，内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表。

使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。常见的方法有：

1)创建索引：在ORDER BY或者GROUP BY的列上创建索引；

2)分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。

如果表的设计已经确定，修改比较困难，那么也可以通过优化SQL语句来减少临时表的大小，以提升SQL执行效率。

MyISAM Key Buffer：

为了最小化磁盘I/O，MyISAM将最频繁访问的索引块(Index block)都放在内存中，这样的内存缓冲区我们称之为Key Cache，它的大小可以通过参数key_buffer_size来控制。在MyISAM的索引文件中，连续的单元组成一个Block，Index block的大小等于该BTree索引节点的大小。Key Cache就是以Block为单位的。

当MySQL请求(读或写)MyISAM索引文件中某个Index Block时，首先会看Key Cache队列中是否已经缓存了对应block。

如果有，就直接在Key Cache队列中进行读写了，不再需要请求磁盘。如果是写请求，那么Key Cache中的对应Block就会被标记为Dirty(和磁盘不一致)。在MyISAM在Key Cache成功请求(读写)某个Block后，会将该Block放到Key Cache队列的头部。

如果Key Cache中没有待请求(读或写)的Block，MyISAM会向磁盘请求对应的Block，并将其放到Key Cache的队列头部。队列如果满了，会将队列尾部的Block删除，该Block如果是Dirty的，会将其Flush到磁盘上。我们看到MyISAM维护了一个LRU(Least Recently Used)的Key Cache队列。队列中的Dirty Block会在Block被踢出队列时Flush到磁盘上。

MyISAM平均每秒key buffer利用率

Key_usage_ratio =Key_blocks_used/(Key_blocks_used+Key_blocks_unused)*100

MyISAM平均每秒key buffer读命中率

Key_read_hit_ratio=(1-Key_reads/Key_read_requests)*100

MyISAM平均每秒key buffer写命中率

Key_write_hit_ratio =(1-Key_writes/Key_write_requests)*100

MyISAM读写次数：

key_read_requests: MyISAM平均每秒钟从缓冲池中的读取次数

Key_write_requests: MyISAM平均每秒钟从缓冲池中的写入次数

key_reads : MyISAM平均每秒钟从硬盘上读取的次数

key_writes : MyISAM平均每秒钟从硬盘上写入的次数

线程状态：

线程数跟连接数是对应的。此时也是连接的总线程数远大于活跃的线程数。

备库延迟：

目前主备延迟(slave-lag)为0.

主备延迟产生的原因：

• 1 主库产生非常大的binlog

a) 主库上执行大量的dml语句

b) 主库上执行大事务

c) 主库上没有主键的全表扫描

• 2 主库上执行ddl语句，时间过长

• 3 备库上对myisam表长时间查询，阻塞主库的binlog同步语句

• 4 备库实例的规格配置低，磁盘IO比较低

查看方法:

• 1 首先查看备库的IOPS是否存在瓶颈

• 2 备库show processlist查看是否存在大事务

• 3 主库的写入压力是否过高，dml语句是否过多

• 4 只读节点执行 show slave status \G，判断是否有 Waiting for table metadata lock；同时在主库排查下是否有DDL 操作

• 5 只读节点执行 show slave status \G，判断是否有 Waiting for table level lock; 同时通过 show full processlist; 同时在主库检查下是否有长时间对 MyISAM 引擎表的查询

慢SQL：

慢SQL数量的变化曲线跟CPU的使用率的变化曲线吻合，在CPU使用率高的时候，慢SQL也跟着增加。可以通过杜康对产生的慢SQL进行优化。

全表扫描次数：

handeler_read%
随着业务量的增加，全表扫描的次数也随之增加。Sql要尽量避免全表扫描

主实例问题与建议：

QPS升高，业务量高的情况下，产生一些慢查询SQL，并且空闲连接数太多

• 连接数：连接数严重超过参考值，并且有过多的空闲线程。首先检查应用是否使用连接池，如果使用连接池，检查连接池的配置是否合理
  
  • 优化慢SQL

root@itpux 12:14:  [(none)]> show global status like "%innodb%read%"\G
*************************** 1. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_rnd
        Value: 0
*************************** 2. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead
        Value: 0
*************************** 3. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_evicted
        Value: 0
*************************** 4. row ***************************
Variable_name: Innodb_buffer_pool_read_requests
        Value: 1356
*************************** 5. row ***************************
Variable_name: Innodb_buffer_pool_reads
        Value: 240
*************************** 6. row ***************************
Variable_name: Innodb_data_pending_reads
        Value: 0
*************************** 7. row ***************************
Variable_name: Innodb_data_read
        Value: 4002304
*************************** 8. row ***************************
Variable_name: Innodb_data_reads
        Value: 271
*************************** 9. row ***************************
Variable_name: Innodb_pages_read
        Value: 239
*************************** 10. row ***************************
Variable_name: Innodb_rows_read
        Value: 8
10 rows in set (0.00 sec)

参数	说明
Innodb_buffer_pool_read_ahead	预读次数
Innodb_buffer_pool_read_ahead_evicted	预读的页，但没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率
Innodb_buffer_pool_read_requests	从缓存池中读取页的次数
Innodb_buffer_pool_reads	从物理磁盘读取页的次数
Innodb_data_read	总共读入的字节数
Innodb_data_reads	发起读取请求的次数，每次读取可能需要读取多个页

缓冲池命中率:

= Innodb_buffer_pool_reads/(Innodb_buffer_pool_reads+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_read_requests)
参考链接