MySQL and Threads

I'm creating a economy plugin that i'm wanting to save all the players bank balances into databases, but the issue is that it slows the thread down every time i try and connect and save players funds. Is there a better way to approach this? or should I run all the SQL stuff on another therad? if so how?

 

Code:java

new BukkitRunnable() {
    @Override
    public void run() {
        // Stuff
    }
}.runTaskAsynchronously(plugin);

or

Code:java

new Thread(new Runnable() {
    @Override
    public void run() {
        // Stuff
    }
}).start();

 

Thanks xTrollxDudex ! The way i'm doing it is every time someone spends money it will update the database, but would that create a new thread every time someone buys/sells something or would it run within the same thread?

 

Yes. I haven't looked at the implementation for BukkitRunnable, however, if you create a lot of requests, use a fixed executor service, by

Code:java

Executor executor = Executors.newFixedThreadPool(5);
 
executor.execute(new Runnable() {
    @Override public void run() {
        // Yeah...
    }
});

Where 5 is the maximum amount of threads able to be used.

This is important because creating too many threads for each individual operation is called unbounded thread creation, because thread construction and teardown incur overhead, thread lifetime incurs overhead, pretty much everything about threads create overhead. I highly reccommend this if you are using MySQL heavily.

 

xTrollxDudex
Ah okay i thought something like that could happen, what could happen if i were to use more than 5 threads? is it possible just to have the one thread that i can hook into to do all of the SQL stuff instead of creating one every time something needs to be done in the database?

 

Sure, executors always are separate from the thread the created them. Also keep in mind thread safety- if you are sharing anything with the runnable outside the method that calls the runnable to be started, or sharing anything the runnable created, you've got thread safety troubles.

 

The way that I deal with SQL:

On Start, load the Database, storing all necessary values.
On update from Server, store to Object.
Intermittently, asynchronously merge updates from the Server into the Database, while drawing updates from the Database into the Server.
On Stop, do a final update ^.

https://gist.github.com/FerusGrim/fa6cef7654be8376c3da

The above is missing certain features, like actual SQL implementation. But it should adequately describe my method for updating the server and database, without causing lag.