Queue SQL Queries so they don't hang the server using a loop

Discussion in 'Plugin Development' started by MistPhizzle, Dec 29, 2013.

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


    Hello, so I am working on a ban plugin that runs on MySQL / SQLite. I am building a converter into the plugin so you can convert from MySQL to SQLite and vise versa. Now, my problem is, some of the queries that are run are huge on the importing side. My problem isn't with getting SQL / Importing to work, as that works fine. My problem is the load is a bit too much for the server (In a test run I am running 500 different queries). How would I get it so that it runs just enough queries at a time to maintain efficiency and performance.

    How Exporting Works:
    1. Plugin pulls data from your current SQL Engine (sqlite / mysql)
    2. Plugin Saves all data to config.
    How Importing Works
    1. Plugin pulls all data from config.
    2. Plugin Saves all data to database.
    So with the import, I am pulling from my config like so:
    2. FileConfiguration data = plugin.getExportedDataConfig();
    3. if (data.get("players") != null) {
    4. for (String player: data.getConfigurationSection("players").getKeys(false)) {
    5. String ip = data.getString("players." + player + ".ip");
    6. DBConnection.sql.modifyQuery("INSERT INTO eb_players (player, ip) VALUES ('" + player + "', '" + ip +"');");
    7. }
    8. }

    Again, I would like to queue up that data so the server will only run it in chunks. All data is gathered just fine. Thanks in advance.
  2. Offline


    If you've already collected all the data, you should b able to use an asynchronous task to do the inserts.

    The exporting could be async too, I think, if all it does is save to a config file.
  3. Offline


    * you could use a prepared statement instead: don't know how much the actual benefit will be, but there will be benefit, especially if you run it in a loop
    * you could run the complete database stuff in a seperated thread to not hang the main thread of the server in the mean time (however you will have to handle plugin disables/reload/server stops (maybe remember where you had to interrupt your import and stop those async tasks) and maybe usage of the data which is meant to be added to the database -> which would also be the case if you "only" split it up into several "chunks": you could for example fetch those data from the memory/configuration file instead of querying the database, while the import process is running)
    * to split your queries up into several parts you could first put all your data in some sort of array or list or queue (linkedlist) and then run a repeating (async) task which gets/pops only a certain amount of the data from that queue/list per run
  4. Offline


    How would I toss something like this into a LinkedList and be able to retrieve all the data? I don't have much experience working with LinkedLists, so any help would be great.

    Config Example:
        bandate: 2013/12/28 05:33:50
        banlength: 259200
        unbandate: 2013/12/31 05:33:50
        bannedby: TestMod
        reason: Test Reason
  5. Offline


    You go through your config like you are doing it above and read the data out of it and maybe store it in some object like:

    class BanData {
      final String playerName;
      final String date;
      final long length;
      final String unbandate;
      final String bannedBy;
      final String reason;
      BanData(String playerName, String date, long length, String unbandate, String bannedBy, String reason) {
        this.playerName = playerName;
        this.date = date;
        this.length = length;
        this.unbandate = unbandate;
        this.bannedBy = bannedBy;
        this.reason = reason;
    and then add this created BanData object to the LinkedList, for each key/playername in the config:
    final LinkedList<BanData> data = new LinkedList<BanData>();
    for (String playerName : configsection.geKeys(false) {
    BanData playerData = new BanData(read the arguments from the configsection);

    or: you could also only put the playerNames into the linkedlist and read the data later in your async repeating task from the config

    in your async repeating task you then could do something like this:

    int counter = 0;
    while (counter < 5) {
    BanData next = data.pop();
    DBConnection.sql.modifyQuery(insert the data from BanData into the database); if (data.size() == 0) {
    //stop the repeating task
    break; // we are done
    MistPhizzle likes this.
  6. Offline


    This method is working wonderfully, it doesn't hang the server, but I am getting an exception when running the method.

    The Stack Trace:
    Show Spoiler

    [19:58:12] [Server thread/WARN]: [EtriaBans] Task #8 for EtriaBans v1.0.0 generated an exception
        at java.util.LinkedList.removeFirst(Unknown Source) ~[?:1.7.0_10]
        at java.util.LinkedList.pop(Unknown Source) ~[?:1.7.0_10]
        at com.etriacraft.EtriaBans.Methods$1.run(Methods.java:495) ~[?:?]
        at org.bukkit.craftbukkit.v1_7_R1.scheduler.CraftTask.run(CraftTask.java:58) ~[Spigot.jar:git-Spigot-1219]
        at org.bukkit.craftbukkit.v1_7_R1.scheduler.CraftScheduler.mainThreadHeartbeat(CraftScheduler.java:345) [Spigot.jar:git-Spigot-1219]
        at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:583) [Spigot.jar:git-Spigot-1219]
        at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:259) [Spigot.jar:git-Spigot-1219]
        at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:540) [Spigot.jar:git-Spigot-1219]
        at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:446) [Spigot.jar:git-Spigot-1219]
        at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617) [Spigot.jar:git-Spigot-1219]

    And here is the code:
    3. importCurrentMuteTask = Bukkit.getServer().getScheduler().scheduleSyncRepeatingTask(plugin, new Runnable() {
    4. @Override
    5. public void run() {
    6. int counter = 0;
    7. while (counter < 10) {
    8. if (mute.size() == 0) {
    9. Bukkit.getServer().getScheduler().cancelTask(importCurrentMuteTask);
    10. }
    12. Mute cMute = mute.pop();
    14. DBConnection.sql.modifyQuery("INSERT INTO eb_mutes (player, mutedate, mutelength, unmutedate, mutedby, reason) VALUES ("
    15. + "'" + cMute.getPlayer() + "', "
    16. + "'" + cMute.getDate() + "', "
    17. + cMute.getLength() + ", "
    18. + "'" + cMute.getUnmuteDate() + "', "
    19. + "'" + cMute.getMutedBy() + "', "
    20. + "'" + cMute.getReason() + "');");
    21. counter++;
    22. }
    23. }
    24. }, 0, 40);

    The import is working just fine, I'm just not sure where the error is coming from.

    Edit: Nevermind, forgot to add "break;" when the List is cleared. Thanks for all the help!
Thread Status:
Not open for further replies.

Share This Page