Multiplayer Web Game - Is SQL fast enough?

Started by
3 comments, last by hplus0603 7 years, 8 months ago

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

Question

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?

Thanks!

"... If fate is a millstone, then we are the grist. There is nothing we can do. So I wish for strength. If I cannot protect them from the wheel, then give me a strong blade, and enough strength... to shatter fate."

Advertisement

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

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.
enum Bool { True, False, FileNotFound };

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!

"... If fate is a millstone, then we are the grist. There is nothing we can do. So I wish for strength. If I cannot protect them from the wheel, then give me a strong blade, and enough strength... to shatter fate."

Good luck!
enum Bool { True, False, FileNotFound };

This topic is closed to new replies.

Advertisement