Analysis of the difference between long connection and short connection in database

What is a long connection?

In fact, long connection is relative to the usual short connection, that is, long time to maintain the client and server connection state.

The usual short connection procedure is:

Connection-"data transfer-" close connection;

And long connections are usually:

Connection-"Data transmission-" Keep connected-"data transmission-" Keep connected-"...-" close connection;

This requires long connection in the absence of data communication, timed to send packets to maintain the connection status, short connection in the absence of data transmission directly off the line

When to use long connection, short connection?

Long connections are mainly used for frequent communications between a few clients and the server, because it is a waste of resources to frequently communicate with a socket if a frequent communication occurs with a short connection.

But for the service side, long connections can also cost a certain amount of resources, requiring specialized threads (which can be managed by the process under Unix) to maintain the connection state.

In short, the choice between long and short connections depends on the situation.

First, if you use a long connection and do not have any operations on the database for a long time, after the timeout value, the MySQL server closes the connection, and the client gets an error similar to the "MySQL server has gone away" when it executes the query.

After using Mysql_real_connect to connect to the database, use the Mysql_options (&mysql, Mysql_opt_reconnect, ...) to set the automatic reconnect. So when the MySQL connection is lost, use mysql_ping to automatically reconnect the database. If it is before MySQL 5.1.6, then execute mysql_options (&mysql, Mysql_opt_reconnect, ...) after each execution real_connect, if it is MySQL 5.1.6+, It is enough to do it once before connect.

View the number of MySQL connections

Mysqladmin-uroot-p processlist

In the actual test, I found that when the Mysql_opt_reconnect is set to 1 o'clock and the timeout is followed by the Processlist, the automatically established connection is not in the list, but the connection is actually established and used.

In MySQL's default settings, if a database connection is not used for more than 8 hours (8 hours idle), the server disconnects this connection and subsequent query operations on the connection fail. The problem is described very much on the network. A corresponding solution is also provided. I'm here to make some comments about myself.

Workaround One: Modify the MySQL server configuration parameters

The reason is very simple, MySQL default setting is not used after the database connection for more than 8 hours to disconnect it, if we change this time to a larger number, then the connection timeout will take longer time, it means that more difficult to timeout. The modification method provided on the network is generally to modify/ETC/MY.CNF, add a row in this file wait_timeout= the timeout you need to set. There is actually a relatively simple way to modify this parameter:

First login to MySQL as Superuser, note that you must be superuser, otherwise you will be prompted not to modify permissions. And then enter

Show global variables like ' wait_timeout ';

The current timeout is displayed after the carriage return is performed:

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Wait_timeout | 28800 |

+---------------+-------+

1 row in Set (0.00 sec)

The default timeout, which is 8 hours (in seconds), is shown above. Now reset the parameter, for example, if we want to set the timeout to 10 hours, you can enter:

Set global wait_timeout=36000;

Carriage return execution, showing:

Query OK, 0 rows Affected (0.00 sec)

Indicates that the setting is successful and you can reuse show global variables like ' wait_timeout ' to verify it.

This method is straightforward, and the parameters that are set take effect immediately. However, if there is no configuration in/etc/my.cnf, the global variable reads the new variable value from the/ETC/MY.CNF after the service is restarted.

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;
}

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

For example, if the HTTP connection-alive is close, then the server receives a request and sends out a response and then actively closes the connection, which is the short connection; if Connection-alive Keep-aliv E, you can exchange multiple conversations in a TCP connection. Is this definition? It's just a matter of program structure design.

--------------------------------------------------------------------------------

Long connection short connection is only a conceptual problem, as long as the concept of knowing it is not a special thing:
Long connection: The system communication connection is established and has been maintained.
Short connection: Only the system needs each other to send a message connection to establish (client initiated), the request message gets the response after the connection closes;
Communication entities using long connections, generally also need to define heartbeat messages, regularly sent to detect the link between the system is abnormal, send a heartbeat at a certain time, if a certain number of times did not receive heartbeat message, this connection is considered a problem, need to disconnect the connection to re-establish.
The format of the specific heartbeat message, and send interval, as well as how many times did not receive the heartbeat to think that the link exception, and whether the data part is counted as heartbeat message (some system if receive packet will clear heartbeat timer also is equivalent to the system packet also counted as heartbeat message); This needs to be negotiated at both ends. For example, GSM commonly used short Message center and other Network entities Interconnect SMPP protocol, the requirement is to establish a long connection.

So the length of the connection is just a concept of the length of the connection socket, is the use of ordinary socket function, nothing special.

--------------------------------------------------------------------------------

PHP in the?? Mysql?r has??? FUNCTION
CONNECT? Pconnect
???? What's the difference between the function and the E?
The general?? Eclipse the kite to pay for the/>? BR Pconnect is the holding of sex, PHP?? The use of the existing "爝" B?

