绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
简单总结mysql高cpu高mem高io高sleep常用的优化手段
2019-05-02 18:22:56


===============================================================================

001-案例:占用CPU100%的优化


一、总体思路

1、先用操作系统命令 top 观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。

2、如果是 mysqld 造成的, show  processlist,看看里面跑的 session 情况,是不是有消耗资源的 sql 在运行。

找出消耗高的 sql,看看执行计划是否准确, index 是否缺失,或者实在是数据量太大造成。

SHOW PROFILES\G查看queryid,sqltext,接着show profile cpu,block io for query 2;查看消耗情况。

3、一般来说,肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降),等进行相应的调整(比如说加索引、改 sql、改内存参数)之后,再重新跑这些 SQL。

4、也有可能是每个 sql 消耗并不多,突然大量的 session 连进来使 cpu 飙升,需要跟应用一起分析为何连接数会激增,再做相应调整,比如说限制连接数等。

5、补充:SQL语句分析三板斧explain+show processlist+show profile for query query_id


二、使用 show processlist

1、查看当前哪些线程正在运行。查看下来一共有160多个

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+


state只是sql执行中的某一个状态,以查询sql为例,

可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成。


由于在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整个查询中耗时最长的状态。


2、从show processlist命令输出的结果看到有一条sql语句重复出现,

但是info列显示的不全只有select a.col1,a.col2,a.col3 from table1 a这样的信息。

那就先从这个表入手查,select count(*) from table1;查出这张表有60W+的数据。

select count(*) 使用了6秒。但是现在不确定这个语句执行的时候有没有where条件。


3、再查询information_schema.processlist表时发现info信息是完整的,

在这里找到上边怀疑的sql的完整版为select a.col1,a.col2,a.col3 from table1 a where a.col4='123' and a.col5='abc';

查看这个语句的执行计划(类似下面这种)

mysql> explain select ename,hiredate,sal from table1 where sal=1000 \G;

         type: ALL全表扫描,没有使用索引


4、哪种type性能好


system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


ALL——全表扫描

index——索引全扫描

range——索引范围扫描,常用语<,<=,>=,between等操作

ref——使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中

eq_ref——类似ref,区别在于使用的是唯一索引,使用主键的关联查询

const/system——单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询

null——MySQL不访问任何表或索引,直接返回结果


三、索引评估及添加

1、经查看发现col4列上没有索引。

mysql> show index from table1 \G;


2、表的存储引擎为 InnoDB,于是在col4列上创建索引

mysql> show table status from table1 like 'table1'\G;

         Engine: InnoDB存储引擎

3、加索引

mysql> create index idx_sal on table1(col4);

4、再次查看执行计划,发现语句使用索引扫描。

mysql> explain select * from table1 where col4=1000 \G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE   # 连接类型

        table: emp

         type: ref      # 建索引之前是:type: ALL全表扫描

possible_keys: idx_sal

          key: idx_sal

      key_len: 6

          ref: const   主键或唯一索引查询



四、sql语句的执行效率立马提升。CPU的使用率也降下来了。


五、高CPU占用总结

cpu消耗过大有慢sql造成,慢sql包括全表扫描,扫描数据量太大,内存排序,磁盘排序,锁争用等;表的现象:

sql执行状态为:sending data,copying to tmp table,copying to tmp table on disk,sorting result,using filesort,locked;

1、sending data

sql正从表中查询数据,如果查询条件没有适当索引,会导致sql执行时间过长

2、copying to tmp table on disk:

因临时结果集太大,超过数据库规定的临时内存大小,需要拷贝临时结果集到磁盘上


3、sorting result,using filesort:

sql正在执行排序操作,排序操作会引起较多的cpu消耗,可以通过添加索引,或减小排序结果集。

所以如果用户的数据量很大,内存很小,因iops的限制,一条慢sql就有可能消耗掉所有io资源,而影响其他sql查询,

对于数据库就是所有的sql需要执行很长时间才返回结果集,对于应用会造成整体响应变慢。



==========================================================================

002-案例:大量的sleep进程的优化


一、系统运行一段时间后通过show processlist发现有大量的sleep状态的连接进程。


二、临时性的批量杀sql


通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件,然后执行临时文件中生成的指令

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' and where command = 'Sleep' ;

+------------------------+

| concat('KILL ',id,';') 

+------------------------+

| KILL 3101;             

| KILL 2946;             

+------------------------+

mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';


mysql>source /tmp/a.txt;


三、通过参数优化彻底解决

1、#没进行过优化,还是默认值28800(8小时)

Mysql> show variables like “%timeout%”;

+--------------------+---------+

| connect_timeout   | 10    | 

| wait_timeout     | 28800  | 

+--------------------+---------+

大量闲置连接,会白白消耗内存,可能会造成'too many connections'的错误。


2、新增 timeout参数,设置为120秒

vi /etc/my.cnf

[mysqld]

wait_timeout=120

interactive_timeout=120

注意:要同时设置interactive_timeout和wait_timeout才会生效。


3、重启mysql



==========================================================================

003-案例:高IO的优化


一、查看mysql线程的IO使用情况

1、安装iotop,yum install iotop

2、iotop查看mysql进程里的哪个线程读写速率比较大

iotop -u mysql ##-u mysql查询用户mysql运行的对应程序

3、查看对应操作系统线程号与mysql哪个功能线程对应,看一下是哪个processlist_id占用较大的io

