Skip to:
Content
Pages
Categories
Search
Top
Bottom

Get latest topics performance “problem” and “solution”


  • anotherdan
    Member

    @anotherdan

    I have just re-launched a site containing about 43000 topics and over 300000 posts…

    I had severe performance issues on all sites using the get_latest_topics functionality (the frontpage was severe because it is using more than once in my custom theme).

    Because of the “index” design it file-sorting because it’s sorting on topic_time and is mysql is hitting the stickies index.

    The solution was to create a new index only with the topic_time field in it. However I was unable to force this index in the query without touching the core files.

    This is what i did:

    Could there be a filter there so I could move this out into a plugin?

    BB_Query::generate_topic_sql function:

    $index_append = '';
    switch ($this->query_id) {
    case 'get_latest_topics':
    $index_append = ' FORCE INDEX (topics_time_order) ';
    break;
    }

    $this->request = $this->_filter_sql( $bits, "$bbdb->topics AS t $index_append" );

    I guess I will find more places where I could do some index optimization that fit’s my install… I can’t be the first running into this issue?

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

  • _ck_
    Participant

    @_ck_

    That’s funny I was just about to make a TRAC ticket pointing out there is no index on topic_time for the latest view.

    Strange you have to force the index though, what is the default ORDER BY, let me go look…

    yup, they order by topic_time, with no index, that’s just crazy, even for just 1000 topics, because it would have to do a full column scan. Ordering by topic_last_post_id would be even faster with an index too though I guess in a very strange circumstance the last post might not be the last time.

    Still, you should not have to force the index.

    Oh wait, I see why – it’s because mysql decides to use the stickies key instead, ugh.

    Yeah I can see why that would bring a large forum to a crawl.

    I think whomever designed the bbPress database tables had a misunderstanding how mysql indexes work because they are using multiple field indexes that are not the primary key. Or a key at all. Which is useless because mysql will only use one index per query regardless of it’s complexity (or lack of). An index on multiple fields is useless unless it’s used as a key. MySQL 5 can use different indexes for the query vs ORDER BY or GROUP BY but bbPress doesn’t take advantage of that at all.


    _ck_
    Participant

    @_ck_

    This is the SQL query to use if others want to add such an index:

    ALTER TABLE bb_topics ADD INDEX (topic_time)

    But that in itself is not enough, bbPress has to be forced to use the index.

    I am trying to figure out a way to do this without hacking the core, via a mini-plugin instead.


    John James Jacoby
    Keymaster

    @johnjamesjacoby

    Seems maybe the core should be updated to work around this eh?


    _ck_
    Participant

    @_ck_

    Not extensively tested but this will do it without a core hack

    (once ALTER TABLE bb_topics ADD INDEX (topic_time) is done)

    <?php
    /*
    Plugin Name: Topic Time Index
    */
    add_filter('get_latest_topics_join','topic_time_index',99999);
    function topic_time_index($join) {return " $join USE INDEX(topic_time) ";}
    ?>


    chrishajer
    Participant

    @chrishajer

    What versions will that work on, _ck_?


    _ck_
    Participant

    @_ck_

    Should work on both 0.9 and trunk.

    Seems fine on my 0.9 and trunk.

    Unfortunately I have too few topics to benchmark any significant performance increase since this is only helpful to more active forums (ie. > 1000 topics)


    chrishajer
    Participant

    @chrishajer

    So long as it doesn’t kill anything (error out), I’m fine with installing it. I can wait for the performance improvements (if there will be any.) My forum has only 600 or so topics right now.


    _ck_
    Participant

    @_ck_

    One possible way to benchmark the performance is to run these two queries in phpmyadmin (via SQL tab)

    SELECT SQL_NO_CACHE t.* FROM bb_topics AS t   USE INDEX(topic_time)  WHERE t.topic_status = '0' AND t.topic_sticky != '2'   ORDER BY t.topic_time DESC LIMIT 25

    vs.

    SELECT SQL_NO_CACHE t.* FROM bb_topics AS t  WHERE t.topic_status = '0' AND t.topic_sticky != '2'   ORDER BY t.topic_time DESC LIMIT 25

    On my setup the one with the forced index takes half the time of the one without, however we are talking 0.0026 sec vs 0.0013 sec so that is almost meaningless. What we need is someone with 10,000 topics and then you’ll know for certain.


    johnhiler
    Member

    @johnhiler

    I added the index and installed the plugin (my bb_topics has 11,826 records).

    The first query took 0.0005 sec. The second one took 0.0920 sec. That’s 184 times faster!


    _ck_
    Participant

    @_ck_

    Wow that is very significant, thanks for sharing that!


    chrishajer
    Participant

    @chrishajer

    With 615 topics, .0095 vs .0044 (~twice as fast). This is MySQL 4.0.27.


    _ck_
    Participant

    @_ck_

    I bet it only doubles the performance when it can sort in memory. Once it spills into a temporary disk sort, then you start seeing a more radical increase in performance between the forced index and not.

    I should also point out the mini-plugin (and index) will also help on any forum/sub-forum page in addition to the front-page. So all that extra index storage is at least multi-purpose.

    One way to boost this even further would be to ORDER BY the topic_last_post_id and then use the last_post_id as a UNIQUE index (key) since no two posts can have the same post_id (and no two topics can have the same last post_id). topic_time is not unique at all so it takes a tad longer for mysql to sort/larger index.


    Sam Bauers
    Participant

    @sambauers

    Here’s the result on WordPress.com’s English support forum:

    41,990 total rows

    Showing rows 0 - 24 (25 total, Query took 0.0024 sec)
    Showing rows 0 - 24 (25 total, Query took 0.0024 sec)
    Showing rows 0 - 24 (25 total, Query took 0.0024 sec)
    Showing rows 0 - 24 (25 total, Query took 0.0024 sec)
    Showing rows 0 - 24 (25 total, Query took 0.0024 sec)

    Showing rows 0 - 24 (25 total, Query took 0.2102 sec)
    Showing rows 0 - 24 (25 total, Query took 0.2062 sec)
    Showing rows 0 - 24 (25 total, Query took 0.2037 sec)
    Showing rows 0 - 24 (25 total, Query took 0.2031 sec)
    Showing rows 0 - 24 (25 total, Query took 0.2060 sec)


    _ck_
    Participant

    @_ck_

    Very nice. So it’s definitely proven to be way faster.

    But another question is, how much space does a non-unique index on 42,000 rows take?


    chrishajer
    Participant

    @chrishajer

    I think it’s cool that the index and the mini-plugin can improve on bbPress, but “severe performance issues” to me is not going from 0.0920 to 0.0005 seconds. That’s from 1/10 of a second to nearly nothing. But 1/10 second was “severe performance issues”?

    Or, does this query from _ck_ just show a way to measure the improvement, where in actual usage, someone with 10K topics or 42K topics would see a performance hit (delay) much different than the test query that _ck_ posted. I’m all for improving what can be improved, but there are much larger issues to worry about than something that is taking 1/10th of a second. With bandwidth and browsers being what they are, the page isn’t going to load that quickly anyway, and adding 1/10 of a second to the processing time is not going to be noticeable to me.


    Sam Bauers
    Participant

    @sambauers

    The same performance gain is achieved using the existing forum_time index.


    Sam Bauers
    Participant

    @sambauers

    @chrishajer

    It depends how many times that 1/10th of a second happens per second.


    _ck_
    Participant

    @_ck_

    Chris, you are missing the concept that if there are 100 people hitting the mysql server with the same or different queries and they can’t be cached, the load goes up exponentially.

    Sam’s benchmark is on a high performance, dedicated mysql server.

    Imagine someone with 40,000 records, non-indexed, on a shared server.


    _ck_
    Participant

    @_ck_

    >> The same performance gain is achieved using the existing forum_time index.

    Ah so in that case, no ALTER required and there is zero extra storage required and the plugin can be used like this:

    <?php
    /*
    Plugin Name: Topic Time Index
    */
    add_filter('get_latest_topics_join','topic_time_index',99999);
    function topic_time_index($join) {return " $join USE INDEX(forum_time) ";}
    ?>


    Sam Bauers
    Participant

    @sambauers

    I’ll adjust core in trunk and 0.9 branch to implement this somehow.


    anotherdan
    Member

    @anotherdan

    Chris, My page went from loading in 2-8 seconds to 0.200 – 0.300 per request… So yes it was severe – as I said i use this function more than once in the theme…

    Ahh there of course is the join filter… Thanks CK I will move this out, so I am able to update the core without a problem… I have another “extremely slow” issue, but I will get back to that later because that’s in the bb-admin part so only few people see that…


    Sam Bauers
    Participant

    @sambauers

    Changes are now in trunk, please test.

    0.9 may be a little more hackish to implement.


    Sam Bauers
    Participant

    @sambauers

    Same implementation now in 0.9 branch, if someone could confirm 0.9 that would be great.


    _ck_
    Participant

    @_ck_

    Confirmed working in 0.9.0.4 with apparent speed boost.

    Those using my mini-plugin, you MUST delete it when using 0.9.0.4 or 1.0-alpha-5 or you’ll find that all topics disappear.

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

You must be logged in to reply to this topic.