Best way to setup server database for app

Solar2D General Questions/Discussion
#1

So I have been working on a big tile based base building / adventure game and I need some advice on setting up the server/database component.

I am currently using php and mysql on my small test server. There are a lot of things that the player can do, like build their base, send explorers, attack cities and so on but all these actions follow the same process:

  1. player does an action in the app, like ”send scouts somewhere”
  2. that action is sent to a php file on the server,
  3. the server will try to log in the player, if it cant then thats it but if it can then
  4. the server will open the database and get the players info,
  5. the server will check if player can perform the given action, like do they have enough gold, people etc
  6. if the player has enough of everything the server will update the players database entry and close it,
  7. the server will send back response of what happened, like success or fail,
  8. the app will run the command that it got

My question is: is php+mysql good for this? Like if I have 1000 concurrent players and whenever they make a gameplay move, then their database entry is opened, checked and update and/or closed. Can a pho+mysql handle this or should I look for some other service to manage this?

#2

PHP + MySQL is a perfectly capable combination. You just need to think about where the bottlenecks are going to be.

Your biggest bottleneck is going to be in how the server itself is optimised. This isn’t the right place for server tech stuff so without going into too much detail, in a nutshell you want your server to be configured to invoke as many PHP instances as possible without exhausting the available memory. Look into PHPs script execution timeout and memory limit settings and also running PHP as an FPM application through Apache. On modern servers you’ll be able to control how many instances FPM launches and thus how many requests the server can process in parallel, but remember that if your script consumes 100mb of memory and you allow 40 requests to process at once, you’ll theoretically consume up to 4gb of memory just with the PHP processes alone.

Your next bottleneck will be MySQLs internal queue. Certain operations like UPDATE queries often need to be left to complete before moving on, otherwise a subsequent SELECT might fetch the record before it has been updated. You’ll notice PHP includes unbuffered variants of it’s MySQL query functions. With the regular functions PHP will wait for MySQL to respond before moving on, where with the unbuffered queries it’ll just fire off to MySQL and then move on without waiting. Use unbuffered as often as possible for updates and deleted etc where you don’t need the request to be processed immediately, but be sensible with it.

Also make sure you use indexes properly otherwise certain SELECT queries will be slower when there are a lot of records to iterate. At the very least, index any column that’s used in WHERE, JOIN, or ORDER clauses. Remember though that updating those columns will be slower than usual as the indexes then need updating at the same time so again use unbuffered queries where you can.

Put simply, it’s more about how you set the engines up than which brand of engine you use.

#3

I do a very similar thing and yes LAMP is perfect for this. 

Not to be too confrontational but allowing too many PHP instances is not the way forward as you will get massive DB thrashing.  I limit PHP instances to 10, have many thousands of concurrent connections and a highly optimised DB.  You will want to have everything as procedures (no adhoc stuff).

But ask yourself if you really need server-authoritative as it is a lot of work?  You will add noticeable lag, unless the internet connection is really good and your server highly optimised.

You are probably better off doing the game logic on the device and then sending the result to your backend.

#4

Thank you both - your answers are very insightful!

#5

Isn’t PHP too resource intensive. My CPU resource is getting exhaust while running multiple task.

#6

That depends on how efficient your code is really. PHP is the most widely used server-side language these days, and the performance gains of the 7 series are especially impressive. But poorly thought out code will eat resources whatever the language.

#7

@richard11, I’ve actually been wondering how to effectively and accurately benchmark one’s LAMP setup. How would you go about determining how much memory some PHP script requires?

#8

Hi,

Server set ups - and the impeding tweaks - will vary by application, One thing to understand is true concurrent connections vs. regular requests, which generally will end a connection shortly after returning data depending on configuration.

PHP and Apache are more than capable to handle your 1000 players (probably with little additional config), because unless all players make a request at the same time, you are not dealing with 1000 concurrent users, your just handling requests, so focus on getting your responses as tight as possible (this is under the assumption that Apache can queue connections like Nginx). Now, that isn’t to say you shouldn’t make sure you have the proper resources in place if that number grows.

A decade ago I built a “faux” MMO using PHP and Apache on budget hardware. There were about 3000 users, and the app pinged the server for updates every 10 seconds and it ran its lifetime over 3 years with no issues (growing to almost 5000 users). I’d imagine that 10 years later performance is much better now. And what I was doing at the time wasn’t even a common use case.

PHP and Apache are “most used” often due to age, but along with this comes the bloat of history. Node and Nginx are moving very quickly in the market and represent a fairly dramatic leap in these spaces. A 1000 users on budget hardware using Nginx would be trivial.

After a 20 year stint using the LAMP stack, I jumped ship and doubt I’ll ever come back. My current stack is OpenResty (Nginx+LuaJit) and the Lua scripting language. Or you could try Coronium Core (shameless self-promotion).

I’m not here to knock the heavyweights, but there are new alternatives rising that might be worth investigating. But in any case just learn as much about the various config files as you can. They can easily make or break an app.

And finally, I want to echo what @SGS said. Unless your app requires an authoritative server, don’t do it! :slight_smile:

-dev

#9

I kinda need to set it up because I want players to be able to inspect or attack other players based and there’s no way to get people’s bases unless they are on a server :stuck_out_tongue:

Thanks for all the help! Openresty looks especially cool, even though I don’t understand it at all yet :smiley: I might look at coronium too, thanks!

