剖析MySQL生产情况CPU使用率过高的排查与解决方案

引言

在生产情况中，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运行情况

可以看到CPU使用率非常高，内存使用较低，可以清除不是内存影响的。而且内存资源还有很大空间。
因此要解决问题，可以从两方面入手：

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

方案一：MySQL设置参数优化

查看服务器资源

查看服务器内存：

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

复制代码

查看服务器CPU个数：

1. [java@localhost ~]$ lscpu
2. 架构：                           aarch64
3. CPU 运行模式：                   64-bit
4. 字节序：                         Little Endian
5. CPU:                             64
6. 在线 CPU 列表：                  0-63
7. 每个核的线程数：                 1
8. 每个座的核数：                   32
9. 座：                             2
10. NUMA 节点：                      2
11. 厂商 ID：                        HiSilicon
12. 型号：                           0
13. 型号名称：                       Kunpeng-920
14. 步进：                           0x1
15. Frequency boost:                 disabled
16. CPU 最大 MHz：                   2600.0000
17. CPU 最小 MHz：                   200.0000
18. BogoMIPS：                       200.00
19. L1d 缓存：                       4 MiB
20. L1i 缓存：                       4 MiB
21. L2 缓存：                        32 MiB
22. L3 缓存：                        64 MiB
23. NUMA 节点0 CPU：                 0-31
24. NUMA 节点1 CPU：                 32-63
25. Vulnerability Itlb multihit:     Not affected
26. Vulnerability L1tf:              Not affected
27. Vulnerability Mds:               Not affected
28. Vulnerability Meltdown:          Not affected
29. Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
30. Vulnerability Spectre v1:        Mitigation; __user pointer sanitization
31. Vulnerability Spectre v2:        Not affected
32. Vulnerability Srbds:             Not affected
33. Vulnerability Tsx async abort:   Not affected
34. 标记：                           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的运行状态是我们排查性能问题的第一步。通过查看全局状态变量，我们可以获取系统的整体运行情况。以下是一些关键的状态变量和信息：

• Threads_running 和 Threads_connected
  

1. SHOW GLOBAL STATUS LIKE 'Threads_running';
2. SHOW GLOBAL STATUS LIKE 'Threads_connected';

复制代码

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

• InnoDB 相干状态
  

1. SHOW ENGINE INNODB STATUS;

复制代码

查看InnoDB引擎状态，关注以下信息：
Innodb_row_lock_current_waits：表示当前正在等待的行锁数目。
Innodb_deadlocks：显示发生的死锁次数。
高的行锁等待和死锁次数大概表明业务逻辑或查询需要优化，大概存在并发访问辩说。

• Key_reads 和 Key_writes
  

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

复制代码

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

• Created_tmp_disk_tables
  

1. SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

复制代码

表示在磁盘上创建的临时表的数目。过多的磁盘临时表大概表明某些查询需要优化，大概 tmp_table_size 参数设置过小。

• Uptime
  

1. SHOW STATUS LIKE 'Uptime';

复制代码

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

• 其他关键状态变量
  

欣赏MySQL官方文档以获取更多有关全局状态变量的信息，根据详细情况添加监控和分析。
通过这些状态变量，我们可以初步了解MySQL的整体运行情况，从而有针对性地继续深入排盘问题。在分析状态时，可以使用各种监控工具，如pt-mysql-summary或MySQL Enterprise Monitor，以更方便地查看和理解MySQL的状态信息。
Mysql参数设置

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

