Mouth
Well-known member
I'm using Percona 5.7 with a reasonably good/optimised configuration. A dedicated server, with MySQL having 8Gb InnoDB buffer pool.
Quite recently, I'm getting VERY high CPU usage by MySQL (400% and above), causing high server load averages (6+) and thus slow site responsiveness.
Looking at MySQL Slow Queries Log, I'm seeing very frequent repetition of the following;
Appreciate recommendations on next step?
Quite recently, I'm getting VERY high CPU usage by MySQL (400% and above), causing high server load averages (6+) and thus slow site responsiveness.
Looking at MySQL Slow Queries Log, I'm seeing very frequent repetition of the following;
Code:
SET timestamp=1501989087;
SELECT thread.thread_id, post.post_id
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
(post.thread_id = thread.thread_id AND thread.node_id IN (60, 83, 4, 67, 94, 69, 81, 86, 187, 82, 169, 56, 61, 76, 113, 77, 177, 107, 57, 75, 161, 85, 92, 95, 91, 87, 159, 160, 71, 156, 115, 157, 176, 73, 114, 79, 70, 93, 108, 168, 58, 59, 66, 88, 101, 175, 98, 97, 99, 100, 96, 185, 103, 3, 68, 72, 80, 186, 109, 111, 110, 89, 62, 164, 63, 165, 64, 74, 106, 84, 27, 153))
INNER JOIN xf_forum AS forum ON
(forum.node_id = thread.node_id AND forum.find_new = 1)
LEFT JOIN xf_thread_read AS thread_read ON
(thread_read.thread_id = thread.thread_id AND thread_read.user_id = '32906')
LEFT JOIN xf_forum_read AS forum_read ON
(forum_read.node_id = thread.node_id AND forum_read.user_id = '32906')
WHERE post.post_date > '1494213076'
AND post.message_state = 'visible'
AND thread.discussion_state = 'visible'
AND post.post_date > GREATEST(
IF (thread_read.thread_read_date IS NULL, 0, thread_read.thread_read_date),
IF (forum_read.forum_read_date IS NULL, 0, forum_read.forum_read_date)
);
# Time: 2017-08-06T03:11:27.759195Z
# User@Host: netrider[netrider] @ localhost [] Id: 14322
# Schema: netrider Last_errno: 0 Killed: 0
# Query_time: 11.743324 Lock_time: 0.000066 Rows_sent: 40307 Rows_examined: 2277189 Rows_affected: 0
# Bytes_sent: 564447
SET timestamp=1501989087;
SELECT thread.thread_id, post.post_id
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
(post.thread_id = thread.thread_id AND thread.node_id IN (60, 83, 4, 67, 94, 69, 81, 86, 187, 82, 169, 56, 61, 76, 113, 77, 107, 57, 75, 161, 85, 92, 95, 91, 87, 159, 160, 71, 156, 115, 157, 176, 73, 114, 79, 70, 93, 108, 168, 58, 59, 66, 88, 101, 175, 98, 97, 99, 100, 96, 185, 103, 3, 68, 72, 80, 186, 109, 111, 110, 89, 62, 164, 63, 165, 64, 74, 106, 84, 27, 153))
INNER JOIN xf_forum AS forum ON
(forum.node_id = thread.node_id AND forum.find_new = 1)
LEFT JOIN xf_thread_read AS thread_read ON
(thread_read.thread_id = thread.thread_id AND thread_read.user_id = '60104')
LEFT JOIN xf_forum_read AS forum_read ON
(forum_read.node_id = thread.node_id AND forum_read.user_id = '60104')
WHERE post.post_date > '1494213075'
AND post.message_state = 'visible'
AND thread.discussion_state = 'visible'
AND post.post_date > GREATEST(
IF (thread_read.thread_read_date IS NULL, 0, thread_read.thread_read_date),
IF (forum_read.forum_read_date IS NULL, 0, forum_read.forum_read_date)
);
# Time: 2017-08-06T03:11:27.844309Z
# User@Host: netrider[netrider] @ localhost [] Id: 14323
# Schema: netrider Last_errno: 0 Killed: 0
# Query_time: 11.766933 Lock_time: 0.000065 Rows_sent: 40307 Rows_examined: 2277189 Rows_affected: 0
# Bytes_sent: 564447Appreciate recommendations on next step?
