My logo
Published on

MySQL 性能抖动与慢查询问题分析

目录

  1. 性能抖动的原因
  2. 磁盘、网络和 CPU 负载对 SQL 性能的影响
  3. 总结与优化建议

性能抖动的原因

Buffer Pool 缓存页刷盘

当 MySQL 的 Buffer Pool 缓存页满了时，执行一个查询语句可能会导致大量缓存页需要刷入磁盘。由于磁盘 I/O 较慢，查询语句必须等待这些缓存页刷盘完成后，才能从磁盘加载所需的数据页到 Buffer Pool 中。这会导致查询语句的执行时间显著增加，表现为性能抖动。

场景：

  • 执行一个查询语句，需要加载大量数据到 Buffer Pool。
  • Buffer Pool 已满，必须将部分缓存页刷入磁盘。
  • 刷盘操作耗时较长，导致查询语句执行时间增加。

Redo Log 文件写满

当 MySQL 的 Redo Log 文件写满时，MySQL 需要回到第一个 Redo Log 文件进行覆盖写。如果第一个 Redo Log 文件中对应的脏页尚未刷入磁盘，MySQL 必须先将这些脏页刷盘，才能继续执行更新语句。这会导致更新语句的执行时间显著增加，表现为性能抖动。

场景：

  • 执行大量更新语句，Redo Log 文件写满。
  • MySQL 需要覆盖第一个 Redo Log 文件，但该文件中的脏页尚未刷盘。
  • 必须等待脏页刷盘完成后，才能继续执行更新语句，导致更新语句执行时间增加。

Buffer Pool 参数的详细说明

# 查询buffer
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

# 查询当前buffer 等的配置
SHOW ENGINE INNODB STATUS;

# 查看当前已经启动和正在运行的线程。
show GLOBAL STATUS like "%thread%";

# 查看数据库的连接数
show VARIABLES like 'max_connections';

# 查看执行计划
EXPLAIN select * from table;

# 这将确保用户"myuser"最多只能使用10个并发连接来操作"mydatabase"数据库中的数据。注意，这个参数# 只针对用户而不是针对数据库，并且只能在MySQL 5.7及以上版本才能使用。
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost' WITH MAX_USER_CONNECTIONS 10;


着查看一下当前被锁定的事物
查看当前的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
查看锁定的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

select * from information_schema.processlist where state = 'Waiting for table metadata lock';

mysql

  • Total memory allocated,这就是说buffer pool最终的总大小是多少
  • Buffer pool size,这就是说buffer pool一共能容纳多少个缓存页
  • Free buffers,这就是说free链表中一共有多少个空闲的缓存页是可用的
  • Database pages 和01d database pages,就是说Iru链表中一共有多少个缓存页，以及冷数据区域里的缓存页数 量
  • Modified db pages,这就是flush链表中的缓存页数量
  • Pending reads和Pending writes,等待从磁盘上加载进缓存页的数量，还有就是即将从Iru链表中刷入磁盘的数 量、即将从flush链表中刷入磁盘的数量
  • Pages made young和not young,这就是说已经Iru冷数据区域里访问之后转移到热数据区域的缓存页的数量， 以及在Iru冷数据区域里1s内被访问了没进入热数据区域的缓存页的数量
  • youngs/s ^not youngs/s,这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区 域里被访问了但是不能进入热数据区域的缓存页的数量
  • Pages read xxxx, created xxx, written xxx, xx reads/s, xx creates/s, 1xx writes/s,这里就是说已经读取、 创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量
  • Buffer pool hit rate xxx / 1000,这就是说每1000次访问，有多少次是直接命中了buffer pool里的缓存的
  • young-making rate xxx / 1000 not xx / 1000,每1000次访问，有多少次访问让缓存页从冷数据区域移动到了 热数据区域，以及没移动的缓存页数量
  • (12) LRU len：这就是Iru链表里的缓存页的数量 (13) I/O sum：最近50s读取磁盘页的总数 (14) I/O cur：现在正在读取磁盘页的数量

磁盘、网络和 CPU 负载对 SQL 性能的影响

磁盘负载过高

当 MySQL 服务器的磁盘 I/O 负载特别高时，磁盘可能无法及时处理 SQL 语句的随机 I/O 请求。这会导致 SQL 语句的执行时间增加，甚至可能使原本快速的 SQL 语句变成慢查询。

