Max_user_connections is the maximum number of MySQL user connections set, the whole statement means: The server MySQL maximum number of connection parameters set. Workaround: Modify the value of the Max_user_connections parameter in the My.ini or my.cnf file under the MySQL installation directory and restart the MySQL server.
But normally, the default number of 100 connections to MySQL is sufficient. We need to consider the procedure. MySQL's default maximum number of connections (n), the actual use of only N-1 for ordinary users, keep a connection is left to the Super administrator to use, to prevent the connection is not full of administrators will also kick out. Many web sites in the runtime will be limited to the number of connections, I think ten is not the site's real traffic too much lead to the number of connections exceeded, more because we design the Web site process using an unreasonable design structure or data structure caused. Abnormal connection can be caused by the following reasons (days of immediate induction may not be complete or no Chong for reference only):
Statistical functions such as number of people, online time, browsing number and so on are very easy to be found in the same data space as the main program database.
Complex dynamic pages, especially when the user is involved in multiple databases or multiple table operations, are also easy to appear.
There is also the unreasonable design (such as complex operations, waiting, etc., in the middle of the database interaction), or the program exists to release bugs.
Computer hardware configuration is too low, but install too high version, too high configuration of MySQL.
No caching technology was used.
The database is not optimized or table-designed and complex.
And so on, for some reason, the data interaction time of the database or increase the number of interactions will be prolonged. Therefore, if you encounter such problems, first of all to consider whether the program has a bug caused the connection to release failed, and again is to consider optimizing hardware and software. Of course, modify the MySQL connection number is also a software optimization of the operation of one of the methods, I hope we can learn from the attitude by studying their own reasons to solve this problem. If you can't find the reason, then you have to modify the number of connections, to suspend the positioning of the real reason.
About PHP's database persistent connection Mysql_pconnect
PHP programmers should know that connecting to a MySQL database can use the Mysql_pconnect (permanent connection) function, the use of database permanent connection can improve efficiency, but the actual application of the database permanent connection will often lead to some problems, The usual performance is in the large traffic on the site frequently intermittent failure to connect to the database, appear similar to "Too many connections in ... "Error message, restarting the server is normal, but not a moment later, the same fault occurred. The cause of these problems, I am afraid not everyone can be clear, although there are some relevant 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 a database permanent connection: 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 permanent connection (previously open). If it exists, the connection is used directly, or a new connection is established if it does not exist. A connection called "Same" means a connection with the same username and password to the same host.
PHP operates on a permanent connection using MySQL as a prerequisite: PHP must be installed as a plug-in or module for multi-threaded or multiple-process Web servers. The most common form is to use PHP as a module for a multiple-process Apache server. A typical feature of a multiple-process server is that there is a parent process coordinated with a set of child processes, where the actual generation of the Web page is a subprocess. Whenever a client makes a request to a 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 the server for the second time, it is likely to be handled by a different subprocess. After a permanent connection is turned on, all the different child processes requesting the subsequent pages of the SQL Service will be able to reuse the established SQL Server connection. It makes each child process only one connection operation in its lifecycle, rather than making a connection request to the SQL server each 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, an Apache subprocess will be put back to the process pool, which makes the mysql_pconnect opened with the MySQL connection resources can not be released, Instead, it is attached to the corresponding Apache subprocess and saved to the process pool. It can then be reused when the next connection request is made. Everything seems to be normal, but in the large number of Apache concurrent access, if the use of Mysql_pconnect, will be due to previous Apache process occupied by the MySQL connection does not close, the MySQL will soon achieve maximum connectivity, So that subsequent requests may not be responded to.
The above part of the text is extracted from the PHP document, it may seem to be some genteel bad understanding, then I use vernacular to give an example to illustrate the problem:
Assuming that Apache configures the maximum number of connections for 1000,mysql, the maximum number of connections is 100, and 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 database connection available at this time, so this involves database access of 100 concurrent will produce 100 database permanent connection, reached the maximum number of database connections, when these operations do not end, any other connection can no longer get the database connection, when these operations are over, The corresponding connection is put into the process pool, at which point the Apache process pool has 200 free subprocess, 100 of which are connected with the database, because Apache will randomly pick up the free subprocess for the access request, So the subprocess you get is probably one of those 100 that doesn't contain a database connection, and the database connection has reached the maximum, you can not successfully create a new database connection, alas, you will have to constantly refresh the page, when the luck is good, happened to be assigned to a database connection with the child process, To navigate the page normally. If it is a large number of visits to the site, there may be a lot of concurrency at any time, so the viewer may be constantly discovering the phenomenon of the inability to connect to the database.
Perhaps you would say, we put the maximum number of Apache and MySQL connection to the same size is not it? Yes, a reasonable adjustment to the maximum number of connections to some extent will avoid this problem, but Apache and MySQL load capacity is different, if according to the Apache load capacity to set, for MySQL, the maximum number of connections is large, will produce a large number of MySQL database permanent connections, for example, as in peacetime to feed a millions of of the army, the cost of the same, and if according to MySQL's load capacity settings, for Apache, the maximum number of connections on the small, a bit of the feeling of killing chicken, Can't play Apache's maximum efficiency.
So according to the PHP manual, it's only appropriate to use database permanent connections on sites with little concurrent access, but for a site with little concurrent access, the efficiency gains from using a database permanent connection do not seem to make much sense, and from this point of view, I think the database permanent connection in PHP is basically a chicken role, if you have to use the concept of database connection pool, you can try Sqlrelay or Apache itself provided mod_dbd, perhaps there will be surprises.
About Mysql_free_result and Mysql_close
Before using MySQL is always in the short link, call Mysql_store_result fetch once data directly after the call:
Copy Code code as follows:
Mysql_free_result (M_result);
Mysql_close (m_database);
But there are two questions:
When using a long connection (that is, after connect is not close), if the final call to Mysql_close, do not need to call mysql_free_result every time?
When the mysql_close is called, the M_result data is still available.
Let me start with the conclusion:
Must be invoked each time. Because after testing, each mysql_store_result pointer is different, can not be seen to share the same piece of buf.
can still be used. After a valgrind scan, only the Mysql_close scan results are called:
Copy Code code as follows:
==9397== 16,468 (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)
Study again later.
