IMPORTANT: Connection issues

[Lapo]

PROBLEM
A few users have reported issues where clients seem unable to connect for some time during the server activity.
The symptom is that lots of sessions seem to go in timeout before they get a chance to send their login request.
Typically this happens with custom login-based Zones.

EXPLANATION
You should pay attention to an important thing which is multi-threading.
If you use a custom login procedure it is very likely that you will check user credentials against a database.
Please remember that accessing a database is typically a long running operation that can keep the current Event thread occupied for hundreds of milliseconds.

This means that many concurrent login requests might wait in line before they are handled if this process is not handled in parallel correctly.

SOLUTION
The solution is to adjust the number of threads in the SFS2X Event dispatcher so that many concurrent requests can be serviced in a timely fashion.

Of course it is also highly required that you benchmark your DB access code and make sure that the queries are optimized and executed in a reasonable amount of milliseconds.
A simple login/pass verification query should take no longer than 50-100ms at worst.

To learn all the details of thread tuning please read here:
http://docs2x.smartfoxserver.com/Advanc ... hreadPools

[ernivan]

hi

what is the right range number of threadPools ?


thanks

[Lapo]

It really depends on the traffic you have on your DB, speed of your DB queries etc.. it's not possible to provide a generic value.
You will have to profile your app a bit and make some basic calculations.
Example: if an average query takes 10ms one thread can handle 100 request per second.

If you have 500 DB hits/sec you will need at least 5-6 threads.

Hope it helps

[pauloamorimbr]

Hey there.

I'm having a issue here that may be connected to this one.

I have a db connection set on Zone configuration.

I start the server and everything goes well with no problem but after some time of inactivity - usually we see it happening on the next day morning - it looks like the db connection sleeps and can't execute a simple query for the login verification.

We have only 1 user trying this at the time as we are testing, it has nothing to do with processing delay or multithreading and it looke like the connection just won't come back until we restart the server.

Any idea on this?

thanks.

[Lapo]

Any errors in the logs of SFS2X?

[pauloamorimbr]

Nope...I only have the errors I caused when trying to acces the DB. It returns to me a Null object where I should have a ResultSet. It makes me believe the connection with DB is having some problems.

I'm guessing and trying to find out here if the DB server is going down during the night for any reason. It would explain the issue.

Is there a way to verify, from the extension, if the DB is down since the DB connection is configured on admin's zone manager? And if so, can I make it repair the connection?

[Lapo]

Sure it is very likely that the DB has a timeout for connections, and it shuts them down after a while.
We always recommend to keep the DB alive by setting a looping task in your extension that fires a simple query every in a while (1 every 1-2 hours or so). The query can be as simple as "SELECT 1"

hope it helps

[thanhbv]

Here is the error in log:

Code: Select all

ERROR [pool-1-thread-2] test.dao.Dao     - Can't getUidFromSession:
java.sql.SQLException: Already closed.
   at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
   at org.apache.commons.dbcp.PoolingDriver$PoolGuardConnectionWrapper.close(PoolingDriver.java:273)
   at com.smartfoxserver.v2.db.SFSDBManager.executeQuery(SFSDBManager.java:166)
   at test.dao.Dao.getUidFromSession(Dao.java:123)
   at test.LoginEventHandler.handleServerEvent(LoginEventHandler.java:23)
   at com.smartfoxserver.v2.extensions.SFSExtension.handleServerEvent(SFSExtension.java:242)
   at com.smartfoxserver.v2.entities.managers.SFSExtensionManager.dispatchEvent(SFSExtensionManager.java:762)
   at com.smartfoxserver.v2.entities.managers.SFSExtensionManager.dispatchZoneLevelEvent(SFSExtensionManager.java:687)
   at com.smartfoxserver.v2.entities.managers.SFSExtensionManager.handleServerEvent(SFSExtensionManager.java:880)
   at com.smartfoxserver.v2.core.SFSEventManager$SFSEventRunner.run(SFSEventManager.java:64)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)

Here is my workaround:
in test.dao.Dao.java#getUidFromSession:

Code: Select all

...
        String sql = "SELECT ...";
        try {
            ret = dbManager.executeQuery(sql);
        } catch (SQLException e) {
         LOG.error("Can't getUidFromSession: ", e);
         //FIXME workaround?
         try{
            ret = dbManager.executeQuery(sql);
         }catch(SQLException e1){
            LOG.error("!!after try, still Can't getUidFromSession: ", e1);
            return -2;
         }
        }
        return ...;


It means that: After the first exception (due to "Already closed" SQLException), the dbManager's connections is connected & the second exception is nerver been throw (in my test)

Ask: is my workaround the right solution?
tkx

[prince_hao]

Sure it is very likely that the DB has a timeout for connections, and it shuts them down after a while.
We always recommend to keep the DB alive by setting a looping task in your extension that fires a simple query every in a while (1 every 1-2 hours or so). The query can be as simple as "SELECT 1"

hope it helps

Hi LAPO,
I have the same problem and i have fired a simple query each hour,but i got that error too.
eg:Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2744)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at com.mysql.jdbc.CallableStatement.setInOutParamsOnServer(CallableStatement.java:2005)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:756)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at com.moyea.magicball.datas.DataOperation.check_id_password(Unknown Source)
at com.moyea.magicball.events.LoginEvent.handleServerEvent(Unknown Source)
at com.smartfoxserver.v2.extensions.SFSExtension.handleServerEvent(SFSExtension.java:242)
at com.smartfoxserver.v2.entities.managers.SFSExtensionManager.dispatchEvent(SFSExtensionManager.java:760)
at com.smartfoxserver.v2.entities.managers.SFSExtensionManager.dispatchZoneLevelEvent(SFSExtensionManager.java:685)
at com.smartfoxserver.v2.entities.managers.SFSExtensionManager.handleServerEvent(SFSExtensionManager.java:878)
at com.smartfoxserver.v2.core.SFSEventManager$SFSEventRunner.run(SFSEventManager.java:64)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **
Last packet sent to the server was 1 ms ago.
SmartFoxServer 2X (2.4.0) is solved the database connection more than eight hours to be automatic disconnect problems?
Help me,thx!

[prince_hao]

Maybe I described above is not clear enough,I have the same problem links viewtopic.php?f=18&t=15228
I want to know whether SmartFoxServer 2X（2.4.0）solved the problem

[Lapo]

Hi,
in all our tests the problem was solved. Also, other customers with a similar problem reported success with version 2.4
Is this happening regularly and at precise intervals with your DB?

thanks

[prince_hao]

Hi,
in all our tests the problem was solved. Also, other customers with a similar problem reported success with version 2.4
Is this happening regularly and at precise intervals with your DB?

thanks

hi,
No updated version 2.4 was accidental,Now updated version is still in testing!