Analysis of database connection pool (i)

Because of the recent project to use the database connection pool, so today simple chat ~,
This topic is caused because I wrote the MySQL database part of a question, the general background processing data part, the server is to create a connection connection to the database, and then all requests through this connection to access the database, Or does each connection create a connction? How long will this connection last? ... Let's see it together .
First, the directory is in order to elicit the following connection pool, because generally we just use the database, and ignore some of the intrinsic things, there may be some efficiency problems.

4,5 connection pool usage and optimal connection pool configuration Select I'll introduce you in the next blog post.

Total directory:
-1. What is the database connection process?
-2. What are the resources used for the connection?
-3. Introduction to Connection Pooling
-4.连接池的使用
-5.最优连接池配置选择

1. What is the connection process of the database?

The database itself is also a server-side program running, so to speak it is also a background server program, I am using MySQL, under the/etc/init.d there is a mysql.server, boot-up is started MySQL servers.
We typically use mysql-uroot-p 管理员 to create a connection using only the identity used to log in to MySQL.
The following is a careful talk about the MySQL connection process

MySQL connection is divided into two, one for UNIX domain socket, the other is based on the TCP/IP protocol, generally if the remote access to the database is definitely based on TCP/IP, but if we are in the native login will be divided into using 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 it, I grabbed a bag with tcpdump to see ^_^.
socket：

You can see that the UNIX domain socket is not caught because it is not through the NIC, Local.
tcp/ip：

TCP/IP caught the data, so we can see the connection process of MySQL, the internal is actually through the TCP/IP protocol, of course, MySQL encapsulated TCP/IP has its own set of protocols.

The following I each execute a SQL statement will have the corresponding packet, then it is obvious that MySQL connection mode and communication mode, in the exit MySQL caught 4 data, 4 times waved.

According to official website, UNIX domain socket connection is faster than TCP/IP connection

A MySQL Client on Unix can connect to the MYSQLD server in both different ways:by using a UNIX socket file to connect thro Ugh a file in the file system (Default/tmp/mysql.sock), or by using TCP/IP, which connects through a port number. A Unix Socket file connection is faster than TCP/IP, but can be used if connecting to a server on the same computer . A Unix socket file is used if you don ' t specify a host name or if you specify the special host name Loc-alhost. '
So on the same computer, we're using UNIX domain sockets.

Second, MySQL will create a thread to handle the incoming connection, we can show status in MySQL, and then in the connection to MySQL, show status again to see the number of thread_connected will increase by 1, Description creates a thread to handle this connection.

Look, we can see that the number of threads_connected connections is 1, because at this time only I have a connection mysql,threads_created of 3, indicating that there were 3 connection connected to the database, Threads_ Cached this is MySQL. In order to improve performance and provide a thread's connection pool internally, the idle connection is not immediately destroyed but placed in the thread connection pool, if the new add-in connection is not immediately create the thread, but first from the thread connection pool to find an idle connection thread, and then assign, If not, create a new thread. The MySQL interior has been optimized for us.

The threads_catched value is not infinitely large, usually around 32.
By the way, MySQL is able to adjust single-threaded and multithreaded modes, single threaded only allows one thread to connect to MySQL, and other connections will be rejected.
| thread_handling | one-thread-per-connection |

Summarize and supplement the above, then the database connection is roughly:
1. Apply data layer to DataSource request database connection
2.DataSource Open database connection using database driver
3. Create a database connection, internal may create a thread, open a TCP socket
4. Apply a read/write database
5. If the connection is no longer needed, close the connection
6. Close the socket

The connection process is simply speaking of this.

2. What are the resources occupied by the connection?

What are the resources for the

MySQL connection?
from the above can be seen that we generally write network programs are based on the TCP/IP connection to access the database, first of all, MySQL has the maximum number of connections.

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

My MySQL max connection number is 15 1, we can change the configuration file to change the maximum number of connections limit, but whether it is really good, to be discussed, because the system default settings will certainly be based on the system can withstand the connection or the resources to be given to limit. So we assume that the connection that we can provide without changing the parameters is 151.

First MySQL each connection is created by a thread that can log in to MySQL input show status to view the size of threads_connected and threads_created, then each time we connect MySQL will create a thread , and one thread is destroyed at a time of disconnection.

We all know that the resource consumption of creating threads and destroying threads is very large, otherwise it will not be the thread pool this thing, then! From a point of view, connection pooling avoids the frequent creation of connections and the destruction of connections (we know that MySQL has done threads_catched optimizations, but not enough), in fact, the internal also avoids the frequent creation of threads and destruction of threads! Is it very similar to the thread pool?, like one of my seniors said, XX pool name sounded very tall on, in fact, so the matter, the principle is interlinked, the principle is very important.
then thread creation and destruction, and the resources consumed we should be familiar with.
First each thread will allocate the stack space, can be viewed through ulimis-s, my Ubuntu 14.04 default is 8M, then 100 connections is 800M, eat memory. Second, the MySQL database allocates connection buffers and result buffers for each connection and consumes time.

Then each time each connection will be a TCP3 handshake and break 4 waves, allocate some cache space, remember to have seen a little bit about the stack of things, the data structure inside, waiting for the queue and other feelings are quite complex, and although the TCP connection consumes a lot of resources, The allotted time is also short, but if we can save would not be better ~ (about TCP connection and disconnected resource consumption I did not delve into, and then add to it, interested people can check the information).

3. Introduction to Connection Pooling

Call ~ finally to the theme connection pool, in fact, said so many are in order to highlight the connection pool of good ...
Database connection Pooling Technology The idea is very simple, the database connection as an object stored in a vector object, once the database connection is established, the different database access requests can share these connections, so that through the reuse of these established database connections can overcome the above shortcomings, Significant savings in system resources and time.
That is, we create these connections in advance, and then we need to get the connection. The idea of a thread pool is consistent.

(Image source Network)

Operation of connection pooling:
(1) Establish database connection pool object (server start).
(2) Create the initial number of database connections (that is, the number of idle connections) according to the parameters specified beforehand.
(3) For a database access request, get a connection directly from the connection pool. Create a new database connection 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).
(4) Access the database.
(5) Close the database, release all database connections (at this point, close the database connection, not really shut down, but put it in an idle queue.) Release the connection if the number of actual idle connections is greater than the number of initial idle connections).
(6) Release the database connection pool object (during server stop, maintenance, release the database connection pool object, and release all connections).

This is a simple introduction to this, the next chapter describes the use of connection pooling and its configuration problems.
Thank you for watching ~

Analysis of database connection pool (i)