mysqlcpu跑不满

开APP

Mysql数据库高CPU问题定位和优化 原创

2020-05-30 19:33:37

5点赞

yw804909465

码龄11年

关注

本课程的主旨及目标

•导致mysql数据库CPU高的常见原因

•常见定位问题的方法

•一般定位步骤

•数据库注意事项

导致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）可能由于内存（泄露）导致数据库CPU高

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

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

12）如果max_heap_table_size配置的过小，增大一点；

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

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

一般定位步骤：

   1】首先看看内存 free –m

目前看没有问题，1G的空闲

2】好了，用我们的必杀技，top看看资源消耗

可以看到服务器负载很高，mysql CPU使用已达到接近400%，基本可以看出mysql是可以进行优化的

3

mysql服务器最近偶尔出现cpu百分百居高不下的情况，所以需要进行分析 兄弟命令show processlist只列出前100条，如果想全列出请使用show full processlist 先 简单说一下各列的含义和用途： 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的 *** 作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 Connect Out 复制从服务器正在连接主服务器。 Copying to tmp table on disk 由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。 Creating tmp table 正在创建临时表以存放部分查询结果。 deleting from main table 服务器正在执行多表删除中的第一部分，刚删除第一个表。 deleting from reference tables 服务器正在执行多表删除中的第二部分，正在删除其他表的记录。 Flushing tables 正在执行 FLUSH TABLES，等待其他线程关闭数据表。 Killed 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志 位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。 Locked 被其他查询锁住了。 Sending data 正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。 Sorting for group 正在为 GROUP BY 做排序。 Sorting for order 正在为 ORDER BY 做排序。 Opening tables 这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。 正尝试打开一个表。 Removing duplicates 正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。 Reopen table 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。 Repair by sorting 修复指令正在排序以创建索引。 Repair with keycache 修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。 Searching rows for update 正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。 Sleeping 正在等待客户端发送新请求. System lock 正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 --skip-external-locking参数来禁止外部系统锁。 U pgrading lock INSERT DELAYED 正在尝试取得一个锁表以插入新记录。 Updating 正在搜索匹配的记录，并且修改它们。 User Lock 正在等待 GET_LOCK()。 Waiting for tables 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种 情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。 waiting for handler insert INSERT DELAYED 已经处理完了所有待处理的插入 *** 作，正在等待新的请求。 大部分状态对应很快的 *** 作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。 还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。 文章转自： http://blog.csdn.net/gumanren/article/details/4658385

升级mysql数据库到5.7版本后，发现MySQL对CPU和内存的消耗增加了不少，内存增加量还好一些，但CPU的飙升就麻烦一些了，这样会占用不少的资源。

其实可以使用MySQL内部的表定位问题SQL，通过这个SQL来定位问题，通过这个SQL的查询结果可以定位具体的SQL问题，然后再进行优化，而我的CPU偏大原因就是因为部分使用了like查询，优化这个部分mysql就正常了。

---