Skip to:
Content
Pages
Categories
Search
Top
Bottom

front page takes 50 mysql queries


  • _ck_
    Participant

    @_ck_

    Can anyone tell me what the standard number of queries for the front page are on a default install of bbpress?

    I think mine are high because of my template hack for the latest reply link. (added: nope, tried removing it and no query reduction)

    There has to be a way to cache that and reduce the number of calls by pre-fetching all the links for all the topics to be listed, instead of doing one call at a time…

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

  • _ck_
    Participant

    @_ck_

    Ah I totally forgot I’ve got visitor tracking which adds some calls but doesn’t explain why it’s more than a dozen calls by design.

    If anyone’s interested, I’ve ported the “query diagnostics” plugin from wordpress so you can see each mysql call used, hidden as a report at the end of each page (for administrators).

    <?php

    /*
    Plugin Name: bb-benchmark
    Plugin URI: http://CKon.WordPress.com
    Version: 0.10
    Description: Prints simple benchmarks and mysql diagnostics, hidden in page footers.
    based on Jerome Lavigne's Query Diagnostics for WordPress http://vapourtrails.ca/wp-plugins
    Author: _ck_
    Author URI: http://CKon.WordPress.com
    */

    /* INSTRUCTIONS:

    1. add this line to your bbpress config.php file: @define('SAVEQUERIES', true);
    2. install & activate plugin
    3. do a "view source" on any bbpress page to see hidden results at bottom

    */

    function bb_benchmark_output() {
    if (bb_current_user_can( 'administrate' ) ) :
    if (SAVEQUERIES) :
    global $bbdb;
    echo "<!-- n === benchmark & query results === n ";

    while($qposition < $bbdb->num_queries){
    $qsubtime=$bbdb->queries[$qposition][1];
    if ($qsubtime>$qmaxtime) {$qmaxtime=$qsubtime;$qmaxquery=$bbdb->queries[$qposition][0];}
    $qtotal += $qsubtime;
    $qposition++;
    }

    $timer_stop=bb_timer_stop(0);

    echo @shell_exec("uptime")."n";
    echo "query count: ".$bbdb->num_queries." nn";
    echo "total query time: ".round($qtotal,4)." seconds nn";
    echo "total page time: ".round($timer_stop,4)." seconds.nn";
    echo "page render difference: ".(round($timer_stop-$qtotal,4))." seconds nn";
    echo "slowest call was: ".$qmaxquery."n at ".round($qmaxtime,4)." seconds nn";

    if (phpversion() >5.0 && function_exists(memory_get_peak_usage()) && function_exists(memory_get_usage())) {
    echo "nn === memory usage === n";
    echo "peak memory ".memory_get_peak_usage()." nn";
    echo "total memory ".memory_get_usage()." nn";
    }

    // echo "nn === resource usage === n";
    // print_r (getrusage());

    echo "nn === mysql queries used === n";
    print_r($bbdb->queries);

    echo "-->";
    endif;
    endif;
    }

    add_action('bb_foot', 'bb_benchmark_output');

    ?>


    _ck_
    Participant

    @_ck_

    Eek – I’m kinda freaked out by how bbpress handles front page topics and appends the last poster’s name.

    Two separate mysql queries are made for each name to get the data. Instead of retrieving the sequence of names all at once so it’s within the same table for speed. So if you have 50 front page topics with 50 different authors, beyond all the other queries will be 100 more queries.

    That’s got to be addressed if bbpress wants to call itself lightweight and efficient. All those names could be gotten with two built up calls, regardless how many on the front page.


    _ck_
    Participant

    @_ck_

    OMG. This is insane.

    Go edit your front-page.php, forum.php and view.php templates.

    Change

    topic_last_poster();

    to

    echo $topic->topic_last_poster_name;

    Cuts mysql queries in half. I went from 50 to under 20.

    The data is already in the retrieved topics in memory, there’s no need to reload all the userdata.


    tisme
    Member

    @tisme

    all the same way it could be probably saved on:

    topic_link()

    topic_title()

    topic_time()

    and so on, but this, I guess, wouldn’t allow to hook any custom filter?


    _ck_
    Participant

    @_ck_

    No, all those are properly cached and do not reduce mysql queries.

    topic_last_poster() for some reason forces a new metadata reload and two new mysql queries, uncached. It’s meant to be able to be used outside loops so it basically has to. It can’t just peek at the entire topics table just returned from the database.

    Any plugin that affects the username has already affected it on the write to the topics table. It’s already set for presentation. No need to hook it unless you are doing something really crazy. Even the “admin use display names” plugin will still work correctly.

    It’s the only really easy optimization.

    “topic_last_poster()” sticks out badly if you install the plugin I posted above and look at the results.

    update: if you are really worried about filters you can either have a lightweight pseudo function or do it this way

    echo apply_filters( 'topic_last_poster', $topic->topic_last_poster_name, $topic->topic_last_poster);

    instead of just

    echo $topic->topic_last_poster_name;

    and that will apply any filters looking for it


    fel64
    Member

    @fel64

    I’ve applied this plugin and I’ve only got 11 queries on the front page in kakumei, 13 in my theme (with an onlinelist and latest blog post). Are you running the latest? If not, that’s the likely cause.


    _ck_
    Participant

    @_ck_

    Hmm. Well I don’t think it’s an old theme but it might be another plugin hooking topic_last_poster(). I will have to dig.

    Also, how many forums + topics are you showing on your front page?

    I’ve got 8 forums and 35 topics with roughly 20 unique last poster usernames on the front page.

    Not that it really matters, if you are only hitting 11 queries than it’s still quite efficient. What’s funny is it could be single digit queries except that bbpress tests that it exists by hitting the database with a useless query every time it starts.


    _ck_
    Participant

    @_ck_

    Aha! The culprit is “Use Display Name” written by no less than the mdawaffle himself…

    His quick coding on that didn’t deal take into account the reprecussions of grabbing the entire meta for every username.

    So I guess this is a caution to anyone using that plugin.


    fel64
    Member

    @fel64

    5 forums, 30 threads, something like 10 dudes? But like you said it doesn’t really matter.

    I think Use Display Name just wasn’t written in the days of efficient usermeta caching, before “mdawaffle” (a dig?) implemented it. Obviously not kept up to date, of course. You can report it on the plugins trac.


    Trent Adams
    Member

    @trent

    Put it in the devlist and TRAC because Automattic people are at WordCamp this week and off til Monday.

    Trent


    _ck_
    Participant

    @_ck_

    Actually he can’t really “fix” the plugin, it’s an overall integration failure which necessarily adds more mysql queries (just like the plugin needed to allow spaces adds more overhead).

    Basically the output template will HAVE to use a difference function call than the “use display name” which fixes it everywhere else before it’s written back to the database.

    It’s the cost of integration and yet another example of why bbpress isn’t magically better than any other forum for integration with wordpress.

    (or just use my workaround to cut out the extra queries ;-)

    ps. “mdawaffle” wasn’t a “dig” – isn’t that his nickname?


    _ck_
    Participant

    @_ck_

    Oh how embarrassing, it’s “mdawaffe” without the “L” I keep inserting. My sincere apologises, it was completely unintentional.


    fel64
    Member

    @fel64

    But your workaround no longer gives you the display names, right? … So you cut out the penalty, but also lost out on any gain?

    Can’t you do what you said earlier and use a hook to get all the display names at once?

    Hehe, actually I thought calling him mdawaffle was pretty good ^^ Just when you kept doing it I was confused.


    _ck_
    Participant

    @_ck_

    Well there’s no way around the fact that “use display names for admin” basically MUST fetch the display name from the wp_users table when asked for it – and if it’s not been accessed before it’s not cached so it must hit the mysql db directly.

    However, since “use display names” forces the forums table to store the display name properly, there’s no need to run the last poster id through the routine again and force a fetch of the display name, it’s already in the username field for last poster.

    The good news however is between that tweak, a couple other bits of fine tuning and the undocumented $bb->load_options = true; I was able to get the queries down to just 10 for the front page for visitors and 13 for logged in members. This includes an extensive number of plugins, including useronline trackline (not the simplified one here but a port from wordpress).

    It’s never been faster and rather impressive :-)

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

You must be logged in to reply to this topic.