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.

I have a MySQL Server that I use for the back-end storage of an online game. The game writes to the database to persist character information such as world location, health status, ability stats, etc .. This data is written every few seconds to the database for each character and vehicle. I have been noticing that as more and more users join the game that queries I write via the database seem slower. Also, as I make the online world more complex, via content added via the database, the game tends to slow.

I am looking for the bottleneck and I am wondering how I go about determining if scaling my database (vertically or horizontally) will make a difference. Knowing the database is MySQL how do I go about seeing if my single database server is the bottleneck and if scaling it would be helpful? Thank you.

share|improve this question
add comment

1 Answer 1

If things are slowing down as you add more data then it's most likely a lack of indexes. If you have a table being queried without indexes then the server will need to do a full table scan (eg. read the entire table).

To understand why it's so much slower read up about Big O Notation. Full table scans are O(n) and will be very slow for large data sets. Index scans would be O(log(n)). For comparison with n=1M rows the linear operation would need to read 1M rows vs about 20 for the O(log(n)) one[1].

If you know all the SQL that is being executed in your app you can jump to the EXPLAIN part of this answer.

If you don't know what SQL is causing the slowness then you need statistics to figure it out. There's no info in your question about the tech stack you're running on but if you're using an ORM framework then you may be able to enable query stats there.

You can also look at enabling the MySQL Slow Query Log. Once enabled your MySQL server will keep track of SQL commands that execute for longer than a predefined amount of time (the default is 0 seconds, eg. everything).

Once you've identified the SQL that is slow you should run an EXPLAIN on it to see the query plan. Based on that you can add additional indexes to the relevant tables that are being accessed via full table scans.

If after adding indexes things are still slow due to excessive load then you can consider scaling to a larger server or a clustered setup. Until you've optimized your SQL though it's pointless as the same slow operations would be equally slow on a clustered setup (you just may be able to support more of them concurrently).


[1]: These numbers aren't entirely correct. The real stat to look at is the number of disk I/Os involved. For a full table scan the number of disk I/Os is is the number of rows divided by the number that can fit in a disk block (usually 4KB) so it would be less than 1M I/Os though still O(n) as it would grow linearly with the number of rows. Also, most database servers use B-Trees with a high fan out (~100 vs 2 for binary trees) for indexes so the number of disk I/O's would be significantly less (4-5 levels of the tree is common). Again it's still O(log(n)) though the log is base 100 instead of 2.

share|improve this answer
add comment

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.