Solution to the problem that the number of MySQL connections exceeds the limit_PHP tutorial

max_user_connections is the maximum setting for the number of MySQL user connections. The entire statement means: the maximum number of connections parameter for the server's MySQL is not set enough. 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 for reference only):

When statistical functions such as number of people, online time, and number of views belong to the same data space as the main program database, It's easy to show up.
Complex dynamic pages are also easy to appear, especially when multiple database or table operations are involved every time the user browses.
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.
The computer hardware configuration is too low but a MySQL version that is too high and too high configured is installed.
No caching technology is used.
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 the MySQL database. Using the database permanent connection can improve efficiency, but in actual applications, the database permanent connection is often This will lead to some problems. The usual manifestation is that on websites with a large number of visits, intermittent inability to connect to the database often occurs, and an error message similar to "Too many connections in..." appears. After restarting the server, everything is normal again. But after a while the same fault occurred again. 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 bit too docile and difficult to understand, so I will give another example to illustrate the problem in plain English:

Assume that Apache is configured The maximum number of connections is 1000, and the maximum number of connections configured in MySQL is 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 they do not exist at this time. Available database connections, so 100 concurrent database accesses will generate 100 database permanent connections at the same time, reaching the maximum number of database connections. When these operations are not completed, any other connections will no longer be able to obtain database connections. , when these operations are completed, the corresponding connection 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, because Apache will provide access requests for Randomly select idle child processes, so the child process you get is likely to be one of the 100 that does not contain a database connection, and the database connection has reached the maximum, and you cannot successfully establish a new database connection, alas. , you have to keep refreshing the page. When 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 few concurrent visits. However, for a website with few concurrent visits, the efficiency improvement brought by using database permanent connections is It doesn't seem to make much sense. From this perspective, I think the permanent database 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:

Copy code The code is as follows:

mysql_free_result(m_result);
mysql_close(m_Database);

But there are two problems:

When using a long connection (that is, never close after connect), if mysql_close is called in the end, do I need to call mysql_free_result every time?
After mysql_close is called, is the m_result data still available?
Let me talk about the conclusion first:

must be called every time. Because after testing, the pointer of mysql_store_result is different every time, which shows that the same buf is not shared.
It can still be used. After valgrind scanning, the scan result of only calling mysql_close is:

Copy code The code is as follows:

==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, std::allocator >&) (db_openright.cpp:58)
== 9397== by 0x8049F10: main (test.cpp:27)

We will study it slowly in the future. .

http://www.bkjia.com/PHPjc/324002.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/324002.htmlTechArticlemax_user_connections is the maximum setting for the number of MySQL user connections. The meaning of the entire statement is: the maximum connection of the server's MySQL Insufficient parameter settings. Solution: Modify MySQL installation...