Effectively maximize the efficiency of the database

Data | database
Effectively maximize the efficiency of the database

Snapbug

DEC 2003



1. Foreword

database applications, which are often used in many software systems, are indispensable to the development of large-scale systems. However, if the database resources are not well managed (such as: the cursor (ResultSet), Statement, Connection (Connection), etc.), it will lead directly to the stability of the system. This kind of instability factors, not only by the database or the system itself, only after the formal use of the system, with the flow, the increase in users will gradually reveal.



In the system based on Java development, JDBC is the main way for programmers to deal with database, and provides a complete database operation method interface. However, given the applicability of the specification, JDBC only provides the most direct database operation specification, the management of database resources, such as: the management and buffering of physical connection, expect the provision of Third-party application Server (Application Server).



Based on the JDBC specification, this paper introduces the related database connection pool mechanism, and introduces the related implementation technology if the database resource is managed effectively in a simple way.


2. Connection Pool Technical Background 2.1 JDBC
JDBC is a specification that follows the JDBC interface specification and each database manufacturer implements its own driver (Driver), as shown in the following illustration:





When you get a database connection, you need to specify the type of driver as a URL, and after you get a specific connection, you can manipulate different types of databases, such as obtaining statement, executing SQL resultset, and so on, as in the following example:


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



Rs.close ();

St.close ();

Dbconn.close ();




After you complete the data operation, you must also close all the database resources involved. This has no effect on the logic of the application, but it is a key operation. The above is a simple example, if mixed with a large number of if-else, exception, the management of resources will inevitably be sparse. As with the memory leak problem in C, the Java system also faces a crash of bad luck. So



The management of database resources relies on the application system itself, which is a hidden danger of insecurity and instability.


2.2 JDBC Connection Pool
In the interface of standard JDBC application, there is no management method for resources. Therefore, the default resource management is the responsibility of the application itself. Although in the JDBC specification, several references are made to the shutdown/recycling of resources and other reasonable uses. But the safest way is to provide an effective management tool for the application. As a result, JDBC provides a management standard interface for Third-party application servers (application server) implemented by the database manufacturer: Connection buffering (connection pooling). The concept of the connection pool (Connection pool) is introduced, which is to manage the resources of the database with the buffer pool mechanism.



There are three types of JDBC resources that are most commonly used:

-Connection: Database connection.

-Statement: Session declaration.

-ResultSet: Result set cursor.

The following relationships exist:



This is a ' ye-father-son ' relationship, the management of connection, is the management of database resources. For example, if you want to determine whether a database connection (Connection) has timed out, you need to determine whether its (all) child statement timed out, as well as the need to determine whether all related resultset timed out; before closing Connection, All associated statement and ResultSet need to be closed.



Therefore, the role of the connection pool (Connection pool) is not simply to manage Connection, but also to statement and ResultSet.


2.3 Connection Pool (ConnectionPool) and resource management
ConnectionPool to buffer pool mechanism, within a certain number of upper limits, control management connection,statement and resultset. The resources of any database are limited and, if depleted, more data services are not available.



In most cases, the exhaustion of resources is not due to the normal load of the application, but to the procedural reasons.



In the practical work, the data resource is often the bottleneck resource, the different application will access the same data source. When one of these applications runs out of database resources, it means that other applications are not functioning properly. Therefore, the first task of ConnectionPool is to limit the maximum resources that each application or system can have. That is to determine the size of the connection pool (poolsize).



ConnectionPool's second task: to maximize resources and shorten the usage cycle of database access within the size of the connection pool (poolsize). In many databases, connectivity (Connection) is not the smallest unit of resources, and controlling statement resources is more important than Connection. Take Oracle for example:

Each application for a connection (Connection) establishes a connection for communication on a physical network (such as a TCP/IP network), and a certain number of statement can be requested on this connection. The number of active statement that can be provided by the same connection can reach hundreds of. While saving the network resources, it shortens the time of each session (the establishment of physical connection is a time-consuming operation). However, in general applications, most of the 2.1 paradigm operations, so that there are 10 program calls, it will produce 10 physical connections, each statement a single physical connection, which is a great resource waste. ConnectionPool can solve this problem, let dozens of, hundreds of statement only occupy the same physical connection, play the original advantages of the database.

By connectionpool the effective management of resources, the total number of statement that can be obtained by application arrives:

(Number of concurrent physical connections) x (number of statement each connection can provide)

For example, a database can be established at the same time the number of physical connections to 200, each connection can provide 250 statement, then ConnectionPool final for the application provided the total number of concurrent statement: x 250 = 50,000. This is a concurrent number, and few systems will break through this magnitude. So at the beginning of this section, it is noted that resource depletion is related to application direct management.



