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....

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!

  • If they cpu is not going above 50%, have you considered the possibility that your long-running queries may be locking the table in some way? Preventing access? In any case, you might need to look at setting up replication. Then having your game point at one instance, and your long-running queries point at a separate instance.
    – Zoredache
    Nov 29, 2012 at 21:48
  • I'd make sure there's an index on Amount, FromUserId, and Status...
    – ceejayoz
    Nov 29, 2012 at 22:20
  • I have considered this. I wanted to get some expert opinions before I attempted it first though. I'll certainly look into it if that's the only option.
    – TimH
    Nov 29, 2012 at 22:25

2 Answers 2


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.

  • Unfortunately, running this index query produces the same game breaking results. Even if it improves query times, we have many different queries we require to run. Would i need to index every time I wanted to run a different query on updated data? If so, then I'm back to square 1. :(
    – TimH
    Nov 29, 2012 at 22:32
  • 1
    You create index only once. Then mysql will keep them updated and will use them in order to speed up queries.
    – eppesuig
    Nov 29, 2012 at 22:35
  • You should find all your slow queries, and add indexes on all tables used in these queries. Please note that indexes require space, so you should probably test what indexes are really improuving much your speed. In order to find the slow queries, you may connect to mysql and run command SHOW PROCESSLIST\G. Please have a loot at http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html
    – eppesuig
    Nov 29, 2012 at 22:40

I solved the problem by creating a read replica of the database. We can now run queries as complex and as long as we like, without taking the game down! :)

  • 1
    I would still encourage you to try setting up indexes (say replicate to a local machine, and try it there?) Because creating the index is resource intensive when you've already got 2GB of data, but otherwise you're going to find that it gives a LOT better performance to have the indexes there.
    – jcolebrand
    Nov 30, 2012 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.