MySQL性能分析常用步骤与命令

最新推荐文章于 2024-01-16 10:08:04 发布

typ2004

最新推荐文章于 2024-01-16 10:08:04 发布

阅读量368

点赞数 1

分类专栏： mysql 文章标签： mysql 故障

本文链接：https://blog.csdn.net/typ2004/article/details/118017511

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1、top查看cpu负载

按1能查看所有核心的状态

# man top
us, user : time running un-niced user processes
sy, system : time running kernel processes
ni, nice : time running niced user processes
id, idle : time spent in the kernel idle handler
wa, IO-wait : time waiting for I/O completion
hi : time spent servicing hardware interrupts
si : time spent servicing software interrupts
st : time stolen from this vm by the hypervisor

2、sar -d 查看0点以来10分钟一次的磁盘IO情况

sar -d 10 以10秒一次的间隔从现在开始查看磁盘IO情况。

其中：

sar -u 查看0点以来10分钟一次的cpu负载情况

其中：ioawait和idle能再一定程度上反映io负载情况。

3、查看主从同步状态

当主从同步开启的时候，slave上会创建2个线程，可以查看这两个线程的binlog状态是否同步。

I/O线程。该线程连接到master机器，master机器上的binlog dump线程会将binlog的内容发送给该I/O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log。
SQL线程。该线程读取I/O线程写入的relay log。并且根据relay log的内容对slave数据库做相应的操作.
当主从同步开启的时候，slave上会创建2个线程。

mysql>show slave status\G;

Master_log_File: mysql_bin.000666

Read_Master_Log_pos: 123456789

Slave_IO_Running: Yes

Slave_SQL_Running:Yes

Relay_Mater_log_File : mysql_bin.000666

Exec_Master_log_pos: 123456789

Seconds_Behind_Master:0

4、查看innodb引擎状态

mysql> show engine innodb status\G;

可以查看BUFFER POOL AND MEMORY下的

reads/m creates/s writes/s //每秒读取、创建和写入的缓存页的数量

hit rate, young-making rate, not //表示1000次访问中，有多少次是命中了BufferPool缓存中的缓存页，以及每1000次访问有多少数据从冷数据区转移到热数据区，以及没有转移仍待在冷数据区的缓存页的数量

I/O sum[5198]:cur[0], //-- 最近50s读取磁盘页的总数，cur[0]表示现在正在读取的磁盘页的总数

最后，附上MySQL官方调优链接https://dev.mysql.com/doc/refman/8.0/en/optimization.html

5、通过doublewrite查看写压力

SQL>show global status like "innodb_dblwr%';

******************1.row*****************************

variable_name:Innodb_dblwr_pages_written

value:15832238

******************2.row*****************************

variable_name:Innodb_dblwr_writes

value:2157262

2 rows in set (0.0 sec)

doublewrite buffer大写为2M，对应有共享表空间的物理磁盘上的2M（128个连续页，2个extent，分为两个1M）。脏页刷新时，先写到doublewrite buffer，doublewrite buffer每次将1M顺序地写到对应的共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘。前者顺序写，开销不大，后者离散写。

Innodb_dblwr_pages_written 最多是Innodb_dblwr_writes的64倍（1M/16K）,如果远小于这个比例，说明写压力不是很大。

6、显示当前正在执行的MySQL连接

select * from information_schema.processlist where command != 'Sleep';

或show processlist；//show processlist的Info过长时会截取（100字符），且也不支持按属性过滤，不利于分析

ID

唯一连接标识。SHOW PROCESSLIST语句所示Id的值，performance_schema.threads表PROCESSLIST_ID的值，mysql 线程 CONNECTION_ID() 函数返回的值，与此值是一样的。
USER

提交该语句的 MySQL 用户。值为 system user 代表是服务器内部执行任务的无客户端线程，比如，一个 delayed-row 处理线程或者主从复制的一个 I/O or SQL 线程。对于 system user，在 Host column不指定任何Host值。值为unauthenticated user 代表已经建立了连接但是客户端用户还没有认证通过的线程。值为 event_scheduler 代表监控 scheduled events 的线程。(参考 Section 25.4, “Using the Event Scheduler”)。
HOST

提交语句的客户端的 host name ( system user 没有 host)。TPC/IP连接的host name以 *host_name*:*client_port* 的格式显示，以便于确认是哪个客户端在操作。
DB

线程的默认数据库，如果没有则是 NULL 。
COMMAND

线程根据客户端行为正在执行的命令的类型，或是 Sleep如果连接空置。对于线程命令的描述，参考Section 8.14, “Examining Server Thread (Process) Information”。该列的值与客户端/服务器协议的 COM_*xxx*命令和Com_*xxx*状态变量相应。参考Section 5.1.9, “Server Status Variables”
TIME

线程处于当前状态的时间，以秒计。对于SQL复制线程，值为最后复制事件的时间戳和复制服务器的真实时间的差，以秒计。
STATE

描述线程正在做什么的 action, event, or state。

大多数状态都对应的很快的操作。如果一个线程在一个状态保持了很长时间，那么就是值得调查的问题了。
INFO

线程正在执行的语句，如果没有执行则为 NULL 。语句可以是客户端发送到服务器的，也可以是某语句执行其他语句的内部语句。比如，一个 CALL 语句调用了一个执行select语句的存储过程， INFO 值会显示该select语句。

7、查看慢查询日志

通过打开慢查询开关，查看慢查询日志即可。

（1）mysql>show variables like "%slow%"; //查看是否已开

Mysql开启慢查询日志

设置慢查询日志方法一：全局变量设置(该方式数据库重启全部失效，得重新配置)

（2）可以设置或更改慢查询日志位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //linux
mysql> set global slow_query_log_file='D:\\mysq\data\slow.log'; //windows
设置查询超过1秒就记录（如果有时候用命令不起作用，那么可以关闭再打开）

（3）设置慢查询日志时间，再将 slow_query_log 全局变量设置为“ON”状态

mysql> set global long_query_time=1;

mysql> set global slow_query_log='ON';

方法二：配置文件设置（服务器重启不影响）
修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log //linux
long_query_time = 1
3.重启MySQL服务

service mysqld restart

最后，附上MySQL官方调优链接https://dev.mysql.com/doc/refman/8.0/en/optimization.html