场景：

  • 磁盘 I/O 负载过高，无法及时处理 SQL 语句的随机 I/O 请求。
  • SQL 语句需要等待磁盘 I/O 完成，导致执行时间增加。

网络负载过高

当 MySQL 服务器的网络负载过高时，可能会出现以下两种情况：

  1. 连接获取延迟：SQL 语句需要等待较长时间才能获取到与 MySQL 的连接。
  2. 数据传输延迟：SQL 语句执行完成后，查询结果因网络带宽打满而无法及时返回。

场景：

  • 网络负载过高，SQL 语句需要等待较长时间才能获取连接。
  • 查询结果因网络带宽打满而无法及时返回，导致 SQL 语句执行时间增加。

CPU 负载过高

当 MySQL 服务器的 CPU 负载过高时，CPU 可能忙于处理其他任务，无法及时执行 SQL 语句。这会导致 SQL 语句的执行时间增加，表现为慢查询。

场景：

  • CPU 负载过高，无法及时执行 SQL 语句。
  • SQL 语句需要等待 CPU 资源，导致执行时间增加。

总结与优化建议

性能抖动的原因

  1. Buffer Pool 缓存页刷盘：当 Buffer Pool 缓存页满时，查询语句需要等待缓存页刷盘，导致性能抖动。
  2. Redo Log 文件写满：当 Redo Log 文件写满时，更新语句需要等待脏页刷盘，导致性能抖动。

磁盘、网络和 CPU 负载对 SQL 性能的影响

  1. 磁盘负载过高：磁盘 I/O 负载过高会导致 SQL 语句执行时间增加。
  2. 网络负载过高：网络负载过高会导致 SQL 语句连接获取延迟或数据传输延迟。
  3. CPU 负载过高：CPU 负载过高会导致 SQL 语句执行时间增加。

优化建议

  1. 监控系统负载：定期监控 MySQL 服务器的磁盘、网络和 CPU 负载，确保系统资源充足。
  2. 优化 SQL 语句：避免执行大量数据加载或更新的 SQL 语句，减少对 Buffer Pool 和 Redo Log 的压力。
  3. 调整 MySQL 配置：根据业务需求调整 Buffer Pool 大小和 Redo Log 文件大小，避免频繁刷盘。
  4. 使用 SSD：使用 SSD 替代传统机械硬盘，提升磁盘 I/O 性能。
  5. 优化网络配置：确保 MySQL 服务器的网络带宽充足，避免网络负载过高。

通过以上优化措施，可以有效减少 MySQL 性能抖动和慢查询问题的发生，提升数据库的整体性能。

基于sysbench构造测试表和测试数据

sysbench 一一db-driver=mysql 一一time=300 一一threads=10 一一report-interval=1 
一一mysql-host=127.0.0.1 一一mysqlport=3306 一一mysql-user=test_user 
一一mysql-password=test_user 一一mysq I - d b=test_d b 
一一tables=20 一一 table_size=1000000 oltp_read_write 一一db-ps-mode=disable prepare

上面我们构造了一个sysbench命令，给他加入了很多的参数，现在我们来解释一下这些参数：

  1. 一db-driver=mysql：这个很简单，就是说他基于mysql的驱动去连接mysql数据库，你要是oracle,或者sqlserver,那自 然就是其他的数据库的驱动了
  2. —time=300：这个就是说连续访问300秒
  3. —threads=10：这个就是说用10个线程模拟并发访问
  4. --report-interval=1：这个就是说每隔1秒输出一下压测情况
  5. 一mysql-host=127.0.0.1 ―mysql-port=3306 ―mysql-user=test_user -mysql-password=test_user： 这一大串， 就是说连接到哪台机器的哪个端口上的MySQL库，他的用户名和密码是什么
  6. 一mysql-db=test_db --tables=20 ―table_size=1000000：这一串的意思，就是说在test_db这个库里，构造20个测试 表，每个测试表里构造100万条测试数据，测试表的名字会是类似于sbtestl, sbtest2这个样子的
  7. oltp_read_write：这个就是说，执行。Itp数据库的读写测试
  8. --db-ps-mode=disable ：这个就是禁止ps模式