Questions regarding frequent MySQL queries

I’m making a gamemode will span across two servers but keep the player’s data consistent. I am using MySQL to go about saving the player’s data, but I’m fairly new to MySQL and don’t know how practical my idea for storage would be.

If I’m only dealing with 100 players at most at a time, would it be alright to update a player’s inventory/money/whatever variable in real time? That is to say, every time a change is made, an update is sent to the database immediately? My other option would be to only load upon a player joining and saving upon a leave, but then if a crash occurs then that means data could be rolled back, and trades where only one party’s items were saved might cause duplicate items.

Please tell me how you guys deal with MySQL or what you think I should do.

In gmod I’ve only ever seen saving on disconnect or at set intervals. I have no idea what the performance is as I have never tested it.
You should try it with your gamemode to save it everytime a change is made. Preferably with a non-remote database(as in all servers and database on the same dedi). Keep us updated on what your tests find out if you do this.

But I’m sure someone has a more thorough answer than I do.

That’s what I was thinking too. Every time a change is made to someone’s data, mark them as “dirty”. Every 60 seconds or so, all dirty users are saved. Is it really better to just update everyone’s data simultaneously on a set interval you think?

Like I said I don’t really have any benchmark data to work with, it’s just how I’ve seen it done.
But in my memory saving it all at once made quite a noticeable lag on a full server. Imagine a coordinated attack where people abuse your instant saving to ddos the server, that would be worse.

Thinking about it this may be the reason for the set interval save.

It should be fine. My servers have a bunch of things that save in real time it causes little impact to performance of the server. MySQL queries are fast and should not cause lag provided they are done correctly.

DarkRP saves data in real time it just doesn’t retrive the data from MySQL which is where the multi run exploit occurs.

I just write to the DB immediately when data is modified. Depending what module you’re using for sql (tmysql or mysqloo) you can easily lock up your DB (depending on the engine + module you’re using) if you’re writing a lot of data at once. This used to happen to me when data was saved every 5 minutes and it would send 6000+ queries to the DB in an instant and it would prevent players from getting getting, writing data, ect because of row locking. But this could of been prevented if the system structure was coded efficiently for SQL + if the way it stored data was efficient

As long as your queries are thought out well, you have primary keys and or indexes, your system in lua is thought out to limit queries being sent to the DB then you’re 100% fine.

Fetch and store the data on the server once and then write the information + update where you saved the data on the server / DB when needed.

An example for a leveling / xp system would be: Fetch the level and the XP of the player once when he joins and store it somewhere like in a table or on the player. Once that happens, you will never have to fetch that data again because when you write updates to SQL just make sure you’re editing that table / variable too.

When you update on intervals you risk losing player data if the server goes down, you risk losing data if the DB disconnects (writing on an update can catch this and store the queries that didn’t get processed to reprocess when it comes back online), you risk flooding the DB with a ton of queries if your system is really inefficient.

MySQL is a database meant for things a lot larger than the data load of an average Garry’s Mod server. You’ll have no problem saving a player’s inventory to the database every update, if you do it right. There are some things that MySQL does well and there are some things it does not.

If your data is probably normalized, you have your indexes set up correctly, and you’re not making any stupid queries, you’ll be fine. That would mean you’d have to design your code to sync data to MySQL in a way that MySQL likes - for example, if you’re saving an inventory, you’d save it in a table with a schema that would probably be (id, userid, itemid, count), with the primary key being id and the indexes being userid and itemid. That would mean every item in a user’s inventory would be a separate row in the table - it sounds like a waste of space, but it’s a form MySQL can handle a lot more efficiently than the usual strategy of dumping everything into a JSON blob in a TEXT column.

If you do want to go about the JSON blob strategy, that would be one of the few cases where a document store is better than a relational store. But I haven’t heard of a Garry’s Mod server using anything other than SQLite or MySQL.

I would advise against delaying your queries and sending them in batch updates. Then you’ll be waiting perhaps several seconds as MySQL processes all your data, rather than a few ms for a single query.