Analysis of Database Connection Pool (I)

Analysis of Database Connection Pool (I)

The database connection pool is used in recent projects ~,

This topic is caused by a question when I write a mysql database. Generally, when the data is processed in the background, the server creates a connection to connect to the database, then all requests access the database through this connection, or create a connction for each connection? How long does the connection last ?... Let's take a look.

First, directory 1 and 2 are used to bring out the following connection pool, because we only use the database and ignore some internal things, which may cause some efficiency problems.

4. I will introduce how to use the connection pool and how to configure the optimal connection pool in the next blog.

Total directory:
-1. What is the database connection process?
-2. What resources are occupied by the connection?
-3. Connection Pool Introduction
-4. Use of the Connection Pool
-5. Optimal connection pool configuration Selection

1. What is the database connection process?

The database itself is also running a server program. It can be said that it is also a background server program. I am using mysql, in/etc/init. d. There is a mysql. server.
We generally use mysql-uroot-p to create a connection as an administrator and log on to mysql.
Next, let's take a closer look at the mysql connection process.

Mysql connections can be divided into two types: unix domain socket and TCP/IP protocol. Generally, If we remotely access the database, it must be based on TCP/IP, however, if we log on to the local machine, it will be divided into socket or TCP/IP.
Socket: mysql-h localhost-uroot-p or mysql-uroot-p
TCP/IP: mysql-h 127.0.0.1-uroot-p

To prove this, I use tcpdump to capture a package and check it. ^_^
Socket:

We can see that the unix domain socket is not caught because it does not pass through the nic and is local.
TCP/IP:

TCP/IP captures data. We can see that the connection process of mysql is actually through the TCP/IP protocol. Of course, mysql encapsulates TCP/IP with its own protocol. <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4NCjxwPs/Cw + bO0sO/authorization + response/response + 3aOsNLTOu9PK1t/CoaM8L3A + DQo8cD6 + response/response + response = "here write image description "src =" http://www.bkjia.com/uploads/allimg/150613/0412405540-2.png "Title =" \ "/>

We can see that the number of connections to Threads_connected is 1, because only one of me is connecting to mysql, and Threads_created is 3, which indicates that three connections have connected to the database, threads_cached: mysql provides a thread connection pool internally to improve performance. Instead of destroying idle connections immediately, it stores them in the thread connection pool, if the newly added connection does not create a thread immediately, it first finds the idle connection thread from the thread connection pool, and then allocates the thread. If not, a new thread is created. We can see that mysql has been optimized internally for us.

The value of Threads_catched is not infinitely large, generally about 32.
By the way, mysql can adjust the single-thread and multi-thread modes. A single thread only allows one thread to connect to mysql. Other connections will be rejected.
"Thread_handling | one-thread-per-connection |

To sum up and supplement the above, the database connection is roughly as follows:
1. The application data layer requests database connection from DataSource
2. The DataSource uses the database Driver to open the database connection.
3. Create a database connection. A thread may be created internally to open a TCP socket.
4. Application read/write Database
5. Close the connection if the connection is no longer needed
6. Disable socket

The connection process is simple.

2. What resources are used by connections?

What resources are occupied by mysql connections?
It can be seen from the above that the network programs we generally write are connected to the database based on TCP/IP. First, mysql has the maximum number of connections.

Input/Usr/bin/mysqladmin-uroot-p variables | grep max_connections

The maximum number of connections in mysql is 151. We can change the configuration file to change the maximum number of connections, but whether it is really good remains to be discussed, because the default settings of the system will definitely be limited based on the connections that the system can afford or the resources it provides. So we assume that the connection provided without changing the parameters is 151.

First, mysql creates a thread for each connection. You can log on to mysql and enter show status to view the sizes of Threads_connected and Threads_created. Then, each connection to mysql creates a thread, each time a thread is disconnected, a thread is destroyed.

We all know that the resource consumption for thread creation and thread destruction is very large, otherwise there will be no thread pool, so! From a certain perspective, the connection pool avoids frequent connection creation and destruction (we know that mysql has optimized Threads_catched, but it is not enough ), in fact, frequent thread creation and destruction are also avoided internally! Is it similar to a thread pool ?, As one of my senior students said, the name of the XXX pool sounds very high. In fact, the principle is the same and the principle is very important.
We should be familiar with thread creation, destruction, and resource consumption.
First, each thread will allocate stack space, which can be viewed through ulimis-s. My ubuntu 14.04 is 8 MB by default, so the 100 connections are 800 mb, which is very memory-consuming. Next, the mysql database allocates a connection buffer and a result buffer for each connection, which also consumes time.

Next, each connection will send a tcp3 handshake and a 4-fold handshake when it is disconnected, and allocate some space such as cache. Remember to read a little bit about the protocol stack and the data structure in it, waiting for a queue is complicated. Although the tcp connection consumes a small amount of resources and the allocation time is short, it would be better if we can save it ~ (I have not studied the consumption of tcp connection and disconnection resources. Please try again later ~, If you are interested, you can check the information ).

3. Connection Pool Introduction

Call ~ The topic connection pool is finally reached. In fact, all the above mentioned are to highlight the connection pool ......
The idea of database connection pool technology is very simple. database connections are stored as objects in a Vector object. Once a database connection is established, different database access requests can share these connections, by reusing these established database connections, you can overcome these shortcomings and greatly save system resources and time.
That is, we have created these connections in advance, and then we need to use the get connection. The idea is consistent with that of the thread pool.

(Image Source Network)

Operations on the connection pool:
(1) Create a database connection pool object (server startup ).
(2) create an initial number of database connections (idle connections) based on the specified parameters ).
(3) For a database access request, a connection is directly obtained from the connection pool. If there is no idle connection in the database connection pool object and the number of connections does not reach the maximum (that is, the maximum number of active connections), create a new database connection.
(4) access the database.
(5) Close the database and release all database connections (close the database connection at this time, rather than actually close it, but put it into the idle queue. If the actual number of idle connections is greater than the initial number of idle connections, the connection is released ).
(6) release the database connection pool object (during server stop and maintenance, release the database connection pool object and release all connections ).