Nine Million Bicycles The solution to the problem that the number of MySQL connections exceeds the limit

max_user_connections is the maximum setting for the number of MySQL user connections. The whole 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), and 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 that 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. It is 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):
It is easy to occur when statistical functions such as the number of people, online time, and number of views belong to the same data space as the main program database.
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 too high a version and too high configuration of MySQL are installed.
No caching technology is used.
The database is not optimized or the table design is extremely complicated.
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 database permanent connection can improve efficiency, but in actual applications, database permanent connections often lead to some problems, usually The most common symptom 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, it is normal again, but the same problem occurs again after a while. failure. 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, let’s look at the definition of a permanent database connection: A permanent database connection refers to a connection that is not closed when the script ends running. When a permanent connection request is received. PHP will check whether there is already an identical persistent connection (that was opened previously). If it exists, the 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 username and password.
There is a prerequisite 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 in 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 text above is excerpted from the PHP document. It may seem a bit clumsy and difficult to understand, so I will use another example to illustrate the problem in plain English:
Suppose Apache is configured with a maximum number of connections of 1000, and MySQL is configured with a maximum number of connections. The number 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 there is no available database connection at this time, so there are 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 connection will no longer be able to obtain a database connection. When these operations are completed, the corresponding connection will be lost. 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, you get The child process is probably 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, and 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 be 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 connection on websites with small concurrent access. However, for a website with small concurrent access, the efficiency improvement brought by using database permanent connection does not seem to be much. Meaning, 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 surprise.
About mysql_free_result and mysql_close
When I used mysql, I always used short links. After calling mysql_store_result to get the data, call it directly:

Copy the code The code is as follows:

mysql_free_result(m_res ult);
mysql_close( m_Database);

But there are two questions:
When using a long connection (that is, never close after connect), if mysql_close will be called in the end, do you need to call mysql_free_result every time?
After mysql_close is called, m_result Is the 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. .

The above introduces the solution to the problem that the number of MySQL connections in Nine Million Bicycles exceeds the limit, including the content of Nine Million Bicycles. I hope it will be helpful to friends who are interested in PHP tutorials.