java - Why am i getting mysql lag on my minecraft bukkit plugin? -

java - Why am i getting mysql lag on my minecraft bukkit plugin? -

i made bukkit plugin mysql on , need know why having lag whenever code runs run server on scheme , mysql server hostgator heres code

openconnection(); try{ int level1 = 0; if(playerdataconatinsplayer(p)){ preparedstatement sql = connection.preparestatement("select level `player_data` player=?;"); sql.setstring(1, p.getname()); resultset result = sql.executequery(); result.next(); level1 = result.getint("level"); preparedstatement levelupdate = connection.preparestatement("update `player_data` set level=? player=?;"); levelupdate.setint(1, level1+1); levelupdate.setstring(2, p.getname()); levelupdate.executeupdate(); levelupdate.close(); sql.close(); result.close(); }else{ preparedstatement newplayer = connection.preparestatement("insert `player_data` values(?,0,1,0);"); newplayer.setstring(1, p.getname()); newplayer.execute(); newplayer.close(); } }catch(exception e1){ e1.printstacktrace(); }finally{ closeconnection(); }

here openconnection method

public synchronized static void openconnection(){ try{ connection = drivermanager.getconnection(""); //i know empty cause dont wanna give info out }catch(exception e){ e.printstacktrace(); } }

heres closeconnection

public synchronized static void closeconnection(){ try{ connection.close(); }catch(exception e){ e.printstacktrace(); } }

there few things can speed queries latency:

if app query intensive utilize persistent connections , maintain them open instead of opening new connection every time need access database.

run mysql server locally speed connection times.

index search fields of tables (e.g. player on player_data) have search run faster.

run mysql server on powerful, dedicated machine ssd drives , lots of ram, , set proper parameters on my.cnf (worker threads, max processes, max number of connections, memory limit, buffer sizes) create utilize of ram , processing powerfulness , speed search , processing times. things this question , answers may help memory settings, best can own, exhaustive, online research , testing. homework!

use kind of caching scheme speed reading (like memcached).

if app info intensive , has back upwards huge number of connections, higher bandwidth or consider setting cluster balance load.

reduce number of queries! don't need query database twice increment level!

try:

if (playerdatacontainsplayer(p)){ preparedstatement levelupdate = connection.preparestatement( "update player_data set level=level+1 player=?;" ); levelupdate.setstring(1, p.getname()); levelupdate.executeupdate(); levelupdate.close(); sql.close(); } else { ... }

java mysql minecraft bukkit