Site causing excessive load on the server - apparently.

HairyArse

Hey guys.

So I have my website AllAboutTheGames.co.uk hosted with Dataflame. A few times in the past few months they have suspended my account saying that it's causing an excessive load on their database server. They then tell me to resolve it in order to have my account activated again.

Thing is, they give me very little to go on other than to paste some logs from their database server.

I've attached the text from the log they've sent me.

I'm confused though becase thee log just shows what appear to me to be valid queries. Am I missing something?

My site has so far this month had about 11k unique visitors accessing 75k pages. Could it just be that my site is using too much bandwidth (11GB so far this month) for the package I'm on? I'm currently on the best package I can get without paying for a dedicated server.

Any help and advice would be gratefully received.

jgetner

yea 11gb is huge you could look to implement some things such as caching , compression, query optimizing etc.. to speed up your site.

there is endless possibilities to accomplish this it's just up to you what route you want to take. i would start with looking and optimizing your code first then maybe look at a diff hosting environmental or maybe look to a faster language than php such as java or pyton or c#.

HairyArse

I should add that my site is quite image-intensive so a lot of that bandwidth will have been taken up by that. It's not 11GB of traffic just to the DB server.

dagon

sounds like dedicated server time.

Res

They sent you the slow query log it looks like, which is the first place to start to optimize the data. I don't know your table structure so the queries don't mean much to me, especially truncated as they are. I do notice all of the queries are dealing with temporary tables and selects from multiple tables with no joins to help them. Optimize your queries to not have to rely on temporary tables, they eat ram and make shared hosts unhappy.

A dedicated server will solve your issue of a host yelling at you, but it will not make your queries any more efficient.

Another question, are you storing the images in the database as blobs or as references to the file system? I can see how retrieving mass images out of the database would cause bandwidth issues on a shared host.

HairyArse

That's a fair point and one with which I don't disagree. However, I'm not sure where the queries to create temporary tables are coming from. That's not a technique I ever use or employ in my database and site design so that might be something worth investigating. I just assumed the temporary queries were automatically generated by the server as a way of streamlining the processes or something.

Any clues?

Res

It wasn't clear to me either, if you are not creating them then it is an issue with your queries. The query parser in mysql will create temporary tables as it needs to, which sometimes is unavoidable but sometimes is a result of unoptimized queries. If you can get an idea of where those queries are coming from, you can add EXPLAIN to the front of them to see what the query is doing. You can then use this information to index some fields are optimize the query.

I'm still curious as to how you are storing the images. If you are serving a lot of them and they are stored as blobs, I could see a hosting company not liking that.

HairyArse

My images are uploaded to my website from within the CMS I built and anywhere I refer to an image in a field in the database, it's simply a text field pointing to the image name, for example "my_image.jpg" etc.

My PHP code then auto-builds an image link using the named reference. I don't store any images in the actual database itself - I know that's a big no-no.

As far as I can tell my tables are all normalised - I work with Access database for my day job - and I don't believe it's a problem with optimisation. I only have about 10 tables and believe my DB to be very lean and streamlined.

But then I would say that as it's my work.

😃

Res

It's still possible to have poor performing queries on efficiently normalized tables though. Things like sorting on un-indexed columns, grouping, selecting distinct values, etc, can really bring things to a halt.

Good on the images, that certainly eliminates one possible bottle neck. Without being able to run those queries with explain though, it's hard to say where to go from here. If you'd like to avoid moving to a dedicated or VPS I'd strongly recommend running the explains.

HairyArse

I've never used explains before so will definitely have to look into them and see if I can put them to good use. Appreciate the advice res - thanks mate. 🙂

DaveRandom

Is it possible the server creates a tmp table simply as part of executing a query which SELECTs from multiple tables? It's not something I have ever heard of/can find any reference to but this may be the case - it would (kind of) make sense.

I would say it's dedicated server time though - I mean 11k unique visitors sounds like more traffic than any hosting company would like on a shared box, especially with that kind of bandwidth usage. Plus, if your serving a lot of images, and pulling data about them from a database, that's a hell of a lot of HTTP requests, each one causing database traffic.

It may simply be volume that is causing this problem. Try setting up a copy of the DB on another box that you control and using that for a while - may give you a pointer on query optimisation, or it may show that the hosting company's DB server is badly configured (wouldn't be the first time) if you don't experience the same problems.

HairyArse

Yeah I think you might be right Dave. I mean 11k doesn't sound that much but I am definitiely on a shared box. My interpretation of the limited information I've been given is that at certain points in time, the shared box I'm on flags up that the database server is about to crash. My host then looks to see which account on the box is using the most bandwidth, and while mine isn't that big, it's probably loads more than all the other accounts sharing the box with me and my account is then flagged and suspended.

HairyArse

Oh and I should add that I'm upto 24k unique users this month now. Though I was only at 11k when my account was suspended. Seems strange that they suspend me after the first 11k but then the following 13k is no problem...

Res

It does indeed create temporary tables in memory as well as on disk, depending on the volume of data and complexity of the joins/groups/sorts. In my experience it's most common with sorting or grouping large amounts of data, or sorting and grouping joined data.

I guess there is an extended explain as well, which is news to me.
http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/

Res

If it's the site in your signature then I would just move to a new host. It loads fast enough that I wouldn't think you have major optimization issues other then only running on a sliver of the resources. You'd also have a lot more control on a dedicated server to track down real performance issues, or even install monitoring software to tell you when there is a problem.

HairyArse

Res - it is indeed the site in my signature. Problem with dedicated hosts is that they cost a fortune and I run the website as a hobbyist project so it has no income. I really don't want to be spending £200+ a month on fees.

Res

Take a look at a VPS in that case. It's comparable to a shared host and acts just like a dedicated server. The major difference being you're only on a slice of resources. The benefit over shared though is you get to pick in your package how much ram/cpu you need and it is yours. With a shared you are competing with everyone else for those resources.

I'm in the states, so I'm not sure it's the best option for you, but I've really enjoyed linode.com

DaveRandom

https://www.google.com/accounts/ServiceLogin?service=adsense&rm=hide&fpui=3&nui=15&alwf=true&ltmpl=adsense&passive=true&continue=https%3A%2F%2Fwww.google.com%2Fadsense%2Fgaiaauth2&followup=https%3A%2F%2Fwww.google.com%2Fadsense%2Fgaiaauth2&hl=en_US&gsessionid=spd98NL0L7-lB8hXIQ0Dxw

Sorry, that's the only way, unless you want to start charging people to use you site...

MaxMiner

I'd start with optimizing your images. According to http://siteloadtest.com some of them are 5 times bigger than they should be.