Database setup question
Question, I'm setting up a database and i never vetted this thought, so i want a second opinion.
Is it typically better to have service based databases, or application based databases.
IE for example we'll take an mc server, you'd have the service, mc. So everything would go into one big "minecraft" database, which has things like bans, economy information, permissions etc all in different tables.
OR should they all go into their own individual database, so there'd be 1 hardware machine running multiple dbs, say 1 for the admin logs, 1 for the permission managers, 1 for the account status/bans, 1 for economy information etc.
I've always done service based, but i want to know if its best practice or not.
56 Replies
Thanks for asking your question!
Make sure to provide as much helpful information as possible such as logs/what you tried and what your exact issue is
Make sure to mark solved when issue is solved!!!
/close
!close
!solved
!answered
Requested by xanthus58#0
Basicly yeah, either have 1 db per plugin/system like economy management. Or have 1 database per whole server.
Thats how most applications i've used in the past has worked xD inside and out of minecraft
So would you reccomend the same system for applications outside of minecraft too then?
So basicly what i'm getting is (normally) 1db per service is good, espeaclly when the code is vetted.
However due to the volitile and individual nature of minecraft plugins, its reccomended to use 1 per db to avoid and security issues or db breaks
yeah i was looking at that and woundering why, i personally thought it was a bit mental, as i normally run things service based. Bc i dont really hook many volitile apps to my core db, mostly things like coreprotect, and luckperms xD
So how would you scale this outside of mc if at all?
Say your making a mock banking application or smth, would you have a whole ass seperate db for the transactions, and loans?
personally i'd just make it a table
very interesting way to think about it
So its mostly because the code were running is unsafe over anything else?
very true i suppose
alright well i think thats about everything i needed, thank you
!solved
post closed!
The post/thread has been closed!
Requested by xanthus58#0
What applications have u used in the past with this structure 🤔
Pretty much everything outside of minecraft, at least from my understanding, espeaclly from a roleplay history, fivem, gmod, and unturned all typcally use one database to manage multiple things, from the amount of money in your account to if your banned and your steamid. At least in the servers i was on in the past,
Outside of that we have things like pretty much any smaller application that needs to store data, ie leaderboard systems, tons of personal websites like blogs, password managers, pretty much any note taking application etc
i mean even something like a calander app, recipe application, your contact list in your phone, etc
pterodactyl
:-:
hehe
ahahha
Someone who used to work in big data, having different app (in our instance different app would be a different plugin, since each is uniquely coded by a different developer and ran for a different use case) in one database is just suicide. In the real world, (at least the companies I’ve worked at) we’ll have 10s if not 100s of databases. Bob the builder hit the money… if 1 of ur plugin fucks up… ALL ur data is fucked up
I see no benefit in having 1 Database
Also I think listing examples of singleton apps is pointless for this, a calendar app ain’t storing such a wide variety of data like a Minecraft server would
Your right in that, having more then one can never really be a bad thing, even if you need cross-db acces you can easily code that into your application.
However from my experience most of the time its service based, minecraft in itself is really just one "app" a game server, sure theres different systems like bans, logs, and permissions, but you can cut down any application into individual parts like that. So at least for me i typaclly see a single service use a single database and thats it
I'm not saying its a good thing to only run on one, i agree that its not optimal.
just that its what i tend to see personally
If the app was all under 1 person sure
But Minecraft isnt like that when u introduce mods or plugins
Exact same reason apps on a phone use different databases
Can i get some examples
Literally every app
Discord has their own database
Oh yeah 100%, the unknown nature of plugins themselves are reason enough for that
discord is also a multi million dollar company with thousands of users, it makes senese.
Scale the chat system down though and not so much, i mean think back to a good old IRC channel
Every app has their own cache store in their own way.. I’m talking about local cache, not cloud
Discord has multiple database for its cache? ima be real i never knew or looked into that
No bro
I’m saying
Discord has different database than ur calendar app
oh right yeah 100%
Ur treating Minecraft as 1 app. When it’s not
my apologies
Minecraft is ur ecosystem
Each plugin or mod is ur apps
That’s the biggest misconception u have here
So how would you define what is an "app" and what isnt then?
standalone code?
Each plugin
outside of mc mostly,
Discord, calendar, chrome, candy crush, they all have their own data stored separately on ur phone
question about this btw, almost every database i've worked with has had a sort of "commit history" similar to those found on github, is that not typical? It's trivially easy to rollback changes from my understanding
Well made ones for sure
And isnt minecraft also a standalone app on your phone too? Similar to the likes of candy crush
In our use case, since we’re running a modded server (like paper) it becomes its own ecosystem
I see, so in this case what would quantify an individual application?
Source code from a single point of origin?
Ie the coreprotect team, the paper team, etc.
Sure I guess, these are kinda all just vague terms tbh, there not really hard defined right or wrong.
Just in our use case, it’s best to isolate plugins/mods to prevent any issues.
This concept is of isolation is pretty much adopted by every mc developer, and they just assume ur going to use ur own database, and in the niche case 1 plugin wants another plugins data, they typically pull it from that plugins runtime (not the actual database)
Oh yeah i agree with that and fully understand, more just trying to extrapolate it for external use.
I mean for example we'll compare 2 apps.
A small progression based minigame mc server and a puzzle game on your phone, from what i understand the phone would normally use 1 database to manage things like your level progression, ingame money, high score, and basic account information.
The mc server would need to manage your ingame economy, a leaderboard, and the worlds you've beaten (say its progression based)
Why is it that in mc you'd use a diff database for the economy, and leaderboard, but the phone uses the same for both?
Because Minecraft isn’t managing ur economy, the plugin is, and that plugin that is managing ur economy isn’t managing ur high scores
thing is you would probably use the same db for keeping track of everything, just different tables
Thats kind of my point
Well 1 plug-in could pull in ur economy from another plugins database (like vault) and create and manage a leaderboard, (tbh idk how vault stores economy or wat)
i'd assume just some sort of sqlite db, honestly not sure
I guess, the simplest thing here is, different authors (whether a group or individual) should use seperate databases
That makes sense, i can get behind that.
You might even lose performance by keeping everything in 1 database at large scale
But that prob depends on the type of database
But realistically I think it’s all negligible in Minecraft
We’re never in the millions of tables/entries
So a question then, asside from proxy systems ie waterfall or smth.
Whats the benifit of hooking a plugin to an external database?
For example you run a small rp and eco based SMP, like dreamsmp or whatever. You decide to use plugins, normally they come with their own sqlite db in their /plugins/<plugname/ folder.
But yet i see so many people hooking it to an mysql db or something, wouldnt that be worse in terms of performance for little to no benifit?
Quicker to pull in data from a database than a file
And you don’t have to have it loaded in memory 24/7
I may be misunderstanding, but isnt sqlite just a fancy dump file? Ie its the same as a normal db, but portible and slightly more limited due to the company who made it is still developing it?
By external, do you mean, on the same machine?
Yeah, effectivly the same as running a ptero db on the same host as the mc server.
Or even fully external, like on another machine, ie aws or azure hosted db, i've seen many a server do that before too
Querying for a user is significantly faster in a db than a flatfile
i understand that, but isnt sqlite a db?
its not like your tryna fetch it from yaml or json
Yeah it is, but it’s not as optimized
Optimized in terms of query speed
So running a mysql db on the same machine, is faster then just letting the plugin manage its own sqlite file?
Woudln't you be bottlenecked by the network request?
Espeaclly if the db is on another machine like i've seen others do in the past
Yes, couldn’t tell u specifics, nor have I ran any benchmarks.
If the mysql is on a separate machine, for sure, you’d be better to have it on the same machine
Now is the loss in performance made up by network latency? Idk tbh
Idk either
maybe something for us to look into
:o
Now I do know that there might be differences in speed depending on how big ur data is
SQLite my perform better at smaller amount of data since its serverless
But idk really
Coreprotect is faster when in sqllite than mysql in all situations iirc
Do u know if coreprotect has redis support?
No idea
If I wasn’t on my phone I’d be lookin more into all this
Well its safe to say that for most servers they wont run into that issue xD