Mysql 导致的I/O运行瓶颈优化记录

Mar 15, 2024

遇到的问题：线上环境卡死，定位原因是因为Mysql的数据表 risk_asset （随便起的表名）数据量过大，读取时间长。

初步排查

查看机器运行环境监控，定位到是磁盘读写次数过多，达到磁盘运行瓶颈。

进入到机器里面，通过 top 命令查看运行环境，可以看到 load average 非常高，已经在 10+ 了（这里没截图，补一张修复中的）

查看具体运行进程，可以看到mysqld 的读写都非常高。定位到是mysql导致的磁盘读写瓶颈。

优化思路

思路1: 直接换一个读写更快的硬盘，简单粗暴，但是不解决本质问题，且更换成本较大，作为备选方案。

思路2: 降低磁盘I/O，需要从多个方面入手，先从这个思路开始处理。

以下是一些降低磁盘I/O的方法：

1. 添加更多内存：

增加内存容量可以帮助减少对磁盘的读写需求，因为MySQL可以缓存更多的数据和索引。这可以通过增加MySQL配置中的innodb_buffer_pool_size参数来实现。

2. 优化查询和索引：

通过优化查询和添加适当的索引来减少对磁盘的访问次数。确保MySQL服务器上的查询是高效的，并且能够利用索引来加速查询操作。

3. 分散数据：

如果可能的话，可以考虑将数据分散到多个磁盘上，以减轻单个磁盘的负载。这可以通过使用MySQL的分区功能或者将表空间放置在不同的物理磁盘上来实现。

4. 使用缓存：

使用缓存来减少对数据库的频繁访问，从而减少对磁盘I/O的需求。你可以使用MySQL自带的查询缓存或者外部缓存系统来缓存查询结果。

因为已经进行过缓存处理，且数据表未到达需要进行分散数据的程度，所以通过第1 & 2 项方案进行优化

优化步骤1 - 添加内存

innodb_buffer_pool_size

innodb_buffer_pool_size 是 MySQL 数据库中的一个重要配置参数，用于指定 InnoDB 存储引擎的缓冲池大小。InnoDB 是 MySQL 的默认存储引擎之一，用于管理数据存储和执行事务处理。

缓冲池是 InnoDB 存储引擎用于缓存数据和索引的内存区域。通过将常用的数据和索引存储在内存中，可以加速数据库的读取操作，减少对磁盘的访问次数，提高数据库的性能。

如果缓冲池太小，可能会导致频繁的磁盘I/O操作，降低数据库性能；如果缓冲池太大，可能会占用过多的内存资源。

进入mysql语句执行页面，执行下列语句：

# 查看当前 innodb_buffer_pool_size 参数的设置：
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
/*
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 134217728  |
+-------------------------+------------+
1 row in set (0.01 sec)

缓冲池默认大小为 128MB，可以根据机器情况对缓冲池进行增加
*/

# 以当前环境为例，设置缓冲池大小为 6G
set global innodb_buffer_pool_size=6442450944;

设置后等待一段时间，可以用top命令看到，mysqld 占用的内存明显升高，平均负载显著降低。

优化步骤2 - 优化查询和索引

磁盘I/O过高的另一个原因是mysql设计问题，导致在数据库读写时占用过多资源，这个要根据具体情况进行优化。以本次优化为例，首先对问题点进行定位。

慢查询日志

为mysql设置慢查询日志记录，进入mysql语句执行页面，执行下列语句：

# 查看慢查询日志是否开启
show variables like 'slow_query%';
/*
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/slow_query.log |
+---------------------+-------------------------------+
*/

# 查看超过多少秒才记录
show variables like 'long_query_time';
/*
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

# 开启慢查询记录
set global slow_query_log='ON';

# 设置日志存放地址
set global slow_query_log_file='/var/lib/mysql/slow_query.log';

# 设置最低记录时间
set long_query_time=5;

# 监控慢查询日志，如果执行下面的语句后，日志中出现对应语句，则证明配置成功
select sleep(6);

设置后等待一段时间，可以看到影响mysql性能的查询语句，以下面的截图为例，该查询语句执行时间为336秒，定位到问题。

在代码中找到对应的查询语句，可以排查出具体问题。

慢查询语句优化

本次遇到的问题为：查询时未限定查询数量，在数据表内数据增多后，导致每次查询都要进行全部数据的遍历，相当于单条接口请求时，每次都会 select 出几十万条数据。添加分页限制后，查询效果显著提升。

面试有个师父发来个crackme