0

I have a PHP script that connects to MySQL does some operations and ends.

At the end there is a mysqli_close($link); statement.

Still I have sleeping processes for that user in MySQL process-list and can't figure out where from?

As far as I know as soon as the PHP script ends it should close the connection even without a mysqli_close at the end (but there is one).

So where are those sleeping processes from on that user?

If I restart httpd they all die.

Adi
  • 5,089
  • 6
  • 33
  • 47
transilvlad
  • 13,974
  • 13
  • 45
  • 80
  • in http://php.net/manual/en/mysqli.close.php i read `Closes a previously opened database connection` So it doenst close all connections. Maby you open the connection multiple times? – Ron van der Heijden Aug 30 '12 at 11:42
  • I doubt that considering the fact the script have just a few lines I would have noticed. – transilvlad Aug 30 '12 at 11:46
  • Also you can read this ;) http://stackoverflow.com/questions/6320771/how-to-close-unclosed-mysql-connections – Ron van der Heijden Aug 30 '12 at 11:52
  • Not really helpful. Additional information: there seem to be a constant 10 sleeping connections. Two groups of 5 each of the 5 in one group having 10 seconds distance from previous and next if any. The time between the groups is 4 seconds. Makes absolutely no sense where these can come from. – transilvlad Aug 30 '12 at 12:06

1 Answers1

2

mysqli provides persistent connections. That is, it provides a layer between the PHP developer's model of connections and the dbms's model.

When you use persistent connections a PHP program closes a connection with mysqli_close(). But, the connection to the DBMS is maintained by the PHP runtime ready for the next open call.

This provides an enormous speedup on busy web sites, because opening a connection from the PHP runtime (or any other dbms client) and the dbms is expensive.

Your nice round number of idle connections (ten of them) smells a lot like a persistent connection pool to me.

Could this be the explanation for what you are seeing?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 1
    +1; I'd say that the connections disappearing upon httpd restart is the clincher. – LSerni Aug 30 '12 at 12:31
  • It has been a long time but having similar issue. What if we have limited connections and we don't want any sleeping processes. Setting interactive_timeout and wait_time is not solving the problem cause don't have enough priviliege on shared server. – Burak Karasoy Nov 20 '15 at 13:43
  • You can manage your persistent connections from php. There are two steps to this: first, configuring php. Read this. http://php.net/manual/en/mysqli.configuration.php#ini.mysqli.allow-persistent Second, managing the number of php processes running. That depends on your web server (apache? ngnix?). The goal of 0 "sleeping processes" is entirely unreasonable, however. It will destroy your website performance. – O. Jones Nov 20 '15 at 14:54