WordPress.org

Ready to get started?Download WordPress

Forums

Blog with 50k posts / 300k comments - slow msyql queries (3 posts)

  1. Klark0
    Member
    Posted 3 years ago #

    I'm pretty new to this mysql stuff but i've been doing alot of reading.

    I setup log slow queries to shows queries that are slow than 1. I stripped the site of all plugins except the caches. Here's some examples:

    ->  Somebody looking at posts by author?
    
    # Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 56540
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND (wp_posts.post_author = 2) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 4840, 20;
    
    -> Viewing a single post?
    
    # Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 165587
    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 ('77', '1', '58', '63', '64')  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, 1;
    
    -> Viewing a category
    
    # Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 145236
    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 ('1') 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 21340, 20;

    These queries eat up almost all of the CPU time and there's hundreds of them running. Luckily the server is surviving. The problem comes when I try to Publish new posts and Approve New comments. Those two actions bring down the server.

    I think my problem is that the normal wordpress queries are not using indexes to return data. I ran myisamchk -r on all the MYI tables. It fixed the indexes but i still get those slow queries above.

    I've been searching but i cannot find any wordpress documentation for dealing with large databases and adding indexes to make queries faster. Can anybody point me int he right direction?

  2. Klark0
    Member
    Posted 3 years ago #

    According to:
    http://codex.wordpress.org/Database_Description#Indexes

    My Indexes don't completely match what a wordpress install should be. And for the ones that do match, the values are off or NULL. Whatever that means.

    Exmaple:

    For wp_postmeta, the codex links say I should have indexes like:

    *INDEX* *CARDINALITY*

    PRIMARY 13
    post_id 15
    meta_key 7

    Instead of that I have:

    *INDEX* *CARDINALITY*

    PRIMARY NULL
    post_id NULL
    meta_key NULL

  3. Klark0
    Member
    Posted 3 years ago #

    http://core.trac.wordpress.org/ticket/10964

    Will probably make it into a final release in future, but i'm running these patches on my live site and so far its mostlyl fixed my problem.

    Hope this helps somebody.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.