您的当前位置：首页正文

解析MySQL生产环境CPU使用率过高的排查与解决方案

2024-11-01 来源：个人技术集锦

引言

在生产环境中，MySQL作为一个关键的数据库组件，其性能对整个系统的稳定性至关重要。然而，有时候我们可能会遇到MySQL CPU使用率过高的问题，这可能导致系统性能下降，应用页面访问减慢，甚至影响到用户体验。本文将详细介绍如何排查和解决MySQL CPU过高的问题，帮助您迅速恢复正常的数据库性能。

首先我们要明白什么是CPU使用率：

CPU使用率是指在单位时间内CPU处于非空闲状态的时间比，反映了CPU的繁忙程度。某个进程的CPU使用率就是这个进程在一段时间内占用的CPU时间占总的时间的百分比。比如在双核CPU某个开启多线程的进程1s内占用了CPU0 0.6s, CPU1 0.9s, 那么它的占用率是150%。这里不深入阐述，网上文章很多。

CPU占用过高原因分析

CPU 占用过高常见原因：

  • 服务器硬件问题
  • 内存溢出
  • 高并发业务中业务设计不合理导致
    • 数据库对象设计不合理
    • 表索引设计不合理
    • 数据库锁导致，如行锁冲突、行锁等待、锁超时、死锁等
    • 系统架构没有缓存中间件
    • 读写分离配置不合理
    • 未合理升级改造为集群环境
    • MySQL 系统参数设置不合理
    • 问题 SQL 导致

SQL 问题导致 CPU 使用率过高是最常见的现象，比如 group by、order by、join 等，这些很大程度影响 SQL 执行效率，从而占用大量的系统资源。

说了这么多常见原因，其实总结一句话来说就是现有系统的现有配置下的现有环境提供不了所需要的数据查询、分析、执行能力，针对这个问题，首先我们要发现问题的所在，就是说我们要准确的定位问题，然后针对问题进行优化，再考虑其他升级改造的事情。

检查MySQL运行情况

因此要解决问题，可以从两方面入手：

  • 优化Mysql参数配置，发挥服务器硬件性能，通过合适的参数配置提升Mysql性能（以空间换时间，见效快，成本高）
  • 找到问题原因，优化问题sql、添加合理的索引、引入缓存等

方案一：MySQL配置参数优化

查看服务器资源

查看服务器内存：

[java@localhost ~]$ grep MemTotal /proc/meminfo 
MemTotal:       266419264 kB   	// 约256G

查看服务器CPU个数：

[java@localhost ~]$ lscpu
架构：                           aarch64
CPU 运行模式：                   64-bit
字节序：                         Little Endian
CPU:                             64
在线 CPU 列表：                  0-63
每个核的线程数：                 1
每个座的核数：                   32
座：                             2
NUMA 节点：                      2
厂商 ID：                        HiSilicon
型号：                           0
型号名称：                       Kunpeng-920
步进：                           0x1
Frequency boost:                 disabled
CPU 最大 MHz：                   2600.0000
CPU 最小 MHz：                   200.0000
BogoMIPS：                       200.00
L1d 缓存：                       4 MiB
L1i 缓存：                       4 MiB
L2 缓存：                        32 MiB
L3 缓存：                        64 MiB
NUMA 节点0 CPU：                 0-31
NUMA 节点1 CPU：                 32-63
Vulnerability Itlb multihit:     Not affected
Vulnerability L1tf:              Not affected
Vulnerability Mds:               Not affected
Vulnerability Meltdown:          Not affected
Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Vulnerability Spectre v1:        Mitigation; __user pointer sanitization
Vulnerability Spectre v2:        Not affected
Vulnerability Srbds:             Not affected
Vulnerability Tsx async abort:   Not affected
标记：                           fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm jscvt fcma dcpop asimddp asimdfhm ssbs

可以看到服务器有两个物理CPU，每个物理CPU有32个内核数，即总共64个逻辑CPU数。
一般情况下，逻辑cpu=物理CPU个数×每颗核数

观察MySQL状态

