MySQL CPU和内存飙高排查_mysqlcpu占用过高 排查

一、查看数据库整体情况

二、CPU飙高

1、主机并发数 

排查数据库脚本1：

select user,substring_index(host,':',1) host,db,command,count(*) from information_schema.PROCESSLIST 
           where command<>'Sleep'
group by user,substring_index(host,':',1),db,command 
order by 5 desc,3;

 

2、正在执行任务

 排查数据库脚本2：

select * from information_schema.processlist where command<>'Sleep'

添加索引解决部分问题：

ALTER TABLE `store_alarm_message_log` ADD INDEX `idx_status` (`status`);

3、查询表数据大小

 排查数据库脚本3：

use information_schema;
 
select table_name,table_rows, round(data_length/1024/1024,2) as DATA_SIZE_MB  from tables 
where TABLE_SCHEMA = 'schema' 
order by table_rows desc;

4、查询指定schema中表大小

排查数据库脚本4：

select TABLE_SCHEMA,table_name,format(data_length/1024/1024/1024,2) "table_size(GB)",
format(index_length/1024/1024/1024,2) "index_size(GB)",
format((data_length+index_length)/1024/1024/1024,2) "szie(GB)",
table_rows from information_schema.tables 
where table_schema='xxx_033'
order by (DATA_LENGTH+INDEX_LENGTH) desc;