cpu经常100%,是为何故?

论坛首页 论坛 寻求帮助 cpu经常100%,是为何故?

正在查看 4 条回复
  • 作者
    帖子
    • #21121
      蓝魔
      参与者
        @cgq630105023
        楼主

        服务器阿里云  2核 4g  3m

        网站  wordpress

      • #21122
        蓝魔
        参与者
          @cgq630105023
          楼主

        • #21124
          孙锡源
          管理员
            @ibadboy
            坏蛋的博客
            ibadboy.net

            贴一下mysql的慢查询日志。

            怀疑是触发了低效的查询

            • #21126
              蓝魔
              参与者
                @cgq630105023
                楼主
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.185977  Lock_time: 0.000080 Rows_sent: 10  Rows_examined: 97056
                SET timestamp=1627638538;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (62995) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210730 17:48:59
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.023150  Lock_time: 0.000080 Rows_sent: 10  Rows_examined: 21502
                SET timestamp=1627638539;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (87782) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (7,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210730 17:49:00
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.215862  Lock_time: 0.000070 Rows_sent: 10  Rows_examined: 111982
                SET timestamp=1627638540;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (113985) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (5,221,203,361,466,540,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210730 20:45:19
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.465029  Lock_time: 0.000155 Rows_sent: 10  Rows_examined: 111177
                SET timestamp=1627649119;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (122921) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (5,203,540,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210730 20:45:22
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.377914  Lock_time: 0.000155 Rows_sent: 10  Rows_examined: 94504
                SET timestamp=1627649122;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (45807) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,22) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210730 20:45:24
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.526419  Lock_time: 0.000152 Rows_sent: 10  Rows_examined: 97058
                SET timestamp=1627649124;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (80623) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210731  2:30:08
                # User@Host: root[root] @ localhost []
                # Query_time: 3.366914  Lock_time: 0.000000 Rows_sent: 741313  Rows_examined: 741313
                SET timestamp=1627669808;
                SELECT /*!40001 SQL_NO_CACHE */ * FROM wp_baiduseo_zhizhu;
                # Time: 210731  2:30:11
                # User@Host: root[root] @ localhost []
                # Query_time: 3.155917  Lock_time: 0.000000 Rows_sent: 367127  Rows_examined: 367127
                SET timestamp=1627669811;
                SELECT /*!40001 SQL_NO_CACHE */ * FROM wp_postmeta;
                # Time: 210731  2:30:14
                # User@Host: root[root] @ localhost []
                # Query_time: 5.593305  Lock_time: 0.000000 Rows_sent: 136109  Rows_examined: 136109
                use www_qh4321_com;
                SET timestamp=1627669814;
                SELECT /*!40001 SQL_NO_CACHE */ * FROM wp_posts;
                # Time: 210731  2:30:18
                # User@Host: root[root] @ localhost []
                # Query_time: 7.349790  Lock_time: 0.000000 Rows_sent: 134577  Rows_examined: 134577
                use baidu_com;
                SET timestamp=1627669818;
                SELECT /*!40001 SQL_NO_CACHE */ * FROM wp_posts;
                /www/server/mysql/bin/mysqld, Version: 5.5.62-log (Source distribution). started with:
                Tcp port: 3306  Unix socket: /tmp/mysql.sock
                Time                 Id Command    Argument
                # Time: 210731  3:30:14
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 4.851890  Lock_time: 0.000129 Rows_sent: 8  Rows_examined: 54113
                use baidu_com;
                SET timestamp=1627673414;
                SELECT   wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
                  wp_posts.post_date > '2021-05-02 03:30:09'
                ) AND wp_posts.ID NOT IN (123181) AND ( 
                  wp_postmeta.meta_key = 'views'
                ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 8;
                # Time: 210731  3:30:19
                # User@Host: www_qh4321_com[www_qh4321_com] @ localhost []
                # Query_time: 3.992659  Lock_time: 0.003675 Rows_sent: 5  Rows_examined: 12249
                use www_qh4321_com;
                SET timestamp=1627673419;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (41113) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (101) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 5;
                # Time: 210731  3:30:23
                # User@Host: www_qh4321_com[www_qh4321_com] @ localhost []
                # Query_time: 4.361817  Lock_time: 0.000072 Rows_sent: 6  Rows_examined: 25812
                SET timestamp=1627673423;
                SELECT   wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND wp_posts.ID NOT IN (41113) AND ( 
                  wp_term_relationships.term_taxonomy_id IN (101)
                ) AND ( 
                  wp_postmeta.meta_key = '_thumbnail_id'
                ) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY RAND() LIMIT 0, 6;
                # Time: 210731 14:41:30
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.787753  Lock_time: 0.000134 Rows_sent: 10  Rows_examined: 98151
                use baidu_com;
                SET timestamp=1627713690;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (74839) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210731 14:41:37
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 4.305315  Lock_time: 0.000089 Rows_sent: 10  Rows_examined: 98151
                SET timestamp=1627713697;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (65608) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210731 14:43:31
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.554191  Lock_time: 0.000060 Rows_sent: 10  Rows_examined: 98149
                SET timestamp=1627713811;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (75008) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;
                # Time: 210731 14:43:32
                # User@Host: baidu_com[baidu_com] @ localhost []
                # Query_time: 3.220765  Lock_time: 0.000112 Rows_sent: 10  Rows_examined: 98151
                SET timestamp=1627713812;
                SELECT   wp_posts.*, count(tr.object_id) as cnt FROM wp_posts INNER JOIN wp_term_relationships AS tr ON wp_posts.ID = tr.object_id WHERE 1=1  AND wp_posts.ID NOT IN (62051) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND tr.term_taxonomy_id IN (4,108) GROUP BY  tr.object_id ORDER BY  cnt DESC, wp_posts.ID DESC LIMIT 0, 10;

                 

            • #21127
              孙锡源
              管理员
                @ibadboy
                坏蛋的博客
                ibadboy.net

                MySQL最近一次记录的慢查询是下午两点了,通过这个日志看不出什么。

                这有可能是实际发生了低效的慢查询但是你的记录阈值太高导致的。

                你是否能通过访问某个特定url来触发这个cpu 100%的情况?

                如果可以的话,安装插件:query monitor

                通过这个插件来监控慢查询(需开启wp的debug模式)

                如果你不知道如何触发的话可以从web日志里找找灵感

              • #21128
                蓝魔
                参与者
                  @cgq630105023
                  楼主

                  页面查询次数、加载时间和内存占用

                  261 queries in 1.135 seconds, using 17.56MB memory

                   

                   

                  多少合适呢? 这是本地测试的

                  • #21129
                    孙锡源
                    管理员
                      @ibadboy
                      坏蛋的博客
                      ibadboy.net

                      建议使用redis cache object或者用水煮鱼的wpjam。

                      总之,你需要开缓存。

                      单次请求261次查询太夸张了,这个应该就是造成你cpu 100%的直接原因

                      • #21130
                        蓝魔
                        参与者
                          @cgq630105023
                          楼主

                          一般多少以下合适?

                          • #21131
                            孙锡源
                            管理员
                              @ibadboy
                              坏蛋的博客
                              ibadboy.net

                              最多100,否则就得考虑优化了。

                    正在查看 4 条回复
                    • 哎呀,回复话题必需登录。