Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from serverfault.com Nov 30 '12 at 0:34

This question came from our site for professional system and network administrators.

    
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 '12 at 21:48
    
I'd make sure there's an index on Amount, FromUserId, and Status... –  ceejayoz Nov 29 '12 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 '12 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.

share|improve this answer
    
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 '12 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 '12 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 '12 at 22:40
    
Thanks. I'll look into that and try it. –  TimH Nov 29 '12 at 23:00
up vote 0 down vote accepted

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! :)

share|improve this answer
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 '12 at 20:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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