mysql的qps与tps等指标监控

1.SQL概念
Structured Query Language- - -结构化查询语言
有 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句；
数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句；
数据查询语言(DQL)，例如：SELECT语句；
数据控制语言(DCL)，例如： COMMIT、ROLLBACK、GRANT、REVOKE等语句

2.COMMIT：提交事务
查看事务状态：select @@autocommit; show variables like ‘%autocommit%’;
1或者ON表示自动提交；0或者OFF表示手动提交：需要commit命令提交事务。

下面实际演示一下：
首先新开session1并建表create table test_a(id1 int,id2 int);

然后设置手动提交set autocommit=0;（这样设置只对当前session有效，是临时的，如果想要永久全局设置，修改my.cnf文件，添加autocommit=0)；

再向表中插入数据insert into test_a values(1,1);

此时新开第二个session2，查询该表select * from test_a；可以发现，此时在session2上是查询不到数据的，原因就是因为在session1的事务还未提交。但是此时在session1是可以查到，这是不是和上述有点矛盾呢，其实不然，未提交的数据会临时写到内存或磁盘（内存不足的情况下），且未提交的数据上有个锁（涉及隔离级别，后续讲解），锁是只有当前事务唯一持有的，所以其他事务取不到，但当前事务是可以读取的。 <这段划重点，可以多理解一下>

接着换一种思路，在session2（自动提交）上插入一条新数据insert into test_a values(2,2);然后去session1查询，发现session1上仍然查不到新增的数据，这里有人会问session2不是设置的自动提交吗，为什么session1查不到数据呢？这是因为当session为非自动提交状态时，自事务开始后的DML操作对其都是不可见的，只有事务结束后才可见。在session1执行commit，就可以查询到新增数据了。

再看第三种情况，在session1新增一条数据，此时在session2查询是查不到新增数据的；此时在session1再新建一张表，再去session2查询，结果发现在session1未执行commit命令的情况下，session2竟然也可以查询到新增数据了。这是因为session1执行了DDL操作，触发了隐式提交事务的规则，所以其他session也可以看到DDL的修改了。但是drop命令有点特殊，如果事务还未结束，drop命令会被阻塞。

3.ROLLBACK：回滚事务
Rollback就是与commit相反，不提交事务，可以理解成撤回的意思

接下来看监控

指标介绍

  • QPS：Queries Per Second
    查询量/秒，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理查询量多少的衡量标准。
  • TPS : Transactions Per Second
    事务数/秒，是一台数据库服务器在单位时间内处理的事务的个数。

QPS指标

先来认识一下以下几个有关查询的指标：

  1. Questions：MySQL从上一次启动到当前客户端发送给MySQL的查询数量，不包括存储过程内部的查询。它不统计COM_PING,COM_STATISTICS,COM_STMT_PREPARE,COM_STMT_CLOSE,COM_STMT_RESET数量，但它会把show命令计算到指标当中。
  2. Queries：MySQL从上一次启动到当前客户端发送给MySQL的查询数量，包括存储过程内部的查询，它不统计COM_PING和COM_STATISTICS两个命令。
  3. Com_select：MySQL从上一次启动到当前所执行的查询语句总数量。

由上可见，在数据库中执行show命令会使questions值加1，而com_select则不记录，而且对于数据库的监控，经常会用到show命令，所以在用questions方式计算时，数据其实是被污染的。
而且questions的值在设置环境变量的时候，也是一直在增长的，而com_select的值在此过程中，并不增长。
所以在使用question方式进行计算时，人为拉高了qps的结果，相对来说，使用com_select此种方式来计算qps，相对比较帖近真实情况一些，也就是说，在同等条件下，拉高了qps的值。

以下将介绍通过Questions方式以及Com_select方式计算QPS

Questions方式计算QPS

1.Questions方式计算QPS公式

questions = show global status where variable_name='Questions';
uptime = show global status like 'Uptime';
qps=questions/uptime

如上，拿当前Questions值除去Uptime=QPS，这个QPS的意义为从MySQL上一次启动到当前并且包含show命令平均每秒的QPS值，假如某个时间段的查询数量特别高，但是通过除Uptime时间，也会被拉下来。并且此questions值包含了show命令及环境变量所造成的数据污染。

2.Questions方式计算QPS指标SQL
注：以下SQL在MySQL8.0中进行测试，5.6或5.7中的稍有差异。

select round(sum(if(variable_name='Questions',variable_value,0))/sum(if(variable_name='Uptime',variable_value,0)),1) as 'QPS' from performance_schema.global_status where variable_name in ('Questions','Uptime');

Com_select方式计算QPS

通过Com_select来计算QPS，可以连续获取两次Com_select指标，拿新指标减去老指标后再除于间隔时长的出间隔时间内的每秒平均值。这种方式更贴近真实一些。
1.Com_Select方式计算QPS脚本