select name,type,thread_id,thread_os_id,processlist_id  from threads;

type字段中BACKGROUND表示后台线程,FOREGROUND表示前端线程,如连接线程等


补充方法:

SHOW PROFILES\G查看queryid,sqltext,接着show profile block io for query 2;查看消耗情况。

4、processlist_id对应show processlist

5、杀processlist_id


二、InnoDB引擎从大表中删除大量行


1、有什么办法可以快速删除?

1亿6000万的表,有一个自增ID,4要删除大于250万以后的数据。

2、三种方案

方法1:重建表(拷贝待保留数据到新表、删旧表、新表改为旧表名)--时间较长

方法2:拆分SQL执行--时间较长

方法3:切换主从--推荐


三、利用硬链接和truncate降低drop table对线上环境的影响


在建立硬链接之后,mysql会认为rm了硬链接文件之后就算操作完毕,不会真正去删除物理文件。

如果手动rm物理文件会产生很多的io影响。

使用coreutils 工具集中的truncate会根据指定的size大小进行逐步删除,会将对IO造成的影响降到最低。

1、建立硬链接。

ln table.ibd table.idb.hdlk

2、mysql执行drop table操作。

drop table if exists tablename;

3、使用truncate删除物理文件。

truncate -s 1024*1024*4 filename


==========================================================================

004-案例:高内存占用的优化


一、内存消耗过高使系统卡住

1、近期是否升级过kernel。

2、innodb内部统计的内存使用量。

3、NUMA开关导致swap。

4、临时表、memory引擎表。

5、连接所消耗内存。

6、table cache相关的内存。

7、真正的mysqld内存泄漏。

8、异常sql


二、紧急处理

1、查杀异常sql

netstat -natpl检查数据库端口连接数

show processlist紧急定位明显异常的sql

2、增加swap交换空间

3、增加内存



三、优化参数限制内存占用

1、查看全局内存使用情况

select event_name,SUM_NUMBER_OF_BYTES_ALLOC  

from memory_summary_global_by_event_name       

order by SUM_NUMBER_OF_BYTES_ALLOC 

desc LIMIT 10;


2、查看进程内存使用情况

(1)select event_name, SUM_NUMBER_OF_BYTES_ALLOC 

from     memory_summary_by_thread_by_event_name       

order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;

(2)show profiles

show profile memory for query 1;

3、调整全局缓存、线程缓存、等

innodb_buffer_pool_size=576M ->256M (InnoDB引擎缓冲区占了大头)

performance_schema_max_table_instances=600 (5.6默认的设置12500)

table_definition_cache=400 (5.6默认的设置1400)

table_open_cache=256 (5.6默认的设置2000)

query_cache_size=0(高并发系统建议禁用)

tmp_table_size=64M(临时表数据量大于tmp_table_size会将临时表存储到磁盘)


4、MySQL内存占用计算

##MySQL 最大可使用内存( M ):

SELECT

  (

    @@key_buffer_size + @@innodb_buffer_pool_size + @@query_cache_size + @@tmp_table_size + @@max_connections * (

      @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack

    )

  ) / 1024 / 1024 AS result

  

##MySQL 单个连接最大可使用内存( M ):

  SELECT

    (

      @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack

    ) / 1024 / 1024 AS result

 

##MySQL 最大可使用内存(不包含连接占用内存)( M ):

    SELECT

      (

        @@key_buffer_size + @@innodb_buffer_pool_size + @@query_cache_size + @@tmp_table_size

      ) / 1024 / 1024 AS result


四、进一步定位


1、由于是怀疑线程, 所以用CURRENT_NUMBER_OF_BYTES_USED倒序, 查询Thread相关的表; 结果类似下图:

select thread_id,event_name,CURRENT_NUMBER_OF_BYTES_USED

from memory_summary_by_thread_by_event_name

order by CURRENT_NUMBER_OF_BYTES_USED desc

limit 3 \G

2、select name,type,thread_id,thread_os_id,processlist_id  from threads;

3、processlist和events_statements_current一一对应起来,可以通过performance_schema.threads表来关联,给出一个可行的sql

select a.SQL_TEXT,c.id,d.trx_started 

from events_statements_current a 

join threads b on a.THREAD_ID=b.THREAD_ID 

join information_schema.processlist c on b.PROCESSLIST_ID=c.id 

join information_schema.innodb_trx d on c.id=d.trx_mysql_thread_id

order by d.trx_started  \G

4、一般占用私有内存多的是tmp_table,sort以及join等操作,是否某个session占用过多的私有内存?

explain SQL_TEXT;

5、show processlist;

6、kill id



==========================================================================

005-参考文章

https://blog.51cto.com/11819159/2128099

https://www.cnblogs.com/xzlive/p/9881507.html

https://www.linuxidc.com/Linux/2016-12/137852.htm

http://blog.itpub.net/29510932/viewspace-2129312/

https://blog.csdn.net/dennis211/article/details/78170079

https://www.fashengba.com/post/mysql-delete-big-table.html

https://www.cnblogs.com/D666/p/9165044.html


动力小刚于2019年4月  个人邮箱:zcs0237#163.com


分享好友

分享这个小栈给你的朋友们,一起进步吧。

MySQL干货资料
创建时间:2020-05-06 14:18:32
每天都有干货输出哦
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~