Server is blocking all my files due to a mySQL query that's doing too much work

[hedgehog90]

.... APPARENTLY.

 

I run a  website called GPStudios.com a games website.

 

I received this email yesterday:

 

This message is to advise you of a temporary block placed on your database. The database was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system's CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case.

 

Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.

 

blah blah blah....

 

| 394188 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto  | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

| 394412 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto  | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

| 394466 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto  | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

 

etc....

 

You can view the entire email from the attachment.

 

So after receiving this email, I quickly got to work on trying to fix it.

I talked to several people from the hosting company, and i worked out that the problem stems from a processor heavy mysql query...

 

After a few more emails back and forth they unlobked all the files and the site was live again. I was pretty sure it was just some freak occurrence, because I have not changed ANYTHING on the website except uploading a couple games everyday.

No new mySQL queries, nothing. And the traffic to the website is the lowest its been in a while.

3 months ago we were seeing over twice the amount of visitors, and as a result, over twice the amount of bandwidth.

All the same queries were working back then without overloading the system... What the hell has happened now though to cause this?

 

Anyway, the site was working again.

This morning, it was still working, then around lunch time, I got the same email from yesterday.

I checked the site: it was down again, due to the same reasons...

 

I'm DESPERATELY in need of some help. The hosting company (Hostgator) are being very unspecific and unhelpful at the moment.

 

The email tells me its all to do with this single function to generate a random game that is used on every page load. You can also click a button to generate a random game once the page is loaded.

 

Select * from games ORDER BY RAND() LIMIT 1

 

Yet I have a query that displays at the bottom of every page with the query :

 

Select * from games ORDER BY RAND() LIMIT 0,4

 

No problems with that querym not one. Just the Single random game query. WTF?

 

I cannot stress enough how NOTHING has been changed except for the database growing a realtively tiny bit (5 new games per week - the database contains a description, title, all that sort of stuff.)

In the last few motnhs, that table has changed in the size of KBs, not very much at all. I;m not convinced that it's due to our games table size.

 

And as I said earlier, the traffic is the lowest its been in months, by a long way.

 

How do I fix this?

Please help.

 

[attachment deleted by admin]

[Alex]

If the problem is indeed with that query here's a post explaining a better way to do that: http://www.phpfreaks.com/forums/index.php/topic,125105.msg522853.html#msg522853