MySQL的运行状态是我们排查性能问题的第一步。通过查看全局状态变量，我们可以获取系统的整体运行情况。以下是一些关键的状态变量和信息：

SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Threads_running 表示当前正在执行的线程数量。
Threads_connected 表示当前已连接到MySQL的线程数量。
如果 Threads_running 较高，而 Threads_connected 较低，可能表明存在某些长时间运行的查询，或者可能是由于连接池配置不当导致连接被频繁创建和销毁。

  1. InnoDB 相关状态
SHOW ENGINE INNODB STATUS;

查看InnoDB引擎状态，关注以下信息：

Innodb_row_lock_current_waits：表示当前正在等待的行锁数量。
Innodb_deadlocks：显示发生的死锁次数。
高的行锁等待和死锁次数可能表明业务逻辑或查询需要优化，或者存在并发访问冲突。

  1. Key_reads 和 Key_writes
SHOW GLOBAL STATUS LIKE 'Key_reads';
SHOW GLOBAL STATUS LIKE 'Key_writes';

Key_reads：表示从磁盘读取索引块的次数。
Key_writes：表示向磁盘写入索引块的次数。
高的 Key_reads 可能暗示着索引未能完全放入内存中，需要调整 key_buffer_size 参数。而频繁的 Key_writes 可能表明索引的写入操作较为频繁，需要考虑索引的优化。

  1. Created_tmp_disk_tables
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

表示在磁盘上创建的临时表的数量。过多的磁盘临时表可能表明某些查询需要优化，或者 tmp_table_size 参数设置过小。

  1. Uptime
SHOW STATUS LIKE 'Uptime';

表示MySQL服务的运行时间。如果CPU问题突然发生，检查这个值，看是否与问题的时间点相关。

  1. 其他关键状态变量

浏览MySQL官方文档以获取更多有关全局状态变量的信息，根据具体情况添加监控和分析。

通过这些状态变量，我们可以初步了解MySQL的整体运行情况，从而有针对性地继续深入排查问题。在分析状态时，可以使用各种监控工具，如pt-mysql-summary或MySQL Enterprise Monitor，以更方便地查看和理解MySQL的状态信息。

Mysql参数设置

数据库属于IO密集型的应用程序，其主职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个 IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。

SELECT version(); // 版本：8.0.30

 // 索引块的缓冲区大小，增加它可得到更好处理的索引
show global variables like 'key_buffer_size';  // 默认值：8M

set global key_buffer_size=1024*1024*64

show global variables like 'max_allowed_packet'; // 默认值：64M

show global variables like 'table_open_cache'; // 默认值：4000

set global table_open_cache=16000

// sort_buffer_size是MySql执行排序使用的缓冲大小
show global variables like 'sort_buffer_size'; // 默认值：256KB

set global sort_buffer_size=1024*1024*16

show global variables like 'net_buffer_length'; // 默认值：16KB

 //read_buffer_size 是MySql读入缓冲区大小。
show global variables like 'read_buffer_size'; // 默认值：128KB

set global read_buffer_size=1024*1024*8

 // tmp_table_size是MySql的heap （堆积）表缓冲大小
show global variables like 'tmp_table_size'; // 默认值：16M

set global tmp_table_size=1024*1024*128

 // read_rnd_buffer_size 是MySql的随机读缓冲区大小
show global variables like 'read_rnd_buffer_size'; // 默认值：256KB

set global read_rnd_buffer_size=1024*1024*4

// thread_cache_size可以重新利用保存在缓存中线程的数量     
show global variables like 'thread_cache_size'; // 默认值：8

set global thread_cache_size=64



// MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，
// 当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存
show global variables like 'max_connections'; // 最多连接数, 默认：151

set global max_connections=5000;

show global variables like 'max_connect_errors'; // 默认值：100

set global max_connect_errors=1000;

show global variables like 'open_files_limit'; // 默认值：1M

show global variables like 'innodb_data_file_path';    

