Saving a player's level / EXP to MySQL

Hello everyone ^-^

I was wondering how I would store a players level and EXP to a MySQL table.

Thanks in advance,

- Myles

(Have a good Christmas / New Year!)

 

@macproman();

First of all, don't take this the wrong way, but justify your use of SQL. It is (in almost all usage cases) slower than using the Configuration API, and it is significantly more difficult to use. If you're worried about speed, @Skionz has developed a library he calls "DataAPI" that performs 2x as fast as the Configuration API (supposedly, which is already pretty quick to begin with).

The way you would do this is using the JDBC, requesting an SQL connection (hopefully only using one connection, or pooling them). Then, using PreparedStatements, request information on a separate thread and either poll the main thread (by submitting a runnable), or using a callback to the main thread (again, using a runnable).

 

@macproman(); My API is faster because it doesn't parse comments or create nice looking lists and such. It simply creates a file with no lines in-between such as:

Code:

Stuff: true
Stuff2: 51
Stuff3: Stuff

If you want a nice looking file use Bukkit's built in YAML API. Saving to the disk is pretty fast, at least fast enough that nobody will notice the delay. I believe SQL on the other hand is pretty slow. If I remember correctly it took around 0.6 seconds to execute a query with PHPMyAdmin. This is very slow although I am not sure if it is the same with Java. I have never done a speed test.

 

SQL is highly functional and is extremely easy to work with. YAML is not faster then SQL by any means. If you are going to use files, at least serialize rather then save strings to later "decode" and load.


If you need help with MySQL, there are TONS of tutorials all over the internet. Since your using playerdata, i highly recommend you use index's to further increase retrieval speed. You could also look up simple optimizations for MySQL. It has TONS of hidden features nobody ever bothers to check out.

Tutorials:
http://zetcode.com/db/mysqljava/
http://www.vogella.com/tutorials/MySQLJava/article.html

 

Thank you both.

 

@bob7

Sorry, but SQL is certainly not faster than YAML in any usage case that could reasonably be applicable to a Plugin. Where are you getting that statistic from? SQL not only requires querying a database file (usually at least 10ms for the smallest tables), and network latency (at least 4 ms if it's not on the same machine). Where as the usual query time for a HashMap (the backing of FileConfiguration) is almost always FAR less than 1 ms. Also, explain what you mean by "serialize," any String operations he performs are still going to be orders of magnitude faster than querying a database.

 

Have some benchmarks.

 

Having a single YAML file to store over 5,000 members - then load it to memory is a massive use of resources.. I could easily cache my entire MySQL table to memory aswell. So i'm not exactly sure what your basing the benchmarks on...

 

@bob7 Benchmarks are based on amortized loading time (I didn't even include the connection and statement execution in the timing). For optimal memory usage (which means less is better) and fast access, YAML seems to be the right one for the job.

What many people seem to overlook, is that all YAML doesn't have to be stored in a single file. You can store it per player, load it when they join and save it when they quit. That way you minimize RAM usage while enjoying the blazingly fast IO (compared to MySQL, even if you move it off-thread you will 'suffer' the delay).

 

His benchmarks are based upon common sense, and demonstrate that in a usage case much larger than 5000 members YAML is approximately a full order of magnitude faster in any usage case that is applicable to a Bukkit plugin.

Why can't you admit it is slower in almost any usage case EVEN WHEN you ignore overhead? Do you feel some personal need to defend SQL? It's not an optimial solution in any usage case that is applicable to any Minecraft server.

P.S. You mention memory usage? Loading the YAML file and storing it's contents into a HashMap takes a lot more memory than running an SQL server, just saying.

 

If your going to load straight from a file, why not use serialization? It's super fast and loads directly to an object. I simply see YAML as a configuration system, rather then a IO management system.

I'm defending SQL because it's much more manageable then YAML. Using YAML means you have one key - nothing more. You can't search by values, you can't have multiple keys (Name, UUID, IP), only by getting the file.. It simply limits your accessibility. Not to mention some file systems don't allow thousands of files per folder..

So maybe YAML beats MySQL in "loading", or fetching by a primary key. But GL taking all the players and listing them by their money or whatever your saving in less then 1-5MS.

If your going to use your IO to ONLY load player files when they login, then use serialization and load it directly to an object. Or at LEAST use JSON. Leave poor YAML to readable configurations.

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

@bob7

I'm willing to bet copying and sorting HashMaps takes far less time than reading in sorted data from SQL does, but I'll have to benchmark that sometime.

 

Ok but you're completely missing my point. SELECT * -> add to hashmap. Boom. All my database info loaded to a hashmap in a few MS vs's a massive LOOP and loading 5k YAML files. We're talking about IO, not memory. It's like comparing a ramdisk to a harddrive here.

 

@bob7

In that case, maybe.

 

yaml is often faster than mysql

 

I wonder how Minecraft manages their player data in the world folder .

I never said you couldn't . My main point was that file IO is faster than using MySQL, YAML is just a simple example of that. I bet if you use implement serialization properly (like, write Maps yourself and such - you would be amazed how much faster it becomes) then you should be able to lower that time even more.

My benchmarks show that YAML is faster nonetheless, even if you still have to load it from the file .

 

Minecraft loads serialized player files that fetch only by the UUID.. But this is really all they need. GL fetching a file by a player's name, or by what's in their inventory


According to my tests, SQL is faster in certain cases.

-- Only on Localhost, i'm sure outside MySQL would be 100x slower --

Mass load:

I selected 500,000 users using an index'd MySQL, took around 10-20 NMS.

I then tried to loop through 500,000 and load them using SnakeYAML.. Well the CPU was completely maxed out and the servers main thread timed out so i couldn't even tell you.

Mass save:

Saving 500,000 users using a MySQL server (Batch + Pooler) took around a second

Saving 500,000 + creating new files crashed my java.


Single:
Selecting/saving a single user took a few MS, so SnakeYAML wins this race!


Basically the ONLY use for YAML is if your planning on either ONLY loading a file by it's name (Like when a user logs on). You cannot easily query through all the files searching for values, You cannot mass save, and You cannot mass load with ease.

My conclusion is i'd much rather have a highly functional SQL database then have to load new files for each player. I'm sure your IO operations are Async either way, so it doesn't really matter if MySQL grabs single players a few MS slower.

 

That's like using MySQL on the main thread - something no one should be doing in the first place .

Usually, mass loading / saving isn't required - and it's generally a waste of RAM, to be honest.

This is probably going to be a pretty nice bottleneck:

That aside, it adds complexity to your code which never is fun to fix .

Regardless, both storages have their purposes, their weaknesses and their strengths. Let's just keep it at that.