When should PHP call mysql_close () and what is the difference between the pconnect method and the traditional method?

I used to think that when the PHP page is executed, it will automatically release everything. I believe many people share the same idea. It turns out that this is not the case. For example, the session will not be released as the page is executed.

PHP's garbage collection mechanism is only for PHP itself. For MySQL, PHP does not have the right to automatically release its contents. If you do not call mysql_close () before the page is executed, MySQL will not close the connection. If you are using the pconnect method, even if you call mysql_close () before the page is executed, you cannot close this connection with another MySQL.
Maybe you don't feel anything wrong when the load is low. Next I will explain what I have observed over the past two days:

Use pconnect to establish a connection in PHP, and then execute show processlist under the mysql client. If your load reaches a certain level, you can see many sleep processes, these processes are what people often say about dead connections. They will keep sleep until my. the time for the wait_timeout parameter set in CNF is reached, and MySQL will kill it by itself. When it is killed, MySQL will also record a log of aborted connection XXX to DB: 'xxx' User: 'xxx' HOST: 'xxx' in the error-log, translate it with Google, and you will get a very strong Explanation of "the connection in the dead "!

 

There are three reasons for sleep:
1. ClientProgramMysql_close () was not called before exiting ().
2. The client sleep does not send any request to the server within the seconds specified by wait_timeout or interactive_timeout.
3. Before the client end, the client end the process by sending a request to the server.

The above was changed based on Google's translation, and the translation may be inaccurate in some places. See the following for the original article:
1 The client program did not call mysql_close () before exiting.
2 The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.
3 The client program ended abruptly in the middle of a data transfer

If the number of sleep processes is too large within the same time, and the number of connections in other States exceeds the value of max_connection, MySQL is used outdoors except for the root user, you cannot continue to process any request and establish a connection with any request or directly go down. Therefore, this problem is quite serious under heavy loads. If you find that many dead connections exist in your MySQL, first check whether your program uses the pconnect method. Second, check whether mysql_close () is called in time before the page is executed (),

There is another way you can go to my. in CNF, wait_timeout and interactive_timeout are added to reduce their values. By default, the value of wait_timeout is 8 hours. You can change it to 1 hour or half an hour. In this way, MySQL will kill dead connections faster. Prevent the total number of connections from exceeding the value of max_connection. Or set the value of max_connection to a greater value, but this is obviously inappropriate. The more connections there are, the more pressure on your server. In fact, those connections are redundant, and killing them as soon as possible is the best strategy.

I used to say that when using PHP to connect to MySQL, I should try not to use pconnect. After reading what I mentioned above, I should be able to understand why, because PHP is used for web development in most cases and web development is intended for multiple users, the number of users is proportional to the number of MySQL connections. Using pconnect, even if you call mysql_close (), the database connection cannot be released, so the number of dead connections in MySQL will increase.

In my opinion, it is necessary to use pconnect only when your application belongs to the point-to-point method or when you can ensure that the number of connections is small, because the number of connections is small, keep it in the connection state, avoiding the process of opening and closing again. This may be better than the traditional method.

The most correct way to call mysql_close () is to call mysql_close () immediately after the last MySQL operation is executed (). This is the most correct practice. It is not always necessary to write mysql_close () in the last line of the page.