Help me... mysql is SLOOOOW

[ZappoMan]

Ok, first off, I know many of you here also frequent the wpmudev forums, and so you have already heard the back story...

I have recently successfully moved to multi-db, which I was hoping would cure my mysql spiking the CPU problems I have been seeing on my setup. But alas 40 hours into running multi-db, I fear that performance has not improved.

My Server is:

Amazon Web Service "Large Instance"

• 7.5 GB memory
• 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each)
• 850 GB instance storage (2 x 420 GB plus 10 GB root partition)
• 64-bit platform
• I/O Performance: High
• Fedora Core 6

Traffic Stats:

• Daily Page Views: about 10,000
• Daily HTTP Requests: 190,000

The behavior I am seeing is that mysqld will be running along at 40%-140% cpu. In top I tend to see about  4 to 10 httpd processes running about 5-10% cpu each.

I am thinking something must be wrong.

Where do I begin?

Here's what I've thought of... and already looked at... although I will admit, I haven't dug SUPER DEEP on any of these issues yet.

1) Slow queries... Maybe there's just some gnarly ass query that keeps running every second causing the system to spike. Stats: In the last 24+ hours, mysql has reported 38 queries taking more than 1 second to complete. That number seems sufficiently low for me to not go down this road much further. Am I wrong?

2) Too many queries... Maybe I just have some lame ass widgets and code that is running way to much and I am just expecting too much from mysql... Stats:89,303 seconds of up time; 15,978,622 'queries' reported by MySql, that means we're averaging 178.9 queries per second. Now, to me, it seems like this machine ought to be able to handle that kind of load. Sure, you can always make a page lighter weight... and make it do less work... but I think this server ought to be able to handle this.

3) wp-cache-- Ok, I admit, I am not running any kind of a high level cache like xcache, or wp-cache or super-cache. I've been reluctant to do this only because I want the site to be as dynamic as possible... we have the start of a pretty healthy community right now, with 6000+ blogs and about thousand users who are actively posting and commenting every day... and I was hoping that fresh content would remain fresh... I am sure you're all going to say that this is my problem and I need to get to it and install a freaking cache... Which I agree we should...

BUUUUUUUUUUUT....... I am really annoyed by the idea that this big ass server appears to be having trouble with less than 200 queries per second! I mean....jeeeez.... that ain't right. It should be killing that? Right!?!?

Feel free to smack me around if I deserve it.

[Luke]

You are at least using the object cache, right?

Either by plugin (1.5.1+) or using the defined constant ENABLE_CACHE (1.3.x and prior).

Other than that, I would perhaps take a look at the hardware. While I'm not completely familiar with Amazon's services, with that many blogs you should be on a dedicated server. You currently appear to be on some type of virtual server, which with that many blogs would probably kill it as MU is more DB intensive than anything.

While I'm sure that the amazon stuff has its place, it may just be unable to handle the db load.

Before getting down that road, one last check you can do is your my.cnf file, and optimizing it. Check "the other forums" for a thread about large hosts and optimizing mysql. It should get you close, at least close enough to make a noticeable difference.

I guess another possibility, although a long shot, is a potentially corrupt or buggy install/version of mysql.

[drmike]

Either by plugin (1.5.1+) or using the defined constant ENABLE_CACHE (1.3.x and prior).

I think the reason why the cache was brought up was because please remember that it was broken for a few versions in there.  This may be the case here.

Drop in a few of the following in your themes' footer.php files and see what you're getting as results:

Code:

<!-- <?php echo get_num_queries(); ?> queries. <?php timer_stop(1); ?> seconds. -->

I actually had to go look that up.

Best would be as low as possible.  Say 20 or less for queries.  Forty or more on average is way too much.

As to the "I want the site to be as dynamic as possible" you may have to bite the bullet and have to accept that it may not be that way.

200 queries per second

That's a bit high in my opinion.  I wonder what the server loads are.

[ZappoMan]

These are good suggestions and helpful for me to frame the problem. Thanks!

Couple comments/questions.

1) Dr. Mike, do you really think it's high to expect a DB to be able to handle 200 queries/second? That seems very very very low to me. I am mostly asking from the perspective of raw throughput of the DB. 200 q/sec = 17m q/day... seems light for a quad proc box. Maybe my expectations are out of whack.

2) Thanks for the tip on 20-40q's per page... that's a good metric to go for. I am logging this stuff now in a separate file and we are much higher than that right now for most pages. So... this sounds like a good area to look at.

