Skip to:
Content
Pages
Categories
Search
Top
Bottom

Performance issues with the wp_bp_activities table

  • Avatar of Vid Luther
    Vid Luther
    Participant

    @zippykid

    Hi,
    We’re running into some performance issues with a couple of sites using BuddyPress 1.9.2 with WordPress 3.8.1 on our systems.

    The problem query seems to be called from the wp-activity-classes.php. At first glance of just the query itself, it seems that the explicit SQL HINT in the query is the culprit. Here’s a gist explaining it..

    <script src=”https://gist.github.com/vidluther/9370018.js”></script>

    I don’t know the entire code base, or the history behind the decision to force SQL hints, so I wanted to ask here, before taking it to Trac.

    For the record, I’ve tried the same query on 3 separate machines, ranging from 128GB RAM, to 48GB RAM.. With the hint, I’ve never been able to get the query to run in less than 1 second.

    Running MySQL server from Percona version 5.5, and MySQL Community edition 5.6.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Avatar of shanebpdev
    shanebp
    Participant

    @shanebp

    The link to your gist:

    https://gist.github.com/vidluther/9370018

    You’ll probably get a better response by opening a ticket at:

    https://buddypress.trac.wordpress.org/

    Avatar of Boone Gorges
    Boone Gorges
    Keymaster

    @boonebgorges

    Hi @zippykid – Thanks for the post and for the gist of the EXPLAIN.

    We’ve made some massive improvements for 2.0 in the activity query, but we haven’t really changed the way that this COUNT query works. In any case, forcing an index on a COUNT that doesn’t need sorting doesn’t really make much sense anyway, so we should probably remove it. If you get a chance, please open an enhancement ticket at https://buddypress.trac.wordpress.org with the suggestion. If not, I’ll try to get to it later.

    Avatar of mpa4hu
    mpa4hu
    Participant

    @mpa4hu

    @boonebgorges I have a suggestion for this:

    What about not using second query at all.

    As long as I can see, this is only used to know if there is more pages and to decide if we need

    	<li class="load-more">
    		<a href="#more"><?php _e( 'Load More', 'buddypress' ); ?></a>
    	</li>

    So instead of second query we can get $per_page + 1 activity but return $per_page and instead of total, return $has_more which is function like this:

    if(count(activities) < $per_page)
      $has_more = false;
    } else {
      $has_more = true;
    }

    I have used such think with infinite scroll and it works. On the other side I’m not sure about performance but with all my logic, this should be better.

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

You must be logged in to reply to this topic.