【数据库】mysql卡顿问题查找和解决方法

你的名字 2022-04-23 17:38 712阅读 0赞

### 一、所遇问题 ###

写在前边的废话：今天面试阿里的时候问到过类似问题，以前做调优的时候都是现查现用，缺乏总结，面试时答得也不好，今天趁此机会做一个梳理，知识只有沉淀下来才是自己的东西。

**问题：**mysql 运行过程中超级卡顿，并出现CPU使用率居高不下的情况，如何定位问题点以及如何解决？

### 二、占用CPU过高，可能原因 ###

1）一般来讲，排除高并发的因素，还是要找到导致你CPU过高的哪几条在执行的SQL，show processlist语句，查找负荷最重的SQL语句，优化该SQL，比如适当建立某字段的索引；

2）打开慢查询日志，将那些执行时间过长且占用资源过多的SQL拿来进行explain分析，导致CPU过高，多数是GroupBy、OrderBy排序问题所致，然后慢慢进行优化改进。比如优化insert语句、优化group by语句、优化order by语句、优化join语句等；

3）考虑定时优化文件及索引；

4）定期分析表，使用optimize table；

5）优化数据库对象；

6）考虑是否是锁问题；

7）调整一些MySQL Server参数，比如key\_buffer\_size、table\_cache、innodb\_buffer\_pool\_size、innodb\_log\_file\_size等；

8）如果数据量过大，可以考虑使用MySQL集群或者搭建高可用环境。

9）可能由于内存latch（泄露）导致数据库CPU高

10）在多用户高并发的情况下，任何系统都会hold不住，所以使用缓存是必须的，使用memcached或者redis缓存都可以；

11）看看tmp\_table\_size大小是否偏小，如果允许，适当的增大一点；

12）如果max\_heap\_table\_size配置的过小，增大一点；

13）mysql的sql语句睡眠连接超时时间设置问题（wait\_timeout）

14）使用show processlist查看mysql连接数，看看是否超过了mysql设置的连接数

### 三、案例一（临时表过大） ###

MySQL负载居高不下，如果打开了慢查询日志功能，最好的办法就是针对慢查询日志里执行慢的sql语句进行优化，如果sql语句用了大量的group by等语句，union联合查询等肯定会将mysql的占用率提高。所以就需要优化sql语句除了优化sql语句外，也可以做一些配置上的优化。在mysql中运行show proceslist;出现下面回显结果：

1.查询有大量的Copying to tmp table on disk状态明显是由于**临时表过大导致mysql将临时表写入硬盘影响了整体性能**。Mysql中tmp\_table\_size的默认值仅为16MB，在当前的情况下显然是不够用的。

mysql> show variables like "%tmp%";+-------------------+----------+| Variable\_name | Value |+-------------------+----------+| max\_tmp\_tables | 32 || slave\_load\_tmpdir | /tmp || tmp\_table\_size | 16777216 || tmpdir | /tmp |+-------------------+----------+4 rows in set (0.00 sec)

**解决办法：调整临时表大小**

1）进mysql终端命令修改，加上global,下次进mysql就会生效mysql> set global tmp\_table\_size=33554432;Query OK, 0 rows affected (0.00 sec)

再次登陆mysqlmysql> show variables like "%tmp%";+-------------------+----------+| Variable\_name | Value |+-------------------+----------+| max\_tmp\_tables | 32 || slave\_load\_tmpdir | /tmp || tmp\_table\_size | 33554432 || tmpdir | /tmp |+-------------------+----------+4 rows in set (0.01 sec)

2）my.cnf配置文件修改\[root@www ~\]\# **vim my.cnf.....tmp\_table\_size = 32M**

重启mysql\[root@www ~\]\# /etc/init.d/**mysqld restart**

### 四、慢查询日志 ###

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。long\_query\_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。参考文章：[https://blog.csdn.net/m\_nanle\_xiaobudiu/article/details/79288257][https_blog.csdn.net_m_nanle_xiaobudiu_article_details_79288257]

[https_blog.csdn.net_m_nanle_xiaobudiu_article_details_79288257]: https://blog.csdn.net/m_nanle_xiaobudiu/article/details/79288257