mysql优化cpu占用

原因

由于数据量大，数据文件已经达到40多G。业务服务启动后，大量mysql请求，导致cpu，占用超过 100%。

1 2	# 分析各表的数据占用 SELECT table_name, data_length, avg_row_length, index_length, table_rows FROM information_schema.tables WHERE table_schema = 'mydb';

优化方案

优化方式：

调整innodb_buffer_pool_size 由128M，调整为21G
增加慢查询日志，监控慢查询sql。

优化后，平均cpu占用，低于100%。

mysql conf调整

Buffer Pool是MySQL中最重要的内存组件，介于外部系统和存储引擎之间的一个缓存区，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘 IO，加快处理速度。

`innodb_buffer_pool_size`

innodb_buffer_pool_size: InnoDB 的缓冲池大小，通常设置为系统内存的 50-70%。这是 MySQL 最重要的一个参数，影响查询性能。

查询

1
2
3

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 
134217728
# 当前值 21340618752

当前值为默认值128M

开启慢查询日志

show variables like 'slow_query%';
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/207c0af8ba8c-slow.log |
set global slow_query_log='ON';
set global long_query_time = 4; # 默认10s
set global slow_query_log_file="/var/lib/mysql/mysql-slow.log";

SET PERSIST

SET PERSIST 是 MySQL 8.0 版本引入的一种机制，用于在运行时（runtime）和持久化（persist）之间设置系统变量。这意味着你可以在修改系统变量的同时，将修改保存到 MySQL 数据目录中的配置文件，以便在下次启动 MySQL 时保留这些更改。

1
2
3

SET PERSIST innodb_buffer_pool_size=3000000000;
set persist slow_query_log='ON';
set persist slow_query_log_file="/var/lib/mysql/mysql-slow.log";

注意调小innodb_buffer_pool_size时，可能无法立刻生效。

缓存池已分配的内存： 当你将缓冲池的大小增加时，MySQL 会分配额外的内存来满足新的大小。但当你尝试将缓冲池的大小减小时，MySQL 可能会遇到已经分配的内存，这样就不能立即释放。在减小缓冲池大小时，MySQL 可能需要更多的步骤来逐渐释放内存。
InnoDB Buffer Pool 尺寸变化的限制： 在 MySQL 中，动态更改 InnoDB Buffer Pool 大小可能会受到限制，特别是在某些情况下。如果有正在进行的事务或查询，MySQL 可能会阻止尺寸的即时更改。你可能需要等待某些操作完成，或者等待一段时间，让 MySQL 在缓慢地释放内存。
逐步减小尺寸： 尝试逐步减小缓冲池大小，而不是一次性更改。例如，先将其设置为比目前大小小一点，观察是否有变化，然后逐步继续。

配置存储路径/var/lib/mysql/mysqld-auto.cnf

mysqld-auto.cnf 文件是 MySQL 8.0 的自动生成的文件，用于存储通过 SET PERSIST 命令设置的持久性系统变量。这个文件的目的是在 MySQL 8.0+ 中支持持久性设置。

不要删除或编辑这个文件。 （但测试发现删除此文件，可以删除PERSIST的配置）

1
2
3

# 恢复原始设置
SET PERSIST innodb_buffer_pool_size = DEFAULT; # 测试无法立刻生效
SET PERSIST slow_query_log = DEFAULT;

慢查询日志分析

监测发现部分慢查询：

1	sql = "select created_at from events where device_id = :device_id order by created_at desc limit 1"

该查询较慢，可以优化为

1	sql = "select max(created_at) from events where device_id = :device_id "