Slow SQL Queries causing High CPU Usage by MySQL

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;

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: 564447
Appreciate recommendations on next step?
 

Chris D

XenForo developer
Staff member
Is your "Read data marking lifetime" set at around 90 days?

If so, that can have a serious impact on performance. Did you increase that recently? (The default is 30 days).
 

Mouth

Well-known member
Is your "Read data marking lifetime" set at around 90 days?

If so, that can have a serious impact on performance. Did you increase that recently? (The default is 30 days).
Yes, it was at 90. Now changed down to 21.
No recent change, been like that for at least several months.
Thanks.
 
Top