In-depth exploration of database connection Performance

When designing a database application, you may feel that the database connection type is not important. If so, you are wrong, the following lists some reasons why the connection type is so important:

◆ Compared with other tasks executed by database applications, creating database connections consumes the most system resources.

◆ When the database connection is enabled, the memory consumed by the server and client is relatively large.

◆ When establishing a connection, you need to make multiple round-trips between the server and the client

◆ Too many connections may cause insufficient memory, resulting in switching pages from memory to disk, which will significantly degrade the performance.

◆ In the current popular architecture, most applications use connection pools, which improves the performance. However, poor connection pool adjustment may also cause a significant reduction in performance, it is difficult to design, adjust, and monitor the connection pool.

Follow these steps:

◆ Obtain a connection from the connection pool

◆ Create only one connection at a time as needed

Correct decisions mainly depend on the CPU and memory conditions of the database server.

Why is the connection overhead large?

Developers often think that creating a database is a simple requirement. Isn't it a network round-trip between the database server and the initialized user? But in fact, A database connection has multiple network trips between the driver and the database server. For example, when a driver is connected to Oracle or Sybase, the connection has 7 to 10 and network connections, A series of actions are performed:

◆ Verify the user's ID card

◆ Negotiate between the desired code page setting of the database driver and the available code page setting of the database (if necessary)

◆ Obtain database version information

◆ Determine the optimal database protocol package size for communication

◆ Set session settings

In addition, the best practice for database management developers to assume the responsibilities of Database Administrators) the system should determine the resources used to establish connections, including disk I/O and memory allocation with high performance overhead, you may think that deploying an application to a database server will eliminate network round-trips. In most cases, this is not realistic, because most enterprises in the real world will not do this, they have a lot of applications and a lot of databases. In addition, the database server will make careful performance adjustments for the database system, and generally will not make adjustments for different applications, even if you can install databases and applications on a single machine, this also has the risk of single point of failure.

Use connection pool

The connection pool is the cache of one or more reusable physical database connections. The connection pool can significantly improve performance, because repeated connections can effectively reduce the system overhead for establishing physical connections, it must be noted that your database server must have enough memory to manage connections in the connection pool.

The focus of our discussion is the Client Connection Pool (the connection pool provided by the database driver and Application Server) rather than the server connection pool (the connection pool provided by the database management system ), some database management systems provide connection pools to work with client connection pools. Although different server connection pools have their own characteristics, the overall goal is to eliminate the system overhead of database servers when establishing and removing connections. Unlike the client connection pool, the server connection pool will not be optimized to the network round-trip of the application.

As we mentioned earlier, the system overhead for connecting to the database is relatively large, because the database-driven resource allocation (Network round-trip between the driver and the database) the resource allocation overhead of the database server is relatively large, and the client connection pool can only solve the problem of free allocation on the database server.

How the connection pool works

In the connection pool environment, once an initial physical connection is established, it is likely that it will not be closed during the lifecycle of the environment, that is, when the application is disconnected, the physical connection will not be closed, on the contrary, it still exists in the connection pool and can be reused, so reestablishing a connection is faster. The following is a brief introduction to how the connection pool works:

◆ When an application or application server is started, the connection pool starts to be connected;

◆ The application generates a connection request;

◆ The driver or connection pool manager (depending on your architecture) allocates a connection from the connection pool to the application without creating a new connection, this means that there will be no network round-trip between the driver and the server, because the connection in the pool is available, so the connection speed is faster;

◆ Connect the application to the database;

◆ When the connection is closed, it is put back in the connection pool.

When do I not use the connection pool?

Some applications are not suitable for using the connection pool. If your application has the following features, it is not suitable for using the connection pool. In fact, forcible use of the Connection Pool on these applications will lead to performance degradation:

◆ The application is restarted multiple times a day: This usually occurs in the architecture where the application server is not used and depends on the configuration of the connection pool, each time an application starts, it may write a record to the connection pool, which increases the system overhead of the connection pool;

◆ Single-user applications, such as report writing programs: If your applications only need to establish a connection for a single user, it is unnecessary to use the connection pool;

◆ Applications that run single-user Batch jobs, such as daily/weekly/Monthly Report programs: the connection pool is not suitable for batch job applications, because Batch jobs usually have only one connection, in addition, batch jobs are often executed during low business periods.

When your application does not use the connection pool, it is recommended that you do not frequently connect to or disconnect when executing SQL statements, because each connection will produce 5-10 network requests.

Design connection

Let's take a practical example to illustrate how to design a database connection. The environment details are as follows:

◆ The intermediate layer in the environment must support 20-concurrent database users. performance is the key

◆ CPU and memory on the intermediate layer and database server are adequate

◆ The database is Oracle, SQL Server, Sybase or DB 2.

◆ The APIs used by applications are ODBC, JDBC, and ADO. NET.

◆ The database server has 25 connection licenses

Below are some feasible solutions:

◆ Solution 1: Use a connection pool. A maximum of 20 connections are supported. Each statement is connected by one connection.

◆ Solution 2: the connection pool is used. A maximum of five connections are supported, with one connection per five statements.

Use a connection for every 5-25 Statements

In this example, the most important thing is that the middle layer and database server must have sufficient CPU and memory capacity, and the database server must have sufficient permissions. Other information is irrelevant to the database connection design.

Solution 1 is the best, it is better to execute than the other two, because each connection executes a statement faster, the architectures of solution 2 and solution 3 use one connection for multiple statements, and a single connection becomes a bottleneck.

Summary

Many factors may affect performance, and some may be beyond your control. However, after carefully designed applications and intermediate layer configurations, the performance can be optimized, we recommend that you use the database connection pool or create a connection once as needed when designing an application.

1. Three Suggestions for DB2 database design
2. Does NoSQL really end relational databases?
3. Oracle Server parameter file maintenance skills
4. SQL Server database query optimization skills
5. 30 Basic DB2 database operation commands