FlatFile or MySql?

I see a lot of plugins that seem to have the option to use a mysql database. When you have to choose between flatfile and mysql, which one do you prefer? This is assuming you have a mysql database available. I am a fan of mysql so I always go for mysql, because its faster and is easier to view some of the data (assuming you're proficient in mysql synthax).

However, with the exception of heavyweight plugins like logblock that require mysql, is there that much of a significant difference when choosing between flatfile and mysql storage systems for simple tasks? I always feel better using mysql when possible because its faster and really powerful. Yet I see the word 'overkill' and 'mysql' pop up next to each other rather often So which one do you prefer?

 

MYSQL.

 

MySQL. When I have to make a backup of the server it saves a lot of files.

 

For plugins that store a lot of data, such as block-loggers, I'll happily use MySQL, but for things like permissions I like to be able to edit them by hand with nano if necessary.

 

Entirely dependent on the plugins. For example, using MySQL for permissions is crazy, but using it for LogBlock makes perfect sense.

 

Given that a lot of people have their MySQL server on a separate server on a different network I would go for files where possible to avoid the extra delay of the network communication. Also like TnT said they both have different usage cases, there should never really be a choice at all for the same data.

 

As TnT said its dependent on the plugin, I wouldnt wanna have to edit a mysql database everytime I want to edit my permissions or configuration however if the plugin is logging info at all times or most of the time on the server, yes I want MySQL because I can back it up, I can easily transport it to different servers if I have to etc. Therefore there is no solid choice on this.

 

MySQL. It doesn't have to be external, just install MySQL...

Although if you're with a GSP and can't use local/LAN MySQL, then flatfile might be a better choice. Only with a GSP that cannot offer low-latency MySQL though, for all other use cases MySQL is better.

 

Unless you're dealing with thousands and thousands of entries, I see no reason why you should use MySQL.

 

because MySQL is faster, more reliable and much easier to communicate with other languages. Many languages have libraries for MySQL =)

 

i dont see the difference in using MYSQL for files smaller than 10 KB

 

CoreProtect actually claims flatfile is faster than mySQL (in their case).

 

Hahaha. They also create hundreds of thousands of files that reek havoc on backups.

 

sigh. so what's recommended? should i use mySQL instead of SQLite (i.e. for LWC)?

 

MySQL for all plugins that support it.

 

MySQL

 

This is an interesting debate. We are currently looking for a good inventory plugin and are pending between using WorldInventories and MultiInv.
- WorldInventories creates 2 xml files for each user in its directory, one for the inv-items and one for the skills.
- MuliInv creates a MySQL database where it stores the users inv-items and skills.

We have 9500 player files atm, which with WorldInventories would result in 19000 xml files.
Or we could be using MultiInv with MySQL, but I have no idea which one actually is faster.
Basically: When a user logs in, is it faster for it to lookup a flatfile among alot others, or to look it up using a MySQL database?

We have about 175players on most times of the day in our timezone, and alot of players joining/leaving all the time if it changes anything. And we are using "My Worlds" by bergerkiller to manage our multi-worlds.

(Worth to notice the MySQL database is localhost and not remote)
Tux2 Drayshak bergerkiller

 

thernztrom
And in MyWorlds I don't use either of them. I simply store all player inventories in the player info file in the world. In NBT tag format. Why bother storing it separately? Plus, if someone moves the world later on, he still has access to his inventory without hassle.

https://github.com/bergerkiller/MyW...rgerkiller/bukkit/mw/WorldInventory.java#L109
https://github.com/bergerkiller/MyW.../bergerkiller/bukkit/mw/WorldConfig.java#L304

Plus, it's smaller and easier too, and automatically adapts to possible changes done by Mojang.

Depends. Fastest is to store the data in memory in a hashmap or similar. I recommend to always do this. Then it's just a matter of regularly saving to a permanent storage, like every 30 seconds or so. And then speed is extremely unimportant; it is on another thread. Then you have to look at: How much do I store? If you only store inventories, I'd go with an XML file. Or just multiple flatfiles per player, like how MC has.

But please, please don't open a new file every time a player joins. Store the data in memory, otherwise you are just going to have a bad time.

Afraid you end up with too many inventories in memory? No problem, add a timestamp to each inventory and dispose of it from memory at a set interval. This way rejoining people won't cause a massive lag on the server. (I don't expect you would have 10000 different players joining at the same time?)

Flat file IS fast, but if it is too big, it is slow. Try to keep the files < 1 mb so you can quickly load them. That being said, try to compress the inventory data prior to saving.

 

Well, I saw on your plugin description that you support separate inventories. On the other hand it also says on your plugin description:

We first run Multiverse, but changed to yours because we liked your portal system better. However we configurated it right, but it was acting buggy. We had 2 separate inventories, like so: http://pastie.org/4738830
But when people joined the survivalgames world, and died they would loose all theirs items, not only the survivalgames inventory, but also the "world2" inventory.
When we heared of this I logged in with two accounts and tested, by giving myself like 1000 stones, walking into survivalgames, dying and then when I went back to any of the other worlds i didnt get back my other inventory, instead it was empty. (I restarted the server in between the tries) Sometimes it would work the first time, but the second time I went into the survivalgames and went back the inventory was cleared.

