网站服务器大小,微信怎么推广自己的产品,wordpress实用,做网站不会框架引言
在生产环境中#xff0c;MySQL作为一个关键的数据库组件#xff0c;其性能对整个系统的稳定性至关重要。然而#xff0c;有时候我们可能会遇到MySQL CPU使用率过高的问题#xff0c;这可能导致系统性能下降#xff0c;应用页面访问减慢#xff0c;甚至影响到用户体…引言
在生产环境中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配置参数优化
查看服务器资源
查看服务器内存
[javalocalhost ~]$ grep MemTotal /proc/meminfo
MemTotal: 266419264 kB // 约256G查看服务器CPU个数
[javalocalhost ~]$ 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的运行状态是我们排查性能问题的第一步。通过查看全局状态变量我们可以获取系统的整体运行情况。以下是一些关键的状态变量和信息
Threads_running 和 Threads_connected
SHOW GLOBAL STATUS LIKE Threads_running;
SHOW GLOBAL STATUS LIKE Threads_connected;Threads_running 表示当前正在执行的线程数量。 Threads_connected 表示当前已连接到MySQL的线程数量。 如果 Threads_running 较高而 Threads_connected 较低可能表明存在某些长时间运行的查询或者可能是由于连接池配置不当导致连接被频繁创建和销毁。
InnoDB 相关状态
SHOW ENGINE INNODB STATUS;查看InnoDB引擎状态关注以下信息
Innodb_row_lock_current_waits表示当前正在等待的行锁数量。 Innodb_deadlocks显示发生的死锁次数。 高的行锁等待和死锁次数可能表明业务逻辑或查询需要优化或者存在并发访问冲突。
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 可能表明索引的写入操作较为频繁需要考虑索引的优化。
Created_tmp_disk_tables
SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables;表示在磁盘上创建的临时表的数量。过多的磁盘临时表可能表明某些查询需要优化或者 tmp_table_size 参数设置过小。
Uptime
SHOW STATUS LIKE Uptime;表示MySQL服务的运行时间。如果CPU问题突然发生检查这个值看是否与问题的时间点相关。
其他关键状态变量
浏览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; // 默认值8Mset global key_buffer_size1024*1024*64show global variables like max_allowed_packet; // 默认值64Mshow global variables like table_open_cache; // 默认值4000set global table_open_cache16000// sort_buffer_size是MySql执行排序使用的缓冲大小
show global variables like sort_buffer_size; // 默认值256KBset global sort_buffer_size1024*1024*16show global variables like net_buffer_length; // 默认值16KB//read_buffer_size 是MySql读入缓冲区大小。
show global variables like read_buffer_size; // 默认值128KBset global read_buffer_size1024*1024*8// tmp_table_size是MySql的heap 堆积表缓冲大小
show global variables like tmp_table_size; // 默认值16Mset global tmp_table_size1024*1024*128// read_rnd_buffer_size 是MySql的随机读缓冲区大小
show global variables like read_rnd_buffer_size; // 默认值256KBset global read_rnd_buffer_size1024*1024*4// thread_cache_size可以重新利用保存在缓存中线程的数量
show global variables like thread_cache_size; // 默认值8set global thread_cache_size64// MySql的最大连接数如果服务器的并发连接请求量比较大建议调高此值以增加并行连接数量
// 当然这建立在机器能支撑的情况下因为如果连接数越多介于MySql会为每个连接提供连接缓冲区就会开销越多的内存
show global variables like max_connections; // 最多连接数, 默认151set global max_connections5000;show global variables like max_connect_errors; // 默认值100set global max_connect_errors1000;show global variables like open_files_limit; // 默认值1Mshow 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; // 默认值128Mset global innodb_buffer_pool_size1024*1024*1024*32 //32G//InnoDB事务日志文件大小show global variables like innodb_log_file_size;// InnoDB存储引擎的事务日志所使用的缓冲区
show global variables like innodb_log_buffer_size; // 默认值16Mset global innodb_log_buffer_size1024*1024*128show global variables like sync_binlog;set global sync_binlog1000可根据自己服务器性能动态调整但重启后会失效最好同时修改my.cnf配置文件
通过参数调优后的MySQL状态
参数参考: MySQL性能优化之参数配置 mysql配置参数调优
方案二SQL问题分析定位解决
MySQL的查询分析是排查性能问题的关键步骤。通过检查慢查询日志和使用性能分析工具我们可以找到潜在的性能瓶颈。
启用慢查询日志 首先确保MySQL的慢查询日志功能已启用。在MySQL配置文件中添加以下配置
slow_query_log 1
slow_query_log_file /usr/local/mysql/slowlog/slow-query.log
long_query_time 1slow_query_log 启用慢查询日志。 slow_query_log_file 设置慢查询日志文件路径。 long_query_time 定义慢查询的时间阈值单位秒这里设置为1秒。
或者使用MySQL客户端 -- 启动慢查询日志
set global slow_query_logON;-- 设置慢查询存储文件地址
set global slow_query_log_file/usr/local/mysql/slowlog/slow-query.log;-- 设置储存sql条件sql 执行时间高于0.001秒存入日志文件
set global long_query_time0.001;-- 开启 记录没有使用索引查询语句
set global log-queries-not-using-indexes on分析慢查询日志 使用以下命令查看慢查询日志中的内容
tail -f /usr/local/mysql/slowlog/slow-query.log或者使用MySQL客户端
SHOW VARIABLES LIKE slow_query_log;
SHOW VARIABLES LIKE slow_query_log_file;通过检查慢查询日志识别执行时间长的查询。注意关注查询的执行计划以便理解MySQL是如何处理这些查询的。
使用慢查询分析工具 使用工具如pt-query-digest来分析慢查询日志
pt-query-digest /path/to/slow-query.log该工具能够生成详细的报告包括执行时间最长的查询、查询频率、索引使用情况等信息。通过这些信息您可以确定哪些查询需要优化以提高其性能。
Explain命令 对于特定的查询使用EXPLAIN命令来查看其执行计划
EXPLAIN SELECT * FROM your_table WHERE your_condition;EXPLAIN命令将显示MySQL执行查询时的执行计划包括使用的索引、访问表的方式等。通过分析执行计划您可以了解查询的性能瓶颈并进行相应的优化。
优化查询 根据慢查询日志和执行计划的分析结果对性能较差的查询进行优化。可能的优化方式包括
优化查询语句避免全表扫描。优化 SQL降低 SQL 复杂度降低 MySQL 执行成本。确保查询涉及的列都有合适的索引。考虑分表、分区表等策略以减少单表的数据量。
通过以上步骤您将能够更好地理解哪些查询对系统性能有负面影响并有针对性地进行优化提高整体性能。
结论
通过以上步骤您应该能够定位和解决MySQL CPU使用率过高的问题。请注意每个生产环境都是独特的可能需要根据实际情况进行适当调整。保持监控和定期优化是确保MySQL性能稳定的关键。希望这篇文章对您解决MySQL性能问题提供了帮助。如果有任何问题或建议请随时留言。
