In-depth discussion: PHP uses database permanent connection way to operate MySQL is with _php skill

Source: Internet
Author: User

PHP programmers should know that connecting to a MySQL database can use the Mysql_pconnect (permanent connection) function, the use of database permanent connection can improve efficiency, but the actual application of the database permanent connection will often lead to some problems, The usual performance is in the large traffic on the site frequently intermittent failure to connect to the database, appear similar to "Too many connections in ... "Error message, restarting the server is normal, but not a moment later, the same fault occurred. The cause of these problems, I am afraid not everyone can be clear, although there are some relevant information in the PHP document, but the explanation is not easy to understand, here I have the cheek to try to do a simple discussion, the views are not all correct, welcome feedback.

First look at the definition of a 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.

PHP operates on a permanent connection using MySQL as a prerequisite: PHP must be installed as a plug-in or module for multi-threaded or multiple-process Web servers. The most common form is to use PHP as a module for a multiple-process Apache server. 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 the different child processes requesting the subsequent pages of the SQL Service will be able to reuse the established SQL Server connection. 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. Each child process will establish its own independent permanent connection to the server. PHP itself does not have the concept of a database connection pool, but Apache has the concept of a process pool, an Apache subprocess will be put back to the process pool, which makes the mysql_pconnect opened with the MySQL connection resources can not be released, Instead, it is attached to the corresponding Apache subprocess and saved to the process pool. It can then be reused when the next connection request is made. Everything seems to be normal, but in the large number of Apache concurrent access, if the use of Mysql_pconnect, will be due to previous Apache process occupied by the MySQL connection does not close, the MySQL will soon achieve maximum connectivity, So that subsequent requests may not be responded to.

The above part of the text is extracted from the PHP document, it may seem to be some genteel bad understanding, then I use vernacular to give an example to illustrate the problem:

Assuming that Apache configures the maximum number of connections for 1000,mysql, the maximum number of connections is 100, and when the Apache server receives 200 concurrent accesses, 100 of them involve database access, and the remaining 100 do not involve database access. Because there is no database connection available at this time, so this involves database access of 100 concurrent will produce 100 database permanent connection, reached the maximum number of database connections, when these operations do not end, any other connection can no longer get the database connection, when these operations are over, The corresponding connection is put into the process pool, at which point the Apache process pool has 200 free subprocess, 100 of which are connected with the database, because Apache will randomly pick up the free subprocess for the access request, So the subprocess you get is probably one of those 100 that doesn't contain a database connection, and the database connection has reached the maximum, you can not successfully create a new database connection, alas, you will have to constantly refresh the page, when the luck is good, happened to be assigned to a database connection with the child process, To navigate the page normally. If it is a large number of visits to the site, there may be a lot of concurrency at any time, so the viewer may be constantly discovering the phenomenon of the inability to connect to the database.

Perhaps you would say, we put the maximum number of Apache and MySQL connection to the same size is not it? Yes, a reasonable adjustment to the maximum number of connections to some extent will avoid this problem, but Apache and MySQL load capacity is different, if according to the Apache load capacity to set, for MySQL, the maximum number of connections is large, will produce a large number of MySQL database permanent connection, for example, as in peacetime to feed a millions of of the army, the cost is not worth it; and if the MySQL load capacity set, for Apache, the maximum number of connections on the small, a bit of the feeling of killing a chicken, Can't play Apache's maximum efficiency.

So according to the PHP manual, it's only appropriate to use database permanent connections on sites with little concurrent access, but for a site with little concurrent access, the efficiency gains from using a database permanent connection do not seem to make much sense, and from this point of view, I think the database permanent connection in PHP is basically a chicken role, if you have to use the concept of database connection pool, you can try Sqlrelay or Apache itself provided mod_dbd, perhaps there will be surprises.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.