Bloggers/CMSers, post your MySQL usage

I'm trying to get a good feeling for what "normal" CPU/database usage is.

Here's mine. Don't kill me; DreamHost hasn't said anything to me about heavy usage, so I guess? that means I'm not affecting other users too much:

Date: 2005-01-13
Disk Usage: 6.844 MB
Connects: 10318
Queries: 820542
Conueries: 1.078 MCn
Ratio: 3.181

2005-01-14
Disk Usage: 6.871 MB
Connects: 9407
Queries: 818505
Conueries: 1.054 MCn
Ratio: 3.480

2005-01-15
Disk Usage: 6.922 MB
Connects: 6329
Queries: 604973
Conueries: 0.763 MCn
Ratio: 3.823

2005-01-16
Disk Usage: 6.934 MB
Connects: 8228
Queries: 815521
Conueries: 1.021 MCn
Ratio: 3.965

2005-01-17
Disk Usage: 6.750 MB
Connects: 6245
Queries: 446571
Conueries: 0.603 MCn
Ratio: 2.860

Totals
Disk Usage: 6.864 MB
Connects: 40527
Queries: 3506112
Conueries: 4.519 MCn
Ratio: 3.461

Here are the stats for the DB my forums are on.

On Server: nobnin
Disk Usage: 8.812 MB
Conueries: 0.848 MCn
Cycle Estimate: 2.183 MCn
Connects: 22561
Queries: 284045
Ratio: 0.504

Not really too bad but let's face it database driven sites are the future so Dreamhost is going to have to adapt to heavier usage.

willscorner.net

How quickly does it take for your forums to respond to initial requests?

Part of the reason I've posted this thread is to find out what sort of experience other DreamHost database users have had. My experience here has been terrible; my site takes over 7 seconds to load, often over 10 seconds, and tonight, over a minute. I'm fed up, needless to say; unanswered emails and orphaned support tickets certainly don't help.

Mine has always loaded fine. There were some issues I think two weeks ago when the database was running slowly then crashed. Dreamhost ordered new hardware and split the load. I'm assuming they moved some users off the database on onto the new one.

Try and load some pages, it seems to load pretty fast. http://www.happylittlethings.com/forum/index.php

willscorner.net

I'm using ExpressionEngine and here's mine average so far:

Disk Usage: 4.588
Connects: 13471
Queries: 522918
Conueries: 0.860
Ratio: 1.553

I have to say also that DH's db servers don't seem to be the best. During the day, my site runs just fine. However most evenings it slows to a crawl. I sent a support ticket in a few days ago about it but haven't heard back yet.

I'm guessing there has to be some very heavy db user who gets alot of traffic around the time my site slows down since during the day my site is very fast.

bryan | website

Thanks, schweb. What server are you on? I got a response from support regarding this yesterday, so I'll post part of the message here:

"Additionally, it looks like your using is consuming large amounts of CPU on your machine. You're actually right on the cusp of when we isolate
users for excessive CPU usage. You are doing between 50 and 90
CPU-minutes per day. A machine like the one you're on (wasabi) can generally handle only around 500 CPU-minutes per day. We don't let users on a shared environment do more than 100 CPU-minutes per day (once they do, we require them to drop it down to under 30 CPU-minutes per day for several weeks straight before we move them back to a shared environment; if they cannot perform this task, we disable their site)."

So, the "very heavy db user" could very well be me. If that's the case, well, I apologize. I'm trying to figure out what is causing so many problems for me (and in turn everyone else on the server). DreamHost is apparently enabling access to some reports for me that will show more details. But there's a few things I'd like to point out:

1. Interestingly enough, Wasabi is not my database server. My database is on gwen. I'm not sure if this is just a mistake on the support guy's part, or if there's some sort of relationship between wasabi and gwen, but I don't think my webserver itself (wasabi) would be having so many problems.
2. Like you, my site slows to a crawl during the evenings. If you look at my site's stats: http://www.techjapan.com/Stats.html, you'll see that my traffic is pretty evenly distributed about the mean: 12:00PM. If my site's traffic peaked during the evening hours, it would be simple to say I'm responsible; but given my traffic, things get a bit more difficult. My site may turn out to be at fault after all, but at least from a statistics standpoint, it doesn't appear to be.
3. Playing around with the console a bit last night, there appear to be ~189 other users on wasabi. If what the support rep says is true (the 500 CPU minutes part), then each of these users would be allocated an average of 2.64 CPU minutes. This didn't seem like much to me, and smelled of vast overselling. I googled a bit for what might be "normal" CPU minute usage or perhaps what other hosts would offer, but came up empty handed. I don't think normal DreamHost users have access to how many CPU minutes they use; access to that information seems to be part of the restriction process mentioned by the support rep.

Hope it helps!

I believe my db is on Felix:whitman whatever that means. I'll let you know what I hear back from support.

bryan | website

I'm running six databases with a total conueries of about 1.052 MCn (3.378 MB). All of these databases minus one (which has 0.050 MCn of the conueries) are almost completely for my own use -- they're public, but traffic is virtually nil (on purpose).

First of all, if my one-person usage amounts to over 23% of what DH considers "on the cusp," I'd hate to see what would happen if I actually used the site as a "strictly business" site instead of a personal/development site and got some traffic -- like, say, five people?