Com_Select = show global status where Variable_name='Com_select';
#!/usr/bin/env bash
OLD_QPS=`echo "show global status where Variable_name='Com_select';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
sleep $1
NEW_QPS=`echo "show global status where Variable_name='Com_select';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
echo "($NEW_QPS-$OLD_QPS) / $1" | bc

TPS指标

获取TPS指标的方式也有两种：

  1. 基于com_commit、com_rollback计算tps
  2. 基于com_insert、com_delete、com_update的status,变量计算tps

基于com_commit、com_rollback计算tps

相关指标介绍：

  • Com_commit：MySQL从上一次启动到当前所执行的提交语句总数量
  • Com_rollback：MySQL从上一次启动到当前所执行的回退语句总数量

1.基于com_commit和com_rollback方式计算TPS公式
这样计算出来的TPS也是MySQL从上次启动到当前平均每秒的TPS指标

com_commit = show global status where variable_name='com_commit';
com_rollback = show global status where variable_name='com_rollback';
uptime = show global status where Variable_name='Uptime';
tps=(com_commit + com_rollback)/uptime

2.计算脚本

#!/usr/bin/env bash
COM_COMMIT=`echo "show global status where Variable_name='Com_commit';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
COM_ROLLBACK=`echo "show global status where Variable_name='Com_rollback';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
UPTIME=`echo "show global status where Variable_name='Uptime';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`

echo "($COM_COMMIT + $COM_ROLLBACK) / $UPTIME" | bc

基于com_insert、com_delete、com_update的status计算tps

相关指标介绍：

  • Com_update： MySQL从上一次启动到当前所执行的更新语句总数量
  • Com_delete：MySQL从上一次启动到当前所执行的删除语句总数量
  • Com_insert：MySQL从上一次启动到当前所执行的插入语句总数量

1.计算脚本
指定间隔时间内取值两次，然后新指标减去老指标后三个指标相加再除以间隔时间得出间隔时间内每秒平均TPS

#/usr/bin/env bash
OLD_COM_INSERT=`echo "show global status where Variable_name='Com_insert';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
OLD_COM_UPDATE=`echo "show global status where Variable_name='Com_update';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
OLD_COM_DELETE=`echo "show global status where Variable_name='Com_delete';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
sleep $1
NEW_COM_INSERT=`echo "show global status where Variable_name='Com_insert';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
NEW_COM_UPDATE=`echo "show global status where Variable_name='Com_update';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
NEW_COM_DELETE=`echo "show global status where Variable_name='Com_delete';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`

echo "(($NEW_COM_INSERT - $OLD_COM_INSERT) + ($NEW_COM_UPDATE - $OLD_COM_UPDATE) + ($NEW_COM_DELETE - $OLD_COM_DELETE)) / $1" | bc

 

1.系统mysql的进程数

ps -ef | grep “mysql” | grep -v “grep” | wc –l

2.Slave_running

mysql > show status like ‘Slave_running’;

如果系统有一个从复制服务器，这个值指明了从服务器的健康度

3.Threads_connected

mysql > show status like ‘Threads_connected’;

当前客户端已连接的数量。这个值会少于预设的值，但你也能监视到这个值较大，这可保证客户端是处在活跃状态。

4.Threads_running

mysql > show status like ‘Threads_running’;

如果数据库超负荷了，你将会得到一个正在（查询的语句持续）增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时， 要同时监视其他的一些值。

5.Aborted_clients

mysql > show status like ‘Aborted_clients’;

客户端被异常中断的数值，即连接到mysql服务器的客户端没有正常地断开或关闭。对于一些应用程序是没有影响的，但对于另一些应用程序可能你要跟踪该值，因为异常中断连接可能表明了一些应用程序有问题。

6.Questions

mysql> show status like ‘Questions’;

每秒钟获得的查询数量，也可以是全部查询的数量，根据你输入不同的命令会得到你想要的不同的值。

7.Handler_*

mysql> show status like ‘Handler_%’;

如果你想监视底层（low-level）数据库负载，这些值是值得去跟踪的。

如果Handler_read_rnd_next值相对于你认为是正常值相差悬殊，可能会告诉你需要优化或索引出问题了。Handler_rollback表明事务被回滚的查询数量。你可能想调查一下原因。

8.Opened_tables

mysql> show status like ‘Opened_tables’;

表缓存没有命中的数量。如果该值很大，你可能需要增加table_cache的数值。典型地，你可能想要这个值每秒打开的表数量少于1或2。

9.Select_full_join

mysql> show status like ‘Select_full_join’;

没有主键（key）联合（Join）的执行。该值可能是零。这是捕获开发错误的好方法，因为一些这样的查询可能降低系统的性能。

10.Select_scan

