Thread

This has, to some degree, been an ongoing issue for us but now it has become more urgent. Our server admin has pointed out on several earlier occasions that our site causes a lot of load on the mySQL database. Now our traffic has gone up somewhat due to an upcoming TV series based on the books that our site is a fansite for and today mySQL has been spinning out of control quite a bit. To quote from a mail from our server admin:

“What I’ll have to figure out is some way to hypertune mysql.  That seems to be the bottleneck right now, due to so many of the pages being dynamically generated.  Nothing’s ever just loaded from disk, almost every hit involves asking the database for data.  So MySQL becomes the bottleneck, hitting its maximum and causing connections to have to wait.”

Now, for our part, we have followed the guidelines for optimizing the site. Every template is set to a 60 minute cache, though our server admin also says regarding the caching: “Unfortunately, that doesn’t seem to be working (I also see a lot of cache-related errors in the eAccelerator log, fwiw).”

I am not sure what else to do and our server admin knows nothing about EE. Is there anything I can tell her in regards to what kind of tuning would work well with EE? Or is there any setup information that I should get from her that you can take a look at, to see if something is obviously wrong?

We’re on the latest 1.7 version and build. A large chunk of the traffic right now is to various Gallery pages, though when I asked about this earlier it was said that the Gallery should behave no differently than a weblog in terms of performance.

