Mysql – SQL queries break our game! (Back-end server is at capacity)

MySQLmysql-workbench

We have a Facebook game that stores all persistent data in a MySQL database that is running on a large Amazon RDS instance. One of our tables is 2GB in size. If I run any queries on that table that take more than a couple of seconds, any SQL actions performed by our game will fail with the error:

HTTP/1.1 503 Service Unavailable: Back-end server is at capacity

This obviously brings down our game!

I've monitored CPU usage on the RDS instance during these periods, and though it does spike, it doesn't go much over 50%. Previously we were on a smaller instance size and it did hit 100%, so I'd hoped just throwing more CPU capacity at the problem would solve it. I now think it's an issue with the number of open connections. However, I've only been working with SQL for 8 months or so, so I'm no expert on MySQL configuration.

Is there perhaps some configuration setting I can change to prevent these queries from overloading the server, or should I just not be running them whilst our game is up?

I'm using MySQL Workbench to run the queries.

Here's an example….

SELECT * 
FROM BlueBoxEngineDB.Transfer 
WHERE Amount = 1000 
AND FromUserId = 4 
AND Status='Complete';

As you can see, it's not overly complex. There are only 5 columns in the table.

Any help would be very much appreciated – Thanks!

Best Answer

What idexes do you have on this table? If the dbms engine is doing a full table scan, it will completely load a cpu and take a long time. If you have a 2 cpu system and all your data is in RAM, then you'll see 50% cpu usage.

Please create and index, such as

CREATE INDEX BlueBoxEngineDB.TransferIdx1
ON BlueBoxEngineDB.Transfer(Amount,FromUserId,Status);

and then run again your query. It should complete in a glimpse.

Related Question