mysql 数据库整体运行状态确认思路

一时失言乱红尘 2022-08-06 10:16 181阅读 0赞

进行MySQL数据库性能优化和维护时，首先需要对MySQL数据库定一个整体的基调，对mysql数据库有一个整体的认识，然后在此基础上就可以比较方便地开展后续工作。

为数据库定个基调可以按照下面的思路依次进行：

# 一、掌握应用的特点 #

一般来说，DBA分为开发DBA和运维DBA两种。开发DBA会涉及到业务需求，逻辑模型涉及，物理模型设计，sql代码编写等内容，或者说涉及“外模式-模式-内模式”等各个方面，都业务运行逻辑多少都会有所了解。

而运维DBA一般对业务了解比较少，但从很多实际情况来看，尤其是处理一些优化和复制故障的问题，DBA不了解业务就会受到很多限制。所以DBA掌握数据库技术，要对业务和业务逻辑也熟悉，而且业务优先与技术，只有了解业务逻辑，才能将数据库运行到一个最好的状态。

# 二、感性的认识 #

数据库运行的业务不同，参数配置和运行特点也不相同。大体上说，数据库主要用于客户端进行DML操作，select用于查询数据，insert/update/delete用于插入/修改/删除数据，根据两类操作的比重不同，数据库可以分为两类：

OLTP型：insert/update/delete 操作多于select

OLAP型：select操作多于insert/update/delete

OLTP是事务型，是增删改比较多，事务小但数量多；OLAP是分析型的，查询比较多，查询数量少但运行时间通常较长。

对于分析性能问题时，对于把握数据库的主要操作进行归类比较重要，这样可以面对整个业务模型进行归类，化整为零，同时这点也要与上面的业务系统结合，只有了解和熟悉业务状态，才可以在数据库层面运维更好；

总之，感性认识就是指一个数据库是事务比较多，还是查询比较多。

# 三、使用 show status 命令查看参数值 #

通过对事务和查询的把握，可以对数据库进行一个整体的感性认识，接下来，还可以通过 show status 命令进行一些主要参数状态的检查，通过实际的数据确认数据库的各种状态。

1.了解各种SQL的执行频率

show global status like 'com\_select';   执行select 操作的次数，一次查询只累加1；

show global status like'com\_insert';    执行insert操作的次数，对于批量插入的insert操作，只累加一次；

show global status like'com\_update';  执行update操作的次数；

show global status like'com\_delete';    执行delete操作的次数；

其他类似参数有： com\_stmt\_prepare, com\_stmt\_fetch,...

通过实际计数器查询出数据，从理性分析出执行查询操作与DML执行次数进行比较；

用技术手段检验由业务了解而形成的感性认识；

对于业务基调的认识，先从业务认识到整体上判断，然后再通过具体数据库参数进行确认，比较查询操作的统计数据和DML操作的统计数据的大小；

2.从整体入手了解数据库服务器的状态

show \[full\] processlist

通过该命令，可以查看目前有哪些进程正在运行。

3.了解事务主动提交和回滚的情况

show global status like 'com\_commit';       提交了的事务计数，主动执行commit命令

show global status like 'com\_rollback';       回滚了的事务计数，主动执行rollback命令

注意：自动提交/回滚的事务不计算在内

通过对事务的提交和回滚的操作比较，可以大致了解事务的发生状况。

4.根据计数器了解读写情况

show status like 'innodb\_rows\_read';           查询返回的行数，不仅是select操作，delete和update也会触发对元组的读操作

show status like 'innodb\_rows\_inserted';       执行insert操作成功插入的行数

show status like 'innodb\_rows\_updated';       执行update操作成功更新的行数

show status like 'innodb\_rows\_deleted';       执行delete操作成功删除的行数

通过对行的计数器进行统计，可以了解数据库的读写操作；元组如果发生删除操作，删除操作发生后计数，read也会增加计数。

5.区分“索引读”还是“随机读”

这种情况，不从缓存区的角度出发，从单表扫描数据的方式的角度出发。

mysql> show global status like'handler\_read%';

\+-----------------------+---------------+

| Variable\_name        | Value         |

\+-----------------------+---------------+

| Handler\_read\_first    |70372176      |

| Handler\_read\_key      |878000134111  |

