MySQL Advice - When should I pull data from MySQL?

Discussion in 'Plugin Development' started by JTGaming2012, Mar 15, 2015.

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

    JTGaming2012

    So I have just recently learnt MySQL but I am trying to find the best way to pull data from the MySQL database. For example, currently I laod all the data from MySQL at once. However, this has a massive side effect: the server takes a while to restart. First it has to save all the data to MySQL upon disabling the plugin and then have to load all the data when enabling the plugin.

    Previously, I used to load individual player's data as they would join the server. However, this would freeze the server for about 5 seconds, which obviously wasn't any good for the other online players.

    Does anybody have any suggestions? Should I continue using my current method? Or is there anything else you could suggest to me?

    Thanks in advance!
     
  2. Offline

    1Rogue

    Just load any relevant data that you'll be referencing frequently for a Player when they join, and save it back to the database when they leave.
     
  3. Offline

    JTGaming2012

    @1Rogue But like I said, that freezes the server for about 5 seconds... If the other players are in the middle of a game it's gonna be really annoying
     
  4. Offline

    1Rogue

    You should be interacting with the SQL database on a different thread than bukkit's main thread.
     
  5. Offline

    Lolmewn

    Sounds like you are having the same issue as I was having with Stats. Some notes:
    - Either use a Connection pool (only when you need to do a shitton of stuff in your database) or make sure the connection stays alive (re-connecting takes quite a lot of time).
    - PreparedStatements everything. Not only does it make your code cleaner, it's also safer (against SQL injection for example) and suspected to be faster.
    - Take SQL stuff off of the main thread. Stats creates a new thread when a player joins, loads the data in there and afterwards merges the data gathered in the mean time with the data loaded. Usually players won't do much for the first 5 seconds anyway so you might as well skip the merging part and only start doing whatever it is you want to do when the player is done loading.
     
  6. Offline

    JTGaming2012

    @Lolmewn Thanks for the reply. But what do you mean by not using Bukkit's main thread? Are you suggestiong starting a new thread in the onEnable method that seperatly loads in the data (because at the moment, I load stuff in on the onEnable method)?
     
  7. Offline

    Lolmewn

    @JTGaming2012 Not in onEnable per-se. If you require data from the database when enabling the plugin, don't thread it. However, for any other SQL related stuff, I would launch a new thread which then loads the data. In my Stats plugin this happens when a player joins, for example. As for data saving - it does that in a repeating task every 10 seconds.
     
Thread Status:
Not open for further replies.

Share This Page