MySQL connection number exceeds the limit of the workaround

Recent website appears User database name has already more than ' max_user_connections ' Active connections error, website paralyzed. It is necessary to study the problem.

Max_user_connections is the maximum number of MySQL user connections set, the whole sentence means: The server's MySQL maximum number of connections parameter setting is insufficient. Workaround: Restart the MySQL server by modifying the value of the Max_user_connections parameter in the My.ini or my.cnf file in the MySQL installation directory.

But normally, MySQL's default number of 100 connections is sufficient. We need to think about it from the procedure. MySQL's default maximum number of connections is (n), the actual use of the average user only N-1, leaving a connection is reserved for the Super administrator to use, to prevent the connection is not full of the administrator also kicked out. Many websites in the run time will appear the connection number is restricted phenomenon, I think ten is not the real traffic of the website is too big cause the connection number exceeds the limit, more is because we design the website program to use unreasonable design structure or data structure to cause. Non-normal connection overrun may be due to the following reasons (the day of the immediate induction may not be complete or no inappropriateness for reference only):

Statistical functions such as number of people, online time, and number of views are very easy to appear when they belong to a data space in the main program database.
Complex dynamic pages, especially when users are involved in multi-database or multi-table operations, are also easy to see.
There is also the unreasonable programming (such as complex operations, waiting for operations to be placed in the middle of the database interaction), or the program has released a bug.
The computer hardware configuration is too low but installs too high version, too high configuration MySQL.
No caching technology is used.
The database is not optimized or table design and complex.
For some reason, the data interaction time of the database is prolonged or the number of interactions is increased. Therefore, if you encounter such problems, first of all to consider whether the program has a bug caused the connection to release the failure, again is to consider optimizing hardware and software. Of course, modify the MySQL connection number is also one of the software optimization operation methods, I hope that we can learn from the attitude of the study of their own reasons to solve this problem. If the reason is not found, then we have to modify the number of connections, the real reason for the delay in positioning.

Database Persistent connection mysql_pconnect for PHP
PHP programmers should know that connecting to a MySQL database can use the Mysql_pconnect (permalink) function, the use of a database permanent connection can improve efficiency, but the actual application of persistent database connection will often lead to some problems, The usual performance is the intermittent inability to connect to a database on a site with a large amount of traffic, and a similar "Too many connections in ... "Error message, restarting the server is normal, but not a moment later, the same failure occurred. (www.jbxue.com) for the cause of these problems, I am afraid that not everyone can be clear, although there are some information in the PHP document, but the explanation is not easy to understand, here I have the cheek to try to do a simple discussion, the views are not all correct, welcome feedback.

First look at the definition of persistent database connections: a permanent database connection is a connection that does not close when the script finishes running. When a request for a permanent connection is received. PHP will check to see if there is already an identical persistent connection (previously open). If present, the connection will be used directly and if it does not exist, a new connection is established. The so-called "Same" connection refers to a connection that uses the same user name and password to the same host.

PHP uses the permanent connection mode to operate MySQL is a prerequisite: PHP must be installed as a multi-threaded or multi-process Web server plug-in or module. The most common form is to use PHP as a module for a multi-process Apache server. For a multi-process server, the typical feature is that there is a parent process and a set of child processes running in harmony, where the actual generation of the Web page is a child process. Whenever a client makes a request to the parent process, the request is passed to a child process that is not yet occupied by another client request. This means that when the same client requests a second time to the server, it is likely to be handled by a different child process. After a persistent connection is turned on, the subsequent pages of all the different sub-processes requesting the SQL service are able to reuse the established SQL Server connection. It allows each child process to do only one connection operation in its lifetime, rather than making a connection request to the SQL server each time a page is processed. Each child process establishes its own, 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, an Apache child process will be put back to the process pool, which makes the mysql_pconnect open with the MySQL connection resource can not be released, Instead, it is attached to the corresponding Apache child process and saved to the process pool. It can then be reused on the next connection request. Everything seems to be normal, but when the Apache concurrency is large, if you use Mysql_pconnect, the MySQL connection occupied by the previous Apache child process is not close, and the MySQL reaches the maximum number of connections quickly. Causes the subsequent request to be unresponsive.

Some of the above text is excerpt from the PHP document, it may seem that some genteel is not good understanding, then I will use plain English to give an example to illustrate the problem:

Assuming that the Apache configuration has a maximum number of connections of 1000,mysql configured with a maximum of 100 connections, 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 this involves the database access of the 100 concurrency will simultaneously generate 100 database permanent connection, reached the maximum number of database connections, when these operations are not finished, any other connections can no longer get the database connection, when these operations are finished, The corresponding connection will be put into the process pool, the Apache process pool has 200 free child processes, of which 100 are with the database connection, because Apache will randomly pick up the idle child process for the access request, So you get the sub-process is likely to not contain the database connection of the 100 of the one, and the database connection has reached the maximum, you can not successfully establish a new database connection, alas, you will have to constantly refresh the page, at which time lucky, happened to be assigned to a child process with database connection, To browse the page normally. If the site is a large amount of traffic, there can be a lot of concurrency at any time, so the browser may not stop to find that the database is unable to connect the phenomenon.

Perhaps you would say, we put the maximum number of Apache and MySQL connection to the same size is not okay? Yes, a reasonable adjustment to this maximum number of connections will avoid this problem to some extent, but Apache and MySQL load capacity is different, if according to Apache's load capacity to set, for MySQL, the maximum number of connections is too large, Will generate a lot of MySQL database permanent connection, for example, like the peace era to feed a millions of of the army, its costs outweigh the cost; If you follow the load capacity of MySQL set, for Apache, the maximum number of connections is small, a little kill chicken sledgehammer feeling, The maximum efficiency of Apache cannot be played.

So according to the PHP manual, only suitable for the use of persistent database connection on a site with little concurrent access, but for a site with little concurrent access, the efficiency of using a database permanent connection does not seem to make much sense, from this point of view, I think the database permanent connection in PHP is basically a chicken role, if you must use the concept of database connection pool, you can try Sqlrelay or Apache itself to provide mod_dbd, perhaps there will be surprises.

About Mysql_free_result and Mysql_close
The previous use of MySQL has been to use a short link, call Mysql_store_result to get the data once and then directly call:
Mysql_free_result (M_result);
Mysql_close (m_database);

But there are two problems:

When a long connection is used (that is, after connect has not been close), if you call Mysql_close at the end, do you need to call Mysql_free_result every time?
When Mysql_close is called, M_result data is still available.
Let me start with the conclusion:
Must be called every time. Because after testing, each mysql_store_result pointer is different, visible is not shared with the same piece of buf.
can still be used. After Valgrind scanning, only the Mysql_close scan results are:
==9397== 16,468 (16,380 Indirect) bytes in 1 blocks is 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)

Later study slowly.