| Handler\_read\_last     |1628850508    |

| Handler\_read\_next     |7153827933028 |

| Handler\_read\_prev     |1253473280990 |

| Handler\_read\_rnd      |448021869416  |

| Handler\_read\_rnd\_next | 1374610044012 |

\+-----------------------+---------------+

7 rows in set (0.00 sec)

该查询结果中的几个参数含义如下：

**Handler\_read\_first **此选项表明SQL是在做一个全索引扫描，注意是全部，而不是部分，所以说如果存在WHERE语句，这个选项是不会变的。如果这个选项的数值很大，既是好事也是坏事。说它好是因为毕竟查询是在索引里完成的，而不是数据文件里，说它坏是因为大数据量时，简便是索引文件，做一次完整的扫描也是很费时的。

**Handler\_read\_key **此选项数值如果很高，那么恭喜你，你的系统高效的使用了索引，一切运转良好。

**Handler\_read\_next **此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数。

**Handler\_read\_prev **此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC。

**Handler\_read\_rnd **简单的说，就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序。

**Handler\_read\_rnd\_next **此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。

数据库的随机读写和顺序读写，也可以通过计数器进行判断。这样层层深入后，就可以更加深入的了解数据库的执行状态。

6.其他参数值确认

定期检查判断锁：

show status like 'Table%';

显示慢查询的次数：

show status like 'Slow%';

显示服务器工作时间：

show status like 'Up%';

查询数据库的各方面运行状态，类似于给系统做了一个体检；然后在通过其他方式进行更深入的了解。这个体检结果是对mysql数据库调优的基础。

注意：上述命令在查看mysql运行状态时，与查询variables一样，都是既有查看当前会话的 show status like'';命令，也有查看所有会话的 show global status like '';命令，有的时候两种方式查询出的结果是一致的。但大部分时候，两者的查询结果是不同的，为了确认mysql运行整体状态，一般建议使用show global status like''; 命令查询结果。

示例：

在一个生产系统中，按照上面的方法进行一个数据库的体检结果为：

1.SQL执行频率

mysql>show global status like 'com\_select';

\+---------------+-------------+

|Variable\_name | Value       |

\+---------------+-------------+

|Com\_select    | 16119391072 |

\+---------------+-------------+

1row in set (0.00 sec)

mysql>show global status like 'com\_insert';

\+---------------+-----------+

|Variable\_name | Value     |

\+---------------+-----------+

|Com\_insert    | 195615221 |

\+---------------+-----------+

1row in set (0.00 sec)

mysql> show global status like 'com\_update';

\+---------------+-------------+

| Variable\_name | Value       |

\+---------------+-------------+

| Com\_update    | 10947755629 |

\+---------------+-------------+

1 row in set (0.00 sec)

mysql>show global status like 'com\_delete';

\+---------------+---------+

|Variable\_name | Value   |

\+---------------+---------+

|Com\_delete    | 1311639 |

\+---------------+---------+

1row in set (0.00 sec)

由这几个参数值 可以得到： select /(insert+update+delete) = 16119391072 /(195615221+10947755629+1311639) = 16119391072/11144682489=1.45

也即整个数据库的select操作与事务操作的比例大致为3:2，可以归类为OLAP系统。

2.服务器整体状态

show \[full\] processlist

查询当前有186个会话。

3.事务主动提交和回滚的状态

mysql> show global status like 'com\_commit';

\+---------------+------------+

| Variable\_name | Value      |

\+---------------+------------+

| Com\_commit    | 4756582998 |

\+---------------+------------+

1 row in set (0.00 sec)

mysql>

mysql> show global status like 'com\_rollback';

\+---------------+------------+

| Variable\_name | Value      |

\+---------------+------------+

| Com\_rollback  | 3544847441 |

\+---------------+------------+

1 row in set (0.00 sec)

由上面两个值，可以得知该数据库中的事务提交、回滚比为 4756582998/3544847441=1.34 ，约为7:5.

4.以行为单位的读写情况

mysql> show status like 'innodb\_rows\_read';

\+------------------+---------------+

| Variable\_name    | Value        |

\+------------------+---------------+

| Innodb\_rows\_read | 9090316959495 |

\+------------------+---------------+

1 row in set (0.00 sec)

mysql> show status like 'innodb\_rows\_inserted';

