MySQL database connection pooling knowledge sharing

Mysql database connection pooling knowledge sharing

Advantages of Use:

The biggest advantage of connection pooling is performance. Creating a new database connection is a time-consuming and resource-intensive process. With connection pooling, connection requests can be provided directly through the connection pool without the need for reconnection and authentication. This saves you time.

Disadvantages of using:

There may be multiple databases in the connection pool that are not being used, which means a waste of resources.

Basic principle:

connection pooling The basic idea is that when the system is initialized, the database connection is stored as an object in memory when the user invokes the database operation class mysqlconnection of the Open () method, instead of establishing a new connection, you remove an established idle connection object from the connection pool. After use, the user does not close the connection, but instead puts the connection back into the connection pool and waits for the next call. Connection pooling is managed by the connection pool itself. You can also set some parameters for the connection pool to control the number of initial connections in the connection pool, the upper and lower limits of connections, the maximum number of uses per connection, maximum idle time, and so on. It is also possible to monitor the number, usage, etc. of database connections through its own management mechanism.

Main attribute Description:

1. connection pooling enabled (pooling=true|false)

indicates whether connection pooling is enabled and connection pooling is not enabled when Pooling=true is enabled when connection pooling is turned on, and the default state is to enable connection pooling.

If connection pooling is enabled, when the database action class calls The close () method does not actually close the connection, but instead puts the connection back in the connection pool for the next use, and if the connection pool is not enabled, the database connection that is called when the Close () method is called is destroyed. The new connection needs to be re-established when the connection object needs to be used again.

2. Timeout period (connectiontimeout)

Refers to the maximum time that a connection object gets a connection in the connection pool, and if that time is exceeded, the program throws a system exception. connectiontimeout=0 means never time out.

3, the minimum number of connections (minimumpoolsize)

is a database connection with a minimum of connection pooling, and if the application has little use for database connections, some of the database connection resources will be wasted.

4, the maximum number of connections (maximumpoolsize)

is the maximum number of connections the connection pool can request, the system default number of connections is ,if the database connection request exceeds this number, subsequent database connection requests will be added to the wait queue, which will affect subsequent database operations.

Test process:

Software: VS2012,Navicat Premium,wampserver

1. Test Pooling, minimum number of connections, maximum number of connections, and timeout

A. Enable connection pooling: Set the minimum number of connections to 2, the maximum number of connections to 4, and the time-out to 5 seconds

650) this.width=650; "Width=" 553 "height=" 208 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 1-1)

B. Run the Connection Pool test program to establish a connection.

650) this.width=650; "Width=" 367 "height=" 189 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 1-2)

Run Navicatpremium, after establishing the connection (process slightly), execute the showprocesslist command. The discovery has actually established 2 connections. The results are as follows: (db is example for our test library)

650) this.width=650, "width=" 381 "height=", "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 1-3)

thus, when the minimum number of connections is set, even if only one connection is established in the program, the database side automatically establishes 2 connections.

C. Click the "Disconnect" button on the first connection. The connection status will be displayed as "Not Connected", and the show processlist command will find that there are still two connections in the connection pool (graphics slightly).

Description Call Close method does not actually close the connection, just put the connection back into the connection pool.

D. An attempt to establish 5 connections,

650) this.width=650; "width=" 389 "height=" 197 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 1-4)

650) this.width=650; "width=" 375 "height=" 219 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 1-5)

After clicking on the 5 connection for 5 seconds , the first 5 connections fail. This shows that the current connection pool can only hold 4 connections, and the program will report an exception after the timeout.

2. do not enable pooling

A. change pooling to False in the database connection string

B. Run the test program, establish a connection, (Figure 1-1), execute the showprocesslist command, result

650) this.width=650; "Width=" 349 "height=" 168 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 2-1)

C. Click the "Disconnect" button on the first connection to execute the show processlist again, as shown in the following:

650) this.width=650 "width=" 392 "height=" "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

(Figure 2-2)

The visible connection is really closed and is not saved in the connection pool.

MySQL database connection pooling knowledge sharing