Thats when we saw your worldinventory message and installed it quickly to get back online, but now afterwards we want to do it properly and in a way affecting performance as little as possible.

We are not the only ones with this problem looking at your plugin pages comments:
http://dev.bukkit.org/server-mods/my-worlds/?comment=402
http://dev.bukkit.org/server-mods/my-worlds/?comment=393
http://dev.bukkit.org/server-mods/my-worlds/?comment=403 (Our own comment)
http://dev.bukkit.org/server-mods/my-worlds/?comment=406 (Suggesting using another one untill its "fixed")

Do you have a clue where the problem could be?

 

thernztrom
Yeah I am very busy right now updating all sorts of plugins, so I guess I can take a look at MyWorlds, too. I first have to check if BKCommonLib has any bugs related to it. When that is veirfied to be fully clean, I can push it out and work on other plugins, including MyWorlds.

It is pending for TrainCarts right now (reported bug with stations is where I'm at, could be last, but maybe not.)

One thing is clear already: One inventory gets cleared, and when the player goes back to creative, it doesn't properly load the inventory of that world. Now, there are several world change hooks around, but I think a 'respawn' event is not handled. Which could explain why dying in survival results in you losing the items. (I could be wrong of course, it could be deeper than I think right now, and it usually is... )

I forgot to remove the 'use worldinventories' message, though, but I still recommend using plugins that are dedicated to do the job. They will most likely support more than MyWorlds can do. (and should do, I do not want to make it all look like a huge clusterfuck, like how other batch-made plugins end up...)

 

Hey there I'm not totally sure what benefit using MySQL would have for cases where the files are like ~10kb max. I don't 'load anything in to memory', the plugin simply reads the file, sets the inventory, then destroys the file handle. Information is saved on a user set timer, or when an inventory needs to be switched.

I suggest that people stress test (I have no way of doing this). If my plugin's performance is provably poor with large servers I have absolutely no problem implementing MySQL support.

(Edit: Some people contacted me and I definitely see their point about ease of backup with large numbers of small files. I'll start fiddling with it on my dev branch.)

(Double edit: I take it back. If you have trouble backing up that many files you've got bigger problems, and I can't see why MySQL would be much faster. Prove me wrong and I'll reconsider)

 

MySQL definatly, if you use flatfile, the plugin will just append the xml files untill it find the correct one, MySQL and other database distributions are optimized for searching data quickly, using complex algorythms and data structures. In your case, with almost 10K files in the need of storage, your best choice is MySQL (or another database server if supported by the plugins and if you want that).

btw: 10k players is pretty impressive =)
zip

 

I'm not quite sure what you're trying to say here. What do you mean by "the plugin will just append the xml files until it finds the correct one"?

Searching for the right file is as simple as making the string: "group/player.inventory.version".

 

Flatfile! WOO

 

My preference is flat files, believe it or not, unless it is a plugin loading data all the time (LogBlock). As it is inventory plugins only load/save data on world changes, so the difference would be negligeble unless you had quite a few players changing worlds all the time. The only reason I even introduced mySQL into MultiInv is because of the amount of requests for it. Just so that you know, my knowledge of mySQL is really limited to just the grabbing and putting of data in a table, I use PhpMyAdmin on my server for all the database editing/creating, so sometimes bug fixes to the mySQL side of things can be a bit slow as I figure out the commands.

 

You have a folder with 10k xml files in, and need to find let's say player "zipron". What has to be done to find that file?

 

To efficiently read that you would need to make a lot of index files for all the data, which could effectively if set up properly be more optimized per the use than MySQL, however for the most part is not.

OP: MySQL is by far more efficient if used properly.

 

Yes, index files are the solution, and if you set up a system like that, guess what you have? a very simple database =) (do I read here: "use MySQL!"??)

 

Yeah, we do have about 7 worlds with a lobby world where all portals are, and there are quite a few people changing worlds all the time, I would guess that we have about maybee 15 people every few second changing world.
But the inventory is shared between all worlds except the survivalgames world that has its own, and its portal is not used much every second like the others are.

You mean like if the plugin wants to find /plugins/worldinventories/world/zipron.inventory.xml it has to look through all the files from top to bottom untill it finds it, and therefore isnt very efficient? (are we talking micro-seconds or what? )
You're taking about index files, would the plugin be able to set those up?
And would MySQL be faster/more efficiant, as it has to connect to something else, lookup and answer? (even tho its localhost)

 

No, no index files are created, at least, not by your plugin. Ofcourse your file management system (NTFS, ext4,..) organises files in a very efficient way (much better than it use to be with FAT). BUT it are still flatfiles. You should see a database as a collection of many manu flatfiles orginised in tables, and than in records. The difference with a database is that it's optimized for data storage, quickly search stuff,.. you can test this "easy": create 10k flatfiles and 10k records in a database, use your OS search option to look up f.e. zipron.inv.xml and wount how long it takes, than run a query on your database like "SELECT * FROM inventories WHERE username = 'zipron'" and compare, database is much faster.

About localhost: hosting your database on the same machine your server is on is the best thing you can do. This way you don't have to use your network or the internet to gather data, everything is on the same machine = much faster =)

And yes, we're talking about microseconds per file, but if there are 15 people every second, it does slow down your server (MySQL will do this too, nothing is as fast as light, but it is faster)