How stable/reliable would an SQL be for hosting a game?

[SkeletalReality]

Hiya!

I have some concerns about using an SQL for being the primary host of a game. I have an idea that’s a little courageous and breaks the barriers, but I need see if I’m not chewing more than I can swallow here. So that’s why I’m coming here first.

So here’s the concept:

tl;dr - Making singleplayer servers of a galaxy consisting of yours and other peoples’ solar systems with cross-server communication and wondering if this is practical, if not possible.

This will be a space exploration game separated into singleplayer servers. You are in a galaxy that will expand when more new people play the game. Upon a user joining the game for the first time, it will select randomized Vector coordinates of the user’s home solar system location. The more existing solar systems, the farther away these Vector coordinates will be from the origin (older players will be closer to the origin). Rogue systems (solar systems not owned by players) will be generated nearby the new player as well.

Here’s a visual representation of the galaxy.

These coordinates will be indexed in an SQL database. Each row will consist of the owner’s username, the planet names, types, etc. In order to reduce the web host’s bandwidth, the name/type values, such as planet appearance, colony data and whatnot, will just be index names to retrieve from the Roblox Datastore.

Of course, not all of these star system locations will be generated at once. While exploring the galaxy, it will take query data from the SQL in a continuous loop, returning the nearby star systems in the database and generate those in the workspace.

You can enter player’s star systems and attack if they’re not pacifists, or give gifts, alliance and so on, or colonize uncolonizes star systems. But this is getting off topic so I’ll end the concept explanation here.

So, here are my concerns,

1) Querying - Plenty of SQL’s are used to hold username and password data for websites and whatnot. Some people, typically with millions of rows in a database, have issues with SQL taken an incredibly long time to find indexes. What’s the most efficient thing I can do to prevent this? Make a new table for rows every ~1 million rows? Any good way to search for nearby vector coordinate indexes without using FIND?

2) Bandwidth - In order to update cross-server communication as often as possible, each one player server will have to do 300-400 requests p/m on average. I use Hostinger with a business plan, SSL certificate and unlimited bandwidth. But can it still handle this? They may think I’m doing something fishy, sending and receiving info back and forth with a third-party service so quickly. Like I’m trying to DDoS their service.

3) Unrelated to SQL stability, is this just too complicated for a Roblox game? Will this overwhelm new players? This may be too courageous of an idea and I should take a few steps back, but at the same time I really want to bring something new to the table. Is this concept practical?

I know this is a lot, sorry for stupid questions. Just need to get my worries about the technical problems out of the way if I ever choose to go forward with this. If anyone has other SQL-related stuff I should be aware of, please let me know!

Thank you!

[LordStrakk]

I have not yet read, but just want to give my two cents.

With my experience with MySQL, NodeJS, and various other things as well as Lua, I have ultimately found that using HttpService is only reliable for the main data transfers of information, NOT streaming.

In other words, I find it completely acceptable to use HttpService for data on planets and such, but I do not agree with using it as a replacement for ROBLOX’s player networking. That is, unless you have a OP server and are willing to have a two second delay for all players.

[EmeraldSlash (Rio)]

300-400 requests per minute is a lot. Why do you need to do so many?

EDIT: Oh cause you want real-time stuff, so maybe not a good idea in Roblox

Updating every 10 seconds or so is still pretty quick and doesn’t use heaps of requests.

For stuff like this, I think you want to be doing a long-lasting HTTP request (if it’s possible in this context) because it won’t use up many requests when stuff isn’t being done.

[LordStrakk]

^

Look just saying now, in order to effectively have that amount of requests you would need a sever better than my own, especially if you plan on having more than just a few servers.

[Osyris]

Yeah real-time streaming would maybe be appropriate with something like websockets. Regular HTTP won’t cut it.

Unfortunately, Roblox doesn’t support this.

[LordStrakk]

Feature request incoming…???

[Kampfkarren (boyned)]

In this thread they denied the feature request for “security reasons”.

[Semaphorism (standalone_ecu)]

In this thread they denied the feature request for “security reasons”.

sadly
though i am not sure if roblox knew i wanted client-implementation, and not server implementation, idk.

for OP:
Based on my experience with HTTP requests, especially if using DB on each request, you’re going to have a REALLY hard time scaling that cheaply for many players. even sending THAT many requests is going to be REALLY hard on your server(s). Because it’s going to have to process each connection (1 per http request), process the request itself, etc. That’ll add up to a lot of CPU usage for sure.

Also remember: SQL databases are REALLY nice and useful for games, (Phantom Forces uses one very heavily), however, you need to consider Reads are more taxing than Writes, so storing data in memory on a big server is probably most ideal, then writing it all to disk every so often. Like 128 GB of ram server for example that stores the entire galaxy on the disk, and when starting, loads it all into RAM.

