Skip to:
Content
Pages
Categories
Search
Top
Bottom

[Resolved] Very slow queries

  • Avatar of J J
    J J
    Participant

    @j-j

    I am getting error messages such as this from my hosting provider, which they are guessing are slowing down my website:

    bp-activity-classes.php:387 Efficiency: 0.000 | Rows Sent/Affected: 20 | Rows Examined: 95413
    bp-activity-classes.php:387 Efficiency: 0.001 | Rows Sent/Affected: 50 | Rows Examined: 95380

    Any ideas?

    I am using BuddyPress 1.9.2 version and WordPress version 3.8.3. I have almost 60,000 registered users, each with multiple profile fields.

    Thanks in advance!

Viewing 25 replies - 1 through 25 (of 26 total)
  • Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    Not exactly an answer to your question but related to your site’s performance, take a look at Boone’s blog post. In particular the section on performance enhancements. Consider upgrading to BP 2.0 before you spend too much time investigating why 1.9.2 is slowing down your site.

    https://buddypress.org/2014/04/buddypress-2-0-juliana/

    Avatar of J J
    J J
    Participant

    @j-j

    Thank you! Will do so right now and get back to you.

    Avatar of J J
    J J
    Participant

    @j-j

    Nope – actually updating to the new version made it worse – impossible to login due to a time out error (502). Trying to revert to my backup now.

    Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    @j-j ah man! Before you roll-back, sometimes a 502 error is easy to resolve so maybe give these steps a try:

    1. Hit F5 or hard reload
    2. Clear your browser’s cache (sometimes outdated files are cached by the browser)
    3. Delete cookies (same as 2 but for cookies)

    Avatar of Renato Alves
    Renato Alves
    Participant

    @espellcaste

    Also try to use in a test website before putting in your life one, in case something go wrong, as it just did.

    Avatar of J J
    J J
    Participant

    @j-j

    My test website doesn’t play well with BP :) And already tried the 502 error suggestions – but thanks! Any idea what the heck could be causing such long queries?

    Avatar of bp-help
    bp-help
    Participant

    @bphelp

    @j-j
    Have you perhaps tried several different browsers and see if the error persists?

    Avatar of J J
    J J
    Participant

    @j-j

    Thank you, but of of course ;)

    Anyone – can you help with these consistent LONG QUERIES specifically? My hosting provider is stumped and thinks its a BuddyPress issue. Thank you!

    Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    @j-j 60,000 members is an awful lot compared to most BuddyPress installations! Whilst low-performance is less noticeable on websites that have low-traffic, it explains why your queries are taking the extra time.

    Going into 2.0, I think the core team realised more could be done to help sites such as your own, so they earmarked lots of areas of 1.9.2 that could be improved performance-wise. I think it might be more profitable in the long run for you to resolve the 502 error, rather than addressing a handful of queries that are running slowly. It’s likely that these queries have already been addressed in 2.0 – along with so much more.

    Actually, 2.0.1 came out yesterday. It includes a number of fixes, some of which are related to the 1.9.x to 2.0.x upgrade routine. See: http://buddypress.org/2014/05/buddypress-2-0-1/

    Avatar of J J
    J J
    Participant

    @j-j

    Thank you! Will try 2.0.1…but when I upgraded to 2.0 the 502 error was so significant I couldn’t even log in to my dashboard to revert to an earlier version of my website…had to do it through my hosting provider.

    Great to hear that my little website (www.sharedabilities.com) may have too much traffic for BP. Those kinds of problems are significant, but nice to have. What drives the query timing? Why is it timing out so early just after a few rows?

    Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    @j-j

    What exactly is happening regarding the queries? Are they just running slowly or are they timing out and failing to complete? If they are timing out then try increasing the value of max_execution_time in your php.ini file, maybe to something like 180 seconds.

    Avatar of bp-help
    bp-help
    Participant

    @bphelp

    @j-j
    I just registered on your site, feel free to delete the account now but I can’t replicate the 502 error. I logged in jumped from page to page and never got the error so I dunno! Sorry but I tried!

    Avatar of J J
    J J
    Participant

    @j-j

    Thank you so much for your time and effort! I am hosted by WP Engine and they speak highly of the BP support.

    The error is sporadic by nature – if 30 people are logging on at the same time, or posting while logged on, for example, multiple queries v. you logging on potentially alone.

    Why would BP only query a few lines and then stop or time out? Ideas welcome! Thx!

    Avatar of J J
    J J
    Participant

    @j-j

    (i.e. what would make BP only look at 20 rows of data and stop, out of 100k lines of data?)

    Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    Why would BP only query a few lines and then stop or time out?

    Could be many things. Most possibly due to a limit of resources available on the server? What is your PHP memory_limit set to?

    Avatar of J J
    J J
    Participant

    @j-j

    Beautiful…will investigate. Thx!

    Avatar of J J
    J J
    Participant

    @j-j

    Any theories beyond that that will help pinpoint the problem? Thank you again for your time!

    Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    You could also check max_execution_time isn’t set too low.

    Is there anything else in the error logs?

    Avatar of J J
    J J
    Participant

    @j-j

    Will look. Extremely grateful for your guidance.

    Avatar of J J
    J J
    Participant

    @j-j

    But, does any of this make sense for only querying 20 out of 60k records?

    Avatar of henrywright
    henrywright
    Moderator

    @henrywright

    Actually that particular query will most probably be querying more than 60k records. You have 60k users, but the query in bp-activity-classes.php on line 387 is querying activity items. Considering an activity item gets added each time a user registers, changes their avatar, writes an update, a comment, a reply etc, then I’d guess there will be far more than just 60k rows.

    Avatar of J J
    J J
    Participant

    @j-j

    So, how do I make my website work? 60k people wanting to connect – BuddyPress can’t handle??

    Avatar of J J
    J J
    Participant

    @j-j

    And, while I’m a Mom of a special needs kid, you can also talk tech to me. :) Besides the timing change, something else is going on.

    Avatar of Ronia
    ronia
    Participant

    @ronia

    JJ, finding your site enough fast and smooth; maybe I am in a different time-zone. I clicked through many pages and profiles – no problem.

    [BTW, this is off-topic - if you must keep social buttons please also add G+, internet is not just FBook :) ]

    Avatar of Boone Gorges
    Boone Gorges
    Keymaster

    @boonebgorges

    @j-j Thanks for your patience working through the issues. As for the original question, I agree with @henrywright that 2.0.1 is a very good upgrade for sites like yours, because some of the queries have been refactored to be much, much more efficient.

    As for your 502 errors, it’s very hard to diagnose something like this without access to various logs. Since you’re hosted on WP Engine, I would suggest opening a ticket with them. Describe the specifics of the problem, and perhaps also point them to this thread. Ask if they can look in your error logs (and perhaps your slow query logs) to see what’s causing the issue. (NB a 502 error is very non-descriptive, and it could be that it’s not directly BP-related at all.) Only your host will be able to tell you for sure. If they come back with specifics that point to BP 2.0.1 as the culprit, we’ll be in a better position to help.

Viewing 25 replies - 1 through 25 (of 26 total)

The topic ‘[Resolved] Very slow queries’ is closed to new replies.