November 16th, 2013, 08:50 AM
Opinions/suggestions for a MySQL used for gaming tournament registration
Maybe I should rephrase my idea...
I have a server that will receive a spike in traffic all at once (from thousands of users) per week for a gaming tournament. What is the best mysql setup to handle thousands of users submitting forms all at once?
November 17th, 2013, 12:21 PM
It's impossible to give a good answer.
Since it depends on how large the database is and how many and how heavy queries one form submit results in.
But let me put it this way.
On my soon 4 year old laptop i can average about 3000 inserts per second when I run both client and server on it.
Some pretty hefty servers I've worked on had a sustained query rate of about 7000 qps. That database was about 1GB and all fit into RAM.
So if you say thousand of users (btw between 1,000 and 10,000 is a factor of ten difference) I'm going to assume 5,000 users and even if all would press submit at the same time and each post ends up to two inserts then that would make 10,000 queries that would be handled in about 3,5 seconds on my laptop.
Which I think would be manageable.
OTOH if the database is big in size, not optimized and some queries are not optimized and complicated for MySQL to execute then that figure can drop to maybe 300 qps at which point the submit for 5,000 people would take about 30 seconds.
And since those thousands of people probably not are synchronized in their submit pressing I'm guessing that you have quite a stretched out time frame.
Summary: If you have a small database and simple optimized queries then I don't think you will have a problem, but if you have a large database with non optimized complex queries then you might run in to one. Either way you will have to perform some predictions and guestimations about how large your database will become and how many queries that are going to be executed.