WordPress › Support » MySQL slow down on standard queries

adaptistration
Member
Posted 11 months ago #
I am having issues with major throttling via my host (bluehost pro account) from my multisite install at adaptistration.com.

According to the mysql_slow_queries log, it seems that some typical WP functions are the culprit (as opposed to a plugin, which I know is usually the case). Here's what the log is identifying as the problem (and has been for the last few snapshots):
```
# Sun Jul  3 08:21:01 2011
# Query_time: 1.186702  Lock_time: 0.000132 Rows_sent: 7  Rows_examined: 35
use orchestr_sswp12;
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (3500, 1226, 1069, 982, 887) ORDER BY t.name ASC
```
Granted, I'm no expert in this area so maybe I'm missing something but here is some additional info to help troubleshoot:
- I optimize my database tables once a week.
- I run WP Super Cache on all of the multisite installs.
- I check the mysql_slow_queries log after installing a plugin I've never used before so as to monitor its impact.
It seems that taking over a second to run what I believe are standard WP queries is a really long time and that seems to be the heart of the issue. But I could be wrong, which is why I'm here looking for help.

If this is an issue with the standard queries then I have no idea how to fix that or if it deserves a trac ticket. Could the theme be causing the problem, etc.?

Thanks in advance for the insight and help.

Drew

P.S. One additional piece of potentially useful info: all of the sites running in the multisite install had their own, individual WP install on the same hosting account; meaning, there was no introduction of new accounts or loading the hosting account up with a bunch of additional domain names etc.
Andrea_r
team pirate
Posted 11 months ago #

Can you get technical specs on your account level? Because it's more than possible they're still throttling you.

Depending on your traffic & stuff, you've quite possibly outgrown them anyway. I don't think their pro accounts are anywhere near equivalent to a vps.
adaptistration
Member
Posted 11 months ago #

correct, the Pro accounts aren't the same as a VPS. They do offer a dedicated IP and SSL but I don't think they really ahve much impact. Other than that, they do keep fewer numbers of accounts per server so you get more resources.

But the real puzzle is this is the very same hosting account I was using to for all of the same accounts when they were single installs. The throttling problems only appeared after converting over to multisite. Likewise, traffic hasn't changed nor have I added any additional sites to the hosting account. The only variable to change is switching over to multisite and using a new theme (Touchsense, from Themeforst: http://themeforest.net/item/touchsense-multipurpose-wordpress-theme/273403).

Again, what's very odd here is any throttling in the past that had a noticeable impact on performance was due to a new plugin being installed etc. It's never been a result of standard WP queries. But maybe I'm missing variable here that I can adjust (which I'm happy to do and am hopeful is the case).

Drew
Andrea_r
team pirate
Posted 11 months ago #

How many sites in the one install?

If you think it might be the theme... change themes and see if it stops.

Are you using sitewide tags? How many terms and taxonomies do you have? Also that query is for a single blog so it's not technically multisite related.

Other than that, they do keep fewer numbers of accounts per server so you get more resources.

I poked around and they still aren't really clear on numbers.

Bluehost.com employs complex mechanisms to protect its Subscribers and systems from abuse. Bluehost.com's offering of "unlimited" services is not intended to allow the actions of a single or few Subscribers to unfairly or adversely impact the experience of other Subscribers.
Bluehost.com's service is a shared hosting service, which means that multiple Subscriber web sites are hosted from the same server and share server resources. Bluehost.com's service is designed to meet the typical needs of small business and home business website Subscribers in the United States. It is NOT intended to support the sustained demand of large enterprises, internationally based businesses, or non-typical applications better suited to a dedicated server.

from tos
adaptistration
Member
Posted 11 months ago #

I'm not sure what sitewide tags are but as for standard post tags, I have about 160 and just over 2000 posts. The multisite causing problems only has two sites installed (adaptistration.com and adaptistration.com/jobs).

Likewise, I'm also not entirely clear on where to find the numbers of terms and taxonomies but I'll be happy to dig it if I know where to look.

Drew
Ipstenu
Half-Elf Support Rogue & Mod
Posted 11 months ago #

Sitewide Tags is a plugin.

http://wordpress.org/extend/plugins/wordpress-mu-sitewide-tags/
adaptistration
Member
Posted 11 months ago #

thanks Ipstenu, and no, I don't use that plugin.
Andrea_r
team pirate
Posted 11 months ago #

Look in the db at the taxonomies tables and see how many records there are. Also check the taxonomies tables with _2_ in them.

With 2 sites there's no way it would be multisite in particular. Lots of categories and tags even in a single site, will start to ramp things up though.
adaptistration
Member
Posted 11 months ago #

Assuming I'm looking at the right table (DB for the blog in question, then I opened up the wp_term_taxonomy table), it has 238 records.
Andrea_r
team pirate
Posted 11 months ago #