\+----------------------+-----------+

| Variable\_name        | Value    |

\+----------------------+-----------+

| Innodb\_rows\_inserted | 453143758 |

\+----------------------+-----------+

1 row in set (0.00 sec)

mysql> show status like 'innodb\_rows\_updated';

\+---------------------+-------------+

| Variable\_name       | Value      |

\+---------------------+-------------+

| Innodb\_rows\_updated | 10870583056 |

\+---------------------+-------------+

1 row in set (0.00 sec)

mysql> show status like 'innodb\_rows\_deleted';

\+---------------------+-----------+

| Variable\_name       | Value    |

\+---------------------+-----------+

| Innodb\_rows\_deleted | 114969563 |

\+---------------------+-----------+

1 row in set (0.00 sec)

5.区分“索引读”还是“随机读”：

mysql> show global status like'handler\_read%';

\+-----------------------+---------------+

| Variable\_name        | Value         |

\+-----------------------+---------------+

| Handler\_read\_first    | 70364190     |

| Handler\_read\_key      |877946762331  |

| Handler\_read\_last     |1628785558    |

| Handler\_read\_next     |7152670258238 |

| Handler\_read\_prev     |1253470447566 |

| Handler\_read\_rnd      |448009044979  |

| Handler\_read\_rnd\_next | 1374495674830 |

\+-----------------------+---------------+

7 rows in set (0.00 sec)

6.其他参数的确认

定期检查判断锁：

mysql> show status like 'Table%';

\+-----------------------+-------------+

| Variable\_name         | Value      |

\+-----------------------+-------------+

| Table\_locks\_immediate | 83087055922 |

| Table\_locks\_waited    | 395205     |

\+-----------------------+-------------+

2 rows in set (0.00 sec)

显示慢查询的次数：

mysql> show global status like 'Slow%';

\+---------------------+----------+

| Variable\_name       | Value   |

\+---------------------+----------+

| Slow\_launch\_threads | 0        |

| Slow\_queries        | 39341149 |

\+---------------------+----------+

2 rows in set (0.00 sec)

显示服务器工作时间：

mysql> show global status like 'Up%';

\+---------------------------+----------+

| Variable\_name            | Value    |

\+---------------------------+----------+

| Uptime                   | 20514193 |

| Uptime\_since\_flush\_status | 20514193 |

\+---------------------------+----------+

2 rows in set (0.00 sec)

# 四、系统监控 #

上面是通过了解业务，对数据库类型的判断建立感性认识，在通过 show status 参数值获得mysql实际运行数据。但在实际运行过程中，对运行状态和性能的监控，还是需要靠监控系统来实现。

mysql官方推荐的方式是 perfmance-schema 性能视图的方式，该方式可以简写为PFS。

PFS可以在配置文件中添加，或者mysql启动命令中加入启动PFS的参数。

PFS的体系结构有五个部分：

setup\_actors 定义监控什么样的用户；

setup\_consumers 定义监控什么事件；

setup\_instruments 定义监控什么仪表；

setup\_objects 定义监控5类数据库对象；

setup\_timers 定义监控那些事件计时器；

PFS的使用模式有全系统监控和定制监控两种方式：全系统监控的监控项全部开启，便于分析不确定的问题，会影响系统系能；

定制监控的监控项部分开启，便于分析确定的问题，影响系统性能相对较少。

开启了PFS后，如果遇到系统卡住的情况，就可以在PFS中依次查询相关表信息，来确认进程状态，进而解决问题。

对于mysql数据库性能优化，需要掌握数据原理，MySQL内部实现，PFS细节和内容三个方面的基础，能够熟练掌握和运用后，就可以晋级为MySQL数据库调优专家。

mysql 数据库整体运行状态确认思路

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关前端页面布局整体思路

相关 SpringBoot单元测试整体运行

相关 php mc服务器状态,怎样实时监控服务器运行状态？求助一点思路

相关【MySQL】运行原理（一）：整体架构

相关 mysql 数据库整体运行状态确认思路

相关解决面试问题的整体思路

相关查看MySQL运行状态

相关 MySQL运行状态show status详解

相关 Linux操作系统调优相关工具(四)查看Network运行状态和系统整体运行状态

相关 NorthWind 数据库整体关系