• mysql占用CPU超过100%解决过程


    2017年12月2日上午,将学校新闻网2015年之前的45000多条记录迁移到了新网站的mysql数据库,新网站上有2015年1月1日之后的9000多条记录,数据量一下子增加了5倍。 
    2017年12月3日晚上9点多,有领导和老师反映新闻网无法访问,立即登录服务器进行排查。 

    一、使用top命令看到的情况如下: 
     
    可以看到服务器负载很高,,mysql CPU使用已达到接近400%(因为是四核,所以会有超过100%的情况)。 

    二、在服务器上执行mysql -u root -p之后,输入show full processlist; 可以看到正在执行的语句。 

    可以看到是下面的SQL语句执行耗费了较长时间。 

    SELECT id,title,most_top,view_count,posttime FROM article 
    where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)  
    order by most_top desc,posttime desc limit 0,8 
    但是从数据库设计方面来说,该做的索引都已经做了,SQL语句似乎没有优化的空间。

    直接执行此条SQL,发现速度很慢,需要1-6秒的时间(跟mysql正在并发执行的查询有关,如果没有并发的,需要1秒多)。如果把排序依据改为一个,则查询时间可以缩短至0.01秒(most_top)或者0.001秒(posttime)。 

    三、修改mysql配置文件中的pool/buffer等数值,重启mysql都没有作用。 

    四、通过EXPLAIN分析SQL语句 

    EXPLAIN SELECT id,title,most_top,view_count,posttime FROM article 
    where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)  
    order by most_top desc,posttime desc limit 0,8 
    可以看到,主select对27928条记录使用filesort进行了排序,这是造成查询速度慢的原因。然后8个并发的查询使CPU专用很高。
     
    五、优化
    首先是缩减查询范围
    SELECT id,title,most_top,view_count,posttime FROM article 
    where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)  and DATEDIFF(NOW(),posttime)<=90
    order by most_top desc,posttime desc limit 0,8
    发现有一定效果,但效果不明显,原因是每条记录都要做一次DATEDIFF运算。后改为
    SELECT id,title,most_top,view_count,posttime FROM article 
    where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)  and postime>='2017-09-05'
    order by most_top desc,posttime desc limit 0,8
    查询速度大幅提高。在PHP中,日期阈值通过计算得到
    $d = date("Y-m-d", strtotime('-90 day'));
    $sql = "
    SELECT id,title,most_top,view_count,posttime FROM article 
    where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)  and postime>='$d'
    order by most_top desc,posttime desc limit 0,8
    "
     
    六、效果
    查询时间大幅度缩短,CPU负载很轻
     
  • 相关阅读:
    cocospods 卡在 Analyzing dependencies
    android px、sp、dp之间的互转
    Android 4.4环境搭建——Android SDK下载与安装
    我心中的核心组件(可插拔的AOP)~大话开篇及目录
    EF架构~AutoMapper对象映射工具简化了实体赋值的过程
    我心中的核心组件(可插拔的AOP)~第二回 缓存拦截器
    EF架构~为EF DbContext生成的实体添加注释(T5模板应用)
    品味编程~底层开发人员应该这样设计一个字体类
    Study notes for Clustering and K-means
    深入理解Oracle索引(25):一招鲜、吃遍天之单字段索引创建思路
  • 原文地址:https://www.cnblogs.com/lidabo/p/14306568.html
Copyright © 2020-2023  润新知