But go after it? Go on, can you get it? Miao na shed a lot of Japanese
A regular suspect? Yes:
What?? R wait to connect what? R Waiting for Pconnect?
Mysql_close what R use?
? There's a lot of sleep on the maple ndatabase?
Obviously with Pconnect? Esource IDs are different?

First go back to the most? Wei Mu 忉, Pconnect??? Does z check for 爝 b?
If so, use the?? ; O, new?? ⒁????
But?? What do you mean by Z? The Apache itinerary?? ⒌ y-爝 b?
Instead of a Web server?? ⒌ y-爝 b?
There may be hundreds on a Web server. Apahe itinerary (2.0??) Max 150?? ）
Is? Under the maximum condition of R. The Ape-learning?? Pconnect caused by the 爝 B?
（??? On?? H less than this)
So the same mail gown? Connect is?? 霈 f with different resource IDs
The Ailanthus support 苁 the con Huang and the harmonious lying mansion and the sash?/p>

And Pconnect is not? Con is finished???? But Huan?
???? Can be determined by the wait_timeout on MySQL?
and mysql_close? K?o law?? The pconnect? ⒌ ni b? Connect house? ⒌? 爝 b?。? what if?] Have close in?? And??? after Timeout?r? B drop off.

Pconnect must be careful to use, in the?? In the bad code,??? Leave a large number of database CONNECTION. Maximum value can be?? Woo, Count.
Apache itinerary for each server? * All Web servewr/>? But you don't need me? kip person Glare????? are???? CPU time for B? MEMORY

?? WEB code?r, do not use where?? Say, there must be a second mail. There's an answer before that.
One is, want to? What does Atabase crawl? Material
The second is, the Pixy to fade n? expected the user side
(Its??? And not just for the web)
The database?? It's very slow, can you? 蛞 Huai Witch ト⊥ Chao Buzhi? y material, don't divide the yarn?/p>

--------------------------------------------------------------------------------

The database connection pool is provided by the database Manager. is independent of the connection language.
PHP provides a pconnect? C-System, you can???? The ability to connect to the pool.
???  nbsp MySQL, mysql_pconnect letter? Rice painting??? :
??? 爝 b after the use? K not to put, but to stay in the department, the next time to use Mysql_pconnect to pick up???? R, first of all, z check to see if there are any available? t use???? Pick up the???. Lan 牟僮??? Provincial Department??? .

1. What is the working mechanism of the database connection pool? How does it improve the speed of data access?
---------------------------------------------------------------
The main purpose of the connection pool is to save time to open the database.
Because opening a database connection is time-consuming, the connection pooling mechanism opens up n database connections in advance, caches them, and saves time by using these open connections directly when you need to use the database.

Creating new objects and initializing them can be a lot of time consuming. This is especially true when the initialization of such an object involves some time-consuming operations, such as reading some data from a host that is located 20,000 kilometres away. When you need to generate a large number of such objects, you may have some negligible impact on performance. To alleviate this problem, in addition to choosing better hardware and a great virtual machine, it is also an effective countermeasure to appropriately adopt some coding techniques that reduce the number of objects created. Object pooling Technology (pooling) is a well-known technique in this field.

The basic idea of object pooling is to save the used objects, wait for the next time when they are needed, and then use them again to reduce the overhead of creating objects in a certain degree. An object that acts as a "container" for saving objects, known as Object pool, or pool.

The database connection pool is a pool that specializes in saving and managing database connections.

The proper use of object pooling technology can effectively reduce the consumption of object generation and initialization, and improve the operation efficiency of the system.

Object pooling is intended to improve overall performance by reducing the number of objects generated and by reducing the overhead of spending on object initialization. However, the pooling process itself has to pay the price, therefore, not in any case is suitable for the use of object pooling.

Basically, object pooling is appropriate only when the operations that repeatedly generate an object become a key factor in performance. If the performance benefits of pooling are not important, the object pooling technology is not used to keep the code simple, and better hardware and better virtual machines are good for performance.

Proper use of object pooling can effectively reduce the overhead of generating certain objects frequently, thereby improving overall performance. With the help of the Jakarta Commons pool component, you can effectively reduce the amount of work you spend on processing object pooling, and then devote more time and energy to other important tasks.

The principle of database connection pooling:

The Java EE server starts up with a certain number of pool connections and maintains no less than this number of pool connections.
When a client program needs to connect, the pool driver returns an unused pool connection and makes it busy.
If there are currently no idle connections, the pool driver creates a new number of connections, and the number of new connections is determined by the configuration parameters.
When a pooled connection call is used, the pool driver makes the connection idle and other calls can use the connection.

--------------------------------------------------------------------------------

1. Long connection
Client side and server to establish a communication connection, the connection is established to open, and then send and receive messages.

2. Short connection
The client side communicates with server every time a message is sent and received, and the transaction is immediately disconnected. This approach is often used in a bit of point-to-point communication, such as multiple client connections to a server.

