Details of PHP database long connection mysql_pconnect_PHP tutorial

PHP’s MySQL persistent connection is a beautiful goal, but it has a bad reputation and is often stayed away from. Why is this? After close observation, I found that this guy is not easy. It depends on Apache's face and must listen to MySQL's command.

For PHP running as an Apache module, to implement MySQL persistent connection, it 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 us review the http request without Keep-Alive. Starting from the customer entering a valid URL address in the browser, the browser will use the socket to send a TCP request to the web server corresponding to the URL. This request Once successful, you need to shake hands three times to confirm. After success, the browser uses the socket TCP connection resource to request the http protocol from the web server. After sending it, it waits for the web server to send the http return header and body back. After sending it back, the browser Close the socket connection, then parse the http return header and body, and finally the beautiful page will be presented on the browser. What's the problem here? TCP connection requires three handshakes, that is, it takes three back and forth requests to determine whether a TCP request is successful, and then TCP is closed? It takes 4 requests to complete the round trip! Each http request requires 3 handshakes and 4 goodbyes. It’s not tiring to go back and forth. How much time and resources are wasted on closing the socket connection. Can one socket TCP connection send multiple http requests? So Keep-Alive came into being. In http/1.0, the client needs to add Connection:Keep-alive in the request header to achieve this. Here we only consider http1.1. We only need to set Apache and let it default. Keep-Alive persistent connection mode (Apache must be 1.2+ to support Keep-Alive). Find the KeepAive configuration item in httpd.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 reach the maximum connection when TCP has not expired. Then next time The connection is a new TCP connection again, setting 0 here means no limit), and then the most important option of mysql_pconnect, KeepAliveTimeout, is set to 15 (meaning 15 seconds).

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

<?php
    echo "Apache进程号:". getmypid();
?>

It’s very simple. Get the process number of the current PHP executor (Apache) and browse this page with a browser. What do you see? Yes, you saw a string of process number numbers. Within 15 seconds, refresh the page continuously to see if the process number changes? No? Now take your hands away, cross them on your chest, and mark the time, 1 second, 2 seconds, 3,...15,16. Okay, after 15 seconds, refresh the page. Has the process number changed? Changed! It is a new Apache process again. Why does it become a new process after 15 seconds? Remember the KeepAliveTimeout we set in Apache? Its value is 15 seconds. Now we should have a rough idea. When the web server turns on KeepAlive by default, after the client's first http request is successful, Apache will not disconnect the socket immediately, but will keep listening for requests from this client. How long will it be monitored? Depending on the time configured with the KeepAliveTimeout option, once this time is exceeded, Apache will disconnect the socket. Then the next time the same client requests again, Apache will open a new process to respond. So we kept refreshing the page in the past 15 years, and the process numbers we saw were all the same, indicating that the browser requested the same Apache process.

How does the browser know that it can send an http request directly without re-establishing the TCP connection? Because the http return header will have two lines: Connection:keep-alive and Keep-alive:15, which means that the client browser understands that this socket connection has not been closed on my side, and you can continue within 15 Use this connection and send an http request, so the browser knows what to do.

How to do PHP

So, how is PHP's MySQL connection resource held? This requires checking the function code of PHP's mysql_pconnect. I looked at it and the general method is that mysql_pconnect generates a hash key based on the current Apache process number and finds the hash. Whether there is a corresponding connection resource in the table, if not, push it into the hash table, if there is, use it directly. Some code snippets can be explained (for details, please view the PHP5.3.8 source code ext/mysql/PHP_mysql.c file line 690 PHP_mysql_do_connect function)

	#1.生成hash key
	user=php_get_current_user();//获取当前PHP执行者(Apache)的进程唯一标识号
	//hashed_details就是hash key
	hashed_details_length = spprintf(&hashed_details, 0, "MySQL__%s_", user);
    #2.如果未找到已有资源，就推入hash表，名字叫persistent_list，如果找到就直接使用
	/* 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(推入hash表) */
		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(连接已在hash表里)*/
            ...
            ...
            mysql = (PHP_mysql_conn *) le->ptr;//直接使用对应的sql连接资源
            ...
            ...
		}

zend_hash_find is easier to understand. The prototype is zend_hash_find (hash table, key name, key length, value); if found, value has value.

MySQL’s wait_timeout and interactive_timeout

After talking about Keep-Alive, it’s time to visit MySQL’s house. I’m talking about mysql_pconnect and how to bypass MySQL settings. The two most important parameters that affect mysql_pconnect are wait_timeout and interactive_timeout. What are they? Putting that aside, first let’s change the above code to the PHP code

<?php
    $conn = mysql_pconnect("localhost","root","123456") or die("Can not connect to MySQL");
    echo "MySQL线程号:". MySQL_thread_id($conn). "<br />";
    echo "Apache进程号". getmypid();
