Very slow queries
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
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!
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.
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.
@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)
Also try to use in a test website before putting in your life one, in case something go wrong, as it just did.
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?
Have you perhaps tried several different browsers and see if the error persists?
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!
@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/
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?
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_timein your php.ini file, maybe to something like 180 seconds.
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!
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!
(i.e. what would make BP only look at 20 rows of data and stop, out of 100k lines of data?)
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
Any theories beyond that that will help pinpoint the problem? Thank you again for your time!
You could also check
max_execution_timeisn’t set too low.
Is there anything else in the error logs?
But, does any of this make sense for only querying 20 out of 60k records?
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.
So, how do I make my website work? 60k people wanting to connect – BuddyPress can’t handle??
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.
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 ]
@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.
You must be logged in to reply to this topic.