and wp_2_term_taxonomy? and both term_relationship tables?
adaptistration
Member
Posted 11 months ago #

I have a _wp5_term_taxonomy, it has 60 records (no _wp2_).

wp_5_term_relationships = 144 records
wp_term_relationships = 9960 records
Andrea_r
team pirate
Posted 11 months ago #

wp_term_relationships = 9960 records

Bingo.
adaptistration
Member
Posted 11 months ago #

okay, that's a good start for me, can you point me toward a resource to help with this one?
Andrea_r
team pirate
Posted 11 months ago #

Don't know of one or I would have. I know I should be able to conjure up Otto so he can explain the finer points.
adaptistration
Member
Posted 11 months ago #

@Andrea: you're an angel THANK YOU :)
Otto
Tech Ninja
Posted 11 months ago #

Okay, from the fact that the query is against the wp_ tables (and not the wp_some-number tables), you can conclude that the site in question is the primary site of the multisite install.

The term_relationships table relates terms (like tags, formats, categories) to objects (like posts). So having 9960 is not unusual, really. Having just 5 tags per post on 2000 posts would do it.

So the problem isn't the size of the table, it's the query. Unfortunately, there's nothing wrong with that query. Indexes are right, keys are right. When I ran a version of it myself on a large site, it returned in 0.0007 sec. Running an EXPLAIN on the query shows that it's using the SIMPLE select_type across all the joins. It shouldn't take that long.

So bottom line is probably that your database server is simply overloaded. You may be on a VPS, but are you sharing the database server? Is the DB running locally and not getting enough runtime because of other processes hogging CPU?
Andrea_r
team pirate
Posted 11 months ago #

He's on a bluehost professional account - which I think is still shared.
adaptistration
Member
Posted 11 months ago #

Correct, the Pro accounts are still shared but they cap the number of users per server at a lower rate than the standard accounts.

So if I'm interpreting Otto correctly, it seems as thought the high record number is due simply to the fact that there are a bunch of entries. Is that correct?

If so, beyond deleting entries (which isn't really an option) is there an option to explore?
Otto
Tech Ninja
Posted 11 months ago #

No, my point was that the number of entries there is not really high at all. 10k items in a table is not a big deal, I've dealt with tables running into the millions of rows.

Your problem isn't the query, and it isn't the number of entries in the table.

Essentially, your problem is outside of WordPress entirely. That's what I'm trying to tell you. You need to focus on why the database server is responding slowly, and figure that out. If the database is overloaded, or misconfigured, or something else, then that is the problem.

There's nothing wrong with the query and there's nothing wrong with WordPress.
adaptistration
Member
Posted 11 months ago #

Then I am rapidly finding myself between a rock and a hard place as the host keeps telling me there's nothing more that can be done.

What I still find very odd is that all of the sudden, the slow downs started and even after removing all of the variables that could have caused it (short of converting multi blog installs back to single installs), the slow down persists.

Very frustrating but I can't begin to express how much I appreciate the insight so far.
adaptistration
Member
Posted 11 months ago #

As a followup to anyone keeping tabs here, bluehost is now telling me that it wasn't a MySQL query issue all along and now it's CPU usage.

It would have been good to know that two days ago but now I have one problem replaced with another.

Anyone have some pointers on reducing CPU usage?
bh_WP_fan
Member
Posted 11 months ago #

Try the tips given in the following post: http://wordpress.org/support/topic/bluehost-throttling?replies=12

Depending on how large and/or popular your sites are the help here may vary, but it should help at least somewhat, if not a whole lot.
adaptistration
Member
Posted 11 months ago #

Many thanks for the pointer link and I've been deep into W3 Total Cache settings although I've discovered it isn't quite bug free in a multisite install (meaning, the .htaccess write rules for one sub site seem to displace the same rules for a sub site in the same parent install - particularly browser caching).
Ipstenu
Half-Elf Support Rogue & Mod
Posted 11 months ago #

If all your subsites have the same settings, then w3tc should be fine.
adaptistration
Member
Posted 11 months ago #

Many thanks, so then selecting setting options in the network activated admin panel of w3tc as opposed to each individual subsite install is the way to go (although enable the plugin for the subsites), correct?
Ipstenu
Half-Elf Support Rogue & Mod
Posted 11 months ago #

I set it up per site (NOT network activated), and then exported my settings from the main site and imported them to all the children. Works fine for me.
adaptistration
Member
Posted 11 months ago #

Didn't even think about that method but I'll get right to it. Many thanks for the shortcut!

One other item I've discovered that's made a large impact on throttling caused by CPU use is to switch all of the RSS feeds to Feedburner as opposed to using the standard sitename.com/feed address.

Reply

You must log in to post.

WordPress.org

Forums

MySQL slow down on standard queries (27 posts)

Reply

About this Topic

Tags

Code is Poetry