MySQL causing TPS drops.

This occurs whenever I try to access a value using ResultSets with PreparedStatements.
It also occurs whenever I try to update a value, the tps drop when I spam something (for example, updating a value about 5 times to simulate an environment of a big server with a lot of people updating values), the TPS drops by 4.

I heard I could use threading for this, though I'm not sure how I could make it so that the thread runs and returns a value that can be used for what I need it to be used for. If someone could point out a few ways to do this, it would be amazing.

 

Either run the whole thing on an async task (just schedule a BukkitRunnable asynchronously), or just the MySQL part, in which case you need to use some sort of callback mechanism Neilnet

 

Please explain because I don't understand this whole async thing.

 

Neilnet async runnables work just like normal runnables, you create an instance of a BukkitRunnable - either anonymous or a class that extends it - and call (for example) runTaskAsynchronously(args) instead of runTask(args).

A callback is just a piece of code you intend to run as soon as the async task is done. Bukkit uses Callable and Future for it. Personally I prefer using an interface Callback, which I implement pretty much like a BukkitRunnable

Code:java

public interface Callback
{
    public void execute();
}

And a sample implementation

Code:java

Callback cb = new Callback()
{
    public void execute()
    {
        // TODO: code
    }
};

In which case the runnable would simply invoke cb.execute() after it is done, thus 'calling back' on the previously defined code. Of course, you can add parameters to the execute method in order to pass values to the piece of code you put in the execute() body.

[edit] Do note that you need to schedule a sync task in order to get back to the main thread!

 

Just make methods that encapsulate the entire work of your method and then call them asynchronously:

Code:java

public void doYourSQL(/* params */) {
    //...
}

Code:java

getServer().getScheduler().runTaskAsynchronously(/* plugin*/, () -> doYourSQL(/* params */));
//OR
getServer().getScheduler().runTaskAsynchronously(/* plugin*/, new Runnable() {
    @Override public void run() { doYourSQL(/* params */); }
});

 

Things like

Code:

            getServer().getScheduler().runTaskAsynchronously(this, new Runnable() {
                public void run() {
                    openConnection();
                }
            });

Give me npe errors :/

 

Then something is null. Are you executing this in onLoad?

 

Yep, I figured it out. I had a check when there was that async thing going on. I understand this fully.

Thanks everyone!

But say if the mySQL connection is still connecting on an async thread and then someone joins the game and it uses another async thread whilst mySQL is connecting. Will I get a huge error?

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

 

1Rogue Neilnet
I find that using a SQL Queuing system (using 1 thread) has worked better for me,
Instead of having to allocate a new thread each time, which can take a while.

 

Please explain.

 

So having a ConcurrentList<String> on the main thread, then having a add to queue method, then in the new thread, I put a while loop, then loop through the list each time, and query the DB, then I personally added a Thread.sleep(500L); so the server doesnt use heaps of CPU on looping through

 

Sounds really complex :/ Any way I could make it so I don't get a massive error when the mySQL database is not open? Should I just add a boolean that gets enabled when MySQL is connected and disabled when MySQL is disabled so I just have if statements in those async methods?

 

http://docs.oracle.com/javase/tutorial/essential/concurrency/

Read it all.

 

Bukkit's scheduler (and things like ScheduledExecutorService) are already a thread pool, they don't spawn new threads.