JBoss调优（二）数据库连接池调优

原文：

http://www.mastertheboss.com/jboss-server/jboss-performance/jboss-as-7-performance-tuning?showall=&start=1

Tuning the database connection pool

Establishing a JDBC connection with a DBMS can be quite slow. If your application requires database connections that are repeatedly opened and closed; this can become a significant performance issue. The connection pools in JBoss AS datasources offer an efficient solution to this problem.
What is important to stress out is that, when a client closes a connection from a data source, the connection is returned to the pool and becomes available for other clients; therefore, the connection itself is not closed. The cost of opening and closing pooled connections can be measured in terms of nanoseconds, so it's irrelevant in terms of performance.
In the following example, we are enhancing the datasource configuration exposed in Chapter 3, Configuring Enterprise Services with some connection pool configuration:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

< datasource jndi-name = "MySqlDS" pool-name = "MySqlDS_Pool"      enabled = "true" jta = "true" use-java-context = "true" use-ccm = "true" >      < connection-url >          jdbc:mysql://localhost:3306/MyDB      </ connection-url >      < driver >mysql</ driver >      < pool >          < min-pool-size >10</ min-pool-size >          < max-pool-size >30</ max-pool-size >          < prefill >true</ prefill >      </ pool >      < timeout >          < blocking-timeout-millis >30000</ blocking-timeout-millis >          < idle-timeout-minutes >5</ idle-timeout-minutes >      </ timeout > </ datasource >

Here, we configured an initial pool capacity of ten connections which can grow up to thirty. As you can see from the following MySQL administration console, when you set the pre-fill element to true, the application server attempts to pre-fill the connection pool at the start-up. This can produce a performance hit, especially if your connections are costly to acquire.

If the application server is not able to serve any more connections because they are all in use, then it will wait up to the blocking-timeout-millis before throwing an exception to the client.
At the same time, connections which have been idle for some minutes over the parameter idle-timeout-minutes, they are forced to return to the pool.

Adjusting the pool size

To determine the proper sizing, you need to monitor your connection usage. This can be done in several ways. If you have access to the Command Line Interface you can monitor the runtime properties of your datasource. Here’s a sample output of our example application delivered in chapter 4:
{xtypo_code}[standalone@localhost:9999 /]/subsystem=datasources/data-source="java:/MySqlDS":read-resource(include-runtime=true)
{
"outcome" => "success",
"result" => {
"ActiveCount" => "10",
"AvailableCount" => "29",
"AverageBlockingTime" => "0",
"AverageCreationTime" => "56",
"CreatedCount" => "10",
"DestroyedCount" => "0",
"MaxCreationTime" => "320",
"MaxUsedCount" => "5",
"MaxWaitCount" => "0",
"MaxWaitTime" => "1",
. . . .
}
}{/xtypo_code}
The output of this command is quite verbose however the most interesting attributes are located at the beginning of the output: in particular the ActiveCount attribute which displays the amount of connections which are currently active and the MaxUsedCount which is the peak of connections used by the application.

Beware: if you are prefilling the pool, as shown in the earlier section, these connection will all result Active. This could be misleading and lead you to assume they are actually busy. 
If you are not able to use the CLI or simply you want to make good use of your DBA certification there are some valid alternative as well: the first and most obvious is monitoring the database sessions. The following table shows some useful commands, which can be used to keep track of active database connections on different databases:

Database	Command / Table
Oracle	Query the V$SESSION view
MySQL	Use the command SHOW FULL PROCESSLIST
Postgre-SQL	Query the PG_STAT_ACTIVITY table

Another option is using a tool like P6Spy which acts as a JDBC proxy driver. (I've blogged an article about it here).
Once you have found the peak of connection used by your application, just set the maximum at least 25-30% higher. Don't be concerned about setting the maximum too high, because if you don't need that many connections, the pool will shrink automatically, provided that you have set idle-timeout-minutes.
On the other hand, your server logs are still an invaluable help to check if your pool is running in trouble. For example, if you start seeing this exception in your server logs, there is a strong clue that you need to look at your connection pooling:

21:57:57,781 ERROR [stderr] (http-executor-threads - 7) Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
21:57:57,782 ERROR [stderr] (http-executor-threads - 7)         at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection