Re: Pooled Connections Lost After 10 Minutes (600 seconds)
Alec, Dan, and Chris, On Wed, Dec 4, 2013 at 1:01 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Dan, On 12/3/13, 12:32 PM, Daniel Mikusa wrote: On Dec 3, 2013, at 12:14 PM, Tomcat Random tomcat.ran...@gmail.com wrote: I considered using a validation query but it seemed like extra overhead when the default behavior was not, um, behaving in the default way. The overhead is typically minimal. Running SELECT 1 or some other very simply query is not likely to bring your database to it's knees. It might add a small amount of latency as the pool will need to execute the query before it give the connection to your application, but that's likely to be dwarfed by whatever your application does with the connection after it gets it. If you are concerned you can do a couple things to make the process even more lightweight. 1.) With MySQL and use /* ping */ SELECT 1 as the validation query. This is a special case with the MySQL JDBC driver that uses even less resources. +1 We use this everywhere. I've never actually benchmarked it, but since it does not execute a query on the server, it pretty much has to be faster by any measure. 2.) You can use the tomcat-jdbc connection pool which has a validationInterval setting. This will ensure that the validation query is only executed one time during the specified time interval. I haven't moved to tomcat-pool yet, but this was my initial reaction to Alec's question about usually not needing the validation query. ...or you can go without a validation query, but it's not something I would recommend and not something I see done very often. The minimal overhead is usually worth knowing that you get a valid connection from the pool. +1 If you don't use a validation query, you need additional try/catch blocks around all your getConnection() calls, and a loop to re-try just in case the first connection was bad. I think without a validationQuery, your pool will effectively dry-up over time. +1 interesting topic and responses. Thanks! Since I'm using TomEE, tomcat jdbc pool is default, and below is the config. Resource id=jdbc/dbJta type=javax.sql.DataSource JdbcDriver org.apache.derby.jdbc.EmbeddedDriver JdbcUrl jdbc:derby:X:/myPathToMyDB;create=true UserName Password JtaManaged true jmxEnabled true InitialSize 10 MaxActive 30 MaxIdle 20 MaxWait 1 minIdle 10 suspectTimeout 60 removeAbandoned true removeAbandonedTimeout 180 timeBetweenEvictionRunsMillis 3 jdbcInterceptors=StatementCache(max=1024) /Resource As you can see, I am one of those rare cases that Dan mentioned...not using validationQuery. Not so much intentional, but I'm still somewhat novice as tomcat user. With that said, I have not had the need to add try/catch to ensure I get a good connection from the pool. I don't have high traffic coming to my web app, but there are times when multiple users are using the app, and I see absolutely 'no' connection issues (ever), and performance is quite good/sound as well. So, I do hear the recommendations, in this thread, about validation query, but my app has not told me yet...that it needs the validation query. :)
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Dan, On 12/3/13, 12:32 PM, Daniel Mikusa wrote: On Dec 3, 2013, at 12:14 PM, Tomcat Random tomcat.ran...@gmail.com wrote: I considered using a validation query but it seemed like extra overhead when the default behavior was not, um, behaving in the default way. The overhead is typically minimal. Running SELECT 1 or some other very simply query is not likely to bring your database to it's knees. It might add a small amount of latency as the pool will need to execute the query before it give the connection to your application, but that's likely to be dwarfed by whatever your application does with the connection after it gets it. If you are concerned you can do a couple things to make the process even more lightweight. 1.) With MySQL and use /* ping */ SELECT 1 as the validation query. This is a special case with the MySQL JDBC driver that uses even less resources. +1 We use this everywhere. I've never actually benchmarked it, but since it does not execute a query on the server, it pretty much has to be faster by any measure. 2.) You can use the tomcat-jdbc connection pool which has a validationInterval setting. This will ensure that the validation query is only executed one time during the specified time interval. I haven't moved to tomcat-pool yet, but this was my initial reaction to Alec's question about usually not needing the validation query. ...or you can go without a validation query, but it's not something I would recommend and not something I see done very often. The minimal overhead is usually worth knowing that you get a valid connection from the pool. +1 If you don't use a validation query, you need additional try/catch blocks around all your getConnection() calls, and a loop to re-try just in case the first connection was bad. I think without a validationQuery, your pool will effectively dry-up over time. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSn236AAoJEBzwKT+lPKRY85sQAMTss/RPDRep+yUhOxCCdCOF 8d2ZhzeDwZ/lM0d+XlH9ZJzbBdwQEpRyZf/uzQRQLK/WO/vReXN3RMptSrN+VoLE TaqWcGva1NPvUOwrMKd1Hm8zKqtcamWTQIHa1MzQ/cN9RChmZbfNwc7CteuTd36C zvXoj1nRhkixfE4jX2/REDtAh0QKQkCj/Dq1BpOlEzJaGwmL/fbwJreUvPiGNvaO xQaCr92Z/Srv32oLCBCu3fs8/FN2KvLKB2YKFWB2iHrRvxiIC7tUWUp9OkZvJga2 ARs7UraFNl/Z+vi8xV2S1cavJD+jeW4ddB5QDr+1yxWRPt0QEE0UzaolATVTUak9 9cRb+87xICd5z/XoFABSPqeicyS/1/cxg/JOHrFtcM8EmXZifB4aVLpnjIqHrRC/ Y3LoCjaIBO78/0i75kC0zeS2opTXMlrvEy/0W/QA8XwGmy7yEvZhbER+TVeHsAcD 6evobH5bbSbnxXgB4o826/ihxMq3JxfZWuaGvCmgB4D4aI0SbtdNP+/SHwirGZA1 Lt/iRCFAGFtJTMygVQZzBn+gbO3mMx7vxesIFbmKRDcUWpQ7MVrs5Wq9mfLUFJp8 YnQS66MM10Nf3O0qNavBN5af4Cq1E+GvcuydchpbDHwd7czkidzLQcGbyIiBpnTq HR5uraaRm89AZ5zUKShW =9ync -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
I think without a validationQuery, your pool will effectively dry-up over time. I'm expecting some very high traffic, so it's unlikely if not impossible a connection will timeout on the mysql side with the default settings. All connections are safely returned to the pool (famous last words, I know), so I'm holding off to see if I do see the pool shrinking over time, and if I'm getting already closed exceptions. If that's the case the validation query and validationInterval are certainly not a great expense. It's good advice, but I'm just curious if they are absolutely necessary Cheers, Alec On Wed, Dec 4, 2013 at 1:01 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Dan, On 12/3/13, 12:32 PM, Daniel Mikusa wrote: On Dec 3, 2013, at 12:14 PM, Tomcat Random tomcat.ran...@gmail.com wrote: I considered using a validation query but it seemed like extra overhead when the default behavior was not, um, behaving in the default way. The overhead is typically minimal. Running SELECT 1 or some other very simply query is not likely to bring your database to it's knees. It might add a small amount of latency as the pool will need to execute the query before it give the connection to your application, but that's likely to be dwarfed by whatever your application does with the connection after it gets it. If you are concerned you can do a couple things to make the process even more lightweight. 1.) With MySQL and use /* ping */ SELECT 1 as the validation query. This is a special case with the MySQL JDBC driver that uses even less resources. +1 We use this everywhere. I've never actually benchmarked it, but since it does not execute a query on the server, it pretty much has to be faster by any measure. 2.) You can use the tomcat-jdbc connection pool which has a validationInterval setting. This will ensure that the validation query is only executed one time during the specified time interval. I haven't moved to tomcat-pool yet, but this was my initial reaction to Alec's question about usually not needing the validation query. ...or you can go without a validation query, but it's not something I would recommend and not something I see done very often. The minimal overhead is usually worth knowing that you get a valid connection from the pool. +1 If you don't use a validation query, you need additional try/catch blocks around all your getConnection() calls, and a loop to re-try just in case the first connection was bad. I think without a validationQuery, your pool will effectively dry-up over time. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSn236AAoJEBzwKT+lPKRY85sQAMTss/RPDRep+yUhOxCCdCOF 8d2ZhzeDwZ/lM0d+XlH9ZJzbBdwQEpRyZf/uzQRQLK/WO/vReXN3RMptSrN+VoLE TaqWcGva1NPvUOwrMKd1Hm8zKqtcamWTQIHa1MzQ/cN9RChmZbfNwc7CteuTd36C zvXoj1nRhkixfE4jX2/REDtAh0QKQkCj/Dq1BpOlEzJaGwmL/fbwJreUvPiGNvaO xQaCr92Z/Srv32oLCBCu3fs8/FN2KvLKB2YKFWB2iHrRvxiIC7tUWUp9OkZvJga2 ARs7UraFNl/Z+vi8xV2S1cavJD+jeW4ddB5QDr+1yxWRPt0QEE0UzaolATVTUak9 9cRb+87xICd5z/XoFABSPqeicyS/1/cxg/JOHrFtcM8EmXZifB4aVLpnjIqHrRC/ Y3LoCjaIBO78/0i75kC0zeS2opTXMlrvEy/0W/QA8XwGmy7yEvZhbER+TVeHsAcD 6evobH5bbSbnxXgB4o826/ihxMq3JxfZWuaGvCmgB4D4aI0SbtdNP+/SHwirGZA1 Lt/iRCFAGFtJTMygVQZzBn+gbO3mMx7vxesIFbmKRDcUWpQ7MVrs5Wq9mfLUFJp8 YnQS66MM10Nf3O0qNavBN5af4Cq1E+GvcuydchpbDHwd7czkidzLQcGbyIiBpnTq HR5uraaRm89AZ5zUKShW =9ync -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
On Dec 2, 2013 10:09 PM, Neven Cvetkovic neven.cvetko...@gmail.com wrote: On Dec 2, 2013 12:47 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Neven, thank you. It was right there in my.cnf: 'wait_timeout=600' You're welcome :) I am curious why tomcat didn't renew expired (terminated) idle connections though. It wouldn't know that the connections have been disconnected, since they were disconnected server side. If the user added a validation query then it could detect and remove the closed connections. Dan
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
I considered using a validation query but it seemed like extra overhead when the default behavior was not, um, behaving in the default way. -Alec On Tue, Dec 3, 2013 at 7:24 AM, Daniel Mikusa dmik...@gopivotal.com wrote: On Dec 2, 2013 10:09 PM, Neven Cvetkovic neven.cvetko...@gmail.com wrote: On Dec 2, 2013 12:47 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Neven, thank you. It was right there in my.cnf: 'wait_timeout=600' You're welcome :) I am curious why tomcat didn't renew expired (terminated) idle connections though. It wouldn't know that the connections have been disconnected, since they were disconnected server side. If the user added a validation query then it could detect and remove the closed connections. Dan
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
On Dec 3, 2013, at 12:14 PM, Tomcat Random tomcat.ran...@gmail.com wrote: I considered using a validation query but it seemed like extra overhead when the default behavior was not, um, behaving in the default way. The overhead is typically minimal. Running SELECT 1 or some other very simply query is not likely to bring your database to it's knees. It might add a small amount of latency as the pool will need to execute the query before it give the connection to your application, but that's likely to be dwarfed by whatever your application does with the connection after it gets it. If you are concerned you can do a couple things to make the process even more lightweight. 1.) With MySQL and use /* ping */ SELECT 1 as the validation query. This is a special case with the MySQL JDBC driver that uses even less resources. 2.) You can use the tomcat-jdbc connection pool which has a validationInterval setting. This will ensure that the validation query is only executed one time during the specified time interval. ...or you can go without a validation query, but it's not something I would recommend and not something I see done very often. The minimal overhead is usually worth knowing that you get a valid connection from the pool. Dan On Tue, Dec 3, 2013 at 7:24 AM, Daniel Mikusa dmik...@gopivotal.com wrote: On Dec 2, 2013 10:09 PM, Neven Cvetkovic neven.cvetko...@gmail.com wrote: On Dec 2, 2013 12:47 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Neven, thank you. It was right there in my.cnf: 'wait_timeout=600' You're welcome :) I am curious why tomcat didn't renew expired (terminated) idle connections though. It wouldn't know that the connections have been disconnected, since they were disconnected server side. If the user added a validation query then it could detect and remove the closed connections. Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
Thanks, that's good to know, especially about the validationInterval setting. The idea of not validating every single time seems more reasonable. -Alec On Tue, Dec 3, 2013 at 12:32 PM, Daniel Mikusa dmik...@gopivotal.comwrote: On Dec 3, 2013, at 12:14 PM, Tomcat Random tomcat.ran...@gmail.com wrote: I considered using a validation query but it seemed like extra overhead when the default behavior was not, um, behaving in the default way. The overhead is typically minimal. Running SELECT 1 or some other very simply query is not likely to bring your database to it's knees. It might add a small amount of latency as the pool will need to execute the query before it give the connection to your application, but that's likely to be dwarfed by whatever your application does with the connection after it gets it. If you are concerned you can do a couple things to make the process even more lightweight. 1.) With MySQL and use /* ping */ SELECT 1 as the validation query. This is a special case with the MySQL JDBC driver that uses even less resources. 2.) You can use the tomcat-jdbc connection pool which has a validationInterval setting. This will ensure that the validation query is only executed one time during the specified time interval. ...or you can go without a validation query, but it's not something I would recommend and not something I see done very often. The minimal overhead is usually worth knowing that you get a valid connection from the pool. Dan On Tue, Dec 3, 2013 at 7:24 AM, Daniel Mikusa dmik...@gopivotal.com wrote: On Dec 2, 2013 10:09 PM, Neven Cvetkovic neven.cvetko...@gmail.com wrote: On Dec 2, 2013 12:47 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Neven, thank you. It was right there in my.cnf: 'wait_timeout=600' You're welcome :) I am curious why tomcat didn't renew expired (terminated) idle connections though. It wouldn't know that the connections have been disconnected, since they were disconnected server side. If the user added a validation query then it could detect and remove the closed connections. Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
Neven, thank you. It was right there in my.cnf: 'wait_timeout=600' On Fri, Nov 29, 2013 at 6:08 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Neven, On 11/27/13, 6:35 PM, Neven Cvetkovic wrote: On Wed, Nov 27, 2013 at 5:32 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Everything behaves as expected, except after 600 seconds (10 minutes) all the pooled connections expire. As far as I can tell the default is to not remove/evict pooled connections below 'minIdle' but that's what's happening. The only thing I can find that relates to connection pools and 600 seconds is in JK, but I'm not using that. Any ideas? Hey Alec, Can it be that connections are timed out on the mysql side? +1 Sounds like it might be a router connection-idle-timeout issue and maybe not anything to do directly with MySQL. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSmR5pAAoJEBzwKT+lPKRY0oQP/2aEHo+L6l07WUGkL+qZ6FhR kgxSlIqu75uCNxHdr0ApqEzDVma0IJeLTJYJomiEJ3ZZFFy0FasWPUGIiO1ssB/e iTr1ttR5rJh8atw7WoAPvVFHvjOA86WZ6/pn5FW9HNGYe5VzsmaUPPfWmdVd+JP2 8jmaV0Cx1CfYVoEMKjEfPrQP5+KnNuYj+KgUwBOb7+B1kAbmyvz4Ri0g3l0t/WIl pE2cD8ZPXT2nYPtNViReLwHOWEAKrM/lLl7bTn6/+foBl8f1AtY95Ck9oBc8f5/c OavEGV0ESVsLmMJs2j6kGkfaVyaD91INHs+lMZ2DqtQZAEwMi05VBFxJztLF8pBB 67XDG8EvlKM7vXtIC+qKmVcUbbEd+MAD75Hcydi+TBQipYtxA/Js/uoLmNMP3CSH 0QuYpRa/ua2imvLZN6x7CP4AoIiAsKGQsV21kLJL77xC15CpjxLL1LcqsMmI2llE 4P3SSPrwZtRbdGFNn4Ou9X1M0van3N+hGv4VqXfFxsn8Zj1IL56YJohSz8j9KGJ6 rQhvxTplu4ifAoSvcrCc+78Gw7KgVOcHbWziHWQlGXTKFJUUeV71iwIABv10FZ6t +xMQSPVS9xGzrlpnSdJExFUHNKq2XYpenI2wPvdHY3rdNTcrAOqw5A3HSOwyGbhd 78xu0nfsJuFEw+2x0qQk =VgsO -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
On Dec 2, 2013 12:47 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Neven, thank you. It was right there in my.cnf: 'wait_timeout=600' You're welcome :) I am curious why tomcat didn't renew expired (terminated) idle connections though.
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Neven, On 11/27/13, 6:35 PM, Neven Cvetkovic wrote: On Wed, Nov 27, 2013 at 5:32 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Everything behaves as expected, except after 600 seconds (10 minutes) all the pooled connections expire. As far as I can tell the default is to not remove/evict pooled connections below 'minIdle' but that's what's happening. The only thing I can find that relates to connection pools and 600 seconds is in JK, but I'm not using that. Any ideas? Hey Alec, Can it be that connections are timed out on the mysql side? +1 Sounds like it might be a router connection-idle-timeout issue and maybe not anything to do directly with MySQL. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSmR5pAAoJEBzwKT+lPKRY0oQP/2aEHo+L6l07WUGkL+qZ6FhR kgxSlIqu75uCNxHdr0ApqEzDVma0IJeLTJYJomiEJ3ZZFFy0FasWPUGIiO1ssB/e iTr1ttR5rJh8atw7WoAPvVFHvjOA86WZ6/pn5FW9HNGYe5VzsmaUPPfWmdVd+JP2 8jmaV0Cx1CfYVoEMKjEfPrQP5+KnNuYj+KgUwBOb7+B1kAbmyvz4Ri0g3l0t/WIl pE2cD8ZPXT2nYPtNViReLwHOWEAKrM/lLl7bTn6/+foBl8f1AtY95Ck9oBc8f5/c OavEGV0ESVsLmMJs2j6kGkfaVyaD91INHs+lMZ2DqtQZAEwMi05VBFxJztLF8pBB 67XDG8EvlKM7vXtIC+qKmVcUbbEd+MAD75Hcydi+TBQipYtxA/Js/uoLmNMP3CSH 0QuYpRa/ua2imvLZN6x7CP4AoIiAsKGQsV21kLJL77xC15CpjxLL1LcqsMmI2llE 4P3SSPrwZtRbdGFNn4Ou9X1M0van3N+hGv4VqXfFxsn8Zj1IL56YJohSz8j9KGJ6 rQhvxTplu4ifAoSvcrCc+78Gw7KgVOcHbWziHWQlGXTKFJUUeV71iwIABv10FZ6t +xMQSPVS9xGzrlpnSdJExFUHNKq2XYpenI2wPvdHY3rdNTcrAOqw5A3HSOwyGbhd 78xu0nfsJuFEw+2x0qQk =VgsO -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
I should add this is with MySQL 5.5.34 On Wed, Nov 27, 2013 at 5:32 PM, Tomcat Random tomcat.ran...@gmail.comwrote: I have two instances of Tomcat 7.0.42, each on their own physical server (RHEL6). There's nothing in front of them, I'm using IPTABLES to route 80 to 8080. They're clustered using the native Tomcat clustering. I'm using the non blocking NIO connector. Everything behaves as expected, except after 600 seconds (10 minutes) all the pooled connections expire. As far as I can tell the default is to not remove/evict pooled connections below 'minIdle' but that's what's happening. The only thing I can find that relates to connection pools and 600 seconds is in JK, but I'm not using that. Any ideas? Both servers have the same configuration The connection pool is configured in context.xml: Resource name=jdbc/my_db auth=Container type=javax.sql.DataSource username=tomcat_db_user password=x driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.100.51:3306/my_db maxActive=200 maxIdle=100 maxWait=2 initialSize=10 minIdle=10 / And in web.xml/ !-- JNDI resource for the database -- resource-ref descriptionMy Database/description res-ref-namejdbc/my_db/res-ref-name res-typejavax.sql.DataSource/res-type res-authContainer/res-auth /resource-ref TIA, Alec
Re: Pooled Connections Lost After 10 Minutes (600 seconds)
On Wed, Nov 27, 2013 at 5:32 PM, Tomcat Random tomcat.ran...@gmail.com wrote: Everything behaves as expected, except after 600 seconds (10 minutes) all the pooled connections expire. As far as I can tell the default is to not remove/evict pooled connections below 'minIdle' but that's what's happening. The only thing I can find that relates to connection pools and 600 seconds is in JK, but I'm not using that. Any ideas? Hey Alec, Can it be that connections are timed out on the mysql side? What does your /etc/my.cnf (or wherever mysql is configured) say about connections? What are wait_timeout and interactive_timeout settings in the mysql configuration file? (although Tomcat should maintain your pool of connections, with minIdle number of them at any time) You can tweak things on the Tomcat side too: http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html (see Ch4. Configure Tomcat's Resource Factory) Good luck!
