PHP database persistent connection mysql_pconnect details _ PHP Tutorial

PHP database persistent connection mysql_pconnect details. The persistent connection to MySQL in PHP has a good goal but a bad reputation. Why. After close observation, we found that this guy was not easy. he had a bad reputation for the persistent connection to MySQL in PHP, which is often far-fetched. Why. After close observation, we found that this guy was not easy. he had to listen to the MySQL command to look at Apache's face.

For PHP running as an Apache module, to achieve persistent MySQL connection, it 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 requests without Keep-Alive, starting from the client entering a valid url in the browser, the browser will use socket to send a TCP request to the web server corresponding to the url. Once the request is successful, you must hold it back three times before you can confirm it, the browser uses socket TCP to connect resources to request the http protocol from the web server. after sending the request, the web server waits for the http response header and body to be sent back. after sending the request, the browser closes the socket connection, then parse the http return header and body, and finally display the beautiful page on the browser. What's the problem? TCP connection requires three handshakes, that is, three round-trip requests to determine whether a TCP request is successful, and then TCP is closed? It takes four requests to complete the process! Three handshakes and four bye-bye for each http request. this is not too tiring. how much time and resources are wasted on closing the socket connection, can I send multiple http requests through a socket TCP connection? Therefore, Keep-Alive came into being. in http/1.0, the client needs to add Connection: Keep-alive to the request header. here we only need to consider http1.1 and set Apache, make it the default Keep-Alive persistent connection mode (Apache must be 1.2 + to support Keep-Alive ). In httpd. find the KeepAive configuration item in the conf, set it to On, and set MaxKeepAliveRequests to 0 (the maximum number of requests allowed by a persistent TCP. if it is too small, it is easy to avoid TCP expiration, when the maximum connection is reached, the next connection will be a new TCP connection. Here 0 indicates no restriction), and then the most important option for mysql_pconnect is KeepAliveTimeout set to 15 (15 seconds ).

Okay, restart Apache, test it, and write something quickly:

It's easy to get the process number of the current PHP executor (Apache). you can use a browser to browse this page. what do you see? Yes, we can see a number of process numbers. within 15 seconds, we can refresh the page continuously to see if the process number has changed? Wood? Now you can take your hands away and cross them on your chest. The time is good: 1 second, 2 seconds, 3,... 15, 16. Okay. after 15 seconds, refresh the page again. Does the process number change? Changed! It's a new Apache process. Why does it become a new process in 15 seconds? Remember the KeepAliveTimeout we set in Apache? The value is 15 seconds. Now we should make it clear that when the web server opens KeepAlive by default, Apache will not immediately disconnect the socket after the first successful http request from the client, but will continue to listen for requests from this client, how long does the listener last? According to the time configured by the KeepAliveTimeout option, Apache will disconnect the socket after this time is exceeded. Apache will open a new process for the next request from the same client. So we refresh the page continuously within the past 15 days and see the same process number, indicating that the browser requested the same Apache process.

How does the browser know that an http request can be sent directly without a TCP connection? Because Connection: keep-alive and Keep-alive: 15 rows will be included in the http response header, which means the client browser understands that this socket Connection has not been closed yet, you can continue to use this connection and send an http request within 15, so the browser knows what to do.

How PHP works

So, how is the connection resource of MySQL in PHP held? you need to check the function code of mysql_pconnect in PHP. I have read it, the general practice is that mysql_pconnect generates a hash key based on the current Apache process number and finds whether there are corresponding connection resources in the hash table. if not, it is pushed to the hash table. if not, it is directly used. Some code snippets can be described. (for details, see the PHP_mysql_do_connect function in line 2 of PHP5.3.8 source code ext/mysql/PHP_mysql.c)

#1. generate hash keyuser = php_get_current_user (); // Obtain the unique ID of the current PHP executor (Apache) process // hashed_details is hash keyhashed_details_length = spprintf (& hashed_details, 0, "MySQL __% s _", user); #2. if no existing resource is found, push it into the hash table named persistent_list, if it is found, use/* try to find if we already have this link in our persistent list */if (zend_hash_find (& EG (persistent_list), hashed_details, hashed_details_length + 1, (void **) & le) = FAILURE) {/* we don't */...... /* hash it up (push hash table) */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 {/* The link is in our list of persistent connections (The connection is already in The hash table )*/...... mysql = (PHP_mysql_conn *) le-> ptr; // use the corresponding SQL to connect to the resource ......}

Zend_hash_find is easier to understand. the prototype is zend_hash_find (hash table, key name, key length, value). if it is found, value has a value.

Wait_timeout and interactive_timeout of MySQL

