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

Question

I made a bukkit plugin with mysql on it and i need to know why i am having lag whenever this code runs i run the server on my system and the mysql server with hostgator heres my code

    openConnection();
                try{
                    int level1 = 0;

                    if(playerDataConatinsPlayer(p)){
                        PreparedStatement sql = connection.prepareStatement("SELECT level FROM `player_data` WHERE 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=? WHERE 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 INTO `player_data` values(?,0,1,0);");
                        newPlayer.setString(1, p.getName());

                        newPlayer.execute();
                        newPlayer.close();
                    }

                }catch(Exception e1){
                    e1.printStackTrace();
                }finally{
                    closeConnection();
                }

here is my openconnection method

    public synchronized static void openConnection(){
    try{
        connection = DriverManager.getConnection(""); //i know its empty cause i dont wanna give that info out
    }catch(Exception e){
        e.printStackTrace();
    }
}

heres my closeconnection

public synchronized static void closeConnection(){
    try{
        connection.close();
    }catch(Exception e){
        e.printStackTrace();
    }
}

Answer 1

There are a few things you can do to speed up your queries latency:

• If your app is query intensive use persistent connections and keep them open instead of opening a new connection every time you need to access the database.

• Run the MySQL server locally to speed up connection times.

• Index the search fields of your tables (e.g. player on player_data) to have the search run faster.

• Run the MySQL server on a powerful, dedicated machine with SSD drives and lots of RAM, and set the proper parameters on my.cnf (worker threads, max processes, max number of connections, memory limit, buffer sizes) to make use of that RAM and processing power and speed up search and processing times. Things like this question and answers may help you with the memory settings, but the best you can do is your own, exhaustive, online research and testing. Do your homework!

• Use some kind of caching system to speed up reading (like memcached).

• If your app is data intensive and has to support a huge number of connections, get a higher bandwidth or even consider setting up a cluster to balance the load.

• Reduce the number of queries! You don't need to query the database twice to increase the level!

Try:

if (playerDataContainsPlayer(p)){

    PreparedStatement levelUpdate = connection.prepareStatement(
        "UPDATE player_data SET level=level+1 WHERE player=?;"
    );
    levelUpdate.setString(1, p.getName());
    levelUpdate.executeUpdate();
    levelUpdate.close();
    sql.close();

} else {
    ...
}