You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Arnaud <ar...@ybo.com.sg> on 2012/11/20 10:23:54 UTC
Re: connection autoReconnect?
We have been struggling with this MySQL last packet message for some times as
well. And we are still not sure if we have solved it yet. However we did
discover quite a few things.
We minimized our DBCP configuration to only a few parameters
<Context path="">
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
name="jdbc/retailUser" type="javax.sql.DataSource"
username="xxxxxx" password="xxxxx"
url="jdbc:mysql://server/db"
validationQuery="SELECT 1" testOnBorrow="true"/>
</Context>
That's it. All the other parameters have acceptable default values. The
problem, we think, is actually that with the default configuration you will
open to many connections and not use them within the wait_timeout of MySQL.
So, now and then you will retrieve a connection that has timed out. The
validation query solves this.
As far as we know the autoReconnect is a jConnector parameter and only
applies to MySQL. MySQL says in their documentation that it's the
applications job to handle failed connections. For the step of just getting
the connection DBCP is solving this with the validationQuery="SELECT 1"
testOnBorrow="true" settings. But if it happens in the midst of a
transaction, your application should handle it.
If you do not set validationQuery than testOnBorrow will simply not run.
removeAbandoned only removes connections that you forget to close in your
application. You can use this setting for a while together with logAbandoned
to find your application leaks where you forget to close the connection
after use, but when done you should remove logAbandoned because the logging
adds overhead from every connection borrowed.
Most of this we found at tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
<http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html> and at
dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html
<http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html>
--
View this message in context: http://tomcat.10.n6.nabble.com/connection-autoReconnect-tp4340944p4989787.html
Sent from the Tomcat - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Arnaud Kleinveld <ar...@ybo.com.sg>.
Ok. The validation query solves this problem we concluded. Confirmed with
testing as well.
Our Resource looks like this now
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
name="jdbc/xxxxxxxx" type="javax.sql.DataSource"
username="xxxxxxxx" password="xxxxxxx"
url="jdbc:mysql://localhost:3306/xxxxxxx"
validationQuery="/* ping */ SELECT 1" testWhileIdle="true"/>
Because of using MySQL we have this specific validation query. Which has to
be exactly lower-case and with spaces as above says the documentation
otherwise it will not work. Get the right validation query if you use a
different type of database.
We chose to run the validation/cleaner thread by setting testWhileIdle
instead of testOnBorrow. Which works fine too and prevents your application
to run the validationQuery every single time when processing high loads.
We have done a test by setting wait_timeout to 90 seconds and reusing the
connections with our application after MySQL had closed them. No exceptions
were thrown. Even within the 5 seconds after the connection had be closed.
--
View this message in context: http://tomcat.10.n6.nabble.com/connection-autoReconnect-tp4340944p4989963.html
Sent from the Tomcat - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Arnaud <ar...@ybo.com.sg>.
So, yes indeed. The problem would then be solved by making sure that all
connections are rotated at least once within the MySQL wait-timeout right?
How do we do this? Measure the average concurrent number of connections and
then set the initialSize the same?
--
View this message in context: http://tomcat.10.n6.nabble.com/connection-autoReconnect-tp4340944p4989844.html
Sent from the Tomcat - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Arnaud,
On 11/20/12 4:23 AM, Arnaud wrote:
> We have been struggling with this MySQL last packet message for
> some times as well. And we are still not sure if we have solved it
> yet.
You can get the "last packet" message for a number of reasons.
Typically, it's because the connection has gone stale due to firewall
or other connection timeout and the connection has been closed but it
is still in the connection pool.
> However we did discover quite a few things.
>
> We minimized our DBCP configuration to only a few parameters
> <Context path=""> <Resource auth="Container"
> driverClassName="com.mysql.jdbc.Driver" name="jdbc/retailUser"
> type="javax.sql.DataSource" username="xxxxxx" password="xxxxx"
> url="jdbc:mysql://server/db" validationQuery="SELECT 1"
> testOnBorrow="true"/> </Context>
>
> That's it. All the other parameters have acceptable default values.
> The problem, we think, is actually that with the default
> configuration you will open to many connections and not use them
> within the wait_timeout of MySQL.
Sounds to me that the default values aren't acceptable, then.
> So, now and then you will retrieve a connection that has timed out.
> The validation query solves this.
Correct. For MySQL, you'll want to use "/* ping */ SELECT 1" for your
query: it will be fasted with any reasonably-recent version of
Connector/J which does a lightweight ping to the database instead of
actually issuing the query, fetching the results, etc.
> As far as we know the autoReconnect is a jConnector parameter and
> only applies to MySQL.
That is correct. The product is Connector/J.
> MySQL says in their documentation that it's the applications job to
> handle failed connections. For the step of just getting the
> connection DBCP is solving this with the validationQuery="SELECT
> 1" testOnBorrow="true" settings. But if it happens in the midst of
> a transaction, your application should handle it.
While true, I can't imagine any database that would
commit-on-connection-failure. Most applications are written to simply
throw a SQLException and either abandon the transaction or re-try it.
In either case, the intended behavior is achieved: no half-performed
transactions are committed.
> If you do not set validationQuery than testOnBorrow will simply not
> run.
This is clear from the DBCP documentation.
> removeAbandoned only removes connections that you forget to close
> in your application.
This isn't strictly true: the abandonedTimeout specifies a window of
opportunity that the client has to return a particular resource. If
the transaction runs long, the connection can be considered
"abandoned" even if the application eventually closes it.
> You can use this setting for a while together with logAbandoned to
> find your application leaks where you forget to close the
> connection after use, but when done you should remove logAbandoned
> because the logging adds overhead from every connection borrowed.
This is also clear from the documentation.
> Most of this we found at
> tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
> <http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html> and at
> dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html
>
>
<http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html>
DBCP != Tomcat-pool
They can be configured in very similar ways, but tomcat-pool is a lot
more flexible and was designed to be higher-performing than DBCP --
though I haven't independently verified that it is actually faster.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with undefined - http://www.enigmail.net/
iEYEARECAAYFAlCr+28ACgkQ9CaO5/Lv0PAG4gCfS225GnljSNwwAaOuVMuRqWD1
umEAnAuYOb5YML3B4Vbxvcj4cjm/Wfu9
=Y/Ij
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org