Use separate thread SQL

Discussion in 'Plugin Development' started by Nogtail, Jan 15, 2014.

Thread Status:
Not open for further replies.
  1. Offline


    Using a separate thread for SQL is fine if you are just doing something like logging blocks that doesn't need an instant response but what if you needed to use it to make a response for a command or to decide what to do in an event listener?
  2. You could start a new thread.
  3. Offline


    That will probably cause a lag spike and freeze all players until the sql statement completes.
    I'm not an expert and I'd love to hear what someone elses opinions or other ways to do this would be, but heres an idea I had...
    If the information you are pulling from SQL only updates every couple hours, possibly save it to a config and then check the config when the event listener is called.

    Have you got a specific bit of code you are working with or are you just curious as to how it could be done?
    Because i'd really like to hear what some other's do for this kind of thing :)
  4. You could try [link] this library if you are managing player data with MySQL. It automatically selects MySQL/files for you and simplifies stuff so you don't need to write MySQL stuff yourself.
  5. Offline


    I was thinking of doing something tricky with each server caching the database every few hours but as I want this to be a "connection" between separate servers so that would need near live data so I don't think this would work.

    To get the response in the same code you would need to let the main thread sleep while the query executes, do any of you have an idea what some well made plugins that need a connection to SQL or some other service outside the server do in this situation?
  6. Offline


    There a basicly 2 things:
    * not giving instant response (useful if you only want to display some information you get from the database to a player for example, but you don't really care if the player gets the result printed a few (milli)seconds later than the time he triggered the command for it)
    * caching, if you really need instant access to the data (for example if you want to take action in an event depending on your data from the database)

    Not giving instant response example: printing some information to the player (pseudo code structre)

    start/inform async task / thread:
    -> (async) getDataFromDatabase()
    -> when you have gathered your needed data from the database continue sync again via BukkitScheduler.runTask()
    -> inside the sync task print the result to the player (if he is still online)

    Caching example: permissions are stored in a database and you need them to decide if the player can click a certain block

    start/inform async task / thread:
    -> (async) getDataFromDatabase()
    -> store the gathered data in memory (for example in a map: playerName/uuid -> permissions information
    check your cache if the permissions data of your player is available:
    -> if not then it is still loading in the background: maybe cancle the event (just in case) until the data finished loading
    -> if it is available: use that data from the cache to check if the player has the permission for clicking that block

    You can/should also combine scheduled response and caching where possible, for example to avoid having to get the data fresh from database every time the player triggers the command.
    You will then have to decide when to remove your data again from the cache (maybe when the player logs out, or after a certain time, or a mixture of both..).
    And if you have multiple servers/processes which modify the data in a shared database you will have to think about when and how often you maybe refresh your cached data / check for changes.
    CubieX and Nogtail like this.
  7. @blablubbabc

    This API loads data from a MySQL database when the player joins and saves data then a player leaves. Most of the data is stored in the MySQL database and caches it when a player joins.


    Sorry if the above chart is unclear.
  8. Offline


    I was planning on caching the data on player join as the only server that can change player data is the one the player is currently connected to so I can just modify the cache of that server to update the values. The data that is stored in the database stores a players bans, I don't want a player to be able to join then get kicked later if they are banned, is there anything I can do about this?
  9. Offline


    Hm, one solution would be to load all data about banned players on plugin start and store it in memory..
    Or you try to block all of the player's actions while the player is being check for bans in the background (similar on how AuthMeReloaded does it for player which didn't yet enter their password).

    Or, probably the best solution, use the AsyncPlayerPreLoginEvent: never used it myself before, but as it is asynchronous it should be no problem to (slightly) freeze the login process there (assuming you are not running in any timeouts of the minecraft client etc..).
    Nogtail likes this.
  10. Offline


    I think the async event would most likely be the way to go for bans as I can't imagine I would need to wait any longer than 10ms at absolute maximum for the database. I would like the plugin to be scalable so I don't think loading all data about banned players would be a great idea and I would rather not allow them to join for a second if they are banned.

    On the topic of commands and other features that need to return a result immediately how do large servers such as Hive return the result of /records immediatly?
  11. Nogtail
    The database might be hosted on the local machine, which would significantly reduce the response time
  12. Offline


    I doubt it would be hosted on the same machine though I would assume it would be hosted in the same datacenter, that may still mean a couple of ms ping and it would also be under a substantial amount of load.
  13. Nogtail
    Maybe try setting up a RAMDisk or something if you can and then have the DB setup locally on that? That would be pretty fast.
  14. Offline


    That could work but as the database grows it may reach a point of where it is too big for a RAMDisk or if the servers have to be hosted on separate machines.
  15. Offline


    Since i read this many times on the forums ... please stop saying "Setup a RAMDisk". This is not a bread and butter!

    Why some hosts are super fast while using a database? Cause they do not use the default values and actually know how to configure a sql server.

    It is actually not a problem having the database server running on the same host or remote as long as the connection is stable and efficiently used.

    A wrong usage of mysql and bukkit in general is that most authenticate -> query -> close the connection everytime they need something from the database or simply have designed their table or database badly.

    E.g. mostly MyISAM is used as default storage engine which is totally wrong to do when heavily updating one table since every insert or update will lock the whole table and everything has to wait for that query to finish before something else can write to it.

    That and executing the query (including authentication everytime) in the main thread slows everything down.

    Using pooled connections can help here IF thats a problem but for everything else the problem needs to be analyized first before some could give an answer.
  16. Offline


    Is there any well-written tutorial you know of that covers JDBC MySQL and threads?
  17. Offline


    Nogtail well there are but nothing is related to bukkit.

    What you have to understand is that bukkit has one thread and if an event happens it will be processed and process another event only after its finished doing whatever it is doing.

    If you connect and authenticate to a database in an event then the delay will increase by whatever it needed to connect and authenticate. This is ok if you have a plugin that manages something so it will not do it every tick but when you monitor block placement for example ... then you need to worry.

    If you write many things in one task then it will produce lag at some point when not optimized. Creating a seperate thread is doable but tricky since you cant use anything non-thread safe in there like the player object.

    You would then need to gather all information first then send it to the writer thread or something like that.

    Before you do all that search for connection pooling. Learn and write a class or use this

    If its still a problem then come back.

    Something worth reading:
    Nogtail likes this.
  18. Offline


    lycano I don't necessarily need a tutorial relating to Bukkit, just one that covers the basics of using multiple threads with SQL and returning the result to the main thread without making it sleep while the query is executing.

    Also, would you know the best way of "triggering" methods of the Bukkit API from a separate thread?
  19. Offline


    Nogtail you need to get to the root of the problem. You cant avoid that a single threaded process will wait for a nother process to finish before it continues processing other stuff.

    So what you want to do is reducing the time it will freeze the main thread.

    When we use mysql we do that by using Pooled Connections. This will at least avoid the need to authenticate everytime we do a query saves us time since this is a time intensive action (authenticate and create a connection).

    Triggering methods from a separate thread?
    Try to avoid that .. Some simply cant be used since they are not thread-safe. Also the more you get used to threads the more you will use them where you simply don't need to ... like in this case.

    In your OP you asked "is using mysql server bad for performance when you want to be able to do stuff depending on returned anwers"...

    Im thinking the following ...

    /getage username - display age of character

    This would probably do a query "SELECT * FROM playerdata WHERE dataKey="age""

    If you use pooled connections only the first query will be slow since it authenticates. It will leave an open connection behind which can be used later. But this time it will be faster than before since the connection will be reused.

    I believe you do not need to worry about "lags" in this case only if the database server needs more than 5 seconds for a reply which is nothing you can fix.

    If you need instant response you might want to prefetch data (if you can). Like in this case when the player connects fetch this data during player login event and store in an object.

    ... getAge();

    Could then be used later. And whenever you update the data for a player through a command remove that object. Next getAge() would then fetch its data from the database again.

    You weren't that specific but i guess thats something you want to do?
  20. Offline


    lycano I am planning on caching all the data which is fetched on a player logging in (not sure if I should cache resultsets or what yet...)

    I am aware that I can't execute a method from a separate thread but I was thinking of doing something like adding the resultset to a queue and checking the queue on each tick for the result of the command but that could mean commands come in at least a tick late.
  21. Offline


    Polling the server each tick would be very inefficient.

    If you cache the data just keep what you need not the whole Resultset.

    What i would try (this is yet untested but should work according to the docs) is to avoid calling network stuff in the main thread since it can slow down things as the main thread will wait for every action to finish.

    E.g. any query to a database should be done async so the main thread does not have to wait.

    You will of course run into a problem ... thread safety. How can you use any Bukkit API when you are running async thread? Well you can't directly. You can however schedule a synced task inside an Async task.

    So what i would do is do your mysql query in an async task and when its done call a synchronous task which will then call a specific custom event.

    Why that way? Well when you use callEvent everything will be either async or synced depending in which context it runs right? (and you should not run callEvent inside an async event anyways ...)

    So we need to make sure that the next step "doing stuff after getting the data" will be done synced (running in the next server tick). E.g. we want to teleport a player to another location or set his name or something ..

    To do so run a task in the next tick and let it call an event which you can listen to in your "ActionListener".

    This would then set data and whatnot and it would be thread safe and not lock the main thread during fetching data.

    Side note: fetching data is not really the problem this is usually fast but whenever the main thread has to wait for something depending on how long it has to wait players will notice it as lag since the server will process the next events after its done doing your stuff.

  22. Offline


    Besides calling a sync event when you got your results (how lycano suggests it), you could also let the initiator provide some sort of "callback" object/method, which gets called (sync) as soon as the result is available.

    -> start an async task / thread or provide an already running one your "request" (for example via a shared queue, which gets polled all the time in the background from the async database thread(s))
    -> the async database task executes your query and then calls the scheduler runTask to continue sync
    -> in that sync task you can either call a fixed method: printResultForPlayer(player, result)
    or your let the main thread provide a callback object (basicly like a runnable but it takes the result as argument for the run method) which gets called:

    public interface Callback<T> {
    public void onComplete(T result);

    getDataAndCall(new Callback<Data>() {
        onComplete(Data data) {
    async database task:
    Data data = getDataFromDatabase();
    // continue sync and run callback when done:
    Bukkit.getScheduler().runTask(yourPlugin, new Runnable() {
        public void run() {
            // either call some fixed method here, or let the "initiator" provide a method to continue at:
            // printResultForPlayer(player, result);  // fixed method example
            if (callback != null) {
                callback.onComplete(data);  // provided method example
    lycano likes this.
  23. Offline


    blablubbabc Is there any plugin that has a good example of this?
  24. Offline


    Whether or not you use threads, you won't get an instant response with MySQL. But if you don't use threads, and it's not instant, other stuff might freeze.
  25. Offline


  26. Offline


    I have looked over mcbans way... i would strongly recommend _not_ doing it this way.

    Also they name their requests that actually do the stuff "callbacks" which is not correct in this case. Their callbacks never return something they just do stuff and thats not a callback. Better name would be executor or something else that targets the area "do stuff".

    Why do i not like it the way mcban did it?

    They use a thread inside a thread and they do it for every command that use a "callback" ... you should really not do this unless you know for sure what you do. Those threads have to be cleaned in certain conditions like reloading the server and such and it is not that easy to coordinate such things and can cause problems besides its not very cheap to create a new thread.

    Create Requestor -> Requestor does create runnable -> Run runnable in a seperate thread ...

    Better way to do would be using BukkitRunnable and BukkitScheduler and create a thread manager so that you can lookup any running task per event and beeing able to cancel the runnable when its running is something you will need.

    Like you need to fetch data for chatevent ... simply schedule an async thread that will call a sync thread after it has done its mysql stuff. Everything will run under the Name "PlayerChatEvent".

    Later on you can build a good monitor around it and you could actually see "live" what happens during execution.

    blablubbabc i think that your previous suggestion was better ^^
    Checkout the kick command.. what will happen if you execute the command multiple times in a row for the same player?
    blablubbabc and Nogtail like this.
  27. Offline


    lycano Got any suggestions on plugins that do this correctly that have the source available? I find looking through the source of a plugin to be extremely useful to understand how things work.
  28. Offline

    Lolmewn Retired Staff

    What I did in Stats was making a new async thread when a player joins the server, which then loads the data from MySQL. As an extra little thingy, I made it 'sync' the MySQL data with the player data (which contains new stats such as lastjoin, joins and playtime). Then I have another thread which runs every 30 or so seconds which saves the player data. Did the player go offline? Remove the player from the PlayerManager after saving.
  29. Offline


    Lolmewn just curious why did you use a polling thread?

    Nogtail blablubbabc is right in saying "this is a real life example" but only if you know how it works. Since you do not know this i can't recommend using this source code as an example as they probably had other problems related to the web requests hence they starting a seperate thread (this is a wild guess).

    Suggestion? Well blablubbabc did give you the basics in his first post. I believe you can work with that.

    He also gave you a step by step example which is why is found his post very useful and liked it. You simply just have to read, understand and build it.

    It doesn't have to be pretty at first and if you do not just "copy paste" the code you could also use mcbans example ..

    Maybe i will find some time later this day and post an example since i really like the idea behind it.
  30. Offline


    I know the basics of what a callback is but can you suggest any tutorial on how to use them?

Thread Status:
Not open for further replies.

Share This Page