// InnoDB
// 对InnoDB表性能影响最大的一个参数。InnoDB缓冲池用于缓存数据和索引，对于读取频繁的表，适当调整缓冲池大小可以显著提升性能。
将
// innodb_buffer_pool_size设置为系统中Mysql可用内存的70%左右。这确保了大部分数据和索引都可以在内存中缓存，减少磁盘I/O操作。
show global variables like 'innodb_buffer_pool_size'; // 默认值：128M

set global innodb_buffer_pool_size=1024*1024*1024*32 //32G

//InnoDB事务日志文件大小
 show global variables like 'innodb_log_file_size';

// InnoDB存储引擎的事务日志所使用的缓冲区
show global variables like 'innodb_log_buffer_size';  // 默认值：16M

set global innodb_log_buffer_size=1024*1024*128

show global variables like 'sync_binlog';

set global sync_binlog=1000

可根据自己服务器性能动态调整，但重启后会失效，最好同时修改my.cnf配置文件：

通过参数调优后的MySQL状态：

参数参考:

  • MySQL性能优化之参数配置

  • mysql配置参数调优

方案二：SQL问题分析定位解决

MySQL的查询分析是排查性能问题的关键步骤。通过检查慢查询日志和使用性能分析工具，我们可以找到潜在的性能瓶颈。

  1. 启用慢查询日志
    首先，确保MySQL的慢查询日志功能已启用。在MySQL配置文件中添加以下配置：
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/slowlog/slow-query.log
long_query_time = 1

slow_query_log 启用慢查询日志。
slow_query_log_file 设置慢查询日志文件路径。
long_query_time 定义慢查询的时间阈值（单位：秒），这里设置为1秒。

或者使用MySQL客户端：

 
-- 启动慢查询日志
set global slow_query_log='ON';

-- 设置慢查询存储文件地址
set global slow_query_log_file='/usr/local/mysql/slowlog/slow-query.log';
 
-- 设置储存sql条件，sql 执行时间高于0.001秒存入日志文件
set global long_query_time=0.001;

-- 开启 记录没有使用索引查询语句
set global log-queries-not-using-indexes = on
  1. 分析慢查询日志
    使用以下命令查看慢查询日志中的内容：
tail -f /usr/local/mysql/slowlog/slow-query.log

或者使用MySQL客户端：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

通过检查慢查询日志，识别执行时间长的查询。注意关注查询的执行计划，以便理解MySQL是如何处理这些查询的。

  1. 使用慢查询分析工具
    使用工具如pt-query-digest来分析慢查询日志：
pt-query-digest /path/to/slow-query.log

该工具能够生成详细的报告，包括执行时间最长的查询、查询频率、索引使用情况等信息。通过这些信息，您可以确定哪些查询需要优化，以提高其性能。

  1. Explain命令
    对于特定的查询，使用EXPLAIN命令来查看其执行计划：
EXPLAIN SELECT * FROM your_table WHERE your_condition;

EXPLAIN命令将显示MySQL执行查询时的执行计划，包括使用的索引、访问表的方式等。通过分析执行计划，您可以了解查询的性能瓶颈，并进行相应的优化。

  1. 优化查询
    根据慢查询日志和执行计划的分析结果，对性能较差的查询进行优化。可能的优化方式包括：
  • 优化查询语句，避免全表扫描。
  • 优化 SQL，降低 SQL 复杂度，降低 MySQL 执行成本。
  • 确保查询涉及的列都有合适的索引。
  • 考虑分表、分区表等策略，以减少单表的数据量。

通过以上步骤，您将能够更好地理解哪些查询对系统性能有负面影响，并有针对性地进行优化，提高整体性能。

结论

通过以上步骤，您应该能够定位和解决MySQL CPU使用率过高的问题。请注意，每个生产环境都是独特的，可能需要根据实际情况进行适当调整。保持监控和定期优化是确保MySQL性能稳定的关键。希望这篇文章对您解决MySQL性能问题提供了帮助。如果有任何问题或建议，请随时留言。

Copyright © 2019- zgxue.com 版权所有
京ICP备2021021884号-5

Top