I've been working on a pseudo-multiplayer game that stores each player's important data on my MySQL database. The game is similar to Clash of Clans in that players build their own bases and may choose to attack other players' bases.
To prevent cheating, all important functions are run on my server via PHP. For instance, if a player wants to build a house, the server is told: Player wants to build BUILDING_A to grid{x,y}, and the PHP script checks from the database if the area is free, if the player has the tech, the money, etc. to do so.
All these functions are relatively simple. They just check who tried to access the database and if they can do what they wanted to do. Then the server will update the player's data in the database and return a message to the device.
So, essentially my game will always follow the same process:
1) Player device makes a request,
2) Server authenticates the player and permits or denies the request,
3) Server sends player device a response and the device follows it.
But I am not sure as to how viable or scaleable my approach is. I've just been testing it alone and it works well, but let's say that I get 100 or 1000 players in the first week. There may be a few hundred server requests per second at peak, where PHP scripts are called and the database is accessed and updated. What if it becomes a hit and there are thousands of people accessing my PHP scripts and database simultaneously?
Is this sort of approach viable at all to begin with? If my server(s) were powerful enough, can a single PHP script be accessed by so many people at the same time, and can the server write to my database hundreds of times within a second? Would it queue the operations, would it ignore some, would overlapping operations just fail?
For instance, if I paid for AWS EC2 and setup my LAMP there, could this kind of system work or would I run into some sort of limitation or issues that I am not aware of?
PHP script be accessed by so many people at the same time, and can the server write to my database hundreds of times within a second? Would it queue the operations, would it ignore some, would overlapping operations just fail?
lock_wait_timeout, which defaults as 50 seconds. (Not likely to happen.)You should probably plan to have enough RAM to handle all the data involved. (It sounds like this will be no problem.) If queries have to reach into the disk too often, that becomes a limitation on scaling the game. AWS EC2 uses SSDs, correct? (HDDs would be too slow.)
Are graphics involved? What handles that? Is it entirely in each user's client browser? I ask because if there are SQL queries involved in redrawing the screen, that could be a heavy load.
It sounds like the typical 'session' is
SELECT user infoand that will happen, on average once every few seconds. A thousand users doing that should be easy to achieve.
That assumes it is a single-person game. If, instead, it is a multi-user game, won't the "newly built house" need to be broadcast to all other users?