Mysql Cuses Lagg

Discussion in 'Plugin Development' started by matanrak, Aug 4, 2014.

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

    matanrak

    Every time i try to add tokens or get tokens from mysql it lags the server... anyway to remove the lagg?

    code:
    Code:java
    1.  
    2.  
    3. public int getTokens(Player player) {
    4. try{
    5. int tokens = 0;
    6. openConnection();
    7. PreparedStatement sql = connection.prepareStatement("SELECT tokens FROM `player_data` WHERE player=?;");
    8. sql.setString(1, player.getName());
    9. ResultSet result = sql.executeQuery();
    10.  
    11. result.next();
    12. tokens = result.getInt("tokens");
    13.  
    14. return tokens;
    15. } catch (Exception e) {
    16. e.printStackTrace();
    17. closeConnection();
    18. return 0;
    19.  
    20.  
    21. } finally { closeConnection(); }
    22.  
    23.  
    24. }


    Code:java
    1.  
    2. public int addTokens(Player player , int Tokens) {
    3. try{
    4. int tokens = 0;
    5. openConnection();
    6. PreparedStatement sql = connection.prepareStatement("SELECT tokens FROM `player_data` WHERE player=?;");
    7. sql.setString(1, player.getName());
    8. ResultSet result = sql.executeQuery();
    9.  
    10. result.next();
    11. tokens = result.getInt("tokens");
    12. PreparedStatement loginsUpdate = connection.prepareStatement("UPDATE `player_data` SET tokens=? WHERE player=?;");
    13. loginsUpdate.setInt(1 ,tokens + Tokens);
    14. loginsUpdate.setString(2, player.getName());
    15. loginsUpdate.executeUpdate();
    16.  
    17. loginsUpdate.close();
    18. sql.close();
    19. result.close();
    20.  
    21. return tokens;
    22. } catch (Exception e) {
    23. e.printStackTrace();
    24. closeConnection();
    25. return 0;
    26.  
    27.  
    28. }
    29.  
    30.  
    31. }
     
  2. Offline

    Rocoty

    Run database calls asynchronously
     
    masons123456 likes this.
  3. Offline

    matanrak

    ok i tried this but it dose not work.
    code:
    Code:java
    1.  
    2. public static int getTokens2(final Player player){
    3. int tokens = 0;
    4. Bukkit.getScheduler().runTask(plugin, new Runnable(){
    5.  
    6. public void run() {
    7. int tokens = 0;
    8. openConnection();
    9. try{
    10.  
    11.  
    12. PreparedStatement sql = connection.prepareStatement("SELECT tokens FROM `player_data` WHERE player=?;");
    13. sql.setString(1, player.getName());
    14. ResultSet result = sql.executeQuery();
    15.  
    16. result.next();
    17. tokens = result.getInt("tokens");
    18.  
    19. } catch (Exception e) {
    20. e.printStackTrace();
    21. closeConnection();
    22. return;
    23. }
    24. }
    25. });
    26.  
    27. return tokens;
    28. }
     
  4. matanrak that's still sync, use one that contains the word "async" ;)
     
  5. Offline

    matanrak

    can u show me?

    Like this?
    Code:java
    1.  
    2. public static int getTokens2(final Player player){
    3. int tokens = 0;
    4. Bukkit.getScheduler().runTaskAsynchronously(plugin, new Runnable(){
    5.  
    6. public void run() {
    7. int tokens = 0;
    8. openConnection();
    9. try{
    10.  
    11.  
    12. PreparedStatement sql = connection.prepareStatement("SELECT tokens FROM `player_data` WHERE player=?;");
    13. sql.setString(1, player.getName());
    14. ResultSet result = sql.executeQuery();
    15.  
    16. result.next();
    17. tokens = result.getInt("tokens");
    18.  
    19. } catch (Exception e) {
    20. e.printStackTrace();
    21. closeConnection();
    22. return;
    23. }
    24. }
    25. });
    26.  
    27. return tokens;
    28. }


    i am testing this right now

    did not work

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  6. Offline

    matanrak

    when i test the getTokens2, it just gives me errors...
    can you take a look at it and check whats wrong

    anyone?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  7. Offline

    fireblast709

    matanrak Post the stacktrace (errors). Some possible causes for errors are:
    • plugin being null
    • connection being null
    • player being null (doubtfull though, you would have to explicitly call it with null)
    • you don't check if there even is a result. As in, you should check whether
      Code:java
      1. result.next();
      returns false. This would indicate that there is no row in your table satisfying the WHERE clause (in other words, no results). Attempting to get a value from a nonexisting row will throw an error.
    • You seem to somehow reuse connection in an asynchronous environment. You cannot guarantee the connection to be open at all times, mainly because when we start two queries, the first can close it while the second is just about to prepare a statement -> no connection, thus errors.
     
  8. Offline

    matanrak

    Ok i thanks i fixed that, but now its very slow and takes about 3 sec to add or check for tokens...
     
  9. Offline

    GameplayJDK

    matanrak I think you can't do much about the speed of your connection to the server or the time it takes to query the database.. :/
     
  10. Offline

    fireblast709

    matanrak well yes that's because you query a remote service.
     
  11. Offline

    matanrak

    Ok i got a way to fix the speed of adding and checking for tokens!
    i removed the open and close connections and make a repeating task that every 10 sec opens a connection.
    do you think this is a good or a bad idea?
    it makes it faster but lags the server a bit

    how can i speed up the open connection

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  12. Offline

    GameplayJDK

    Open the connection when the server starts (onEnable). If you aren't already doing that..
     
  13. Offline

    matanrak

    how can i keep the connection always open?

    yaw but if i make a change to a value it wont change right

    ok i changed my code a bit!
    but every time i set tokens if i don't open connection it will set them to 0

    Code:java
    1.  
    2. public synchronized int addTokens(Player player , int Tokens) {
    3. try{
    4. int tokens = 0;
    5.  
    6. PreparedStatement sql = connection.prepareStatement("SELECT tokens FROM `player_data` WHERE player=?;");
    7. sql.setString(1, player.getName());
    8. ResultSet result = sql.executeQuery();
    9.  
    10. result.next();
    11. tokens = result.getInt("tokens");
    12. PreparedStatement loginsUpdate = connection.prepareStatement("UPDATE `player_data` SET tokens=? WHERE player=?;");
    13. loginsUpdate.setInt(1 ,tokens + Tokens);
    14. loginsUpdate.setString(2, player.getName());
    15. loginsUpdate.executeUpdate();
    16. loginsUpdate.close();
    17. sql.close();
    18. result.close();
    19.  
    20. return tokens;
    21. } catch (Exception e) {
    22. e.printStackTrace();
    23.  
    24. return 0;
    25.  
    26.  
    27. }
    28.  
    29.  
    30. }


    every time i use openconnection() in the get tokens or add tokens there is a delay of about 5 sec, and if i only open the connection at enable it wont for after i will try to add tokens to it and set it to 0

    any ideas how i can auto open connection? whats the best way?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  14. matanrak Can you post your full code please?
     
  15. Offline

    matanrak

    i just want to know how to auto update the mysql connection
     
  16. matanrak When getting the mysql connection, check if if it's closed. If not, reopen it.
     
  17. Offline

    mythbusterma

    AdamQpzm matanrak

    Alternatively, send a dummy query to keep the connection open, or use MySQL's configuration to set the timeout extremely high (I'd recommend the first, as it handles broken connections better).
     
  18. Offline

    matanrak

    can you please show me how?
     
  19. Offline

    fireblast709

    matanrak Connection#isValid(int timeout)
     
  20. Offline

    mythbusterma

    matanrak

    Use a task that runs every 30 seconds or so that runs "SELECT 1"
     
  21. Offline

    matanrak

    yaw but every time i add tokens for some reason it closes the connetion
     
  22. Offline

    matanrak

    XD i messed the post up it looks like a quote

    I tried async but it wont work....
    code:
    Code:
            private static int tokens = 0;
            public static int getTokens2(final Player player) {
                Bukkit.getScheduler().runTaskAsynchronously(plugin, new BukkitRunnable() {
                    @Override
                    public void run() {
               
                       
                        try{
                   
                            PreparedStatement sql = connection.prepareStatement("SELECT tokens FROM `player_data` WHERE player=?;");
                            sql.setString(1, player.getName());
                            ResultSet result = sql.executeQuery();
                           
                            result.next();
                            tokens = result.getInt("tokens");
     
                   
                        } catch (Exception e) {
                            e.printStackTrace();
                       
                            return;
                        }
                       
                       
                       
                       
                       
                       
                    }
                });
                return tokens;
            }
            
    any ideas?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
Thread Status:
Not open for further replies.

Share This Page