Solved Best way to store data

Hey all!

We're developing a minigame for a big server (~700 player average) which has to store quite some data per player on a high player base (lots of players will visit). We're kinda questioning what'd be the best way to store our data, which would include statistics for each player, ability upgrades for each player (leveling), and some extra info for each ability. Would SQL be a good choice, or player-based YML-files with additional files for the extra info? We know how to access each file in an efficient way and we try to open a connection only when it's really necessary.

We already had one developer make a minigame which was based on MySQL which caused huge lag when it got big, eventually leading to forcing us to close the minigame (very long story short).

In this case, for a plugin which has to store a lot for a big crowd, what would be our best way of storing the data?

 

You shouldn't run into lag issues with MySQL more than you would with yml. I would not blame lag issues on MySQL.

YML is good for smaller servers with less data, but if you are running one with 700 players you should use MySQL.

 

There are really three database options for you to choose from. Based on my own personal testing through my Playtime plugin (with abstract data management), I would personally recommend using SQL as a storage type. Depending on how often you would be read/writing to the database, you may either want SQLite or SQL in a database itself (PostgreSQL / MySQL etc).

If you choose SQLite, know that the reading/writing will be faster, but you'll absolutely need to using thread-safe methods when writing. i.e.

Code:java

/**
* Executes a query that can change values, and will lock the database for
* the duration of the query
*
* @param query The string query to execute
* @return 0 for no returned results, or the number of returned rows
* @throws SQLException Database locked from other program
*/
public synchronized int update(String query) throws SQLException {
    Statement stmt = con.createStatement();
    return stmt.executeUpdate(query);
}

You must have synchronized methods for SQLite, as SQLite locks the database upon writing. Mulitple reads at the same time is fine, but writing is not.


The alternative would be PostgreSQL/MySQL. If you want the best speed result from this, make sure the database is on the same box as the server itself. There is a lot more flexibility in MySQL at the expense of using a heavier solution.

In the end, it really depends on how heavily you will be reading/writing from the database. I would recommend SQLite if you're planning on not writing multiple times within the same second, otherwise I would recommend PostgreSQL/MySQL. Note that in using synchronization, you will essentially queue your writing queries to proceed one after the other, so many within the same moment will take longer to execute.

EDIT: I strongly discourage the use of flatfile/YAML storage for data. Configuration is fine, but data is slow and unreliable in comparison.

 

StevenMG and especially 1Rogue - thanks for helping! I guess MySQL is the way to go.

 

short question: where can i learn how to store in a SQL database and how i create a database?

 

Here:


(It is a link to a playlist, but it auto converts to a media)

 

thx, i'll try it out at home

 

As far as I know, a SQLite DB will be threadsafe unless you explicitly define a non-threadsafe threading mode upon connection creation.

See: http://www.sqlite.org/threadsafe.html
It says:

Frequently accessed DBs should be using MySQL, because it provides table-locking (MyISAM) or even row-locking if InnoDB engine is used.
But when using a DB for a bukkit server, the methods for executing queries also have to be implemented by using an asynchronous approach, to not block the main thread for too long.
Or it will start to lagg at some point if the DB traffic gets intense. (which might be the case on a 700 players server...)
So a proper implementation is vital here. Regardless if you use SQLite or MySQL.

 

Thanks for the info, did not know this about SQLite.

For almost any implementation using asynchronous management for the data is usually necessary. If you are dependant upon the real-time execution of this as well, then you will also need to create your own system for scheduling these tasks, since the Bukkit scheduler is based on tps (ticks) and if the tickrate slows down then so will the execution of any repeated tasks.