Skip to:
Content
Pages
Categories
Search
Top
Bottom

Need help with slow MYSQL query & high CPU usage


  • TKServer
    Participant

    @tkserver

    Recently my MYSQL CPU usage went from 40% to over 100% and my server has been slowing down as a result. In doing some research I’ve learned to setup some mysql logging for slow queries. I have a particular query that happens often. In the query below it appears that over 700,000 rows are queried in the DB taking up almost 3 seconds. Imagine that times however many users and it is bogging the site down. Is this query below from bbpress or how can I tell? It’s hard to trace where it is being called:

    # Time: 211220 17:42:06
    # User@Host: mysite_wp[mysite_wp] @ localhost []
    # Thread_id: 1667  Schema: mysite_wp  QC_hit: No
    # Query_time: 2.641487  Lock_time: 0.000104  Rows_sent: 10  Rows_examined: 717793
    # Rows_affected: 0  Bytes_sent: 188
    SET timestamp=1640047326;
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND (
      wp_postmeta.meta_key = '_bbp_last_active_time'
      AND
      (
        ( mt1.meta_key = '_bbp_forum_id' AND CAST(mt1.meta_value AS SIGNED) != '1773' )
      )
    ) AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;
Viewing 1 replies (of 1 total)

  • Robin W
    Moderator

    @robin-w

    hmmm…agree it is very hard from this snippet to know where it is coming from.

    Presuming you can repeat this timing, I’d suggest that you do the standard fault finding ie swicth to a default theme and just have bbpress as the only plugin. If the query still shows, then it is a bboress one, and you can work through individual bbpress displays to see if you can work out where it is called.

    If it doesn’t show, then I’d suspect a bbpress add-n and you can add back plugins to try and find the culprit

Viewing 1 replies (of 1 total)
  • You must be logged in to reply to this topic.
Skip to toolbar