Going over the user guide on performance, I see that a note on the page about data caching (http://ellislab.com/expressionengine/user-guide/general/caching.html) recommends not using SQL Query Caching for most environments.

How can I determine if having this on would be a benefit or a drawback?

Linda, do you know which pages are being hit the most?

Looking at Google Analytics .... the front page of the whole site, the main page for our TV series section and then the TV series Gallery. The Gallery is the main one that’s not been high up before, though we’ve always had issues at high traffic so something isn’t configured quite right for EE or some EE setting isn’t right for the system.

Hrm, does having a view counter on a Gallery page negate page caching?

If mysql and EE (and most other stuff) is set up on the proper server (RAM, conf files), then the majority of the content, dynamic or not, is served from RAM, not from disk. Take an example - let’s say your total EE db is 300 megs - that is very easy for mysql to load into RAM. The same goes for the php and various other parts.

I’m not an expert but it seems to me that images are mostly loaded from disk…..for instance, a large directory full of uploads or attachments. Otherwise, most stuff should be in RAM since other users have probably requested it.

Your host may want to run one of the mysql tuner programs…....even phpmyadmin has suggestions listed as to where mysql bottlenecks might be.

Lots of other ways you might be able to help with the load, but we’d have to know more about the specific server. If you are on a box with hundreds of other users, there is little hope (most hosting accounts are set up this way).....

Every little bit helps! You can even open an Amazon S3 account and move some of your big graphic files there (static graphics).....but you will have to fiddle with your templates or entries a bit.

Part of the issue here is that I know absolutely nothing about the server side of things and the server admin knows pretty much nothing about any EE-specific requirements. So in part I am trying to get a grip on what sort of settings or fine-tuning to recommend to the server admin or what sort of questions to ask her about the setup in order to determine what selections to make in EE. Like whether or not I should use SQL Query Caching, for example.

The server is privately run and shared with some other users, but our site is by far the most resource-intensive. We lag the others, they never lag us. Moving images off to Amazon S3 isn’t, unfortunately, within our budget.

S3 costs me about $10-$15 a month, but you have to know how to move them (I did it myself).

The server admin does not need to know anything about EE. It is the same as 90% of the stuff out there, that is Apache and PHP and Mysql.
He can easily tell which is bogging down, and it seems like he knows it is Mysql. The solution is, if possible, for him to allocate more resources on the server to the mysql program. This is done in the conf (.cnf) file for mysql.

Mysql comes with 3 or 4 various sample config files - called names like:
my.cnf
mybig.cnf
myhuge.cnf

as you can imagine, the various names relate to how much power Mysql is set up with…..in this case meaning RAM.

RAM is so inexpensive that it seems a pity to slow down site and users for lack of an extra GB.

If you use the discussion forums, they can tend to be a resource hog…..lots more mysql calls than most regular pages.

Could you share approx. how many page views you are getting per day.

BTW, the VAST majority of pages on the internet today are dynamically generated, so that is not a problem in itself. Lots of factors are in play - but if you are getting traffic, you should be able to turn that into enough revenue to easily pay server bills (and more)......

Oh, you said you took some steps…make sure you….
Turn off ALL logging - that is inside of EE and also httpd (apache logging).
Turn the crawl rate for googles crawler down…..to 1/4 or less of the rate it is at now. Google is probably sucking vast amounts of your pages down, and does not need to….

One of the big factors that you don’t hear mentioned much is the fact that MANY more of our users (internet surfers) now have very fast connections. Think about it. Back when, pages loaded so slowly due to various bottlenecks along the way. As the internet and all connections get faster, each user tends to suck down vastly more data!

Most servers are as powerful - or less powerful than the computers we each are using at home. Imagine hundreds of people trying to work on your computer at home at the same time…and you get the idea.

Good luck.

Thank you for taking the time to give some information. 😊 Lets see what else I can provide…

First some on the server:

PHP Version 5.1.6
eAccelerator (whatever that is) was enabled by the admin recently to try and deal with things
mySQL is set to allow persistent connections, but we have EE set to non-persistent since that is recommended in the files

I am afraid I don’t know what sort of memory the server has or how much is allocated to mySQL.

Across the last month we’ve had 480 000 page views, 390 000 of them unique views, according to Google Analytics.

We’re not using the EE forums (we’re using IPB, but that’s on a separate server).

All EE logging is turned off, not sure about apache logging.

I think eAccelerator is a php cache type thingy…..speeds up php, but maybe not the db.

The problem with most hosted accounts is that you honestly don’t know how many other people are on the server…not what kind of server it is…..although some of that info might be available to you.

As a really rough guide - there are a lot of variables - but if you had a dedicated server mid-level which was built within the last two years, it should be able to serve 150K to 200K EE pages like yours per day…

So, if you are serving 30,000 pages a day in EE, you are effectively in need of 1/5 or more of a fast server. Sure, some of the other accounts might not use a lot of bandwidth, but ISP’s are not in the business of giving away ten pounds of oats for the price of one pound.

It sounds to me like your site should be on either a lower-end dedicated server (start at about 100 a month), or a serious VPS ($60-90 per month?) on a tricked out XEON server where it is limited to 10 accounts on the server, etc….

A lot of speculation in the above, but at least it gives you a reference….with that kind of traffic, you should be able to bring in the big bucks one way or another (to help pay).

Of course, EE has their own related hosting company also, which might be something to consider if you don’t have access to someone fairly technical for the server end of things…..

Well, its not a commercial hosting situation, so I know I am not sharing it with a bunch of random sites, just a few other people that the server admin is handling hosting for, primarily personal pages or small MUD-style games. The server is not as new as the server running the forum, I know this much, but I would say we’ve got access to more than 1/5th from what I know about the sites on it. The server admin basically allows our site what it needs but now she’s finding that for some reason the traffic is coming up against a mySQL bottleneck.

Its unfortunately not the kind of site that’s easy to monetize effectively; its a fan site for a series of books which are now being turned into a TV series, hence the increased traffic. We use Adsense but what we’re able to donate each year to the server admin wouldn’t be anywhere near enough for commercial hosting.

Your estimates are something I could pass onto our server admin, however, to see if she feels that maybe the server isn’t holding up given those figures. Actually, if you have any advice on what sort of server setup you think is needed, it might help determine whether the current setup is reasonable. I am not sure what a recent mid-level server would be like in terms of RAM, CPU, etc.

Really rough, again…...but a lower-level server might have 2-4 GB of RAM and a dual core 3.0 GHZ CPU….a mid-level might have a 2.5 GHZ Xeon with 4GB of RAM.

It may be that simply fixing the mysql config file would solve most of your problems. Your admin could figure that out pretty easily…if the server is using all of it’s RAM and a lot of the CPU as-is, then stepping up the conf file might not help since there are not free resources to grab.

But, if your admin sees a server load of less than 2-3 (in general) and yet mysql is choking, then chances are that configs will help.

You’d be amazed how the public hits servers HARD these days. One of my user told me he opens 10 tabs and loads up various forum threads in each…..that’s a lot of queries for one person!

Also, server load does not rise in a linear fashion. I just moved from dual-core 3GHZ to a 4-core XEON 2.5 GHZ, and one might guess the new server works at about 1/2 the load of the old. No way. It works vastly faster than that….that is, the improvement is much larger than it would seem. Once mysql or some other process hits the wall, things start slowing down fast!

It’s another subject, but I went to a google seminar in the summer in Boston and the google folks talked about various ways to increase your adsense results. You seem to be hardly using it (only one ad, etc.) and not maximizing the potential….of course, that might not be part of your goal. But suffice it to say that folks with that kind of traffic can usually monetize to a reasonable degree.

Don’t forget to tell the google crawler in webmaster tools…to slurp slower!
Google was downloading as many as 90,000 pages of my site IN A DAY earlier this year…...I slowed it down to 15K pages or so when things were tight, and now turned it up. But that is a big load….

Think of it as the straw that broke the camels back…somewhere in there! Turning off the apache logging is a pretty big thing too…..

Thanks, some good things to consider. We are definitely seeing what you say, with people loading up a bunch of tabs in one go, especially as we load up new gallery images.

Coming back to what our admin initially said, she felt that there seemed to be very little caching going on, everything seemed to load straight from disk. That does make me wonder if there’s something in my EE configuration or my pages that is negating page caching, or if something on the server end is affecting it. I did see I had the number of urls to cache set to just 150 so I increased that to 500.

I suppose you have observed the obvious tuning tipps, but there’s a few more things you might want to consider. There’s also Solspace’s Cache module although that’s a non-free option.

I’ve looked at the tuning tips and also the page on handling extreme traffic, but as I said, I don’t always get what is best. For example, is it best not to track online users at all, or track it so you can use that as a limiter when there’s too many? Or, when is and isn’t it right to use SQL Query Caching?