SQL queries break our game! (Back-end server is at capacity)
- by TimH
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!
