I. Overview
1)The core of the connection pool technology lies in "connection reuse". To do that, we built a database connection pool and a series of policies for connection usage, allocation and management , which make it possible to reuse connections in the pool with efficiency and security, avoiding overheads from constantly setting up as well as closing database connections. Besides, since the original connection JDBC has been encapsulated in FineReport system, the use of connections (especially for transaction processing) by database applications is now facilitated, with the efficiency of accessing database connection remarkably improved. This is a result of the presence of an encapsulation layer, which isolated the processing logic within the application from specific database access logic, so that the application itself can be reused.
2)Connection pool status allows you to check the status of the current dataset.
| You will learn |
|---|
|
II. Instructions
1. User Interface
Note: only JDBC databases support the connection pool.
Take the local decision-making platform as an example.
1) Open the platform in Help -> Demo.
2) Go to Manage -> Data Connection -> Connection Pool Status to check the status.
3)You can see the numbers of current/maximum active connections and current/maximum idle connections on this page.
2. Connection Pool Basics
The connection pool mainly consists of 3 things, namely, establishment, governance and shutdown.
1) Establish a Connection Pool
The connection pool established in an application is actually static. By "static" we mean that the connections within the pool have already been allocated when initializing the system, and can not be easily closed. Java provides quite a few container classes to build a connection pool, including Vector, Stack, Servlet, Bean, etc. Connections can be established with the connection property file "Connections.properties". Connections need to be created with appropriate configurations and put into the pool when initializing the system, so that they can be acquired when needed, to avoid overheads from randomly setting up and closing database connections.
2) Manage a connection pool
The key point of the connection pool mechanism is the management strategy. Once the pool is established, the way to manage connections, their allocation and release in the pool have a great impact on system performance. With an appropriate allocation and release strategy, the reusability of connections and users' accessibility will be improved, reducing system overheads in establishing new connections. Please keep reading to learn more about the strategy to allocate and release connections in the pool.
Since the strategy is of great importance to effectively reuse connections, we've used a famous design pattern here, which is "Reference Counting". The pattern is widely implemented when handling resources reuse, so it is an appropriate way to handle the allocation and release of connections. For each of the database connections, we'll keep a reference to count its users.
To implement this pattern:
First, check if there are idle (or unallocated yet) connections in the pool when clients request a database connection. If so, allocate one of them to the client, mark it as "in use" and add 1 to the reference counter. If there aren't, then check the number of currently allocated connections and see whether it's already reached maxConn. If not, create a new one for the client; otherwise, wait for a maxWaitTime, and throw an exception of "no idle connection" when no connections can be allocated anymore.
When a client released a database connection, the first thing to do is to check if its reference counter has reached the specified limit. If so, delete this connection. Then check whether the number of connections in the pool goes below minConn, and the pool needs to be filled if it does; otherwise, mark the connection as available for reuse. It is evident that the strategy plays an important role in ensuring the effective reuse of database connections, making it possible to avoid system overhead due to constantly establishing and releasing connections.
3) Close a connection pool
The connection pool should be closed when exiting the application, and it's time to return all connection objects requested from the database at pool establishment (that is, to close all database connections). It is a revert process of establishing the connection pool.
After the pool allocates a connection, such as defining a dataset, the connection will be returned to the pool once the SQL query is executed with Preview.
3. Connection Pool Properties
FR Connection Pool is generally a DBCP connection pool. Below you can learn how to configure it, as well as all the information about parameters in its properties. If a connection is timed out or put in idle when accessing a template, the warning will be shown as follows:
Warning: Cannot get a connection, pool error Timeout waiting for idle object
at com.fr.third.org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
at com.fr.third.org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.fr.data.pool.MemoryConnection.create(Unknown Source)
at com.fr.data.impl.JDBCDatabaseConnection.createConnection(Unknown Source)
In this scenario, you will need to increase the limit of connections.
1) Setup Instructions
Go to Server -> Define Data Connection in the designer, and then click on Connection Pool Attributes and adjust the Max Active Connections:
2) Parameters
| Parameters | Examples | Meanings |
|---|---|---|
Initialize Connections | initialSize="1" | Number of threads at initialization to automatically start database connection |
Max Active Connections | maxActive="200" | Max number of objects that can be extracted from the pool. "0" means "no limit" |
Max Idle Connections | maxIdle="100" | Max number of idle connections. Any number less than 0 means "no limit" (or "max number of objects in the pool") |
Min Idle Connections | minIdle="2" | Min number of objects in the pool |
Max Wait Time(ms) | maxWait="1000" | Max wait time in milliseconds. Error messages will be thrown when the value is exceeded |
SQL Validation Query | validationQuery="SQL query" | Validate if it's successfully connected. SQL and SELECT query must return 1 line at least . |
Test before Getting Connections | testOnBorrow="false" | Determine whether or not to verify the validity of an object when obtaining it, set to "false" by default |
Test before Returning Connections | testOnReturn="true" | Determine whether or not to verify the validity of an object when returning it, set to "false" by default |
Open Idle Connections Recyclers Test | testWhileIdle="true" | Determine whether or not to verify the validity of an object when it's idle, set to "false" by default |
Sleep Time of Idle Connections Recyclers(ms) | timeBetweenEvictionRunsMillis="1000" | Time interval for the thread running failure check. Any value less or equal to "0" means "not to run thread check" |
Recycled and Tested Idle Connections | numTestsPerEvictionRun="2" | Number of thread running failure checks |
Keep Min Idle Time Value(s) | minEvictableIdleTimeMillis="18005000" | Value greater than 0 means to run checks on connections idle time; equal to 0 means no checks |
When the Max Active Connections value exceeds the number of connections in databases, the number in databases shall be in effect. Adjust the connection number in databases accordingly if you need more Max Active Connections.
III. Solution
If you have set the Max Active Connections in the Connection Pool Properties to too high and an error also occurs, it is usually caused by database processes that have reached the upper limit. You can increase the number of connections in the database to resolve this problem. Change the maximum number of connections in the database as follows:
View the current number of connections：
select count(*) from V$process;
View the maximum number of connections allowed by the database：
select value from V$parameter where name='processes';
Change the maximum number of connections to 1000：
alter system set processes=1000 scope=spfile
Restart the database and query the maximum number of connections. If the number changes, the modification is successful.
Note: When the customer releases the database connection, first determine whether the number of references of the connection has exceeded the specified value, if so, delete the connection, and determine whether the total number of connections in the current connection pool is less than minConn (minimum number of connections), if less than the connection pool full; If not, mark the connection as open for reuse. It can be seen that this strategy ensures the effective reuse of database connections and avoids the system resource overhead caused by frequent establishment and release of connections.
Previous：Advanced Setting in Data Connections
