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

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

[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

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

SHOW ENGINE INNODB STATUS;

SHOW GLOBAL STATUS LIKE 'Key_reads';
SHOW GLOBAL STATUS LIKE 'Key_writes';

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

SHOW STATUS LIKE 'Uptime';

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

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

 
-- 启动慢查询日志
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

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

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

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

EXPLAIN SELECT * FROM your_table WHERE your_condition;