Sign in to follow this  

Multiplayer Web Game - Is SQL fast enough?

Recommended Posts

What my networking setup looks like:


=> Player performs action

=> Action checked and executed server-side with PHP

=> Information is grabbed or updated from SQL DB


Game Setup


Apocalyptic city simulator.  Players can interact with each other (Attacking, trades, messaging) and players can interact with their own cities.  The game follows along the lines of old FB games like Mafia.  You can track stats, upgrade units/structures, attacking a city would be pretty simple (click the attack button on a city's page, yourForces - theirForces, subtract some forces from the losing side, subtract some resources from the losing side, end of battle.)




Is SQL fast enough to track all of these things?  Will SQL be fast enough to track players stats, update them quick enough, and be able to output stats in real time to other players?  Will SQL be fast enough to carry out all the actions of the players connected to the game?


I don't expect much traffic for the game < 100 players at any given time (if that).


If SQL is fast enough what are some things I could do to speed up the time of queries?



Edited by Talvish

Share this post

Link to post
Share on other sites

"Fast enough" depends on your needs.  A SQL database works well for persistent data, but is terrible if you need things more than once. Load it, use it for a long time, store periodically as you modify the data.


Simple SQL database operations that hit the disk are usually on the order of about 10ms per call. It can be much faster if it doesn't need to hit the disk, it can be much longer if it is more complex or requires significant data.  For example if you only need simple values from a single table based on an indexed key on a fast machine with SSD storage, you'll likely be a single-digit milliseconds.  But if you are searching for aggregate data or using a non-indexed value and need a full table scan from a 200 gigabyte table on a slower spindle disk, you'll be waiting quite a while.  


For an interactive game, that is about equivalent to a full graphics call, 60 frames per second = 16ms, a bit less once you account for the overhead of other tasks.  However, if your game is involving a whole HTML page being requested and loaded and processed that task is usually on the order of 200ms-500ms, so a few database calls are just fine.  If you need something intermediate from the two, such as server processing, there are systems out there that cache data access so you only incur the full cost of database reads the first time they're encountered and not in the cache. 


Without knowing quite a lot more about your game needs and your architecture, an "it depends" answer is about the best you will get.

Edited by frob
Clarity on why the variation.

Share this post

Link to post
Share on other sites
Think of it like this:
If the turns could conceivably be delievered over email, then using MySQL to store/forward them is fine.
If the turns need to be interactive, then you need an interactive game server/persistence system of some sort.
If you have to live with the PHP "no global state persists past a web request" model, then an in-RAM storage like Redis can help, but of course you still have to pay the cost of inflating world state and then re-saving world state after each request.
If the game is truly interactive, then HTTP is the wrong protocol, and you want a persistent process server, a la most AAA 3D multiplayer games out there.

Share this post

Link to post
Share on other sites

Alright, so I'm thinking I should probably go with something like Node.js then.  Server/Client systems scare the shit out of me.  Ha.  But I think it'll work, for the most part, how I do PHP backend stuff, with JS front end stuff.  Wish me luck!

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this