Effectively play the maximum efficiency of the database
Snapbug
DEC 2003?
1. Foreword database application, often used in many software systems, is an indispensable assist in the development of large systems in the development. However, if there is no good management of database resources (eg, there is no resource such as a prominet, STATEMENT, Connection, etc.), often leads to the stability of the system. Such unstable factors, not only by the database or system itself, only after the system is officially used, will gradually expose with the increase of traffic, the user increases. ?
In a Java-based system, JDBC is the main way to deal with programmers and databases, providing a complete database operation method interface. However, JDBC only provides the most direct database operating specification, and the database resource management, such as the management and buffer of the physical connection, and is desired to provide a third-party application server. ?
In this paper, based on JDBC specification, introduce the relevant database connection pool mechanism, and if you realize the effects of efficient management of database resources, you can realize the implementation technology of the database resource. ?
2. ???? connection pool technology background 2.1 ???? JDBC JDBC is a specification that follows the JDBC interface specification, each database manufacturer's own driver (DRIVER), as shown below:
?
Application When obtaining a database connection, you need to specify the type of Driver in a URL. After getting a specific connection, you can operate different types of databases in accordance with the fixed interface, such as: STATEMENT, execute SQL, resulting, etc., Such as follows:
Import java.sql. *;
... ..
?
?
DriverManager.RegisterDriver (new oracle.jdbc.driver.OracleDriver ());
Connection dbconn = drivermanager.getConnection
"JDBC: Oracle: Thin: @ 127.0.0.1: 1521: Oracle",
"Username", "password");
?
Statement ST = dbconn.createstatement ();
ResultSet RS = St.executeQuery ("Select * from demo_table");
?
... Some Data Source Operation In Here
?
Rs.close ();
St.close ();
Dbconn.close ();
?
After completing the data operation, it is necessary to close all database resources involved. This does not have any effects on the logic of the application, but the key operation. The above is a simple example, and if more IF-ELSE, Exception, the management of resources is also inevitable. As the memory leak problem in C, Java system will also face the crash. Therefore, the management of database resources depends on the application system itself, is an unsafe, unstable hidden danger. ?
2.2 ???? JDBC Connection Pool In the interface of the standard JDBC, there is no management method for resources. Therefore, the default resource management is responsible for applying itself. Although in the JDBC specification, the closure / recovery of resources and other reasonable applications are mentioned many times. But the most secure way is to provide effective management means for applications. Therefore, JDBC provides a management standard interface implemented by a database manufacturer for a third-party application server (Connection Pool). The concept of connecting pool is introduced, that is, manages the resources of the database with the mechanism of the buffer pool. ? JDBC's most commonly used resources have three categories: - ?????????? Connection: Database connection. - ????????? statement: session statement. - ????????? resultset: Results gauge. • There is the following relationship, respectively:
This is a relationship of '- parent - sub', the management of Connection, is the management of database resources. For example: If you want to determine if a database connection is timeout, you need to make sure it (all) substi is timeout, the same, you need to determine if all related results are timeout; before turning off the connection, you need to close all relevant Statement and resultSet. ?
Therefore, the role of connecting pool is not just simply managing Connection, but also on Statement and ResultSet. ?
2.3 ???? ConnectionPool and Resource Management ConnectionPool With buffer pool mechanism, control management Connection, Statement, and ResultSet within a certain amount of upper limit. The resources of any database are limited, and more data services cannot be obtained if they are exhausted. ?
In most cases, the depletion of resources is not due to the normal load of the application, but the cause of the program.
?
In actual work, data resources are often bottleneck resources, and different applications will access the same data source. One of the applications run out of the database resources, other applications can not run normally. Therefore, the first task of ConnectionPool is to limit: the maximum resource that can be owned each application or system. That is to determine the size of the connection pool (Poolsize). ?
The second task of ConnectionPool: Use resources to maximize the use of resources to the poolsize range. In many databases, connectivity is not the smallest unit of resources, and the STATEMENT resource is more important than Connection. Take oracle as an example:
Each application will establish a connection for communication on a physical network (such as TCP / IP network), and you can also apply for a certain number of statement on this connection. The active statement number of available connections can reach several hundred. While saving network resources, shortening each session cycle (the establishment of physical connection is a fee). However, in the general application, most of them operate in accordance with the 2.1 example, there is 10 programs, which will generate 10 physical connections, each Statement, separately, is a very resource waste. ConnectionPool can solve this problem, let hundreds, hundreds of Statement only occupy the same physical connection, and play the original advantages of the database. Through ConnectionPool's effective management of resources, the total number of STATEMENTs can be obtained: (concurrent physical connection) X (number of STATEMENTs available for each connection), for example, some database can be established at the same time of 200, each The connection can also provide 250 Statement at the same time, then the total number of concurrent STATEMENTs provided by ConnectionPool is: 200 x 250 = 50,000. This is a concurrent number, very few systems will break through this level. Therefore, at the beginning of this section, it is pointed out that the depletion of resources is related to the direct management of applications. ?
For the optimization management of resources, it is largely relying on whether the database itself has a JDBC DRIVER. Some databases JDBC Driver does not support logical connection between Connection and Statement, such as SQL Server, we can only wait for her own update version. ?
For resource application, release, recycling, sharing, and synchronization, these management are complex and precise. Therefore, another feature of ConnectionPool is to encapsulate these operations, providing simple, or even do not change the application style call interface. ?
3. ???? Simple JDBC Connection Pool Realization According to the principle mechanism in Chapter 2, Snap-ConnectionPool (a simple and fast connection pool tool, can be downloaded in accordance with part of the JDBC specification, The connection pool is equipped with the database resource valid management function. ?
3.1 ???? System Description In the JDBC specification, apply the resources of the DRIVER Interface Direct Method Database. For effective, reasonable management of resources, the connection pool is added between the application and JDBC DRIVER: SNAP-ConnectionPool. And through the object-oriented mechanism, most of the operation of the connection pool is transparent. See the following figure, SNAP-ConnectionPool system:
?
As shown in the figure, three logical resource objects are generated in the Snap-ConnectionPool inside the Snap-ConnectionPool in the figure, and three logical resource objects are generated in Snap-ConnectionPool: PooledConnection, PooledStatement and PooledResultSet. They are also mainly managed operation objects that connect pools and inherit the corresponding dependence in JDBC. Such a system has the following characteristics:?
- ????????? Transparency. Provide resource management services without changing the application of the original JDBC driver interface. Application system, like the original JDBC, using the logical object resources provided by the connection pool. Simplified the application's connection pool transformation. - ????????? Resource package. Complex resource management is encapsulated inside the Snap-ConnectionPool, and there is no need to interfere with excessive application. The reliability of the management operation is guaranteed by the connection pool. Interference (such as: Active Close Resources), only the role of optimizing system performance, and missing operations will not have a negative impact. - ????????? Resource reasonable application. According to the dependence of resources in JDBC, Snap-ConnectionPool is not only buffering Connection, but also has corresponding mechanisms to Statement. In 2.3, the relationship between Connection and Statement is reasonably used, and resources can be used in greater limits. So, Snap-ConnectionPool encapsulates the Connection resource, manages the PooledConnection inside, provides more Statement resources for the application. - ????????? Resource chain management. Snap-connectionPool includes three logical objects that inherit the dependence between the corresponding objects in the JDBC. In internal management, chain management is also performed in accordance with the dependent relationship. For example, if it is determined whether a connection is timeout, it is necessary to determine whether the STATEMENT is active; it is judged that Statement should also be based on the activity of ResultSet. ? 3.2 ???? Connecting pool central management ConnectionManager ConnectionPool is the connection pool object of Snap-ConnectionPool. In Snap-ConnectionPool, you can specify a plurality of different connection pools (ConnectionPool) for application services. ConnectionManager Manages all connection pools, each connection pool differently. Adapted to different database types by profile. As shown below:
With ConnectionManager, multiple different connection pools can be managed at the same time, providing a pass management interface. In the application system through the ConnectionManager and related profiles, you can scatter the database configuration information (including: database name, user, password, etc. information) in a file in a file. Easy to maintain the system's maintenance. ?
3.3 ???? Connecting pool use example to the standard JDBC of 2.1, change to use the connection pool, the result is as follows:
Import java.sql. *;
Import net.snapbug.util.dbtool. *;
... ..
?
ConnectionPool dbconn = connectionsManager.getConnectionPool ("testoracle");
?
Statement ST = dbconn.createstatement ();
ResultSet RS = St.executeQuery ("Select * from demo_table");
?
... Some Data Source Operation In Here
?
Rs.close ();
St.close ();
?
?
In the example, SNAP-ConnectionPool encapsulates the application of the application to the Connection. As long as the JDBC gets the connection pool (bold portion), other data operations can not be modified for the CONNECTIONPOOL. According to this way, Snap-ConnectionPool helps to use effectively manage database resources. If the application ignores the release of the final resource: rs.close () and st.close (), the connection pool will be automatically recycled through the time-out mechanism. ????? Skumber, whether it is Snap-ConnectionPool or other database connection pool, should have basic functions: - ????????? Solid database resources - ?????? ??? Take advantage of the effective resources of the database - ????????? Simplified the application's database interface, enclose resource management. - ????????? For the automatic recycling and finishing of the legacy resource, improve the re-utilization of resources. Under this premise, the application can put more energy in their respective business logic. Database resources are no longer a bottleneck of the system. Note: Download free SNAP-ConnectionPool and more detailed documentation for free at website www.snapbug.net. ?
