Topic: How Can I Fix a Slow Queries Problem ?

How Can I Fix a Slow Queries Problem ?

@honestscott

Participant

14 years, 5 months ago

Hi,

My bbpress 1.0.2 forum is causing a lot of slow queries, and I had to shut it down because my host Monster Host was Throttling my account big time.

Is there a fix for this, I am not running many plug-ins that would use the database except for bbpm, but I tried disabling this plug-in, and I still get clobbered with slow queries and CPU throttling.

I hate to have to shut the forum down, but I am not a php coder or a database administrator.

I tried moving my sites to a semi-dedicated server (to get more server resources) but their Myphp interface clashed somehow with all my WP databases.

I was on NTC hosting and their support team told me it was a problem with my WP sites, although I did get one blog to work half way decent, but my bbpress forum would not.

I was using Blue Host when I created my sites, so I went to Monster Host (their sister company) and loaded my files and DB’s and they came up just fine, but I am back to the DB strangulation with slow queries.

I check the slow queries log on my server and I see a lot of forum queries in there, but the logs don’t really pin-point a cause (plug-in or script).

Here’s a list of the plugins I have on my forum:

ajaxed quote

adsense for bbpress

bbcode buttons

bbcumulus

bbpm

bbpress signatures

bb-smiles

check for updates (not activated)

delete all bozos

mass delete users

members online

php4bb

super search

approve registration

bb-avatars

bbcode lite

bb twitter (not active)

google analytics

human test

moderator notification

new user notification

restrict registration

I would appreciate any, and all ideas so I could save my forum from the trash bin.

Thanks for your time,

Scott

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

@chrishajer

Participant

14 years, 5 months ago

rescued from akismet

@nightgunner5

Member

14 years, 5 months ago

Try installing this plugin: https://bbpress.org/plugins/topic/bb-benchmark/

And adding the following line to your bb-config.php:

define('SAVEQUERIES', true);

Then, you can view source on any forum page (when logged in as admin) and it will tell you the slowest query, along with the function that used it. It’s usually simple guessing to figure out which plugin a query came from after that.
@kevinjohngallagher

Member

14 years, 5 months ago
Hi Scott,

Just been through this with my WorldCup Fansite Forum after the final on Sunday night, so I’ve a few things to throw your way. I’m sorry about the slow and pedantic manner of this post too; but if it’s this or close yoru forum, we’ll just get through it!

My gut feeling is that this is a plugin and/or host issue. So lets see what we can do here:

First, lots of boring questions I’m afraid:

Is your website using WordPress at all?

If so, have you set up “deep integration”?

Did you do the installing/setup yourself?

Second, ask your host if they’re intentionally throttling you or if it’s automatic?

If it’s happening, ask them what the issue is (I doubt they’ll give you a huge run down but might find a specific issue you didn’t know was the ‘biggie’)

Third, Make backups

Copy a back up of every plugin and your theme on your hardrive somewhere, then log into bbpress and deactivate them all (apart from akismet) and change to the normal default theme.

Fourth, delete all of the plugins and themes.

Fifth, Tell your users

Write a post (then sticky to font) about how your website is going through an “upgrade” and in order to get the latest most secure most awesome software installed you’re having to accept the default theme for half a day.

Sixth, Lets have a look at those SQL queries.

Open up your bb-config.php file and add the following line before the database connection stuff (at around line 33):

define('SAVEQUERIES', true);

Seventh, edit your footer

open up your footer.php in (my-templates/kakumei/) and add this to the very bottom:
```
if (bb_current_user_can('administrate'))

{

global $bbdb;

print_r($bbdb-&gt;queries);

}
```
Apologies for this, but that last line is going to have something that looks like “->” in it. Please, make this into “-” and then a right arrow (usually shift+period). It’s a bug here on this forum – should be fixed soon.

Eight, load your forum.

At the bottom of your homepage you should see a big list of stuff, which will have lots of SQL commands.

This is a list of all the SQL commands that load, in order, along with the time taken to execute.

Do any of them look to be taking longer than the others?

Copy and paste this list into a text file and save on your desktop (there will be about 15 SQL commands with a few lines around them).

Nine, recount.

Log into your admin area and go to Tools.

Select ONE checkbox at a time and hit “RECOUNT ITEMS”.

Do this for all checkboxes going down the list.

Ten, load your forum (again).

Repeat Step 8.

Compare the results betwen the two text files.

Have any of the “times” come down?

Are any of them above 0.1 seconds?

Are any of them betwen 0.5 and 1 second?
@_ck_

Participant

14 years, 5 months ago

honestscott, any update on this?

Even 1.x should not be causing slow queries, something might be misbehaving.

My Super Search plugin DOES do a very heavy non-indexed query, it’s unavoidable. But unless you have a large forum with frequent searches, I am not sure that would get you in trouble.

The good news is if you’ve moved to a VPS (I assume that’s what “semi-dedicated server” means) you have direct control over a few important things you didn’t have before. It means we can make sure your MySQL cache is on (it’s off by default on many setups for some strange reason) and you can install an opcode cache like eAccelerator which will cause a fantastic reduction in load and improve speed.