Connection Pooling with Connector/J
2098
* that we don't 'leak' resources...
*/
if (stmt != null) {
try {
stmt.close();
} catch (sqlexception sqlex) {
// ignore, as we can't do anything about it here
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (sqlexception sqlex) {
// ignore, as we can't do anything about it here
}
conn = null;
}
}
}
}
As shown in the example above, after obtaining the JNDI
InitialContext
, and looking up the
DataSource
, the rest of the code follows familiar JDBC conventions.
When using connection pooling, always make sure that connections, and anything created by them
(such as statements or result sets) are closed. This rule applies no matter what happens in your
code (exceptions, flow-of-control, and so forth). When these objects are closed, they can be re-used;
otherwise, they will be stranded, which means that the MySQL server resources they represent (such
as buffers, locks, or sockets) are tied up for some time, or in the worst case can be tied up forever.
Sizing the Connection Pool
Each connection to MySQL has overhead (memory, CPU, context switches, and so forth) on both
the client and server side. Every connection limits how many resources there are available to your
application as well as the MySQL server. Many of these resources will be used whether or not the
connection is actually doing any useful work! Connection pools can be tuned to maximize performance,
while keeping resource utilization below the point where your application will start to fail rather than just
run slower.
The optimal size for the connection pool depends on anticipated load and average database
transaction time. In practice, the optimal connection pool size can be smaller than you might expect. If
you take Sun's Java Petstore blueprint application for example, a connection pool of 15-20 connections
can serve a relatively moderate load (600 concurrent users) using MySQL and Tomcat with acceptable
response times.
To correctly size a connection pool for your application, create load test scripts with tools such as
Apache JMeter or The Grinder, and load test your application.
An easy way to determine a starting point is to configure your connection pool's maximum number
of connections to be unbounded, run a load test, and measure the largest amount of concurrently
used connections. You can then work backward from there to determine what values of minimum and
maximum pooled connections give the best performance for your particular application.
Validating Connections
MySQL Connector/J can validate the connection by executing a lightweight ping against a server. In
the case of load-balanced connections, this is performed against all active pooled internal connections
that are retained. This is beneficial to Java applications using connection pools, as the pool can
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
