How to solve the problem of MySQL exceeding the maximum number of connections_PHP tutorial

Recently, an error message appeared on the website that the User database name has already more than 'max_user_connections' active connections, and the website was paralyzed. It is necessary to study this issue.

max_user_connections is the maximum value setting for the number of MySQL user connections. The entire statement means: the maximum number of connections parameter setting for the server's MySQL is insufficient. Solution: Modify the value of the max_user_connections parameter in the my.ini or my.cnf file in the MySQL installation directory and restart the MySQL server.

But normally, MySQL’s default number of 100 connections is sufficient. We need to think about it from a procedural perspective. MySQL's default maximum number of connections is 100 (N), but only N-1 are actually used by ordinary users. One connection is reserved for the super administrator to prevent the administrator from being kicked out when the connection is full. Many websites will have limited connections when they are running. I think nine times out of ten it is not because the actual number of visits to the website is too large and the number of connections exceeds the standard, but more because we use unreasonable methods when designing the website program. Caused by design architecture or data structure. Possible reasons for abnormal connection exceeding the limit are as follows (Tianyuan’s real-time summary may not be complete or error-free and is for reference only):

1. It is easy to occur when statistical functions such as the number of people, online time, and number of views and the main program database belong to the same data space.
2. Complex dynamic pages are also easy to appear, especially when multiple database or table operations are involved every time the user browses.
3. There are also unreasonable program design (for example, complex operations, waiting and other operations are placed in the middle of database interaction), or there are release bugs in the program.
4. The computer hardware configuration is too low but a MySQL version that is too high and too high configured is installed.
5. No caching technology is used.
6. The database is not optimized or the tables are extremely complex.

Some other reasons will prolong the data interaction time of the database or increase the number of interactions. Therefore, if you encounter this kind of problem, you must first consider whether there is a BUG in the program that causes the connection release to fail, and then consider optimizing the software and hardware. Of course, modifying the number of MySQL connections is also one of the methods of software optimization. I hope everyone can solve this problem by studying their own reasons with a learning attitude. If you really can't find the reason, you have to modify the number of connections first and postpone locating the real cause.

About PHP’s database persistent connection mysql_pconnect

PHP programmers should all know that you can use the mysql_pconnect (permanent connection) function to connect to a MySQL database. Using a permanent database connection can improve efficiency, but in actual applications, permanent database connections often lead to some problems. The usual manifestation is that during large access On a large number of websites, intermittent inability to connect to the database often occurs, and an error message similar to "Too many connections in..." appears. After restarting the server, it is normal again, but the same failure occurs again after a while. I'm afraid not everyone can explain clearly the causes of these problems. Although there is some relevant information in the PHP documentation, the explanation is not easy to understand. Here I am shamelessly trying to make a simple discussion and the stated views. Not all may be correct, and everyone’s feedback is welcome.

First look at the definition of permanent database connection: A permanent database connection refers to a connection that is not closed when the script ends running. When a request for a permanent connection is received. PHP will check whether there is already an identical persistent connection (that was opened previously). If it exists, this connection will be used directly; if it does not exist, a new connection will be established. The so-called "same" connection refers to a connection to the same host using the same user name and password.

There are prerequisites for PHP to use permanent connection to operate MySQL: PHP must be installed as a plug-in or module for a multi-threaded or multi-process web server. The most common form is to use PHP as a module in a multi-process Apache server. For a multi-process server, the typical feature is that there is a parent process and a group of child processes running in coordination, among which the child process actually generates the Web page. Whenever a client makes a request to the parent process, the request is passed to the child process that has not been occupied by other client requests. This means that when the same client makes a request to the server for the second time, it may be handled by a different child process. After opening a permanent connection, all subsequent pages of different sub-processes that request the SQL service can reuse the established SQL server connection. It allows each child process to perform only one connection operation during its life cycle, instead of making a connection request to the SQL server every 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. After an Apache child process ends, it will be put back into the process pool. This also allows the mysql connection resource opened with mysql_pconnect to not be released. It is attached to the corresponding Apache child process and saved in the process pool. Then it can be reused on the next connection request. Everything seems to be normal, but when Apache has a large amount of concurrent access, if you use mysql_pconnect, the MySQL connection occupied by the previous Apache child process will not be closed, and MySQL will soon reach the maximum number of connections, making subsequent requests impossible. No response.

