I inherited a samopisny project.

If you increase the number of visits, you create too many connections to the database and it hangs up other sites on the Mysql server (there are several of them). The max_connections connection limit was previously increased to 200 (default 151). The same guys used to log connections, which suggests that the problem lies precisely here. At peak times, up to 200 new connections per minute are created in the log (connection logging code is below). The pieces of code responsible for connecting to the database:

private static $_connectionLink = null; private static $_connectionErrors = null; public function __construct() { if (empty(self::$_connectionLink)) { self::connect(); $handle = fopen('connect.log','a'); fputs($handle,"----------- NEW connect ".date('Ymd H:i')."----------- \n"); fclose($handle); } } private static function connect() { $host = 'localhost'; $user = 'user'; $password = 'pass'; $dbName = 'dbname'; self::$_connectionLink = @mysqli_connect($host,$user,$password,$dbName); if (!mysqli_connect_errno()) { mysqli_set_charset(self::$_connectionLink, 'utf8'); } else { } }

Special attention to the line: if (empty(self::$_connectionLink)) -

Question: Is it correct in this case to check the presence of an already open connection using empty ()?

Maybe there is an error here and in the case of using the self :: $ _ connectionLink construction empty () still returns TRUE even when the variable is not empty and then a new connection is created even if there is an active unclosed? Maybe you need to do something like if( null === self::$_connectionLink ) or if (!self::$_connectionLink) ? Or what else could be the reason for the large number of open connections in this case?

  • Tried to use the prefix p: when specifying the host to open a permanent connection? php.net/manual/ru/mysqli.construct.php - naym
  • How many instances of this class are in the project code? $ db = new Your_Database_Class_Name (); - noadev
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

2 answers 2

There should be a lot of them, if you don’t build a queue of requests from the application and do not limit the number of connections by the pool through which it works. Here I understand it is not. Another thing is that they should immediately work out and you should not see them and the load from them.

It is better to dig in the direction why there are a lot of them in principle - why did the project work before, and now the problems? Maybe the pages are not cached or the cache is broken, as a result, the application for every sneeze climbs into the database? Maybe the database is already under load and the requests have started to be repeated (a little bit at a time)? Requests are slowed down, they take longer to execute, they take longer to execute, they keep the connection longer, they keep the connection longer, there are fewer free connections left to serve the remaining requests. In principle, MySQL uses the same mechanism — and the culprit is usually not visible, it’s just that all queries have been executed longer and SHOW PROCESSLIST shows those that are executed more often. It will not be possible to endlessly load one server with new and new projects. Maybe there are not enough server resources? Or is the server cold?

PS Plus, delete the record on the disk when you open the connection. This is an extra wait when the connection is already open, if there is a load on the disk, you only increase the connection time with the MySQL server, which further aggravates the situation. If possible, send log information to another server using syslog until the situation is resolved. Provided, of course, that the project itself does not twitch the disk every now and then.

  • And if you just throw another 50 - 100 to max_connections? Will it significantly reduce memory? - haver
  • It is best to calculate, add the size of read_buffer_size + read_rnd_buffer_size + tmp_table_size + sort_buffer_size + thread_stack + join_buffer_size and multiply by 50. This is the maximum that may be required under the most unfavorable circumstances (estimate from the top). The fact is that most of these buffers are included only as needed. Those. it will actually be consumed less, even if all 50 threads are involved. - cheops
  1. Use persistent connections (prefix p: before host p:localhost )
  2. Open a connection only when it is really needed. Those. when sending a request to the server.