PHP/mySQL: how to debug a 'too many connections' error?

Hello!

I am working right now with PHP website on my client's dedicated server and I get periodically an error too many connections but ~5 users are browsing this page.

In phpMyAdmin I can see that max connections variable is set to 400.

On our local MySQL server in my corporation we have max connections set to 200 and other mates uses in the same time this server.

I heard that on their server some other web is using the same MySQL with big trafiic.

Here are my questions:

• How to debug this problem?
• Can I look into some list of SQL queries that was executed in last 10 minutes to check if it is our problem or their?

From serverfault hsz

• The symptoms you describe point to persistent connections. In some setups PHP does not handle persistent connections very well: it tends to open new connections rather than reuse the existing ones. I suggest you disable this feature and see if the issue persists:

  • At code level: find calls to mysql_pconnect() and replace them with mysql_connect().
  • At configuration level: find the mysql.allow_persistent directive and set it to false.

  If you notice an improvement, you can try to fine tune persistent calls or get rid of them permanently.

  hsz : I also shoot that case. At all I can not modify their web/server configuration to make that changes (my web does not uses `persistent connections` anywhere). Tomorrow I am going to meet their man and I will ask them to do that.

  Michael Mior : This doesn't require modifying any server configuration. You would just need to update the PHP scripts for the application.

  hsz : Sure, but I have no access to the second webpage which is also using the same MySql server - so I can not update their PHP script that may uses `mysql_pconnect`. `phpinfo()` shows me that ` mysql.allow_persistent` is `On`. Changing that flag to `Off` will not give any results at all - it should be changet on their machine.

  hsz : They changed `mysql.allow_persistent` to `Off` and our CMS accelerated and now there are no problems with connections. Thanks! But I am still wondering how to check what scripts was connecting with MySql server?

  Álvaro G. Vicario : SHOW PROCESSLIST tells you the user, host, DB and (if you're lucky) query.

  hsz : But it will show me only current processes, right ? Are not there something like query history ?

  Álvaro G. Vicario : http://dev.mysql.com/doc/refman/5.1/en/query-log.html
  From Álvaro G. Vicario