剖析MySQL生产情况CPU使用率过高的排查与解决方案 - ToB企服应用市场:ToB评测及商务社交产业平台

[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;

复制代码