PHP connection MySQL function mysql_connect and mysql_pconnect contrast

When you need to use PHP to connect to the MySQL database, there are two types of database connection functions: mysql_connect () and Mysql_pconnect ().

1, mysql_connect ()

At the beginning of the script, mysql_connect () creates a new connection and closes the connection at the end of the script. Every time the script executes, it creates a new connection.

2, Mysql_pconnect ()

When using Mysql_pconnect () to connect to a database, it first determines whether a database connection already exists, and if it does, it takes advantage of the first resource ID and, conversely, creates a new connection.

After the connection is created, the function returns the resource ID (if any). When the script calls again, it does not create a new connection. Of course, the connection is not closed at the end of the script. This is called a persistent connection.

However, when using Mysql_pconnect (), you need to fine-tune the server configuration, such as limiting the number of connections, time-out connections, and handling empty connections.

3, how to use these two functions

If your application has a lot of traffic, you should use the Mysql_pconnect () function, whereas consider the mysql_connect () function.

If PHP and MySQL are on the same server or on the local network, the number of connections can be ignored, because in either case, there is no advantage to a persistent connection.

4, use Mysql_pconnect () should pay attention to the matter

Generally, when you lock a table, the connection is automatically unlocked after it is closed. But because a persistent connection does not close, any table you accidentally lock will remain locked, and the only way to unlock it is to wait for the connection to timeout or manually kill the process. This problem can also be caused by the handling of things.

Similarly, temporary tables are deleted after the connection is closed. However, because the persistent connection does not close, the temporary table is no longer a temporary table. If you do not delete the temporary table after you complete the requirement, it will connect as a new client and be for the same connection. The same problem is created when you set the session variable.

The effect of using a persistent connection on Apache is not very good. When Apache receives a request from the client, it opens a new database connection, rather than using a persistent connection to open the database, which results in a lot of idle processes, wasted resources, or an unknown error when the maximum number of connections is reached.


Suppose the MySQL server is configured to support up to 10 concurrent. Apache is configured to use 100 child processes.

Apache is coordinated by a parent process to distribute the received HTTP request to which idle subprocess processing, so that 10 HTTP requests are processed quickly, assuming that 10 are assigned to different subprocess, then 10 persistent connections to MySQL are established, MySQL's ability has reached its limit.

Then came an HTTP request that Apache would assign to any other process that was not in the 10 sub processes, and then there was no way to build a connection to MySQL because the pit was full.


There are other problems with using persistent connections.

If you use a persistent connection in your script, and you have a lock table operation, if you do not unlock the script at the end. So the next time this script is run, it will be there for the lock table to wait for the unlock table of the past, it can't come back, it's a dead loop. Unless you restart the Web or MySQL server. Another thing that will cause a lock is the transaction.

Apache module mode:

The difference is that when PHP is running in the Apache module, because Apache has a process pool, a httpd process is put back into the process pool, which allows the MySQL connection resource that is opened with Pconnect to be freed, so that the next connection request can be reused.

This makes the Apache concurrent access is not large, because of the use of Pconnect, PHP saves the time to repeatedly connect DB, so that the speed of access. This should be better understood.

However, when the Apache concurrent access is large, if the use of pconnect, because some of the previous httpd processes occupy the MySQL connection does not close, it may be because MySQL has reached the maximum of the next, so that some of the subsequent requests will never be satisfied.

For example:
If the maximum number of MySQL connections is set to 500, and Apache's maximum simultaneous access is set to 2000
Suppose all accesses require access to DB, and the operation time is longer

The current 500 requests httpd are not finished ... Subsequent HTTD processes are unable to connect to MySQL (since the maximum number of MySQL connections has been reached). Only the current 500 httpd processes end or are reused to connect to the MySQL.

Well, that's a good one, too. The xgy_p test, if the operation is relatively simple, pconnect than connect efficiency is much higher, and with the use of JSP connection pool speed is relatively close. Because this time the httpd process can continue to reuse.

And when the DB operation is complex, time-consuming, because httpd will fork many concurrent process processing, and the first generation of httpd process does not release the DB connection, so that the resulting httpd process can not connect to db. Because this does not duplicate the MySQL connection of other httpd processes. This results in a lot of connection timeouts, like the first 1000 concurrent connection tests that say almost all connection timeouts are the reason.

---
(Back to see if the JSP with a pure DB connection pool, there will not be due to the maximum MySQL connection is not connected to the problem, because the JSP connection pool will be able to wait for other connections to use the complete and reuse.)


Therefore, when the concurrent traffic is not high, the use of pconnect can simply improve access speed, but after the increase in concurrency, whether or not to use the pconnect depends on the programmer's Choice.

Personally, I think that the PHP connection to MySQL is not really used to connect the pool, Pconnect is just the equivalent of borrowing the process of Apache pool use, so in the concurrent access to a large amount of time pconnect does not improve access to DB efficiency. At this point. PHP does not compare with JSP