Database "persistent connection" and "short connection"

What is persistent connection?
In fact, persistent connections are relative to common short connections, that is, to maintain the connection between the client and the server for a long time.
The common short connection procedure is as follows:
Connection-data transmission-close the connection;
The persistent connection is usually:
Connection-data transmission-keep connection... -Close the connection;
This requires that long connections send data packets regularly when there is no data communication to maintain the connection status. Short connections can be closed directly when there is no data transmission.

When can I use persistent connections or short connections?
Persistent connections are mainly used inMinorityClient and serverFrequentCommunication, because in this case, if a short connection is used for frequent communication, a socket error will often occur, and frequent creation of socket connections is also a waste of resources.
However, for the server, persistent connections consume certain resources and require dedicated threads (process management can be used in UNIX) to maintain the connection status.
In short, the choice of persistent connection and short connection depends on the situation.

First, if a persistent connection is used and no operations are performed on the database for a long time, the MySQL server closes the connection after the timeout value, when the client executes the query, it will get an error similar to "MySQL server has gone away.
After using mysql_real_connect to connect to the database, use mysql_options (& MySQL, mysql_opt_reconnect ,... To automatically reconnect. In this way, when the MySQL connection is lost, you can use mysql_ping to automatically reconnect to the database. If it is before MySQL 5.1.6, execute mysql_options (& MySQL, mysql_opt_reconnect ,... ), If it is MySQL 5.1.6 +, it is enough to execute one operation before connect.

View MySQL connections
Mysqladmin-uroot-P processlist
In actual tests, I found that when mysql_opt_reconnect is set to 1 and processlist is checked after timeout, The automatically established connection is not in the list, but the connection is actually established and used.
 
In the default settings of MySQL, if a database is not used for more than 8 hours (idle for 8 hours), the server will disconnect this connection, subsequent query operations on the connection will fail. This problem is described in many ways on the network. Corresponding solutions are also provided. I would like to give some comments here.
Solution 1: modify the configuration parameters of the MySQL server
The principle is very simple. The default setting of MySQL is to disconnect the database after it is not used for more than 8 hours. If we change this time to a larger value, the connection timeout takes a longer time, which means it is not easy to time out. The modification method provided on the network is generally to modify/etc/My. CNF. Add a line in this file wait_timeout = the timeout time you need to set. In fact, there is a simple method to modify this parameter:
First, log on to MYSQL as a Super User. Be sure to be a Super User. Otherwise, you will be prompted not to modify the permission. Enter
Show global variables like 'wait _ timeout ';
Press enter to display the current Timeout:
+ --------------- + -------------- +
| Variable_name | value |
+ --------------- + -------------- +
| Wait_timeout | 28800 |
+ --------------- +
1 row in SET (0.00 Sec)
The above shows the default timeout time, that is, 8 hours (in seconds ). Set this parameter again. For example, to set the timeout time to 10 hours, enter:
Set global wait_timeout = 36000;
Press enter to execute and display:
Query OK, 0 rows affected (0.00 Sec)
The setting is successful. You can use show global variables like 'wait _ timeout' to verify the setting.
This method is intuitive and the set parameters take effect immediately. However, If/etc/My. CNF is not configured, after the service is restarted, the global variable reads the new variable value from/etc/My. CNF.
 
Below is a sample code:
If (! Mysql_real_connect (& logdb, my_hostname, my_user, my_password, my_dbname, my_port, my_sock, 0 )){
Ast_log (log_error, "failed to connect to MySQL database % s on % S. \ n", my_dbname, my_hostname );
Use_mysql = 0;
} Else {
Char value = 1;
Mysql_options (& logdb, mysql_opt_reconnect, (char *) & value );
Use_mysql = 1;
}

Bytes ------------------------------------------------------------------------------------------------------------------------------

For example, if the HTTP connection-alive is close, the server will automatically close the connection after receiving a request and sending a response. This is a short connection; if connection-alive keep-alive is enabled, You can exchange multiple conversations in a TCP connection. Is this definition? It's just a matter of program structure design.
 
Bytes ------------------------------------------------------------------------------------------------------------------------------

Persistent connection short connection is just a conceptual problem. As long as you know its concept, it is not a special thing:
Persistent connection: the system communication connection is maintained after it is established.
Short connection: the connection is established only when the system needs to send messages to each other (initiated by the client). The connection is closed after the request message is responded;
Communication entities use persistent connections. Generally, heartbeat messages need to be defined and sent regularly to check whether links between systems are abnormal. Heartbeat messages are sent at intervals. If heartbeat messages are not received for a certain number of times, this indicates that the connection is faulty and the connection needs to be closed and re-established.
The specific Heartbeat message format, as well as the sending interval, and the number of times the Heartbeat message is not received, the link is considered abnormal, and whether the data department counts as a heartbeat message (if some systems receive a packet, the heartbeat timer is cleared, which is equivalent to a packet in the system as a heartbeat message). Both Ends Need to negotiate. For example, the smpp protocol used by GSM to connect Short Message Centers with other network entities requires a persistent connection.

Therefore, short-and-short connections are just a concept of Short-and-short connections. They use common socket functions and there is no special problem.

Bytes ------------------------------------------------------------------------------------------------------------------------------
PHP has two functions when connecting to MySQL.
Connect and pconnect
Where are the differences between these two functions?
The general answer to the mark is:
Pconnect is a reliable connection, and PHP will reuse existing resources.
 
However, when I continue to ask questions, there will be fewer people to come up.
Frequently Asked Questions:
When will I use connect and when will I use pconnect?
When is mysql_close used?
Why is there a bunch of sleep processes on the database?
Why is the resource ID different every time when pconnect is used?
First, go back to the simplest solution, and pconnect will check whether there are existing resources.
If yes, use the dynamic route entry. If no, a new dynamic route entry is created.
However, the keyword I mentioned here refers to the information provided during this Apache trip.
Instead of the information provided by the Web server.
A Web server may have hundreds of apahe itineraries (up to 2.0 in 150)
That is to say, in the worst case, there may be hundreds of pconnect-related resources.
(In fact, there will be fewer in the future)
Therefore, pconnect may produce different resource IDs.
Because he may be traveling on a different itinerary.
Pconnect does not close the connection after the query is completed, but waits for a certain period of time.
This time can be set by wait_timeout on MySQL.
In addition, mysql_close does not allow users to access the volume opened by pconnect. Users can only access the volume of resources opened by connect. However, if there is no close, it will also be dropped by the DB after the timeout time.
Pconnect must be used with caution. In poor code, a large number of database connections will be reserved. The maximum value can be calculated in this way.
Apache Route number of each server * Number of all web servewr
Of course, I don't need to remind you that every shard consumes the CPU time and memory of the DB.
When using web code, no matter what kind of suggestion is used, there must be two answers before you can start the manual operation.
First, what resources should be crawled from the database?
Second, what information should I send to the user end?
(In fact, this is not only used for Web)
Database metadata is very slow. If you can capture the required information at a time, do not divide it twice.

Bytes ------------------------------------------------------------------------------------------------------------------------------

The database connection pool is provided by the Database Manager. It has nothing to do with the connection language.
PHP provides the pconnect mechanism to implement the Failover pool function.
For MySQL, The mysql_pconnect function can be implemented as follows:
After using the Data Synchronization connector, It is not stored in the system. Instead, it is stored in the system. When using mysql_pconnect connector for the next time, first, check whether there are idle connections that are not in use. If there is one, use this connection instead of importing data, this feature was developed from the provincial system.
 

1. What is the working mechanism of the database connection pool? How does it speed up data access?
Bytes -----------------------------------------------------------------------------------------
The function of the connection pool is to save the time for opening the database.
Since it is time-consuming to open database connections, the connection pool mechanism opens n database connections in advance and caches them. When you need to use the database, you can directly use these opened connections, this saves time.

Creating and initializing new objects may consume a lot of time. This is especially true when initialization involves time-consuming operations (for example, reading data from a host other than 20,000 km. When a large number of such objects need to be generated, it may have some negligible impact on performance. To alleviate this problem, in addition to selecting better hardware and better virtual machines, it is also an effective countermeasure to appropriately adopt some encoding techniques that can reduce the number of object creation times. Object pooling is a well-known technique.

The basic idea of object pooling is to save the used objects and use them again when this object is needed for the next time, this reduces the overhead caused by frequent object creation to some extent. The object used as the "Container" for saving objects. It is called the "Object pool" (or "pool" for short ).

The database connection pool is a pool dedicated to saving and managing database connections.

The appropriate use of object Pooling technology can effectively reduce the consumption during object generation and initialization, and improve the system operation efficiency.

The purpose of object pooling is to reduce the number of object generation times and reduce the overhead spent on object initialization, thus improving the overall performance. However, pooled processing itself has to pay a price. Therefore, object pooling is not suitable for all situations.

Basically, object pooling is suitable only when repeated object generation is a key factor affecting performance. If the performance improvement caused by pooling is not important, we still do not use the object Pooling technology to keep the code concise, but use better hardware and better virtual machines to improve performance.

The appropriate use of object pooling can effectively reduce the overhead caused by frequent generation of some objects, thus improving the overall performance. With the help of the Jakarta commons pool component, You can effectively reduce the workload spent on processing object pooling, and then invest more time and energy into other important work.
Principles of database connection pool:
When the J2EE server is started, a certain number of pool connections will be established, and a certain number of pool connections will be maintained.
When the client program needs to connect, the pool driver returns an unused pool connection and logs its table as busy.
If no idle connection exists, the pool driver creates a certain number of connections. The number of new connections is determined by the configuration parameters.
When the pool connection call is completed, the pool driver records the connection table as idle, and other calls can use the connection.

Bytes ------------------------------------------------------------------------------------------------------------------------------
 
1. persistent connection
The client establishes a communication connection with the server. After the connection is established, the connection is continuously enabled, and then the packets are sent and received.

2. transient connection
The client and server communicate with each other every time a message is sent and received. After the transaction is completed, the connection is closed immediately. This method is often used for one-to-multiple-point communication. For example, multiple clients connect to one server.
 
 
 
Short connections are common in major customers. For example, if each connection of the Web server uses persistent connections, It is very costly for each customer to retain a socket system.

Persistent connections are mostly used for frequent operations. Each TCP connection requires three-step handshake. This takes time. If each operation is first connected and then operated, the processing speed will be much lower. it is okay to send data packets directly during next processing without establishing a TCP connection.

In addition, there are synchronous and asynchronous operations. synchronous operations refer to data packets that can be sent only after the result of the previous operation is returned; asynchronous operations refer to sending all operation data packets before waiting for their return results. In comparison, asynchronous operations are fast, especially when each packet processing method is independent.

The above is just a reference to determine which type is used at the end or depends on you. For example, China Unicom's text message protocol can send multiple SMS packets after connection, but if the connection is disabled for a period of time (for example, 60 s.
 
Bytes ------------------------------------------------------------------------------------------------------------------------------
 
 
Permanent database connection
A permanent database connection is a connection that is not closed when the script ends running. When receiving a permanent connection request. PHP will check whether there is already a permanent connection (previously Enabled. If yes, the connection is directly used. If no, a new connection is established. The "same" connection refers to the connection to the same host with the same user name and password.

Readers who do not fully understand the work and distributed load of web servers may mistakenly understand the role of permanent connections. In particular, permanent connections do not provide the ability to establish "user sessions" on the same connection, nor can they effectively establish transactions. In fact, in a strict sense, permanent connections do not provide any special functions that cannot be provided by non-permanent connections.

Why?

This is related to how the Web server works. The Web server can generate Web pages using PHP in three ways.

The first method is to use PHP as a "shell ". Running in this way, PHP will generate and end a PHP interpreter thread for each PHP page request sent to the web server. Since this thread ends with the end of each request, any resources used in this thread (such as connections to the SQL database server) will be closed with the end of the thread. In this case, using a permanent connection won't be changed-because they are not permanent at all.

The second and most common method is to use PHP as a module of a multi-process Web server. This method is only applicable to Apache at present. For a multi-process server, a typical feature is that a parent process is running in coordination with a group of sub-processes. The child process is actually generated on the web page. When a client sends a request to the parent process, the request is sent to a child process that is not occupied by other client requests. This means that when the same client sends a second request to the server, it may be processed by a different sub-process. After a permanent connection is enabled, all subsequent pages requesting SQL service can re-use the established SQL server connection.

The last method is to use PHP as a plug-in for multi-threaded web servers. Currently, PHP 4 supports ISAPI, wsapi, and nsapi (in Windows), which allows PHP to be used as Netscape FastTrack (iPlanet), Microsoft's Internet Information Server (IIS) and O 'Reilly's Website Pro. The behavior of permanent connection is essentially the same as the multi-process model described above. Note that PHP 3 does not support SAPI.

If the persistent connection does not have any additional functions, what are the advantages of using it?

The answer is very simple-efficiency. When the client has frequent connection requests to the SQL Server, permanent connection is more efficient. The criteria for frequent connection requests depend on many factors. For example, the database type, whether the Database Service and Web Service are on the same server, and how the SQL Server loads the load. But at least we know that when there are frequent connection requests, permanent connections will significantly improve the efficiency. It allows each sub-process to perform only one connection operation in its lifecycle, rather than submitting a connection request to the SQL server every time it processes a page. This means that each sub-process will establish a separate permanent connection to the server. For example, if 20 different sub-processes run a script to create a permanent SQL
If the server is permanently connected, 20 different permanent connections are established to the SQL server. Each process occupies one connection.

Note: If the number of permanent sub-processes exceeds the set number of database connections limit, the system will have some defects. If the number of concurrent connections in the database is limited to 16, and 17 threads attempt to connect in the case of busy sessions, one thread cannot connect. If an error occurs in the script that makes the connection unable to be closed (for example, infinite loop), 16 connections of the database will be quickly affected. Please refer to the database documentation for instructions on how to handle abandoned and idle connections.
 
 
 
 
Bytes ------------------------------------------------------------------------------------------------------------------------------

Bytes ------------------------------------------------------------------------------------------------------------------------------
 

The application establishes a connection with the database. If the application does not access the database for more than wait_timeout (8 hours by default) and the connection remains idle, MySQL automatically closes the connection, if you perform a query operation on the connection, the MySQL server has gone away error occurs.

Wait_timeout
The number of seconds the server waits for action on a connection before closing the connection. The default value is 28800. That is, if nothing happens, the server closes the connection after 8 hours.

Solution:
<1>. This is because the timeout value wait_timeout is too short, so you can modify its value. Open mysql. ini in the MySQL installation directory and add a configuration item wait_timeout = 1000000 to the file. (This option is not available by default and needs to be manually added)
This method is feasible.
 
<2>. Set the automatic reconnection option in the code,
M_connection.set_option (New mysqlpp: reconnectoption (true ));
 
Then, when the MySQL server has gone away error occurs, call the connection: Ping () method. This function checks whether the connection to the server is working and reconnects when necessary, however, tests show that this method does not work.