My suggestion: Push roblox to give us a UDP/TCP socket of some sort, hence why I suggested web sockets, easy to implement, and assuming they followed the web standard, it’d be easy to setup via Nodejs or something.

[Kampfkarren (boyned)]

if roblox knew i wanted client-implementation, and not server implementation,

Arguably worse. That would let you steal someone’s IP. Same reason they don’t let you use HttpService on the client.

[Kiansjet]

Has anyone requested a dynamic cap for HttpService usage, simlar to DataStoreService, to accommodate for upcoming high capacity servers? (200 player beta)

[SkeletalReality]

Sorry for late response but going to try and conclude this!

Updating every 10 seconds or so is still pretty quick and doesn’t use heaps of requests.

Yeah I think it’s better just to do that. The 300rpm was from a cross-server chat of mine. Instead, while using 10rpm, I can maybe query a list of chatted messages along with the os tick when it was sent. That way for every HTTP request, the returned messages are posted on the speed of which they were added, just 6 seconds late. Good enough for me!

Yeah real-time streaming would maybe be appropriate with something like websockets. Regular HTTP won’t cut it.

I had a feeling, I think I’m going to derive less from trying to go for fast cross-server communication. Judging by LordStakk’s first reply, I should just get the galaxy expansion operable, and let the updating do it itself for whenever data needs to be fetched from the SQL. I guess automatic updating for the solar systems weren’t that crucial anyways, since the data being sent from the SQL are just going the be index names for the Roblox Datastore, which keys typically take 30-90 seconds to update across servers.

Based on my experience with HTTP requests, especially if using DB on each request, you’re going to have a REALLY hard time scaling that cheaply for many players. even sending THAT many requests is going to be REALLY hard on your server(s). Because it’s going to have to process each connection (1 per http request), process the request itself, etc. That’ll add up to a lot of CPU usage for sure.

I know what you mean, about 10 other testers and I were testing my cross-server chatter. We were in one player servers, each of them doing 300 requests per minute. The chat worked just fine but the network receive was awful. Like said before, I think I’m just going to stick with 10 rpm with a 6 second delay.

So, after thoroughly reading everyone’s replies, here’s my hypothetical solution for now (if web sockets never become a thing):

The game will instead be split into 50 player servers, which each player will be running everything client side. Every 10/60 seconds, the server will retrieve the clients’ positions in the galaxy. It will then take the 1-50 positions and get the nearby solar systems of each in the SQL, and give it to the clients. If a client ever needs to write data to the SQL, it will queue it for the next time the server needs to get locations from the clients again. That way, in all circumstances, each server runs off of 10 requests per minute. There will be a ~6 second delay, which is good enough.

This way, the rpm has been drastically dropped from 300 per minute per player to just 0.2! The bandwidth stays just about the same, but as said before, the database’s rows only consist of index names for the Roblox Datastore, so you’re really only reading and writing byte-sized (pun-intended) information each time.

So this might just work, lemme know!

I’m moving along to the Hostinger forums to see the server stabilities, and see how this can be achieved. Thank you all very much! I’m all ears if there’s any other stuff I need to know.

[Corecii]

That’s not what Semaphorism means. Semphorism is not suggesting implementing web socket support for Roblox clients. Semaphorism is suggesting implementing web socket client support for Roblox servers.

Web sockets have clients and servers. Generally, a web server is on the server side, the client connects, then the two can quickly and easily exchange data.

Semaphorism is talking about the Roblox servers acting as websocket clients. Roblox servers already act as http clients when they make http requests.

There are understandable security problems with Roblox servers acting as web socket servers. I don’t see an issues with Roblox servers acting as web socket clients – it’s not significantly different from acting as http clients, which Roblox servers already do. This is what Semaphorism means by “client side implementation”.

[buildthomas]

If your data has few relational properties or you can do without enforcement of relational properties, it is better to use a NoSQL database. NoSQL databases scale better than SQL databases.

[Kampfkarren (boyned)]

Feel free to make a feature request then.

[Froast]

SQL is not slow at all to access rows given you have an index for the column(s) you are querying for. My archive database with hundreds of millions of rows has absolutely no problem pulling indexed records. You can google for information about indexing in SQL.

You are talking about a huge number of requests per server. I am skeptical of whether 5-6 requests per second is actually necessary. I would not recommend Hostinger but a cloud service such as AWS or GCP. Their services much more flexible, allowing you to scale or load balance as you please, they wouldn’t not care even if you were actually making 400,000 requests per minute (given 1000 servers) – that is, as long as your pockets are deep enough – and I dare say they are more reliable, stable, and fast compared to what you can get from Hostinger.

If you are using a database for production you should make sure you know what you’re doing and consider using load balancing, multi-AZ (high availability), etc. for reliability and speed.

As @buildthomas said, I would recommend looking at different database types to try and find out what is best for your game. Relational databases have specific use cases and they may or may not be right for what you’re trying to do.