I guess my main worry now are the database read and write processes, both in terms of capacity and cost. I read that Amazon DynamoDB charges around $0.25 per million read requests and around $1.25 per million write requests. That’s not really all that expensive and I expect I would at worst pay a few dollars if my app is successful. But if it would still make sense to cut down all unnecessary read and write processes.

If anyone has ideas on how to limit the number of read and write processes necessary for a base building game, I would appreciate it.

#10

Do check the small print… a read is 4kb and a write is only 1kb.  Also, you’ll need to add storage and data transfer charges.

If I consider just only writes, my DB averages 10gb per hour, which is 10m writes (at 1kb) or $12.5 per hour or around $9k per month!  Which is just a crazy amount.

Now, because I have my own servers, I simply save an ecrypted JSON stream and do not need to process delta changes.  If you choose AWS then you will probably have to send per tile changes and just hope there isn’t too much lag.

Base building/War game is something I’m considering.

#11

Don’t you think NodeJS is more suitable for this example? so I read, the node supports more requests for being Single Thread than PHP, in addition to having a larger ecosystem.

#12

Hi,

Some possibly interesting information:

Node.js is a JS runtime environment which is also an HTTP server with some event-driven features and has many drawbacks in terms of concurrency and high load or user requests to handle a large number of users concurrently. Nginx has the best performance in this case and it provides the best performance. Nginx is a fast web server that supports routing, static content, etc., in an objectively faster time to provide the greater user experience.

Nginx should be preferred in terms of high response rate especially in the case of large concurrent users where a large number of applications running on the same system. Also, multiple numbers of domains on a single system instance can be handled easily by using Nginx.  Nginx outperforms the Apache webserver which was the primary goal when it was developed.

Souce: https://www.educba.com/node-js-vs-nginx/

-dev

#13

@Develephant - are there any compelling reasons (like ease of use) to use Node.js?  I feel like a lot of devs talk about it but Nginx seems to be a more powerful solution on all fronts.

#14

@sporkfin, as always, it it how you use it not what it is that is important. 

I’ve seen really bad DBs running on lots of hardware just to make it perform.  For example, queries returning all rows and being filtered in the client, etc.  (they figured distributed processing would be faster!).

#15

@SGS, I guess what I’m asking is which solutions are easier to implement and which solution are more robust - sometimes they are the same.

I used to have an e-commerce set up a dozen years ago that used Apache, MySQL, and PHP (the name for the method escapes me, not, it wasn’t “AMP” :) ).  I currently use Coronium but I want to have a grasp on what is out there in case I need to make a custom build.  I’m most intrigued by OpenResty (Nginx+LuaJit) but I have a book that shows how to build a multiplayer game with Node.JS.

In the same way that I’m familiar with Unity, Defold and Gamemaker but choose Corona, I’d like to have an understanding of the strengths and shortcomings of the different server-side solutions.  In the above example, it would be that I find Unity too bloated for most of my needs and I prefer Lua to GML.

#16

I dont know if you are joking or not, but it is LAMP, linux apache mysql php. :stuck_out_tongue:

#17

Yeah, the “AMP” was a joke on LAMP.  This was before LAMP was king or at least before I had heard of LAMP.  I still can’t remember the name but it was not a “winning-stack bundle” so no longer “exists”.  Maybe it needed a catchier name  :wink:

#18

@sporkfin I prefer Lua, even though I do Node.js/React work as well, but mostly for client side and some custom tools. I personally did not like Express, which is one of the more popular and “easier” to use server backends for Node (admittedly, I have not looked to see if there is anything newer now). In most use cases Node is proxied by Nginx anyway.

I like to develop tools that can run on budget hardware, I’m not rich. :slight_smile: In which case I try a lot of different tech to see what is what. In the end I chose OpenResty for a couple reasons. One is that Nginx is highly performant with a small memory footprint. And second, 95% of the time you are working in the Lua language you know and love.

Working with OpenResty is like working with Corona, except on the server. You can build complex applications that are solid if the code is tight. Most functionality is covered with modules, and of course you can build your own.

Now, the reality is that in any case there is going to be a learning curve, and I will admit it took me quite some time to get a good handle on OpenResty (much like the first time using Corona) but once the concept clicked and I understood more about the ecosystem; where to find docs, examples, modules, etc. it became a joy to develop on (similar to Corona). I find Node more cumbersome, and not as fun to work with.

Something to think about is that both solutions are used in enterprise endeavours. We are talking about large user bases, and very busy sites. Unless you are pushing a ton of content over the wire, the differences in speed will probably be nominal at best. But at larger scale, Nginx is going to perform better.

With OpenResty you get three highly performant technologies in one simple package; Nginx, LuaJIT, and the Lua language itself. I call that a win.

But, as with all software/stack debates it will come down to what you are more comfortable coding in, and which style of set up you prefer. For me it is “What is the most fun but still enterprise level.” For you, it could be completely different. I prefer to be able to jump right into code and see a result in short order.

So yeah, you get it, I like OpenResty (apparently quite a bit). I like it so much that if anyone is interested in how to get set up and start creating Lua server applications, I’d be happy to share my knowledge with some blog posts and/or screencasts outlining the process.

Happy coding.

-dev

  

#19

I was using LAMP configuration previously, till the Apache gave in. It was too resource hungry, which  could be the reason for your high resource usage. On my ubuntu, apache was using load of >10 and then crashes. On nginx, it goes upto 5 only without any crashes.

For the past 8+ years, i am using LNMP configuration, as Nginx is much faster. It handles heavy load very well. Thats why a lot of major other webservers uses this.