JBossAS7 Performance Tuning (2)

Optimize database connection pool

The process of establishing a JDBC connection with DBMS may be quite slow. If your application needs to repeatedly open and close the database connection, this can become a significant performance problem. The connection pool of the data source in JBoss AS provides an effective solution to this problem.

It should be emphasized that when the client closes a data source connection, the connection is returned to the pool, which can be used by other clients. Therefore, the connection itself is not closed. The cost of opening and closing the connection to the pool management can be measured in nanoseconds, so its impact on performance does not matter.

In the following example, we will emphasize the data source configuration in Chapter 3 and use the connection pool configuration to provide enterprise services:

<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 have configured the capacity of the 10 initial connection pools, up to 30. As you can see in the following MySQL console, when you set the pre-fill element to true, the application server tries to create a connection in advance at startup. This may cause performance loss, especially if the connection is expensive.

If the application server cannot obtain more connections because all connections in the connection pool are in use, it will wait until the blocking timeout (blocking-timeout-millis ), an exception is thrown to the client.

At the same time, if the idle connection exceeds the time set by idle-timeout-minute, they are forced to return to the pool.

Adjust the pool size

To determine the appropriate pool size, you need to monitor the use of database connections. This can be done in several ways. You can use command lines to monitor the running performance of data sources. The following is an example of an application that will be described in Chapter 4th:

[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 ", .... } }

The output of this command is a bit long, but the most interesting attribute is the beginning of the output:ActiveCount, It shows the number of active connections,MaxUsedCountIs the maximum number of connections used by applications.

Note: If you have set a pre-initialized connection pool, these connections will remain active in the previous section. This may cause misunderstanding and lead you to think they have been very busy.

If you cannot use CLI or you only need to make good use of your DBA authentication, there are some effective options: first, the most obvious is to monitor database sessions. The following table lists some useful commands that can be used to track active connections in different databases:

Database	Command/table
Oracle	Query the V $ SESSION View
MySQL	Run the show full processlist command.
Postgre-SQL	Query the PG_STAT_ACTIVITY table

Another option is to use a tool like P6Spy, which acts as a JDBC proxy driver. (My blog post on it is here )).

Once you find the application uses the connection peak value, set at least 25-30% higher as the maximum value. Don't worry about setting the maximum value too high, because if you don't need so many connections, the pool will automatically contract, provided that you have set idle-timeout-minutes.

On the other hand, server logs are still a very valuable means to help you check the connection pool operation problems. For example, if you see this exception in your server log, you need to check whether the connection pool is normal:

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