MySQL Long connection

PHP's MySQL persistent connection, a good goal, but has a bad reputation, often a very shy. What the hell is this? Close-up observation found that this guy is not easy ah, to see the face of Apache, but also to listen to the MySQL command.

For PHP running as an Apache module, to implement a MySQL persistent connection, first depends on whether the Apache Web server supports keep-alive.

Keep-alive

What is Keep-alive? It is part of the HTTP protocol, let's review the HTTP request without keep-alive, starting with the client entering a valid URL address from the browser, the browser will use the socket to send a TCP request to the Web server corresponding to the URL. This request must be held back and forth three times to be sure, after success, the browser uses the socket TCP connection resources to the Web server to request the HTTP protocol, sent after the Web server to send the HTTP return header and body sent back, sent back after the browser closed socket connection , and then do the HTTP return header and body parsing work, and finally presented in the browser is a beautiful page. What's wrong with that? TCP connection requires three handshake, that is, the request three times to determine whether a TCP request is successful, and then TCP shutdown it? Back and forth requires 4 requests to complete! Each HTTP request on 3 times handshake, 4 times, this to go back and forth not too tired ah, how much time and resources are wasted on the socket connection is closed, can I send a socket TCP connection multiple HTTP requests? So keep-alive came into being, http/1.0 in need of the client in the request to join the connection:keep-alive side can achieve, here we only consider http1.1, only need to set up Apache, let it default is keep- Alive Persistent connection mode (Apache must be 1.2+ to support keep-alive). Find the Keepaive configuration item in httpd.conf, decisively set to on,maxkeepaliverequests decisive for 0 (a persistent TCP maximum allowable number of requests, if too small, it is easy to reach the maximum connection in the case of TCP not expired, that The next time the connection is a new TCP connection, where 0 is not limited, then the most important option KeepAliveTimeout for Mysql_pconnect is set to 15 (representing 15 seconds).

OK, restart Apache, test it, write the line:

Very simple, get the current PHP performer (Apache) process number, browse this page with a browser, see what? Yes, you see a serial number of process numbers, in 15 seconds, continuously refresh the page to see whether the process number has changed? Is it wood? Now take your hands off, cross the chest, good time, 1 seconds, 2 seconds, 3, ... 15,16. OK, after 15 seconds, and then to refresh the page, the process number has changed? It's changed! Again a new Apache process, why 15 seconds to become a new process? Remember the keepalivetimeout we set up in Apache? Its value is 15 seconds. Now we should be roughly clear, in the case of the Web server by default open KeepAlive, the client after the first HTTP successful request, Apache will not immediately disconnect the socket, but always listen to the request from this client, listening for how long? Depending on the time configured for the KeepAliveTimeout option, Apache will disconnect the socket once it is over, and the next time the same client requests again, Apache will open a new process accordingly. So we have to refresh the page in the previous 15, see the process number is consistent, indicating that the browser request to the same Apache process.

How does the browser know that it is possible to send HTTP requests directly without having to reconnect to the TCP connection? Because HTTP returns to the head will take connection:keep-alive,keep-alive:15 two lines, meaning to let the client browser understand, this socket connection I have not closed, you can continue to use this connection within 15, and send HTTP requests , so the browser knows what to do.

What does PHP do?

So, how is the MySQL connection resource of PHP is hold, this need to see PHP mysql_pconnect function code, I looked at the next, presumably, Mysql_pconnect according to the current Apache process number, generate hash key, Find the hash table there is no corresponding connection resources, no push into the hash table, there is direct use. Some code snippets can be described (see PHP5.3.8 source EXT/MYSQL/PHP_MYSQL.C file 690 line Php_mysql_do_connect function)

#1. Generate hash key user=php_get_current_user ();//Gets the current PHP performer (Apache) process Unique identification number//hashed_details is the hash key Hashed_details_ Length = spprintf (&hashed_details, 0, "Mysql__%s_", user); #2. If no existing resources are found, push into the hash table, called persistent_list, and if found, directly use if (Zend_hash_find (&eg (persistent_list), Hashed_details, Hashed_details_length+1, (void *) &le) ==failure) {... Z_type (New_le) = Le_plink; new_le.ptr = MySQL; if (Zend_hash_update (&eg (persistent_list), Hashed_details, hashed_details_length+1, (void *) &new_le, sizeof ( Zend_rsrc_list_entry), NULL) ==failure) {...}} else {... mysql = (Php_mysql_conn *) le->ptr;//directly using the corresponding SQL connection resource ...}

Zend_hash_find is easier to see, the prototype is zend_hash_find (hash table, key name, key length, value); If found, value will be worth it.

MySQL's wait_timeout and interactive_timeout

Say finish keep-alive, should go to MySQL house string to visit, say is mysql_pconnect, how can bypass MySQL's setting. The two most important parameters affecting Mysql_pconnect are wait_timeout and interactive_timeout, what are they? First off, let's start with the code above to change the PHP code

The above code is not a good explanation, let us use a browser to browse this page, see what? See two conspicuous figures. One is the MySQL thread number, one is the Apache process number, OK, after 15 seconds to refresh the page, found that the two IDs are changed, because it is a new Apache process, the process ID is new, the hash key has changed, PHP had to reconnect MySQL, The connection resource is pushed into the persistent list. What if it refreshes within 15? The Apache process must be the same, will the MySQL thread number change? The answer is to ask MySQL. First of all, what's this mysql_thread_id thing? Shell mode login MySQL after executing command ' show processlist; ', see what?

 mysql> Show Processlist, +-----+------+-----------+------+--------+-----+------+-----------------+ | Id | User | Host | db | command| time| state| Info | +-----+------+-----------+------+--------+-----+------+-----------------+ | 348 | Root | localhost | NULL | Query | 0| NULL | Show Processlist| | 349 | Root | localhost | NULL | Sleep | 2| | NULL | +-----+------+-----------+------+--------+-----+------+-----------------+

A very important information is found, this processlist list is to record the running thread, ignoring info listed as show processlist that line is your current shell login MySQL thread. PHP connection MySQL thread is ID 349 that line, if the reader to do their own test, should know that the id=349 in your test environment is another value, we put this value and the output of the page mysql_thread_id ($conn) do a comparison, right! They are the same. The next most important thing is to observe the command column and the time column, command = Sleep, what does it mean? Indicates that we have been in the Sleep,time field since we mysql_pconnect the connection, so how long does this thread sleep, and how long does it take for this thread to expire? That is wait_timeout or interactive_timeout to do the work, their default value is 8 hours, God, too long, so if the Web server turned off keepalive support, the processlist can easily be blown up, Just burst out that too many connections mistake, MAX_CONNECTIOSNS configuration too much also useless. In order to observe these two parameters, we can set these two values in the MySQL config file my.cnf, find the [MySQLd] node, set more than two lines inside

Interactive_timeout = Wait_timeout = 30

After configuration, restart Mysql,shell login MySQL, this time show processlist can be found only when the front-end process. Then run the PHP page with Mysql_pconnect, and then come back to MySQL side show processlist can be found, a commond for the sleep of the thread, nonstop show processlist (arrow key on the +enter key) Watch the time column change 2,5,10 ... 14!, suddenly that sleep line regulation regulation was killed, what happened, not to 30 seconds, oh! Forgot to modify the Apache keepalive parameters, the KeepAliveTimeout from 15 to 120 (only for observation, just so change), restart Apache. Refresh that page, OK, start the show processlist,2..5..10..14,15,. 20...26....28,29! thread was killed, this time because the Wait_timeout has played a role, the browser has stopped for 30 seconds, 30 if the browser refresh, then this will start from 0 times. This connection is not part of the interactive connection (MySQL shell login that connection belongs to interactive connection), so the Wait_timeout value is used. What if the 4th parameter of Mysql_pconnect is changed?

Refresh the next page, MySQL side began to brush show processlist, this time > 30 will not be kill,>60 to be killed, instructions set mysql_client_interactive, will be considered by MySQL Interactive connection, when the MySQL connection for PHP is not refreshed within 120 seconds, the time to void will depend on the configuration value of the MySQL interactive_timeout.

Summarize

PHP mysql_pconnect to achieve efficacy, first of all must ensure that Apache is supporting keep alive, and then KeepAliveTimeout should be set how long, according to their own site access to make adjustments, time is too short, keep alive no meaning , the time is too long, it is possible for a busy client connection to sacrifice a lot of server resources, after all, hold the socket listening process is to consume CPU memory. Finally Apache KeepAliveTimeout configuration and MySQL time out configuration to have a balance point, contact the above observation, assuming that mysql_pconnect not with the 4th parameter, If Apache's keepalivetimeout is set to a smaller number of seconds than wait_timeout, the real effect on Mysql_pconnect is Apache rather than MySQL configuration. At this time if MySQL wait_timeout large, concurrent large cases, it is likely to be a bunch of abandoned connection, MySQL this side if not timely recovery, it is likely too many connections. But if the keepalivetimeout is too big, and back to the previous problem, so it looks like Apache. Keepalivetimeou not too big, but more than MySQL. Wait_timeout slightly larger, or equal is a better solution, so that the keep alive expires, the discarded MySQL connection can be recovered in time.

MySQL Long connection