Welcome, guest! Please login or register.

    * Shoutbox

    RefreshHistory
    • hexedss1: Anyone would like to help on client side got a variable declartion Error
      August 15, 2018, 09:06:30 PM
    • SOUPSOUP: just get a vpn
      August 13, 2018, 06:44:28 PM
    • jme: _^_ will answer all of your javascript questions
      August 12, 2018, 07:11:00 PM
    • doomsale: and where is boxman?
      August 10, 2018, 03:16:59 AM
    • doomsale: Where can i play the Moparscape MMORPG with the Metallica instrumentals?
      August 10, 2018, 03:16:47 AM
    • doomsale: no longer clicking on doors and dummies at east bank
      August 10, 2018, 03:16:07 AM
    • doomsale: were all grown up now huh?
      August 10, 2018, 03:15:58 AM
    • _^_: hey everyone look at my graphs [link]
      August 06, 2018, 01:52:16 PM
    • braedyn: Someone pl0x help me out with PI Lottery!!
      August 03, 2018, 02:12:25 AM
    • Travas: p3n1s
      July 26, 2018, 09:46:33 PM
    • Krackkid: You up :eggplant:
      July 23, 2018, 06:18:17 PM
    • DeathsChaos9: no
      July 23, 2018, 03:53:31 PM
    • Emulous: Come check out Emulous: [link]
      July 22, 2018, 03:37:19 PM
    • Emulous:[link]
      July 22, 2018, 03:37:00 PM
    • Travas: ur mom
      July 21, 2018, 06:04:50 PM
    • YNRDAB: Watcha big ballers up too?
      July 20, 2018, 11:01:06 AM
    • dark250: i need help for coding client
      July 19, 2018, 08:27:37 AM
    • Travas: yes
      July 14, 2018, 02:19:44 PM
    • drubrkletern: lmao racist and sexist much?
      July 14, 2018, 01:11:52 PM
    • Cole1497: yeah i got you lil boat give me a second
      July 13, 2018, 03:19:35 AM

    Author Topic: MySQL Saving - Too many connection bs timeout zomg  (Read 2453 times)

    0 Members and 1 Guest are viewing this topic.

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    MySQL Saving - Too many connection bs timeout zomg
    « on: September 17, 2014, 07:59:28 AM »
    Okay, I'm saving my users every 100ms to a mysql database.

    I'm reusing the connection so after a while I'll get an error something along the lines 'max connections from ip reached' then I can't connect to the database anymore unless I open a new connection.

    I'm using Hibernate by JBoss to handle all my services.

    My save method:

    Code: Java
    1.     Session s = HibernateUtil.getSessionFactory().openSession();
    2.     s.beginTransaction();
    3.  
    4.     try{
    5.         Player p = HibernateUtil.getPlayer(s, username);
    6.         if(p !=null){
    7.             s.merge(this);
    8.         }
    9.     }finally{
    10.         s.getTransaction().commit();
    11.     }
    12.  


    What can I do?
    Runescape Gambling

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #1 on: September 17, 2014, 09:18:03 AM »
    Okay, I'm saving my users every 100ms to a mysql database.
    You're nuts.

    But uh, Im not sure how Hibernate works exactly, but why are you creating a new session every time you are saving a player? You say you are reusing the connection but then you create a new one for each save?  :confused:
    « Last Edit: September 17, 2014, 09:21:33 AM by Davidi2 »
    Runescape Gambling

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #2 on: September 17, 2014, 10:13:06 AM »
    Okay, I'm saving my users every 100ms to a mysql database.
    You're nuts.

    But uh, Im not sure how Hibernate works exactly, but why are you creating a new session every time you are saving a player? You say you are reusing the connection but then you create a new one for each save?  :confused:

    I am reusing the connection. Hibernate sorts all that stuff out in the background. Right now I'm not creating a new session. After a while I get some gay error message saying too much crap happening on 1 session! Then to fix that I need to disconnect and reconnect.

    It's not a problem with me sending a bunch of connections every 100ms (I've checked that by sleeping the thread every x).

    Is 100ms really crazy? Performance wise it looks perfectly fine.

    On localhost, everything is fine too. So I don't know what I should do about this webhost. 
    Runescape Gambling

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #3 on: September 17, 2014, 10:44:30 AM »
    I think it's a MySQL setting you can change, depending on your webhost permissions.

    And yes 100ms is insane. In fact there is no reason to save players at all unless they log out or the server will be going down. Unless you don't trust your base enough to assume it wont crash ;) (But even then with some error handling you should be able to save players on crash)

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #4 on: September 17, 2014, 02:10:05 PM »
    Fixed!

    I think it's a MySQL setting you can change, depending on your webhost permissions.

    And yes 100ms is insane. In fact there is no reason to save players at all unless they log out or the server will be going down. Unless you don't trust your base enough to assume it wont crash ;) (But even then with some error handling you should be able to save players on crash)
    Okay there was a setting for it. I've changed it to refesh the connection if the error ever came up again just incase though.

    In my application, people tend to xlog (even I do). I was thinking about having a cron job or batch updating or something but I didn't know if the object would still be alive. I'm not really losing anything, I'm just saving with my application cycle (not as bad as it sounds lol).

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #5 on: September 17, 2014, 02:36:54 PM »
    I assumed this was for RSPS, whether or not it is insane depends on the application I guess. If it is RSPS it is quite insane, especially as you get more players logged in :P

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #6 on: September 17, 2014, 02:56:46 PM »
    I assumed this was for RSPS, whether or not it is insane depends on the application I guess. If it is RSPS it is quite insane, especially as you get more players logged in :P
    Yes it is!

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #7 on: September 18, 2014, 11:18:04 AM »
    why would them x logging matter?
    I'm storing each player into a list. Once they close the connection they're removed from it.
    I'm using the stored objects for saving.

    OfflineAmbokile

    • Member
    • ****
    • Posts: 3,009
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #8 on: September 18, 2014, 11:26:12 AM »
    I think it's a MySQL setting you can change, depending on your webhost permissions.

    And yes 100ms is insane. In fact there is no reason to save players at all unless they log out or the server will be going down. Unless you don't trust your base enough to assume it wont crash ;) (But even then with some error handling you should be able to save players on crash)

    Power outage?

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #9 on: September 18, 2014, 11:40:23 AM »
    I think it's a MySQL setting you can change, depending on your webhost permissions.

    And yes 100ms is insane. In fact there is no reason to save players at all unless they log out or the server will be going down. Unless you don't trust your base enough to assume it wont crash ;) (But even then with some error handling you should be able to save players on crash)

    Power outage?
    Could be stupid but you could have a serialised backups, so if your server shutdown unexpectedly you could load the copies. Idk how people store states and their other stuff, don't wanna Google it.

    OfflineHcoJustin

    • Member
    • ****
    • Posts: 2,400
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #10 on: September 18, 2014, 11:52:36 AM »
    I think it's a MySQL setting you can change, depending on your webhost permissions.

    And yes 100ms is insane. In fact there is no reason to save players at all unless they log out or the server will be going down. Unless you don't trust your base enough to assume it wont crash ;) (But even then with some error handling you should be able to save players on crash)

    Power outage?
    That's what a UPS is for. If your data center doesn't have them you should switch immediately.

    OfflineJustin Bieber

    • Member
    • ****
    • Posts: 2,941
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #11 on: September 18, 2014, 01:59:32 PM »
    why would them x logging matter?
    I'm storing each player into a list. Once they close the connection they're removed from it.
    I'm using the stored objects for saving.
    So have a list of unsaved players?

    Offline-Nothing

    • Member
    • ****
    • Posts: 2,008
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #12 on: September 18, 2014, 02:05:17 PM »
    why would them x logging matter?
    I'm storing each player into a list. Once they close the connection they're removed from it.
    I'm using the stored objects for saving.
    Save their data before removing from the list, at the same place as the connection close is handled.

    OfflinePure_

    • Member
    • ****
    • *
    • Posts: 4,687
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #13 on: September 18, 2014, 02:11:27 PM »
    server ticks once 600ms
    players can do one thing per 600ms
    we save 100ms (6 times per second)
    that is your issue rofl
    i won the forum

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #14 on: September 18, 2014, 03:37:36 PM »
    server ticks once 600ms
    players can do one thing per 600ms
    we save 100ms (6 times per second)
    that is your issue rofl
    It's not a rsps tho. My app ticks every 100ms so I'm using that.

    OfflinePure_

    • Member
    • ****
    • *
    • Posts: 4,687
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #15 on: September 18, 2014, 03:53:53 PM »
    You said it was earlier, btw my bad, I meant 6 saves a tick.
    i won the forum

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #16 on: September 18, 2014, 04:00:24 PM »
    server ticks once 600ms
    players can do one thing per 600ms
    we save 100ms (6 times per second)
    that is your issue rofl
    It's not a rsps tho. My app ticks every 100ms so I'm using that.
    I assumed this was for RSPS, whether or not it is insane depends on the application I guess. If it is RSPS it is quite insane, especially as you get more players logged in :P
    Yes it is!
    Well that was confusing. It sounded like you were saying 'yes it is a rsps' lol

    OfflineJustin Bieber

    • Member
    • ****
    • Posts: 2,941
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #17 on: September 18, 2014, 04:00:53 PM »
    committing to the db every 100ms is batshit crazy whatever your appliation

    OfflineZymus

    • Member
    • ****
    • *
    • Posts: 7,267
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #18 on: September 18, 2014, 04:53:53 PM »
    Code: Java
    1. publicinterface PlayerRepository {
    2.  
    3.     void fetch(finalString name);
    4.  
    5.     void commit(final Player player);
    6. }
    7.  

    Code: Java
    1. publicclass SQLPlayerRepository implements PlayerRepository {
    2.  
    3.     public SQLPlayerRepository(finalConnection connection){
    4.         if(connection ==null){
    5.             thrownewIllegalArgumentException("connection must not be null");
    6.         }
    7.         if(connection.isClosed()){
    8.             thrownewIllegalStateException("connection must be open prior to using");
    9.         }
    10.         this.connection= connection;
    11.     }
    12.  
    13.     publicvoid fetch(finalString playerName){
    14.         // call fetch player stored procedure
    15.     }
    16.  
    17.     publicvoid commit(final Player player){
    18.         if(player ==null){
    19.             thrownewIllegalArgumentException("player must not be null");
    20.         }
    21.         finalString playerName = player.getName();
    22.         // get other variables, and send to commit player stored procedure
    23.     }
    24.  
    25.     publicvoid commitAll(final Set<Player> players){
    26.         if(players ==null){
    27.             thrownewIllegalArgumentException("players set must not be null");
    28.         }
    29.         for(final Player player : players){
    30.             finalString statementString ="EXEC commit_player ? ? ? ? ? ? ? ? ? ";// ideally, load this from a plain text file
    31.             finalPreparedStatement statement = connection.prepareStatement(statementString);
    32.             statement.setString(1, player.getName());
    33.             statement.setInt(2, player.getLocation().getX());
    34.             statement.setInt(3, player.getLocation().getY());
    35.             // ...
    36.             statement.executeUpdate();
    37.         }
    38.     }
    39.  
    40.     privatefinalConnection connection;
    41. }
    42.  
    « Last Edit: September 18, 2014, 05:01:22 PM by zyle1992 »

    OfflineAshi

    • Member
    • ****
    • Posts: 3,584
    • Thanks: +0/-0
      • View Profile
    Re: MySQL Saving - Too many connection bs timeout zomg
    « Reply #19 on: September 18, 2014, 05:15:17 PM »
    server ticks once 600ms
    players can do one thing per 600ms
    we save 100ms (6 times per second)
    that is your issue rofl
    It's not a rsps tho. My app ticks every 100ms so I'm using that.
    I assumed this was for RSPS, whether or not it is insane depends on the application I guess. If it is RSPS it is quite insane, especially as you get more players logged in :P
    Yes it is!
    Well that was confusing. It sounded like you were saying 'yes it is a rsps' lol
    Sorry lol, I was using my phone and got lazy typing. Was hoping you all got what I meant :D

    committing to the db every 100ms is batshit crazy whatever your appliation
    Why is saving every 100ms crazy though? It was much easier just to run it as a background service on my applications tick then scheduling a job for it. I don't lose anything.



    Code: Java
    1. publicinterface PlayerRepository {
    2.  
    3.     void fetch(finalString name);
    4.  
    5.     void commit(final Player player);
    6. }
    7.  

    Code: Java
    1. publicclass SQLPlayerRepository implements PlayerRepository {
    2.  
    3.     public SQLPlayerRepository(finalConnection connection){
    4.         if(connection ==null){
    5.             thrownewIllegalArgumentException("connection must not be null");
    6.         }
    7.         if(connection.isClosed()){
    8.             thrownewIllegalStateException("connection must be open prior to using");
    9.         }
    10.         this.connection= connection;
    11.     }
    12.  
    13.     publicvoid fetch(finalString playerName){
    14.         // call fetch player stored procedure
    15.     }
    16.  
    17.     publicvoid commit(final Player player){
    18.         if(player ==null){
    19.             thrownewIllegalArgumentException("player must not be null");
    20.         }
    21.         finalString playerName = player.getName();
    22.         // get other variables, and send to commit player stored procedure
    23.     }
    24.  
    25.     publicvoid commitAll(final Set<Player> players){
    26.         if(players ==null){
    27.             thrownewIllegalArgumentException("players set must not be null");
    28.         }
    29.         for(final Player player : players){
    30.             finalString statementString ="EXEC commit_player ? ? ? ? ? ? ? ? ? ";// ideally, load this from a plain text file
    31.             finalPreparedStatement statement = connection.prepareStatement(statementString);
    32.             statement.setString(1, player.getName());
    33.             statement.setInt(2, player.getLocation().getX());
    34.             statement.setInt(3, player.getLocation().getY());
    35.             // ...
    36.             statement.executeUpdate();
    37.         }
    38.     }
    39.  
    40.     privatefinalConnection connection;
    41. }
    42.  
    That's some cute code you have over there.

    I like the way Hibernate does things. Mapping is so awsum!

     

    Copyright © 2017 MoparScape. All rights reserved.
    Powered by SMFPacks SEO Pro Mod |
    SimplePortal 2.3.5 © 2008-2012, SimplePortal