?>

以上的代码没啥好解释的，让我们用浏览器浏览这个页面，看到什么？看到两个显眼的数字。一个是MySQL线程号，一个是Apache进程号，好了，15秒后再刷新这个页面，发现这两个id都变了，因为已经是新的Apache进程了，进程id是新的，hash key就变了，PHP只好重新连接MySQL，连接资源推入persistent list。如果15内刷新呢？Apache进程肯定不变，MySQL线程号会变吗？答案得问MySQL了。首先这个MySQL_thread_id是什么东西？shell方式登录MySQL后执行命令'show processlist;'，看到了什么？

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

发现了很重要的信息，这个processlist列表就是记录了正在跑的线程，忽略Info列为show processlist那行，那行是你当前shell登录MySQL的线程。PHP连接MySQL的线程就是Id为349那行，如果读者自己做测试，应该知道这个Id=349在你的测试环境里是另外一个值，我们把这个值和网页里输出的MySQL_thread_id($conn)做做比较，对！他们是一样的。接下来最重要的是观察Command列和Time列，Command = Sleep，表明什么？表明我们mysql_pconnect连接后就一直在sleep，Time字段就告诉我们，这个线程Sleep了多久，那么Sleep了多久这个线程才能作废呢？那就是wait_timeout或者interactive_timeout要做的工作了，他们默认的值都是8小时，天啊，太久了，所以如果说web服务器关掉KeepAlive支持，那个这个processlist很容易就被撑爆，就爆出那个Too many connections的错误了，max_connectiosns配置得再多也没用。为了观察这两个参数，我们可以在MySQL配置文件my.cnf里设置这两个值，找到[MySQLd]节点，在里面设置多两行

interactive_timeout = 60
wait_timeout        = 30

配置完后，重启MySQL，shell登录MySQL，这时候show processlist可以发现只有当前线程。然后运行那个带有mysql_pconnect的PHP页面，再回来MySQL端show processlist可发现，多了一个Commond为Sleep的线程，不停的show processlist(方向键上+enter键)观察Time列的变化2，5，10...14!，突然那个Sleep线程程被kill掉了，咋回事，还没到30秒呢，噢!忘了修改一下Apache keepalive的参数了，把KeepAliveTimeOut从15改成120(只为观察，才这么改)，重启Apache。刷新那个页面，好，开始不停的show processlist，2..5..10..14，15，..20...26....28，29!线程被kill，这次是因为wait_timeout起了作用，浏览器那边停了30秒，30内如果浏览器刷新，那这个Time又会从0开始计时。这种连接不属于interactive connection(MySQL shell登录那种连接就属于interactive connection)，所以采用了wait_timeout的值。如果mysql_pconnect的第4个参数改改呢

<?php
$conn = mysql_pconnect('localhost','root','123456',MySQL_CLIENT_INTERACTIVE);
echo "MySQL线程号:".MySQL_thread_id($conn)."<br />";
echo "Apache进程号:".getmypid();
?>

刷新下页面，MySQL那边开始刷show processlist，这回Time > 30也不会被kill，>60才被kill了，说明设置了MySQL_CLIENT_INTERACTIVE，就会被MySQL视为interactive connection，那么这次PHP的MySQL连接在120秒内未刷新的情况下，何时作废将取决于MySQL的interactive_timeout的配置值。

总结

PHP的mysql_pconnect要达到功效，首先必须保证Apache是支持keep alive的，其次KeepAliveTimeOut应该设置多久呢，要根据自身站点的访问情况做调整，时间太短，keep alive没啥意义，时间太长，就很可能为一个闲客户端连接牺牲很多服务器资源，毕竟hold住socket监听进程是要消耗cpu内存的。最后Apache的KeepAliveTimeOut配置得和MySQL的time out配置要有个平衡点，联系以上的观察，假设mysql_pconnect未带上第4个参数，如果Apache的KeepAliveTimeOut设置的秒数比wait_timeout小，那真正对mysql_pconnect起作用的是Apache而不是MySQL的配置。这时如果MySQL的wait_timeout偏大，并发量大的情况下，很可能就一堆废弃的connection了，MySQL这边如果不及时回收，那就很可能Too many connections了。可是如果KeepAliveTimeOut太大呢，又回到之前的问题，所以貌似Apache。KeepAliveTimeOu不要太大，但比MySQL。wait_timeout 稍大，或者相等是比较好的方案，这样可以保证keep alive过期后，废弃的MySQL连接可以及时被回收。

http://www.bkjia.com/PHPjc/752494.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/752494.htmlTechArticlePHP的MySQL持久化连接，美好的目标，却拥有糟糕的口碑，往往令人敬而远之。这到底是为啥么。近距离观察后发现，这家伙也不容易啊，要...