The kbase says we shouldn't worry -- "but honestly unless you've got a crazy (and crazily inefficient) database-driven site that gets thousands and thousands of visitors a day, don't sweat it!" -- but that report from support is really alarming me, because I am planning in the near future to host a database-driven site (XOOPS, to be precise) for my husband that will conceivably get thousands of visitors a day.

But secondly -- weird issue -- one of my databases is on gwen as well, and even though it's one of the tiniest scripts on my server, it's doing 0.881 MCn conueries all by its lonesome (ratio above 3). So here's an idea -- is it possible gwen is misreporting?

Oh, yes, and my database-driven sites are also intolerably slow to load. I'm running a CRM on one of them, and it takes about twenty to twenty-five seconds in the middle of the day to come up... while when run locally, it's virtually instantaneous.

Thanks for your response.

Because of the access DreamHost has granted me, I've finally got access to /zmcnulty/resources/, which gives specific information about CPU minute usage. CPU minutes are apparently how DreamHost determines whether or not to cap your usage...it has nothing to do with conqueries, as far as I can tell. I'm still trying to figure out the relationship between conqueries and CPU minutes.

I'm in the process of making a lot of changes to the way my site loads. After monitoring how my site performs without any sort of caching enabled for two days, tomorrow I'll find out how my site performs with full-page caching enabled.

I'll report back to this thread when I get those results. You could be right -- it may just be gwen misreporting. But in my case, I don't think that's the problem; the support rep referred to my CPU minute numbers that have been on-target.

And my database-driven site really does get thousands and thousands of visitors a day. Whether or not it's crazily inefficient, I'll find out at 12PM EST tomorrow.Edited by zmcnulty on 01/20/05 10:10 PM (server time).

So how's your analysis going?

bryan | website

There's no relationship between the two. Conueries are just our silly metric for roughly assessing MySQL usage; the "CPU minutes" comes from process accounting on your web machine iteself, and reflects your resource consumption (in terms of CPU) on your web machine itself.

You have to use up a lot of system resources before we'll flag you.

We don't do this just because we like to charge people more money or screw them over, but because in a shared hosting environment, we need to ensure that one user isn't adversely affecting a machine's stability and ruining things for all of the other users. Generally, static sites don't cause big problems.

I know some people are probably going to get upset and / or worried about this, so please remember that this is /never/ going to affect 99.9% of you.

Basically, if we notice that a user is using up too many resources, we move their site to one of two special holding machines. These have much fewer users than the regular hosting machines, and hold up better under the load. At that point, we can make some tools available to them to gauge their usage, and we give them a time period (currently a couple of weeks) in which to make adjustments and reduce their resource consumption, switch to a dedicated machine with us, or move their site elsewhere. If it's a mistake (i.e., badly written or inefficient code), or if the user removes the software that's causing the problem or otherwise manages to reduce their usage -- no problem.

Side note...

I'd say by far the biggest problem I see are sites which are entirely or mostly dynamically generated. Much of the time this isn't necessary, or it would be possible to cache / rebuild the site dynamically on a periodic basis. Dynamic content doesn't scale as well. Obviously there are cases where it's necessary or desirable - however if it's a busy site, shared hosting is probably not the place for it.

A site we host got slashdotted over the weekend (linked to from the main page of Yahoo! news and Google News - and subsequently Slashdot)... with a wordpress installation as its main page. This caused such instability that the server was grinding to a halt as soon as the Apache instance started. Switching to a dynamic page fixed the problem almost immediately - the server load was completely normal even though the site was taking a pretty good pounding.

It's coming along. I'll be sure to post my final solution here when I figure out what's going on.

One thing is improper usage of robots.txt. Somehow, my site has been getting 6,000 "non-viewed pages" per day. Ouch.

looks like you're using nuke based site. Those are notoriously inefficient. For example just to generate that stats page there is a query kicking for every page request. Every request to your rss file is dynamic too. I would disable that, logfile stats show all that.

Additionally you should check out PEAR:Cache to cache your output.

On my site (http://www.kungfoo.com) I make liberal use of cacheing both with PEAR:Cache and cacheing queries with ADODB. For example all comments are cached, when someone posts a new comment it clears the existing cache and on the next request of that page it rebuilds the cache. I have a perl script in crontab that builds my rss feed every hour.

These all may seem trivial but every little query counts and over the course of a day can save alot of processing time.

Sincerly,
RickySilk | Kung Foo?

Are CPU usage stats hard to deliver, or is it just the case that it's not worthwhile to report them when 99.9% of users are well below the limit? I'd be curious to keep an eye on my own usage, so I can re-work things well in advance if I ever start exceeding 2.64 minutes per day. :) It also would have been interesting to see how much caching improved the performance of my site.

I wouldn't say they are hard to deliver. You'll get a directory called "resources" inside of your site's home directory, which contains a number of files such as "username.sa.analyzed.0," which would be the statistics from 0 days ago. You can open them with your program of choice, but I generally just use pico at the console to take a look at them.

A quick update to my situation -- I'm switching my site from PostNuke to either Mambo or Xaraya. Both of these use PEAR::Cache_Light, rather than Smarty, so hopefully caching will make a difference for me.Edited by zmcnulty on 02/05/05 09:56 PM (server time).