MySQL Load on Server and unhelpful internet host

HairyArse

Last week and then again today without warning, my web host suspended my website due to it apparently causing a high load on their MySQL server.

They being Dataflame and my website being AllAboutTheGames.co.uk.

I've asked them to help me get to the bottom of this and all they can do is tell me to check my logs.

So, bearing in mind my entire site is hand-coded in PHP and with a MySQL back-end database, and also bearing in mind that it has been running fine for about 2 years with no such issues, what can I do given the limited information they've given me?

bradgrafelman

HairyArse wrote:
what can I do given the limited information they've given me?

Find a better host? Honestly, with the large number of website providers out there, it seems silly to waste your money on one that doesn't care about its customers.

Even if you do change hosts, though, there may still be merit to what they're saying. As such, you'd want to have access to things such as MySQL's slow query log. Other than that, it's hard to offer simple suggestions. Here are some things you might look out for in your code, however:

Any SQL query executed inside of a loop (especially inside of a loop from another SQL query)
Any 'SELECT ' query
Any 'ORDER BY RAND()' query
Any query with columns in the 'WHERE' clause that are not indexed
Any query with columns in the 'ORDER BY' clause that are not indexed

If any of the above appear in a query, there's a good chance it can be optimized further. Also note that the DB structure itself should be examined to see if optimizations can be made.

HairyArse

They kept on asking me "Do you have any third party software running on your server?" To which I always replied no.

They then insisted on sending me a sample of the log file again, which was just reams of queries and times and dates and no use whatsoever. One thing I could construe, however, was the user account generating these queries. So what I've done is delete that user, then setup another user and given it limited permissions and used that account in the included inc_db_connect.php script I use. All pages on my site use this page to connect to the DB server so they should all now use this restricted user - but not restricted to the point of stopping my site from working.

One interesting thing I discovered since posting my original post is PHPMYADMIN has a DB status page which shows the number of queries run between certain dates. Apparently 32k Created_tmp_disk_tables queries have been run which seems awry to me. None of my queries should be creating temporary tables so I wonder if something untoward has been doing this and causing the problem.

I'm starting to double check my indexes. What rules should I follow when determining indexes? Just index fields used in queries often?

HairyArse

Oh and thanks for your advice brad. I realise that as I have limited information, you have even less so I really appreciate your patience and expertise.