3) Object Cache -  Apparently I didn't have the object cache enabled, so I enabled it last night, and didn't see an immediate performance improvement. So, maybe the object cache is broken on my install. If your experience, how much improvement do you get in total q's per page by using the object caching? Vs... poorly written plugins? Is there a benchmark/prototypical page (say kubrik, no widgets, etc) that without the object cache it takes 100 qs, and with the object cache it takes 10qs. If so this would help me determine if my object cache is broken. Any other tips on debugging the object cache?

4) Slow queries - It turns out that I was wrong in my number of slow queries (note: SHOW STATUS LIKE "Slow_queries";  is not the same as SHOW GLOBAL STATUS LIKE "Slow_queries"; ) Soooo.... I now have slow queries being logged, and I've got some fertile ground to research there.... my instinct says this is a good place to start/focus my efforts.... but I'm curious if you'd all agree.

My logic goes something like this:

1) The DB should be able to handle more load no matter what the application is doing - Am I off base here?

2) If I'm right that the DB should be able to handle more load, then this could be an issue of my server/db config (note: I'm using a config similar to the recommend "large" config, and similar to one that Farms posted on the other forums a year ago).

3) If my config is ok, then maybe it's lame SQL, aka slow queries, and therefor the server is working hard and doing a good job, its just the app is asking it to do complicated things... slow queries.

My sense is, and here's where you all might say I'm way off base, is that even if the object cache is broken, that it should be asking the DB to do easy things, just a lot of easy things, which to my mind, the DB ought to be able to handle.

Anyway, thanks for the support, advice, and good natured ass whoopin I'm sure you'll give me if I deserve it.

[Luke]

FYI Zap, I'm pulling about 60Q/sec.

The home theme out of the box is going to pull about 30 queries, maybe 40. I haven't looked at it much.

WP runs a lot of queries, which is why they started the object cache in the first place.

Some page views will require more, some less.

Typically, for an active MU install with a lot going on, I'd say 100 queries or less is a good target to begin with.

Then you can whittle it down a bit from there.

There will be many things that take queries, and there are some really, really poorly written plugins out there.

Without object caching on, I worked on a site who's home page was pulling almost 2000 queries every time.

Why? Plugins. Really, really bad ones.

For some of them, there just wasn't any hope. They were some chap's really poor attempt at making a global function. Sure the hacked up code worked, in theory, but they ran hundreds upon hundreds of queries every time.

Another fine example was a plugin that made your category list collapsible. It would show the top cats on the main page, and clicking through would expand the list. There went about 300+ queries. How, I have no idea but it sure was running them.

For global lists and content, I do one of two things. First, make sure the query has the capability to be cached. Second, I cache the resulting output in a static html file.

On a rare occasion I do both, but it depends on the resulting output, etc.

Depending on the content, I may cache the output for anywhere from 15 minutes to a few hours. Mileage varies depending on its use, but sometimes even for 24 hours.

Another key is effective SQL writing when you make your custom stuff, and making sure you index properly. If the query is run often, make sure anything in a WHERE clause is indexed.

Sometimes you just have to think outside the box.

For example, a good friend asked me for some query advice a while back. They were looking to do some things and estimated it would probably take about 50 queries to the database every time a function was run. This function would be run on average about 10,000 times a day, resulting in somewhere around an additional 500k queries a day.

Luckily I had something similar on one of my installs, and was able to cut it down to running 1 query, and on a rare occasion 2, with the same result as the original 50.

Not that they didn't know what they were doing, quite to the contrary. They're very well versed in these things. However, I took an approach from a different point of view that hadn't yet been considered.

I will be the first to step up and say that I've got things in my closet that need cleaning. I do, and I know it.

Anyway, through all of this, the point of the story is that there is always room for improvement.

You can take it in steps, and try to get the big query hogs out of the way first, and then refine it further.

Honestly, unless you're caching your entire front page (which is possible), 20 queries is a little low of an overall target if you have a lot of things going on.

WordPress alone, without any real nifty stuff will typically run more than that.

However, what you can do is cut down as many queries as you can on heavily visited pages. Especially when the data isn't too time critical.

For example, the main site home page. That's a big one, typically with a lot of global stuff going on. That being the case, a lot of that output can (usually) be cached in a file. Even if it's only for 15 minutes at a time.

Let's say your homepage runs 200 queries, and that 30 of those result from user checks and things that must be current. The remaining 170 queries come from global results and other data which aren't necessarily time sensitive.

You optimize your home page to cache the resulting html to a static file, and expire it after 15 minutes.

Now, let's assume your home page is getting 1000 page views an hour, to keep the math simple.

