cpu经常100%，是为何故？

[蓝魔]

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

网站  wordpress

[蓝魔]

…

[孙锡源]

贴一下mysql的慢查询日志。

怀疑是触发了低效的查询

[蓝魔]

# 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;

 

[孙锡源]

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

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

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

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

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

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

[蓝魔]

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

261 queries in 1.135 seconds, using 17.56MB memory

 

 

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

[孙锡源]

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

总之，你需要开缓存。

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

[蓝魔]

一般多少以下合适？

[孙锡源]

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

[作者]

帖子