mysql> show status like ‘Select_scan’;

执行全表搜索查询的数量。在某些情况下是没问题的，但占总查询数量该比值应该是常量（即Select_scan/总查询数量商应该是常数）。如果你发现该值持续增长，说明需要优化，缺乏必要的索引或其他问题。

11.Slow_queries

mysql> show status like ‘Slow_queries’;

超过该值（–long-query-time）的查询数量，或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长，表明系统有性能问题。

12.Threads_created

mysql> show status like ‘Threads_created’;

该值应该是低的。较高的值可能意味着你需要增加thread_cache的数值，或你遇到了持续增加的连接，表明了潜在的问题。

13.客户端连接进程数

shell> mysqladmin processlist

mysql> show processlist;

你可以通过使用其他的统计信息得到已连接线程数量和正在运行线程的数量，检查正在运行的查询花了多长时间是一个好主意。如果有一些长时间的查询，管理员可以被通知。你可能也想了解多少个查询是在”Locked”的状态——该值作为正在运行的查询不被计算在内而是作为非活跃的。一个用户正在等待一个数据库响应。

14.innodb状态

mysql> show engine innodb status;

该语句产生很多信息，从中你可以得到你感兴趣的。首先你要检查的就是“从最近的XX秒计算出来的每秒的平均负载”。

(1)Pending normal aio reads: 该值是innodb io请求查询的大小（size）。如果该值大到超过了10—20，你可能有一些瓶颈。

(2)reads/s, avg bytes/read, writes/s, fsyncs/s:这些值是io统计。对于reads/writes大值意味着io子系统正在被装载。适当的值取决于你系统的配置。

(3)Buffer pool hit rate:这个命中率非常依赖于你的应用程序。当你觉得有问题时请检查你的命中率

(4)inserts/s, updates/s, deletes/s, reads/s:有一些Innodb的底层操作。你可以用这些值检查你的负载情况查看是否是期待的数值范围。

15.主机性能状态

shell> uptime

16.CPU使用率

shell> top

shell> vmstat

17.磁盘IO

shell> vmstat

shell> iostat

18.swap进出量(内存)

shell> free

19.MySQL错误日志

在服务器正常完成初始化后，什么都不会写到错误日志中，因此任何在该日志中的信息都要引起管理员的注意。

20.InnoDB表空间信息

InnoDB仅有的危险情况就是表空间填满—-日志不会填满。检查的最好方式就是：show table status;你可以用任何InnoDB表来监视InnoDB表的剩余空间。

21.QPS每秒Query量

QPS = Questions(or Queries) / seconds

mysql > show /* global */ status like ‘Question’;

22.TPS(每秒事务量)

TPS = (Com_commit + Com_rollback) / seconds

mysql > show status like ‘Com_commit’;

mysql > show status like ‘Com_rollback’;

23.key Buffer 命中率

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

mysql> show status like ‘Key%’;

24.InnoDB Buffer命中率

Innodb_buffer_read_hits = (1 – innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

mysql> show status like ‘innodb_buffer_pool_read%’;

25.Query Cache命中率

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

mysql> show status like ‘Qcache%’;

26.Table Cache状态量

mysql> show status like ‘open%’;

27.Thread Cache 命中率

Thread_cache_hits = (1 – Threads_created / connections ) * 100%

mysql> show status like ‘Thread%’;

mysql> show status like ‘Connections’;

28.锁定状态

mysql> show status like ‘%lock%’;

29.复制延时量

mysql > show slave status

30.Tmp Table状况(临时表状况)

mysql > show status like ‘Create_tmp%’;

31.Binlog Cache使用状况

mysql > show status like ‘Binlog_cache%’;

32.Innodb_log_waits量

mysql > show status like ‘innodb_log_waits’;

 

IOPS：(Input/Output Operations Per Second)，即每秒进行读写（I/O）操作的次数，多用于数据库等场合，衡量随机访问的性能。

存储端的IOPS性能和主机端的IO是不同的，IOPS是指存储每秒可接受多少次主机发出的访问，主机的一次IO需要多次访问存储才可以完成。例如，主机写入一个最小的数据块，也要经过“发送写入请求、写入数据、收到写入确认”等三个步骤，也就是3个存储端访问。

IOPS的测试benchmark工具主要有Iometer, IoZone, FIO等，可以综合用于测试磁盘在不同情形下的IOPS。对于应用系统，需要首先确定数据的负载特征，然后选择合理的IOPS指标进行测量和对比分析，据此选择合适的存储介质和软件系统。

 

 

转载 https://blog.csdn.net/weixin_43639512/article/details/84304672

转载 https://zhuanlan.zhihu.com/p/441831281

posted on 2022-12-08 11:14  该用户很懒  阅读(1715)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 该用户很懒
Powered by .NET 8.0 on Kubernetes