Short connections are common in big customer situations such as Web servers if each connection uses a long connection, then each customer retains one socket system resource that is expensive.

Long connection is more frequently used for operations each TCP connection requires a three-step handshake. This takes time if each operation is connected first and then the processing speed will be reduced a lot, so after each operation is always open the next time processing directly send packets on the OK do not have to establish a TCP connection.

There is also synchronous operation and asynchronous operation, the synchronization operation refers to the last operation returns the result can send the next operation of the packet, the asynchronous operation refers to the first of all the operation of the packets after the end of the wait for their return results. In contrast, asynchronous operations are faster, especially if each packet processing method is independent.

It's just a reference. Which type is ultimately used or determined by you. such as Unicom's SMS protocol is connected to send multiple SMS packets, but if a period of time (such as 60s) does not operate then the connection will be closed.

--------------------------------------------------------------------------------

Database Permanent Connection
A persistent database connection is a connection that is not closed when the script finishes running. When a request for a permanent connection is received. PHP will check to see if there is already an identical permanent connection (previously open). If it exists, the connection is used directly, or a new connection is established if it does not exist. A connection called "Same" means a connection with the same username and password to the same host.

Readers who do not fully understand the work and distribution of the Web server may incorrectly understand the effect of a permanent connection. In particular, a permanent connection does not provide the ability to establish a "user session" on the same connection, nor does it provide the ability to effectively establish a transaction. In fact, in the strictest sense, a permanent connection does not provide any special functionality that cannot be provided by a non-persistent connection.

Why?

This is related to how the Web server works. Web servers can use PHP to generate Web pages in three different ways.

The first approach is to use PHP as a "shell." Running this way, PHP generates and ends a PHP interpreter thread for each PHP page request to the Web server. Because the thread ends with the end of each request, any resource that is exploited in this thread, such as a connection to the SQL database server, is closed with the end of the thread. In this case, using a permanent connection will not get any change?? Because they are not permanent at all.

The second, and most common, method is to use PHP as a module for a multi-process Web server, which is currently available only for Apache. A typical feature of a multiple-process server is that there is a parent process coordinated with a set of child processes, where the actual generation of the Web page is a subprocess. Whenever a client makes a request to a parent process, the request is passed to a child process that is not yet occupied by another client request. This means that when the same client requests the server for the second time, it is likely to be handled by a different subprocess. After a permanent connection is turned on, all subsequent pages requesting the SQL Service are able to reuse the established SQL Server connection.

The final approach is to use PHP as a plug-in for a multithreaded Web server. Currently, PHP 4 already supports ISAPI, WSAPI, and NSAPI (in Windows), which enables PHP to be used as a Netscape FastTrack (iPlanet), Microsoft ' s Internet inform ation Server (IIS) and O ' Reilly ' s WebSite Pro and other multithreaded Web server plug-ins. The behavior of a permanent connection is essentially the same as the multiple process model described earlier. Note that PHP 3 does not support SAPI.

If a permanent connection does not have any additional functionality, what is the benefit of using it?

The answer is very simple.? Efficiency. Permanent connections are more efficient when the client's connection requests to the SQL server are very frequent. The criteria for frequent connection requests depend on a number of factors. For example, the type of database, whether the database service and Web service are on the same server, how the SQL server loads the load, and so on. But at least we know that permanent connections are significantly more efficient when connection requests are frequent. It makes each child process only one connection operation in its lifecycle, rather than making a connection request to the SQL server each time a page is processed. This means that each child process will establish its own independent permanent connection to the server. For example, if you have 20 different child processes running a script that establishes a permanent SQL Server permanent connection, you actually have 20 different permanent connections to the SQL Server, one for each process.

Note that if the number of child processes that are permanently connected exceeds the set number of database connections, the system will have some drawbacks. If the database has a limit of 16 concurrent connections and 17 threads attempt to connect in the event of a busy session, then one thread will not be able to connect. If at this point in the script there is an error (such as an infinite loop) in which the connection cannot be closed, the 16 connections to the database are quickly affected. Consult the documentation for the database you are using to obtain a method for handling discarded and idle connections.

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

Application and database connection, if more than wait_timeout (default 28,000 seconds 8 hours) The application does not access the database, the connection has been idle, then MySQL will automatically shut down the connection, if the connection to perform the query operation, will occur MySQL Server has gone away error.

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.

Try the solution:

<1> This is due to the timeout time wait_timeout too short, so modify its value. Open the Mysql.ini under the MySQL installation directory and add a row of configuration items to the file wait_timeout=1000000. (This option is not available by default and needs to be manually added)
The method is feasible.

<2> Set the auto-reset option in code.

M_connection.set_option (New Mysqlpp::reconnectoption (true));

Then, when the MySQL server has gone away error occurs, call Connection::p the ING () method, which checks whether the connection to the server is working and reconnect if necessary, but after testing it is found that this method does not work.