1. SELECT version(); // 版本：8.0.30
3. // 索引块的缓冲区大小，增加它可得到更好处理的索引
4. show global variables like 'key_buffer_size';  // 默认值：8M
6. set global key_buffer_size=1024*1024*64
8. show global variables like 'max_allowed_packet'; // 默认值：64M
10. show global variables like 'table_open_cache'; // 默认值：4000
12. set global table_open_cache=16000
14. // sort_buffer_size是MySql执行排序使用的缓冲大小
15. show global variables like 'sort_buffer_size'; // 默认值：256KB
17. set global sort_buffer_size=1024*1024*16
19. show global variables like 'net_buffer_length'; // 默认值：16KB
21. //read_buffer_size 是MySql读入缓冲区大小。
22. show global variables like 'read_buffer_size'; // 默认值：128KB
24. set global read_buffer_size=1024*1024*8
26. // tmp_table_size是MySql的heap （堆积）表缓冲大小
27. show global variables like 'tmp_table_size'; // 默认值：16M
29. set global tmp_table_size=1024*1024*128
31. // read_rnd_buffer_size 是MySql的随机读缓冲区大小
32. show global variables like 'read_rnd_buffer_size'; // 默认值：256KB
34. set global read_rnd_buffer_size=1024*1024*4
36. // thread_cache_size可以重新利用保存在缓存中线程的数量     
37. show global variables like 'thread_cache_size'; // 默认值：8
39. set global thread_cache_size=64
43. // MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，
44. // 当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存
45. show global variables like 'max_connections'; // 最多连接数, 默认：151
47. set global max_connections=5000;
49. show global variables like 'max_connect_errors'; // 默认值：100
51. set global max_connect_errors=1000;
53. show global variables like 'open_files_limit'; // 默认值：1M
55. show global variables like 'innodb_data_file_path';   
57. // InnoDB
58. // 对InnoDB表性能影响最大的一个参数。InnoDB缓冲池用于缓存数据和索引，对于读取频繁的表，适当调整缓冲池大小可以显著提升性能。
59. 将
60. // innodb_buffer_pool_size设置为系统中Mysql可用内存的70%左右。这确保了大部分数据和索引都可以在内存中缓存，减少磁盘I/O操作。
61. show global variables like 'innodb_buffer_pool_size'; // 默认值：128M
63. set global innodb_buffer_pool_size=1024*1024*1024*32 //32G
65. //InnoDB事务日志文件大小
66. show global variables like 'innodb_log_file_size';
68. // InnoDB存储引擎的事务日志所使用的缓冲区
69. show global variables like 'innodb_log_buffer_size';  // 默认值：16M
71. set global innodb_log_buffer_size=1024*1024*128
73. show global variables like 'sync_binlog';
75. set global sync_binlog=1000

复制代码

可根据本身服务器性能动态调整，但重启后会失效，最好同时修改my.cnf设置文件：
通过参数调优后的MySQL状态：

参数参考:

• MySQL性能优化之参数设置
  
• mysql设置参数调优
  

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

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

• 启用慢查询日记
  起首，确保MySQL的慢查询日记功能已启用。在MySQL设置文件中添加以下设置：
  

1. slow_query_log = 1
2. slow_query_log_file = /usr/local/mysql/slowlog/slow-query.log
3. long_query_time = 1

复制代码

slow_query_log 启用慢查询日记。
slow_query_log_file 设置慢查询日记文件路径。
long_query_time 定义慢查询的时间阈值（单位：秒），这里设置为1秒。
大概使用MySQL客户端：

2. -- 启动慢查询日志
3. set global slow_query_log='ON';
5. -- 设置慢查询存储文件地址
6. set global slow_query_log_file='/usr/local/mysql/slowlog/slow-query.log';
8. -- 设置储存sql条件，sql 执行时间高于0.001秒存入日志文件
9. set global long_query_time=0.001;
11. -- 开启 记录没有使用索引查询语句
12. set global log-queries-not-using-indexes = on

复制代码

• 分析慢查询日记
  使用以下下令查看慢查询日记中的内容：
  

1. tail -f /usr/local/mysql/slowlog/slow-query.log

复制代码

大概使用MySQL客户端：

1. SHOW VARIABLES LIKE 'slow_query_log';
2. SHOW VARIABLES LIKE 'slow_query_log_file';

复制代码

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

• 使用慢查询分析工具
  使用工具如pt-query-digest来分析慢查询日记：
  

1. pt-query-digest /path/to/slow-query.log

复制代码

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

• Explain下令
  对于特定的查询，使用EXPLAIN下令来查看其执行计划：
  

1. EXPLAIN SELECT * FROM your_table WHERE your_condition;

复制代码

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

• 优化查询
  根据慢查询日记和执行计划的分析结果，对性能较差的查询举行优化。大概的优化方式包罗：
  

• 优化查询语句，制止全表扫描。
  
• 优化 SQL，降低 SQL 复杂度，降低 MySQL 执行本钱。
  
• 确保查询涉及的列都有合适的索引。
  
• 思量分表、分区表等策略，以减少单表的数据量。
  

通过以上步调，您将可以或许更好地理解哪些查询对系统性能有负面影响，并有针对性地举行优化，提高整体性能。
结论

通过以上步调，您应该可以或许定位息争决MySQL CPU使用率过高的问题。请留意，每个生产情况都是独特的，大概需要根据现真相况举行得当调整。保持监控和定期优化是确保MySQL性能稳定的关键。盼望这篇文章对您解决MySQL性能问题提供了帮助。如果有任何问题或发起，请随时留言。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。