MySQL调优之性能监控

最新推荐文章于 2024-09-13 11:25:40 发布

原创最新推荐文章于 2024-09-13 11:25:40 发布 · 1.2k 阅读

5 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍MySQL 5.7版本下，如何使用SHOW PROFILE和Performance Schema进行性能监控，包括数据库连接数、查询性能分析等关键操作。通过具体示例，帮助读者掌握数据库性能调优技巧。

文章目录

前言

MySQL：5.7
存储引擎：InnoDB

准备工作

实操之前，先准备一个数据库，以便读者可以跟我进行一样的操作。
在MySQL官网下载官网提供的数据库 sakila database。
下载地址 https://dev.mysql.com/doc/index-other.html
在这里插入图片描述
下载zip文件夹，解压之后，将sakila-schema.sql和sakila-data.sql 导入数据库中。

性能监控

SHOW PROFILE

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

type 参数解释

all：显示所有性能信息
```
show profile all for query n;
```
block io：显示块io操作的次数
```
show  profile block io for query n;
```
context switches：显示上下文切换次数，被动和主动
```
show profile context switches for query n;
```
cpu：显示用户cpu时间、系统cpu时间
```
show profile cpu for query n;
```
IPC：显示发送和接受的消息数量
```
show profile ipc for query n;
```
page faults：显示页错误数量
```
show profile page faults for query n;
```
source：显示源码中的函数名称与位置
```
show profile source for query n;
```
swaps：显示swap的次数
```
show profile swaps for query n;
```

set profiling=1;

此工具默认是禁用的，可以通过服务器变量在会话级别动态的修改。

当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。

select * from store;

在mysql的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间

show profiles;

执行如下命令可以查看详细的每个步骤的时间：

show profile for query Query_ID;

操作演示
在这里插入图片描述
官网里说show profile 以后会被废弃，要用 performance schema 来替代。不过5.7还是支持的。

MySQL Performance Schema

提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。
performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失

MySQL5.7 是默认开启该功能的。如果要关闭要去配置文件修改。
在这里插入图片描述
Performance Schema 不太熟悉，跳过！

查看数据库连接数

show processlist;

在这里插入图片描述

id 表示 session id
user 表示操作的用户
host 表示操作的主机
db表示操作的数据库
command表示当前状态
- sleep：线程正在等待客户端发送新的请求
- query：线程正在执行查询或正在将结果发送给客户端
- locked：在mysql的服务层，该线程正在等待表锁
- analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
- Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
- sorting result：线程正在对结果集进行排序
- sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据
info表示详细的sql语句
time表示相应命令执行时间
state表示命令执行状态

一般项目里都会使用数据库连接池…