After Keep-Alive is finished, it's time to go to the MySQL house and talk about mysql_pconnect. how can we bypass MySQL settings. The two most important parameters that affect mysql_pconnect are wait_timeout and interactive_timeout. What are these parameters? First, let's change the above code to the PHP code.

 "; Echo" Apache process number ". getmypid ();?>

The above code does not have a good explanation. let's use a browser to browse this page. what can we see? Two prominent numbers are displayed. One is the MySQL thread number and the other is the Apache process number. okay, refresh the page 15 seconds later and find that both IDs have changed because they are already new Apache processes, the process id is new, and the hash key is changed. PHP has to reconnect to MySQL and connect resources to push them to the persistent list. What if it is refreshed within 15 days? The Apache process will definitely remain unchanged. Will the MySQL thread number change? The answer is MySQL. First, what is MySQL_thread_id? Log on to MySQL using shell and run 'show processlist; '. what do you see?

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            |+-----+------+-----------+------+--------+-----+------+-----------------+

The processlist list records the running threads and ignores the line "show processlist" in the Info column. The line is the thread where your current shell logs on to MySQL. The thread connecting PHP to MySQL is the row whose Id is 349. if you do the test yourself, you should know that this Id = 349 is another value in your test environment, we compare this value with the MySQL_thread_id ($ conn) output on the webpage! They are the same. Next, the most important thing is to observe the Command column and Time column. Command = Sleep. what does it mean? It indicates that we have been sleep since mysql_pconnect connection, and the Time field tells us how long this thread has been Sleep, so how long has it been Sleep before this thread can be voided? That is the work to be done by wait_timeout or interactive_timeout. their default values are 8 hours. Oh, my God, it's been too long. so if the web server turns off KeepAlive support, the processlist is easily cracked, and the Too connector connections error is reported. it is useless to configure max_connectiosns. To observe the two parameters, we can set these two values in the MySQL configuration file my. cnf, find the [MySQLd] node, and set two more lines in it.

interactive_timeout = 60wait_timeout        = 30

After the configuration, restart MySQL and log on to MySQL through shell. at this time, show processlist can find that only the current thread is available. Then run the PHP page with mysql_pconnect, and then return to the MySQL-side show processlist. we can find that there is another thread with the Commond as Sleep, and the show processlist will not stop (+ enter on the direction key) observe the changes in the Time column, 10... 14 !, Suddenly the Sleep thread was killed. what happened? it's not 30 seconds. Oh! Forget to modify the Apache keepalive parameter, change KeepAliveTimeOut from 15 to 120 (only for observation), and restart Apache. Refresh the page. well, the show processlist, 2... 5... 10... 14,15,... 20... 26... 28, 29! The thread is killed. this Time it is because wait_timeout has played a role. The browser has stopped for 30 seconds. if the browser refreshes within 30 seconds, the Time will start from 0. This connection does not belong to interactive connection (the connection used for MySQL shell login belongs to interactive connection), so the wait_timeout value is used. What if the 4th parameters of mysql_pconnect are modified?

 "; Echo" Apache process no.: ". getmypid ();?>

Refresh the page and MySQL will start to fl show processlist. this Time> 30 will not be kill, and> 60 will be kill. it means that if MySQL_CLIENT_INTERACTIVE is set, MySQL will regard it as interactive connection, when the MySQL connection in PHP is not refreshed within 120 seconds, it depends on the configuration value of MySQL interactive_timeout.

Summary

To achieve the efficacy of mysql_pconnect in PHP, we must first ensure that Apache supports keep alive. Secondly, how long should KeepAliveTimeOut be set? it should be adjusted based on the access situation of the website. The time is too short, keep alive is meaningless. if it takes too long, it is very likely that many server resources will be sacrificed for a idle client connection. after all, it is necessary to hold the socket listening process to consume cpu memory. At last, the KeepAliveTimeOut configuration of Apache must have a balance between the time out configuration of MySQL and the time out configuration of MySQL. contact the preceding observations. assume that mysql_pconnect does not contain 4th parameters. if the number of seconds set by Apache KeepAliveTimeOut is smaller than wait_timeout, the configuration of Apache instead of MySQL is actually used for mysql_pconnect. At this time, if the wait_timeout of MySQL is Too large and the concurrency is large, it is very likely that there will be a bunch of discarded connections. if the MySQL side does not recycle it in time, it is very likely that Too has connections. However, if KeepAliveTimeOut is too large, it will return to the previous issue, so it looks like Apache. KeepAliveTimeOu is not too large, but it is better than MySQL. Wait_timeout is slightly larger, or equal is a good solution, this can ensure that after the keep alive expires, the discarded MySQL connection can be recycled in time.

Bytes. Why. After close observation, I found that this guy is not easy...