WP Database overload

  • Hello, I recently had an overload of my database, here’s one of the queries that seemed to have caused that, it’s the one that took the longest (7671 sec)…

    Time: 40% (7671 sec)
    Amount: 28% (228 queries)
    Rows Examined/Sent: 22126835 / 1402
    Avg. Query Exec/Lock Time: 33 / 0 sec.
    Used databases: [database_name]
    Query example: use database_name; SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

    As far as I understand, it’s the long time (7671 sec) that caused the overload, or is it rather the amount of queries (228) that’s the faulty?
    Anyway, here and in some of the other queries just like in this one, there is the SELECT SQL_CALC_FOUND_ROWS function, I read a bit about it and found out that it can slow down queries, could anybody please tell me when does wordpress run such a query with this function, in the database – I mean what action you need to do in your wordpress site – like search something or maybe edit a post, etc…

    Here’s another one just for reference (second longest):

    Time: 14% (2764 sec)
    Amount: 14% (113 queries)
    Rows Examined/Sent: 15465614 / 1130
    Avg. Query Exec/Lock Time: 24 / 0 sec.
    Used databases: [database_name]
    Query example: use database_name; SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (‘9’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

    Same question here; when does wordpress run such a query in the database, what exactley does it return? Also – SQL_CALC_FOUND_ROWS is present here as well…

    Thanks a bunch!

Viewing 4 replies - 1 through 4 (of 4 total)

  • Have you tried:

    – deactivating all plugins to see if this resolves the problem? If this works, re-activate the plugins one by one until you find the problematic plugin(s).

    – switching to the default theme to rule out any theme-specific problems?

    Thread Starter popokolok

    (@popokolok)

    I can do that, thanks for the tip, but I actually had 2 database overloads over 3 days, and before it’s been almost 4 months since the last one – so I suspect it might have been some spam bot attack or something like that…
    But looking at that first query I posted above – I can conclude that that query basically calculates how much posts I have in my entire database which are posts and not revisions and which are published and not drafts, etc… Is that correct? Also what does “WHERE 1=1” mean exactly? When would wordpress run such a query, could it run it when I’m inside the admin panel, in the posts panel, looking at the published posts section – when it tells me the total of published posts, etc… although I haven’t been to my admin anywhere around when the query seemed to have started If i assume it’s starting time by the number of seconds it ran…

    Any info is appreciated, thanks!

    Hi popokolok
    I have seen these same queries in my slow query log, and have also had server problems.

    I guess if you have literally thousands of posts the SELECT SQL_CALC_FOUND_ROWS and the sorting involved in that query can use up a lot of memory.

    The other query gets the category for each post in get_posts() – The join & sort can cause high memory usage if there are a lot of posts and categories

    Are you using a caching plugin like WP SuperCache ?
    Caching plugins don’t protect you when crawlers crawl the un-cached longtail pages of your site…

    Hope someone is watching this thread. Is there a way (of course there is, or so I hope) to see what queries are invoked and how much data is processed using a wordpress plugin? Server guys can’t tell me what scripts are making >10GB data flow per day with 50mb database. How do I track that?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘WP Database overload’ is closed to new replies.