{"id":133,"date":"2014-06-24T16:01:45","date_gmt":"2014-06-25T01:01:45","guid":{"rendered":"http:\/\/blog.box.kr\/?p=133"},"modified":"2014-06-24T16:01:45","modified_gmt":"2014-06-25T01:01:45","slug":"tomcat-jdbc-connection-pool-configuration-for-production-and-development","status":"publish","type":"post","link":"https:\/\/blog.box.kr\/?p=133","title":{"rendered":"Tomcat JDBC Connection Pool configuration for production and development"},"content":{"rendered":"<header class=\"entry-header\" style=\"color: #141412;\">\n<h1 class=\"entry-title\">Tomcat JDBC Connection Pool configuration for production and development<\/h1>\n<\/header>\n<div class=\"entry-content\" style=\"color: #141412;\">\n<div id=\"fsb-social-bar\" class=\"fsb-social-bar fsb-fixed\" data-post-id=\"485\" data-socialite=\"true\">\n<div class=\"fsb-share-facebook\">\n<div class=\"socialised facebook socialite-loaded\">\n<div class=\"socialite-button\">\n<div class=\"fb-like fb_iframe_widget\" data-fsb-service=\"facebook\" data-href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/\" data-send=\"false\" data-layout=\"button_count\" data-width=\"60\" data-show-faces=\"false\">\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>As mentioned in the post\u00a0<a style=\"color: #bc360a;\" title=\"Install Eclipse Kepler on Windows 7 \" href=\"http:\/\/www.codingpedia.org\/ama\/install-eclipse-kepler-64-bit-on-windows-7-64-bit\" target=\"_blank\">Install Eclipse Kepler 64 bit on Windows 7 64 bit<\/a>,\u00a0<a style=\"color: #bc360a;\" title=\"Podcastpedia.org, knowledge to go\" href=\"http:\/\/www.podcastpedia.org\/\" target=\"_blank\">Podcastpedia.org<\/a>\u00a0uses\u00a0<a style=\"color: #bc360a;\" title=\"Apache Tomcat\" href=\"http:\/\/tomcat.apache.org\/\" target=\"_blank\">Apache Tomcat 7<\/a>\u00a0as application server. This post presents how the Tomcat JDBC Connection Pool is configured in development and production for\u00a0<a style=\"color: #bc360a;\" title=\"Podcastpedia.org, knowledge to go\" href=\"http:\/\/www.podcastpedia.org\/\" target=\"_blank\">Podcastpedia.org<\/a>. The used database is MySql.<\/p>\n<div id=\"toc_container\" class=\"no_bullets\">\n<p class=\"toc_title\" style=\"font-weight: bold;\">Contents\u00a0<span class=\"toc_toggle\">[<a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#\">hide<\/a>]<\/span><\/p>\n<ul class=\"toc_list\">\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Production_environment\">Production environment<\/a>\n<ul>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Sizing_the_conection_pool\">Sizing the conection pool<\/a><\/li>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Validate_connections\">Validate connections<\/a><\/li>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Connection_leaks\">Connection leaks<\/a><\/li>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#The_validationcleaner_thread\">The validation\/cleaner thread<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Development_environment\">Development environment<\/a><\/li>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Watch_out_for\">Watch out for<\/a><\/li>\n<li><a style=\"color: #bc360a;\" href=\"http:\/\/www.codingpedia.org\/ama\/tomcat-jdbc-connection-pool-configuration-for-production-and-development\/#Resources\">Resources<\/a><\/li>\n<\/ul>\n<\/div>\n<p>&nbsp;<\/p>\n<p>The Tomcat Connection pool is configured as a\u00a0<strong>resource<\/strong>\u00a0described in The\u00a0<a style=\"color: #bc360a;\" title=\"Apache Tomcat 7 - JNDI Datasource HOW-TO\" href=\"http:\/\/tomcat.apache.org\/tomcat-7.0-doc\/jndi-datasource-examples-howto.html\" target=\"_blank\">Tomcat JDBC documentation<\/a>with the only difference being that you have to specify the\u00a0<code>factory<\/code>\u00a0attribute and set the value to<code>org.apache.tomcat.jdbc.pool.DataSourceFactory<\/code>. For\u00a0<a style=\"color: #bc360a;\" title=\"Podcastpedia.org, knowledge to go\" href=\"http:\/\/www.podcastpedia.org\/\" target=\"_blank\">Podcastpedia.org<\/a>, it is configured in the<em>context.xml<\/em>\u00a0file of the web application:<br \/>\n<a style=\"color: #bc360a;\" href=\"https:\/\/i0.wp.com\/www.codingpedia.org\/wp-content\/uploads\/2013\/08\/context-file.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-517\" src=\"https:\/\/i0.wp.com\/www.codingpedia.org\/wp-content\/uploads\/2013\/08\/context-file-246x300.png?resize=246%2C300\" alt=\"context-file\" width=\"246\" height=\"300\" data-recalc-dims=\"1\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><span id=\"Production_environment\">Production environment<\/span><\/h3>\n<p>&nbsp;<\/p>\n<div id=\"crayon-53a82856eb6f4557520667\" class=\"crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate\" data-settings=\" minimize scroll-mouseover\">\n<div class=\"crayon-plain-wrap\">\n<\/div>\n<div class=\"crayon-main\">\n<table class=\"crayon-table\">\n<tbody>\n<tr class=\"crayon-row\">\n<td class=\"crayon-nums \" style=\"color: #5499de !important;\" data-settings=\"show\">\n<div class=\"crayon-nums-content\">\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-1\">1\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-2\">2\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-3\">3\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-4\">4\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-5\">5\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-6\">6\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-7\">7\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-8\">8\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-9\">9\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-10\">10\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-11\">11\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-12\">12\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-13\">13\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-14\">14\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-15\">15\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-16\">16\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-17\">17\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-18\">18\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-19\">19\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb6f4557520667-20\">20\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb6f4557520667-21\">21\n<\/div>\n<\/div>\n<\/td>\n<td class=\"crayon-code\">\n<div class=\"crayon-pre\" style=\"color: #000000;\">\n<div id=\"crayon-53a82856eb6f4557520667-1\" class=\"crayon-line\"><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">&lt;<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">Resource<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-2\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">name<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;jdbc\/pcmDB&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-3\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">auth<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;Container&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-4\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">type<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;javax.sql.DataSource&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-5\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">factory<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;org.apache.tomcat.jdbc.pool.DataSourceFactory&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-6\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">initialSize<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;34&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-7\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">maxActive<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;377&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-8\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">maxIdle<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;233&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-9\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">minIdle<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;89&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-10\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">timeBetweenEvictionRunsMillis<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;34000&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-11\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">minEvictableIdleTimeMillis<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;55000&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-12\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">validationQuery<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;SELECT 1&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-13\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">validationInterval<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;34&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-14\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">testOnBorrow<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;true&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-15\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">removeAbandoned<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;true&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-16\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">removeAbandonedTimeout<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;55&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-17\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">username<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;xxx&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-18\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">password<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;yyy&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-19\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">driverClassName<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;com.mysql.jdbc.Driver&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-20\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">url<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;jdbc:mysql:\/\/localhost:3306\/pcmdb?allowMultiQueries=true&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb6f4557520667-21\" class=\"crayon-line\"><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">\/<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">&gt;<\/span>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>&nbsp;<\/p>\n<h4><span id=\"Sizing_the_conection_pool\">Sizing the conection pool<\/span><\/h4>\n<ul>\n<li><code>initialSize = 34<\/code>\u00a0\u2013 the initial number of connections that are created when the pool is started<\/li>\n<li><code>maxActive = 377<\/code>\u00a0\u2013 the maximum number of active connections that can be allocated from this pool at the same time. This attribute is used to limit the number of connections a pool can have open so that capacity planning can be done on the database side \u2013 in\u00a0<em>my.cnf<\/em>\u00a0MySql configuration file\u00a0<code>max_connections = 610 (maxActive+maxIdle)<\/code><\/li>\n<li><code>maxIdle = 233<\/code>\u00a0\u2013 the maximum number of idle connections that should be kept in the pool at all times. Idle connections are checked periodically (if enabled) and connections that have been idle for longer than\u00a0<code>minEvictableIdleTimeMillis<\/code>\u00a0will be released<\/li>\n<li><code>minIdle= 89<\/code>\u00a0\u2013 the minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail.<\/li>\n<li><code>timeBetweenEvictionRunsMillis = 34000<\/code>\u00a0\u2013 the number of milliseconds to sleep between runs of the idle connection validation\/cleaner thread. This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often we validate idle connections.<\/li>\n<li><code>minEvictableIdleTimeMillis = 55000<\/code>\u00a0\u2013 the minimum amount of time an object may sit idle in the pool before it is eligible for eviction.<\/li>\n<\/ul>\n<h4><span id=\"Validate_connections\">Validate connections<\/span><\/h4>\n<p>At first I avoided to configure connection validation, as I thought it would have an impact on performance. But several problems forced me to activate that. With the following configuration, connections are validated, but no more than every 34 seconds:<\/p>\n<ul>\n<li><code>testOnBorrow = true<\/code>\u00a0\u2013 by setting this, the objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.<em>\u00a0NOTE \u2013 for a true value to have any effect, the validationQuery parameter must be set to a non-null string.<\/em><\/li>\n<li><code>validationInterval = 34000<\/code>\u00a0\u2013 used to avoid excess validation, only run validation at most at this frequency \u2013 time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The larger the value, the better the performance, but you increase the chance of a stale connection being presented to your application.<\/li>\n<li><code>validationQuery= \"SELECT 1\"<\/code>\u00a0\u2013 MySql SQL query used to validate connections from the pool before returning them to the caller<\/li>\n<\/ul>\n<h4><span id=\"Connection_leaks\">Connection leaks<\/span><\/h4>\n<p>There are several configuration settings to help detect connection leaks:<\/p>\n<ul>\n<li><code>removeAbandoned = true\u00a0<\/code>\u2013 Flag to remove abandoned connections if they exceed the<code>removeAbandonedTimeout<\/code>. A connection is considered abandoned and eligible for removal if it has been in use longer than the\u00a0<code>removeAbandonedTimeout<\/code>. This way db connections can be recovered from applications that fail to close a connection.<\/li>\n<li><code>removeAbandonedTimeout = 54<\/code>\u00a0\u2013 timeout in seconds before an abandoned(in use) connection can be removed. The value should be set to the longest running query your applications might have.<\/li>\n<li><code>validationQuery= \"SELECT 1\"<\/code>\u00a0\u2013 MySql SQL query used to validate connections from the pool before returning them to the caller<\/li>\n<\/ul>\n<h4><span id=\"The_validationcleaner_thread\">The validation\/cleaner thread<\/span><\/h4>\n<p><code>timeBetweenEvictionRunsMillis &gt; 0 AND removeAbandoned=true AND removeAbandonedTimeout &gt; 0<\/code>\u00a0means the\u00a0<strong>pool sweeper<\/strong>\u00a0is enabled. The pool sweeper is the background thread that can test idle connections and resize the pool while the pool is active. The sweeper is also responsible for the detection of connection leaks. In this case the number of idle connections can grow beyond\u00a0<code>maxIdle<\/code>, but can shrink down to\u00a0<code>minIdle<\/code>\u00a0if the connection has been idle for longer than\u00a0<code>minEvictableIdleTimeMilis<\/code>.<\/p>\n<h3><span id=\"Development_environment\">Development environment<\/span><\/h3>\n<p>&nbsp;<\/p>\n<div id=\"crayon-53a82856eb71f981850885\" class=\"crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate\" data-settings=\" minimize scroll-mouseover\">\n<div class=\"crayon-plain-wrap\">\n<\/div>\n<div class=\"crayon-main\">\n<table class=\"crayon-table\">\n<tbody>\n<tr class=\"crayon-row\">\n<td class=\"crayon-nums \" style=\"color: #5499de !important;\" data-settings=\"show\">\n<div class=\"crayon-nums-content\">\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-1\">1\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-2\">2\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-3\">3\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-4\">4\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-5\">5\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-6\">6\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-7\">7\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-8\">8\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-9\">9\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-10\">10\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-11\">11\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-12\">12\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-13\">13\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-14\">14\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-15\">15\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-16\">16\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-17\">17\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-18\">18\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-19\">19\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb71f981850885-20\">20\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb71f981850885-21\">21\n<\/div>\n<\/div>\n<\/td>\n<td class=\"crayon-code\">\n<div class=\"crayon-pre\" style=\"color: #000000;\">\n<div id=\"crayon-53a82856eb71f981850885-1\" class=\"crayon-line\"><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">&lt;<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">Resource<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-2\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">name<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;jdbc\/pcmDB&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-3\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">auth<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;Container&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-4\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">type<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;javax.sql.DataSource&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-5\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">factory<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;org.apache.tomcat.jdbc.pool.DataSourceFactory&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-6\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">initialSize<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;5&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-7\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">maxActive<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;55&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-8\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">maxIdle<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;21&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-9\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">minIdle<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;13&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-10\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">timeBetweenEvictionRunsMillis<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;34000&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-11\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">minEvictableIdleTimeMillis<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;55000&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-12\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">validationQuery<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;SELECT 1&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-13\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">validationInterval<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;34&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-14\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">testOnBorrow<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;true&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-15\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">removeAbandoned<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;true&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-16\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">removeAbandonedTimeout<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;233&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-17\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">username<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;xxx&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-18\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">password<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;yyy&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-19\" class=\"crayon-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">driverClassName<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;com.mysql.jdbc.Driver&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-20\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-h\" style=\"color: #006fe0 !important;\">\u00a0\u00a0\u00a0\u00a0 <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">url<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">=<\/span><span class=\"crayon-s\" style=\"color: #008000 !important;\">&#8220;jdbc:mysql:\/\/localhost:3307\/pcmDB?allowMultiQueries=true&#8221;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb71f981850885-21\" class=\"crayon-line\"><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">\/<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">&gt;<\/span>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>&nbsp;<\/p>\n<p>The development environment configuration is just a copy of the configuration used in production, with smaller values for attributes to size the pool, and bigger values for attributes to determine leaked connection, so that I can be in debug mode longer.<\/p>\n<h3><span id=\"Watch_out_for\">Watch out for<\/span><\/h3>\n<p>One of the exceptions I have got was:<\/p>\n<div id=\"crayon-53a82856eb72f067386580\" class=\"crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate\" data-settings=\" minimize scroll-mouseover\">\n<div class=\"crayon-plain-wrap\">\n<\/div>\n<div class=\"crayon-main\">\n<table class=\"crayon-table\">\n<tbody>\n<tr class=\"crayon-row\">\n<td class=\"crayon-nums \" style=\"color: #5499de !important;\" data-settings=\"show\">\n<div class=\"crayon-nums-content\">\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb72f067386580-1\">1\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb72f067386580-2\">2\n<\/div>\n<div class=\"crayon-num crayon-marked-num crayon-top crayon-bottom\" style=\"color: #1561ac !important;\" data-line=\"crayon-53a82856eb72f067386580-3\">3\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb72f067386580-4\">4\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb72f067386580-5\">5\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb72f067386580-6\">6\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb72f067386580-7\">7\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb72f067386580-8\">8\n<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-53a82856eb72f067386580-9\">9\n<\/div>\n<div class=\"crayon-num crayon-striped-num\" style=\"color: #317cc5 !important;\" data-line=\"crayon-53a82856eb72f067386580-10\">10\n<\/div>\n<\/div>\n<\/td>\n<td class=\"crayon-code\">\n<div class=\"crayon-pre\" style=\"color: #000000;\">\n<div id=\"crayon-53a82856eb72f067386580-1\" class=\"crayon-line\"><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">After <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">mysql <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">server <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">was <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">restarted <\/span><span class=\"crayon-st\" style=\"color: #800080 !important;\">or<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">connection <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">was <\/span><span class=\"crayon-i\">lost<\/span><span class=\"crayon-i\">I<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">got <\/span><span class=\"crayon-r\" style=\"color: #800080 !important;\">this<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">kind <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">of <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">errors<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">:<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-2\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\" style=\"color: #002d7a !important;\">org<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">springframework<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">dao<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">DataAccessResourceFailureException<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">:<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-3\" class=\"crayon-line crayon-marked-line crayon-top crayon-bottom\"><span class=\"crayon-p\" style=\"color: #b85c00 !important;\">### Error querying database.\u00a0\u00a0Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-4\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-p\" style=\"color: #b85c00 !important;\">### The error may exist in maps\/PodcastMapper.xml<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-5\" class=\"crayon-line\"><span class=\"crayon-p\" style=\"color: #b85c00 !important;\">### The error may involve org.podcastpedia.dao.PodcastDao.getTopRatedPodcasts<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-6\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-p\" style=\"color: #b85c00 !important;\">### The error occurred while executing a query<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-7\" class=\"crayon-line\"><span class=\"crayon-p\" style=\"color: #b85c00 !important;\">### SQL: SELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.podcast_id,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.url,\u00a0\u00a0 (select sum(rating) \/ count(rating) from ratings\u00a0\u00a0\u00a0\u00a0where podcast_id = p.podcast_id and episode_id=-1) as podcast_rating,\u00a0\u00a0 (select count(rating)\u00a0\u00a0from ratings\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0where podcast_id = p.podcast_id and episode_id=-1) as podcast_number_ratings,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.number_visitors,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.description,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.short_description,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.podcast_image_url,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.title,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.last_episode_url,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.title_in_url,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.publication_date\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0podcasts p\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0p.availability=200\u00a0\u00a0\u00a0\u00a0 ORDER BY podcast_rating DESC\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0limit 0, ?;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-8\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-p\" style=\"color: #b85c00 !important;\">### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-9\" class=\"crayon-line\"><span class=\"crayon-sy\" style=\"color: #333333 !important;\">;<\/span><span class=\"crayon-i\">SQL<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">[<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">]<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">;<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">No <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">operations <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">allowed <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">after <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">connection <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">closed<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">Connection <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">was <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">implicitly <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">closed <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">by <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">the <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">driver<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">;<\/span>\n<\/div>\n<div id=\"crayon-53a82856eb72f067386580-10\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">nested <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">exception <\/span><span class=\"crayon-st\" style=\"color: #800080 !important;\">is<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">com<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">mysql<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">jdbc<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">exceptions<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">jdbc4<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">MySQLNonTransientConnectionException<\/span><span class=\"crayon-o\" style=\"color: #006fe0 !important;\">:<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">No <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">operations <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">allowed <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">after <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">connection <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">closed<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">Connection <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">was <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">implicitly <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">closed <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">by <\/span><span class=\"crayon-e\" style=\"color: #004ed0 !important;\">the <\/span><span class=\"crayon-v\" style=\"color: #002d7a !important;\">driver<\/span><span class=\"crayon-sy\" style=\"color: #333333 !important;\">.<\/span>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>&nbsp;<\/p>\n<p>This was solved by introducing the validation attributes mentioned above.<\/p>\n<p>Well that\u2019s it\u2026 Thanks again to the open source community for developing Tomcat, and a special thank you to\u00a0<a style=\"color: #bc360a;\" title=\"Apache Tomcat\" href=\"http:\/\/www.tomcatexpert.com\/users\/fhanik\" target=\"_blank\">Filip Hanik<\/a>\u00a0for explaining the JDBC- pool configuration so clearly.<\/p>\n<p>If you notice any room for improvement, please\u00a0<a style=\"color: #bc360a;\" href=\"mailto:contact@codingepdia.org?Subject=Apache%20optimization\" target=\"_top\">contact us<\/a>\u00a0or leave a message.<\/p>\n<p>If you liked this, please show your support by\u00a0<a style=\"color: #bc360a;\" title=\"Podcastpedia.org how can I help\" href=\"http:\/\/www.podcastpedia.org\/how_can_i_help\" target=\"_blank\">helping us<\/a>\u00a0with\u00a0<a style=\"color: #bc360a;\" title=\"Podcastpedia.org, knowledge to go\" href=\"http:\/\/www.podcastpedia.org\/\" target=\"_blank\">Podcastpedia.org<\/a><br \/>\nWe promise to only share high quality podcasts and episodes.<\/p>\n<h3><span id=\"Resources\">Resources<\/span><\/h3>\n<ul>\n<li><a style=\"color: #bc360a;\" title=\"Apache Tomcat\" href=\"http:\/\/tomcat.apache.org\/\" target=\"_blank\">Apache Tomcat<\/a><\/li>\n<li><a style=\"color: #bc360a;\" title=\"The Tomcat JDBC Connection Pool\" href=\"http:\/\/tomcat.apache.org\/tomcat-7.0-doc\/jdbc-pool.html\" target=\"_blank\">The Tomcat JDBC Connection Pool<\/a><\/li>\n<li><a style=\"color: #bc360a;\" title=\"Apache Tomcat 7 - JNDI Datasource HOW-TO\" href=\"http:\/\/tomcat.apache.org\/tomcat-7.0-doc\/jndi-datasource-examples-howto.html\" target=\"_blank\">Apache Tomcat 7 \u2013 JNDI Datasource HOW-TO<\/a><\/li>\n<li><a style=\"color: #bc360a;\" title=\"Using the Tomcat 7 JDBC Connection Pool in Production\" href=\"http:\/\/www.tomcatexpert.com\/blog\/2012\/01\/24\/using-tomcat-7-jdbc-connection-pool-production\" target=\"_blank\">Using the Tomcat 7 JDBC Connection Pool in Production<\/a><\/li>\n<li><a style=\"color: #bc360a;\" title=\"Configuring jdbc-pool for high-concurrency\" href=\"http:\/\/www.tomcatexpert.com\/blog\/2010\/04\/01\/configuring-jdbc-pool-high-concurrency\" target=\"_blank\">Configuring jdbc-pool for high-concurrency<\/a><\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Tomcat JDBC Connection Pool configuration for production and development As mentioned in the post\u00a0Install Eclipse Kepler 64 bit on Windows 7 64 bit,\u00a0Podcastpedia.org\u00a0uses\u00a0Apache Tomcat 7\u00a0as application server. This post presents how the Tomcat JDBC Connection Pool is configured in development and production for\u00a0Podcastpedia.org. The used database is MySql. Contents\u00a0[hide] Production environment Sizing the conection pool Validate connections Connection leaks The validation\/cleaner thread Development environment Watch out for Resources &nbsp; The Tomcat Connection pool is configured as a\u00a0resource\u00a0described in The\u00a0Tomcat JDBC documentationwith the only difference being that you have to specify the\u00a0factory\u00a0attribute and set the value toorg.apache.tomcat.jdbc.pool.DataSourceFactory. For\u00a0Podcastpedia.org, it is configured in thecontext.xml\u00a0file of the web application: &nbsp; Production environment &nbsp; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 &lt;Resource \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0name=&#8220;jdbc\/pcmDB&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0auth=&#8220;Container&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0type=&#8220;javax.sql.DataSource&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0factory=&#8220;org.apache.tomcat.jdbc.pool.DataSourceFactory&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0initialSize=&#8220;34&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0maxActive=&#8220;377&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0maxIdle=&#8220;233&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0minIdle=&#8220;89&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0timeBetweenEvictionRunsMillis=&#8220;34000&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0minEvictableIdleTimeMillis=&#8220;55000&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0validationQuery=&#8220;SELECT 1&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0validationInterval=&#8220;34&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0testOnBorrow=&#8220;true&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0removeAbandoned=&#8220;true&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0removeAbandonedTimeout=&#8220;55&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0username=&#8220;xxx&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0password=&#8220;yyy&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0driverClassName=&#8220;com.mysql.jdbc.Driver&#8221; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0url=&#8220;jdbc:mysql:\/\/localhost:3306\/pcmdb?allowMultiQueries=true&#8221; \/&gt; &nbsp; Sizing the conection pool initialSize = 34\u00a0\u2013 the initial number of connections that are created when the pool is started maxActive = 377\u00a0\u2013 the maximum number of active connections that can be allocated from this pool at the same time. This attribute is used to limit [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[25,7],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-29","jetpack-related-posts":[{"id":135,"url":"https:\/\/blog.box.kr\/?p=135","url_meta":{"origin":133,"position":0},"title":"Tomcat MySQL Connection &#8211; Using JDBC to Connect Tomcat to MySQL","date":"2014-06-24","format":false,"excerpt":"Tomcat MySQL Connection - Using JDBC to Connect Tomcat to MySQL Stumped by Tomcat JDBC connections? Can't get your head around MySQL drivers, connection pools, and JNDI resources? This article will guide you step-by-step through\u00a0connecting your MySQL database to Tomcat, in plain English. Along the way, we'll also cover setting\u2026","rel":"","context":"In &quot;WAS, Servet Containers&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":139,"url":"https:\/\/blog.box.kr\/?p=139","url_meta":{"origin":133,"position":1},"title":"JDBC Connection Pool Timeout Errors","date":"2014-06-24","format":false,"excerpt":"If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection. If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection, and could throw the\u00a0following exception in Tomcat: Caused\u2026","rel":"","context":"In &quot;WAS, Servet Containers&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":649,"url":"https:\/\/blog.box.kr\/?p=649","url_meta":{"origin":133,"position":2},"title":"[\ud38c]Tomcat7 tomcat-jdbc \uc124\uc815 &#8211; Broken pipe \uc5d0\ub7ec \ud68c\ud53c","date":"2015-04-05","format":false,"excerpt":"tomcat-jdbc\ub85c MySQL\uc5d0 \uc5f0\uacb0\ud55c \uacbd\uc6b0 \"java.net.SocketException: Broken pipe\" \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\ub97c \uac00\ub054 \ubcfc \uc218 \uc788\ub2e4. \ub300\ubd80\ubd84\uc758 \uacbd\uc6b0 connection idle time\uc774 MySQL\uc5d0 \uc124\uc815\ub41c wait_timeout\uc744 \uc9c0\ub098\uc11c DB\uc5d0\uc11c \uc5f0\uacb0\uc744 \ub04a\uc740 \uac83\uc774\ub2e4. \uc774\ub97c \ubc29\uc9c0\ud558\ub824\uba74 connection\uc774 \uc77c\uc815 \uae30\uac04\ub3d9\uc548 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc73c\uba74 close\ub418\ub3c4\ub85d \uc124\uc815\ud558\uac70\ub098 connection \ub300\uc5ec\uc2dc\uc5d0 connection\uc744 \uccb4\ud06c\ud558\ub3c4\ub85d \ud558\uba74 \ub41c\ub2e4. \uc124\uc815 1 - \uc77c\uc815 \uae30\uac04\ub3d9\uc548 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc73c\uba74 connection\uc744\u2026","rel":"","context":"In &quot;\uae30\uc220\uc790\ub8cc&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":678,"url":"https:\/\/blog.box.kr\/?p=678","url_meta":{"origin":133,"position":3},"title":"[Linux] Installing Tomcat 8 on a CentOS 7","date":"2015-04-13","format":false,"excerpt":"UPDATE SYSTEM First thing to do is to SSH to your CentOS 7 VPS, fire up a screen session and update your system using yum: ## screen -U -S tomcat8-centos7 ## yum update You may also want to install a text editor like nano or vim ## yum install vim\u2026","rel":"","context":"In &quot;\uae30\uc220\uc790\ub8cc&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":373,"url":"https:\/\/blog.box.kr\/?p=373","url_meta":{"origin":133,"position":4},"title":"Application Performance Monitoring (APM) Framework for J2EE Applications","date":"2014-09-15","format":false,"excerpt":"https:\/\/code.google.com\/p\/monitor-24x7\/ \u00a0 Description 24x7Monitoring is an Open Source Application Performance Monitoring (APM) Framework for J2EE Applications that uses Aspect Oriented Programming to collect Performance metrics about the running JVM and display the data to the user in a tabular\/graphical format. 24x7Monitoring does not require any modification to the source code\u2026","rel":"","context":"In &quot;\ucc38\uace0\ub97c \uc704\ud55c \uc800\uc7a5\ubb3c&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":653,"url":"https:\/\/blog.box.kr\/?p=653","url_meta":{"origin":133,"position":5},"title":"[\ud38c]Too many connection \ubb38\uc81c \ud574\uacb0","date":"2015-04-05","format":false,"excerpt":"http:\/\/netholic.tistory.com\/116 \u00a0 \u00a0 \uc774\ubc88\ud574 \ucd08\ubd80\ud130 \uc2dc\uc791\ub41c too many connection \ubb38\uc81c\uac00 \ud558\ub8e8\uc5d0 \ud55c\ubc88\uc529 mysql \uc744 \ubed7\uac8c \ub9cc\ub4e4\uc5c8\ub2e4. \ub418\ub294\ub300\ub85c... \uc544\ubb34\uac83\ub3c4 \ubaa8\ub978\ucc44\ub85c \ub9cc\ub4e0 \uc6b4\uc601\ud234\uc774 \uadf8 \uc6d0\uc778... \ucffc\ub9ac\ub3c4 10\ucd08 \uc774\uc0c1 \uac78\ub9ac\ub294 \uac83\ub3c4 \uc874\uc7ac\ud588\uace0..\uc778\ub371\uc2a4\ub3c4 \uc81c\ub300\ub85c \uc548\uac78\ub824\uc788\uace0.. \uadf8\ub798\uc11c \uc2ac\ub85c\uc6b0 \ucffc\ub9ac\ub97c \uc7a1\uace0 \uc778\ub371\uc2a4\ub3c4 \uc81c\ub300\ub85c \uac78\uc5b4\uc11c...\uadf8\ub7f0 \ucffc\ub9ac\ubb38\uc81c\ub97c \uc5c6\uc570\uc73c\ub85c\uc11c \ucee4\ub125\uc158 \ubb38\uc81c\ub294 \uc5c6\uc5b4\uc9c8 \uc904 \uc54c\uc558\ub2e4. \uadf8\ub7f0\ub370 \uc5ec\uc804\ud788 \ud558\ub8e8\uc5d0 \ud55c\ubc88\uc529...\uc11c\ubc84\ub97c \ub0b4\ub838\ub2e4\u2026","rel":"","context":"In &quot;\uae30\uc220\uc790\ub8cc&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/133"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=133"}],"version-history":[{"count":0,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/133\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}