How to solve the problem that the number of MySQL connections exceeds the limit

Max_user_connections is the maximum number of MySQL user connections. The full-range statement indicates that the maximum number of connections of MySQL on the server is insufficient. Solution: Modify the value of the max_user_connections parameter in my. ini or my. cnf file in the MySQL installation directory and restart the MySQL server.

But normally, the default 100 connections of MySQL are sufficient. We need to consider from the program. MySQL's default maximum number of connections is 100 (N), the actual use of ordinary users only N-1, keep a connection is left for the super administrator to use, prevent the administrator from being kicked out when the connection is full. Many websites may encounter limited connections during operation. I think that the ninth day is not because the actual traffic of the website is too large, leading to excessive connections, this is because we have adopted an unreasonable design architecture or data structure when designing website programs. The possible causes for abnormal connection exceeding the limit are as follows (tianyuan's instant induction may not be complete or there is no error limit for your reference only ):

Statistical functions such as the number of people, online time, and number of views are easy to appear when they belong to the same data space as the master program database.
Complex Dynamic Pages, especially when users access multiple databases or tables each time.
There is also unreasonable Program Design (such as complex operations, waiting and other operations placed in the middle of the database interaction behavior), or the program has released bugs.
The computer hardware configuration is too low, but the installation is too high version, too high configuration of MySQL.
Cache Technology is not used.
The database has not been optimized or the table design is complex.
For other reasons, the data interaction time of the database is prolonged or the interactions are increased. Therefore, if you encounter such problems, you must first consider whether the program has a BUG that causes the connection to fail to be released, and then consider optimizing the software and hardware. Of course, modifying the number of MySQL connections is also one of the methods for software optimization. I hope everyone can solve this problem by studying their own reasons. If the cause is not found, you have to modify the number of connections first to suspend locating the real cause.

Persistent connection to the PHP database mysql_pconnect
PHP programmers should know that they can use the mysql_pconnect (permanent connection) function to connect to the MySQL database. Using a permanent connection to the database can improve efficiency. However, in actual applications, permanent connection to the database may lead to some problems, the general performance is that the database connection is often interrupted on websites with high traffic volumes, and similar to "Too connector connections in... "error message: it is normal to restart the server again, but the same fault will occur again in a short time. I am afraid not everyone can make it clear about the causes of these problems. Although the PHP document contains some relevant information, the explanation is not easy to understand, here I am cheeky and try to make a simple discussion. The points mentioned are not necessarily correct. You are welcome to give your feedback.

First, let's take a look at the definition of permanent database connection: permanent database connection refers to a connection that is not closed when the script ends running. When receiving a permanent connection request. PHP will check whether there is already a permanent connection (previously Enabled. If yes, the connection is directly used. If no, a new connection is established. A "same" connection means a connection with the same user name and password to the same host.

PHP uses the permanent connection method to operate MySQL on the premise that PHP must be installed as a plug-in or module of a multi-threaded or multi-process Web server. The most common form is to use PHP as a module of a multi-process Apache server. For a multi-process server, a typical feature is that a parent process is running in coordination with a group of sub-processes, and the actual webpage is a sub-process. When a client sends a request to the parent process, the request is sent to a child process that is not occupied by other client requests. This means that when the same client sends a second request to the server, it may be processed by a different sub-process. After a permanent connection is enabled, All The subprocesses that request SQL services can re-use the established SQL server connection. It allows each sub-process to perform only one connection operation in its lifecycle, rather than submitting a connection request to the SQL server every time it processes a page. Each sub-process establishes a separate permanent connection to the server. PHP itself does not have the concept of database connection pool, but Apache has the concept of process pool. After an Apache sub-process ends, it will be put back into the process pool, this allows the mysql connection resource opened with mysql_pconnect to be stored in the process pool on the corresponding Apache sub-process instead of being released. So it can be reused in the next connection request. Everything seems to be normal, but if mysql_pconnect is used when Apache has a high concurrency traffic, the MySQL connection occupied by the Apache sub-process is not closed, soon MySQL reaches the maximum number of connections, and subsequent requests may not be responded.

Some of the above text is excerpted from the PHP document. It may seem a bit difficult to understand, so I will explain the problem with another example in the vernacular:

Assume that the maximum number of connections configured for Apache is 1000, and the maximum number of connections configured for MySQL is 100. When the Apache server receives 200 concurrent accesses, 100 of them involve database access, the remaining 100 do not involve database access, because there is no available database connection at this time, so the 100 concurrency involved in database access will generate 100 permanent connections at the same time, the maximum number of connections to the database is reached. When these operations are not completed, no other connections can be connected to the database. When these operations are completed, the corresponding connections will be placed in the process pool, at this time, there are 200 idle sub-processes in the Apache process pool, of which 100 are connected to the database, because Apache will randomly select idle sub-processes for access requests, so the sub-process you get is probably one of the 100 that does not contain database connections, and the database connection has reached the maximum value, and you cannot establish a new database connection. Alas, you have to refresh the page constantly. When is the page lucky? It happens that you are assigned a sub-process with a database connection before you can browse the page normally. For websites with large traffic volumes, there may be a large number of concurrent connections at any time, so viewers may constantly find that they cannot connect to the database.

Maybe you will say that we can adjust the maximum number of connections between Apache and MySQL to the same size? Yes, reasonable adjustment of the maximum number of connections will prevent this problem to some extent, but the load capacities of Apache and MySQL are different, for MySQL, the maximum number of connections is too large, and a large number of MySQL databases will be Connected permanently. For example, it is like a peaceful age that needs to support millions of troops. The overhead is not worth the candle; however, if you set the load capacity according to Mysql, for Apache, the maximum number of connections is too small, which is a bit cool and cannot maximize the efficiency of Apache.

Therefore, according to the introduction in the PHP manual, it is only suitable for permanent database connections on websites with low concurrent access, but for a website with low concurrent access, the efficiency improvement caused by permanent database connection does not seem to be significant. From this perspective, I think the permanent database connection in PHP is basically a chicken role, if you must use the concept of database connection pool, you can try sqlrelay or mod_dbd provided by Apache itself, which may surprise you.

About mysql_free_result and mysql_close
I used to use mysql for a short link. I called mysql_store_result to obtain data once and then called it directly:
Copy codeThe Code is as follows:
Mysql_free_result (m_result );
Mysql_close (m_Database );

But there are two problems:

When persistent connections are used (that is, they are not closed after connect). If mysql_close is called at last, do you need to call mysql_free_result every time?
After mysql_close is called, can m_result data be used.
Conclusion:

It must be called each time. After testing, the pointers of mysql_store_result are different each time. Therefore, we do not share the same buf.
It can still be used. After valgrind scan, the scan result that only calls mysql_close is:
Copy codeThe 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)
= 9397 = by 0x806D314: mysql_store_result (in/data/home/dantezhu/appbase/application/platform/openqqcom/share/db_openright/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)

I will study it later ..