The optimal management of resources relies heavily on the JDBC driver of the database itself. Some database JDBC driver do not support logical connectivity between connection and statement, such as SQL Server, we can only wait for her own updated version.



The management of resource applications, releases, recoveries, shares, and synchronizations is complex and sophisticated. So, another function of ConnectionPool is to encapsulate these operations, providing a simple, even not changing, application-style invocation interface for the application.




3. Implementation of simple JDBC Connection pool
According to the principle mechanism in the second chapter, Snap-connectionpool (a simple and fast connection pooling tool, which can be downloaded in www.snapbug.net), realizes the effective management function of the connection pool to the database resource according to the JDBC specification of the part.


3.1 System Description
In the JDBC specification, resources are applied directly to the method database through the driver interface (Driver Interface). In order to manage resources effectively and rationally, there is an increased connection pool between application and JDBC driver: Snap-connectionpool. And through the object-oriented mechanism, the majority of the operation of the connection pool is transparent. See the following figure, the Snap-connectionpool system:





As shown in the figure, three logical resource objects are generated within Snap-connectionpool by implementing some of the resource object interfaces of JDBC (Connection, Statement, ResultSet): Pooledconnection, Pooledstatement and Pooledresultset. They are also the primary administrative operations object of the connection pool, and inherit the corresponding dependencies in JDBC. Such a system has the following characteristics:



Transparency Provide resource management services without changing the original use of JDBC-driven interfaces. Application systems, like the original JDBC, use the logical object resources provided by the connection pool. Simplifies the application's connection pool transformation.

-Resource encapsulation. Complex resource management is encapsulated within the Snap-connectionpool and does not require excessive interference from the application system. The reliability and security of the management operation are ensured by connection pooling. Application of interference (such as: active shutdown of resources), only to optimize the performance of the system, the omission of the operation will not bring negative impact.

-Rational use of resources. According to the subordinate relationship of the resources in JDBC, snap-connectionpool not only connection the buffer, but also has the corresponding mechanism to deal with the statement. In 2.3 It has been described that the rational use of the relationship between connection and statement can be used more limited by resources. Therefore, Snap-connectionpool encapsulates connection resources and provides more statement resources for application systems through internal management pooledconnection.

-Resource chain management. The Snap-connectionpool contains three logical objects that inherit dependencies between the corresponding objects in JDBC. In the internal management, also according to the subordinate relation carries on the chain management. For example: To determine whether a connection timeout, need to be based on the included statement whether active, Judge statement also according to ResultSet active degree.


3.2 Connection Pool Centralized management ConnectionManager
ConnectionPool is the Snap-connectionpool connection pool object. Within the Snap-connectionpool, multiple different connection pools (ConnectionPool) can be specified for the application service. ConnectionManager manages all connection pools, and each connection pool is distinguished by different names. The configuration file adapts to different database types. As shown in the following illustration:

Through ConnectionManager, you can manage several different connection pools at the same time, providing a management interface of one. In the application system through ConnectionManager and related configuration files, can be scattered in their respective applications of the database configuration information (including: database name, user, password and other information), centralized in a file. Facilitate the maintenance of the system.


3.3 Connection Pool Usage examples
Using the example of standard JDBC for 2.1, the connection pool is used instead, with the following results:


Import java.sql.*;

Import net.snapbug.util.dbtool.*;

.....



ConnectionPool dbconn = Connectionmanager.getconnectionpool ("testoracle");



Statement st = Dbconn.createstatement ();

ResultSet rs = st.executequery ("SELECT * from demo_table");



... some data source operation in



Rs.close ();

St.close ();






In the example, Snap-connectionpool encapsulates the management of the application for connection. As long as you change the way JDBC gets connection, for the connection pool (ConnectionPool) (The bold part), other data operations can be modified without modification. In this way, Snap-connectionpool can help the application manage database resources efficiently. If the application ignores the release of the Last resource: Rs.close () and St.close (), the connection pool is automatically recycled via a timeout (time-out) mechanism.


4. Summary
Both Snap-connectionpool and other database connection pools should have basic functionality:

-Protection of source database resources

-Make the most of the effective resources to play the database

-Simplify the application of database interface, closed resource management.

-Automatic recovery and collation of the application of legacy resources to improve the reuse of resources.

In this context, the application can devote more effort to its own business logic. Database resources are no longer a bottleneck in the system.

Note: Snap-connectionpool and more detailed documentation are available for free download at the website www.snapbug.net.