Before optimizing it, you would query the database some 200k times per hour.

After optimizing it, you would query the database about 30,680 times per hour, with a net savings of 169,320 queries per hour.

Those are saved because now you're only running 30 queries per page load, and running the remaining 170 queries 4 times per hour.

Daily it would work out to:
   Before: 4.8M queries/day
   After: 736k queries/day
   Saving: 4M queries/day

And you're only on a 15 minute delay.

Granted, I'm just throwing examples out here, but you get the idea.

Another thing though, and it does concern me, is that you're running this off of a VPS, not a dedicated server. It may appear to have stats, but it's virtual. That may very well be the underlying problem. You may whittle it down, but I would venture to say you're just buying a minimal amount of time. However, it may be enough to at least get you by until you can work out an exit strategy to a truly dedicated box.

Other than that, it might also be time to ask the host what they think. They know their stuffs capabilities better than we can guess at. If you've optimized your conf file, you should at least be in the ball park. Since that had little to no effect, neither did breaking up the database, then that leaves only a couple things.

a) hardware just can't handle it

b) custom code/queries need drastic improving (including 3rd party plugins)

c) software issues


Honestly, with the amount of traffic you have, the number of active bloggers, and the horizon looming, I'd be running a dedicated web and database server. It would technically be overkill this very minute, as a single dedicated box should suffice, but with what we've discussed (somewhere) about your upcoming traffic spike you should be prepared for it. Once it's hit, you'll need the second box to keep from having a big slow down in performance (driving new users away).

[ZappoMan]

Thanks... this is all great feedback.

You're doing 60q/sec and the server is not sweating.
I'm doing about 180q/sec and I feel like the server is sweating.

This could mean my expectations are unrealistic, and it could mean that the server is actually doing pretty well, and it could also mean that some of (too many of) my 180qs are ugly and slow... which accounts for the extra hard work.

I'm sorting through my queries right now... the good news is, that after looking at my slow queries log, there are a couple of very obvious culprits... and ironically, they appear to be in the section of code that I was already suspicious of... and had a plan on rebuilding to be more "cache" friendly.

Your suggestions are all good ones, and in fact in line with my normal thinking... but not necessarily the way the code was written... DAMNIT! Sounds like it's time for fundamentals.

[ZappoMan]

Hey, I just noticed I've been promoted from Newbie to Jr. Member... I feel so special.

I'm going to go digging through that box of cereal looking for my decoder ring now!

[Luke]

Honestly, unless you have just a ton of super duper plugins and code, the amount of queries/sec seems high.

For the record, I've got caching in place in several forms. Xcache, super cache, and object caching. As well as caching outputs of various functions, etc.

Although if I were running 3 times that many queries, I can't see the load being unusual. Far from what you're seeing.

[ron_r]

My thought was that not all queries are created equal. There are going to be some queries even when optimized require alot more cpu time & resources to execute than others.

Secondly, there is a substantial amount of overhead that occurs with each and every database query (check locks, etc.). This overhead cost is not a straight line. As the number of near simultaneous queries increases the overhead per query increases (more locks to check, etc).

You can't rely on a simple formula like 10 Q/sec = 1% CPU therefore 100 Q/sec = 10% CPU.

[ZappoMan]

My thought was that not all queries are created equal. There are going to be some queries even when optimized require alot more cpu time & resources to execute than others.

Yep, a query that includes sub queries, joins, and non-indexes where's will look like 1 query in the "question" count... but it will certainly be slower than a simple query on indexed fields.

You can't rely on a simple formula like 10 Q/sec = 1% CPU therefore 100 Q/sec = 10% CPU.

I knew/know that this was a simplification, but I was more interested in looking for the general scale of what we would expect from a mysql instance running on the basic class of server I was running.

Anyway, I'm now pretty much convinced the issue is in large part caused by the handful of "gnarly queries" that are running on my system. They appear to be from on plugin in particular, that was written by me... so it's my own damn fault. This plugin was on my list of things to implement better... and now it looks like it's time to get crackin... I guess I was hoping that faster hardware and a proper "shareded" implementation of my user blogs (via multi-db) would be a silver bullet... but I should have known better.

[ZappoMan]

Honestly, unless you have just a ton of super duper plugins and code, the amount of queries/sec seems high.

Yes, I agree, I need to look into this... it seems as if most pages are taking more than 100 queries to render... so I think something might be broken with the object cache... that's also on my list to look at.

Any tips on determining what queries are being called? Does wpdb have a query stack you can dump? I would imagine it does... but I haven't looked yet.