Part of the above text is excerpted from the PHP document. It may seem a little clumsy and difficult to understand, so I will give another example in vernacular to illustrate the problem:

Assume that Apache is configured with a maximum number of connections of 1000, and MySQL is configured with a maximum number of connections of 100. 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 are no available database connections at this time, the 100 concurrency involved in database access will generate 100 permanent database connections at the same time, reaching the maximum number of database connections. When these operations are not completed, any other connections will No more database connections can be obtained. When these operations are completed, the corresponding connections will be put into the process pool. At this time, there are 200 idle child processes in Apache's process pool, 100 of which have database connections. Since Apache will randomly select idle child processes for access requests, the child process you get is likely to be one of the 100 that does not include a database connection. The database connection has reached the maximum and you cannot successfully establish it. For a new database connection, alas, you have to keep refreshing the page. If you are lucky, you happen to be assigned a child process with a database connection, so that you can browse the page normally. If it is a website with a large number of visits, there may be a lot of concurrency at any time, so visitors may constantly find that they cannot connect to the database.

Maybe you will say, can’t we just adjust the maximum number of connections of Apache and MySQL to the same size? Yes, reasonable adjustment of the maximum number of connections will avoid this problem to some extent, but the load capabilities of Apache and MySQL are different. If it is set according to the load capacity of Apache, for MySQL, the maximum number of connections will If it is too large, it will generate a large number of permanent connections to the MySQL database. For example, it is like supporting an army of several million in peacetime. The cost outweighs the gain; and if it is set according to the load capacity of MySQL, for Apache, This maximum number of connections is too small, which feels like overkill and cannot bring out the maximum efficiency of Apache.

So according to the introduction in the PHP manual, it is only suitable to use database permanent connections on websites with low concurrent access. However, for a website with low concurrent access, the efficiency improvement brought by using database permanent connections does not seem to be much. In a big sense, from this perspective, I think the database permanent connection in PHP is basically a useless role. If you must use the concept of database connection pool, you can try sqlrelay or mod_dbd provided by Apache itself. Maybe There will be surprises.

About mysql_free_result and mysql_close

When I used mysql before, I always used short links. I called mysql_store_result once to get the data and then called directly:

mysql_free_result(m_result);
mysql_close(m_Database);

But there are two problems:

1. When using a long connection (that is, never close after connecting), if mysql_close will be called in the end, do you need to call mysql_free_result every time?
2. After mysql_close is called, whether the m_result data is still available.

Let me talk about the conclusion first:

1. 必须每次调用。因为经过测试，每次mysql_store_result的指针都是不同的，可见并不是共享了同一块buf。
2. 还是可以使用。经过valgrind扫描，只调用mysql_close的扫描结果是：

==9397== 16,468 (88 direct, 16,380 indirect) bytes in 1 blocks are definitely lost in loss record 4 of 5
==9397==    at 0x40219B3: malloc (vg_replace_malloc.c:195)
==9397==    by 0x8053EA2: my_malloc (in /data/home/dantezhu/appbase/application/platform/openqqcom/share/db_openright/test/test)
==9397==    by 0x806D314: mysql_store_result (in /data/home/dantezhu/appbase/application/platform/openqqcom/share/db_openright/test/test)
==9397==    by 0x804BB04: CMySQLCppClient::Result(st_mysql_res*&) (mysql_cpp_client.cpp:127)
==9397==    by 0x804AB58: CDBOpenRight::GetUinsByApp(unsigned int, std::set<unsigned int, std::less<unsigned int>, std::allocator<unsigned int> >&) (db_openright.cpp:58)
==9397==    by 0x8049F10: main (test.cpp:27)

以后再慢慢研究。。

http://www.bkjia.com/PHPjc/752548.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/752548.htmlTechArticle最近网站出现 User 数据库名称 has already more than 'max_user_connections' active connections 的报错，网站瘫痪。有必要研究下这个问题。 max_user_connec...