Mysql connections too many

Question

I'm preparing chat system with comet style programming.It delays(sleeps) page for a 60 second and I use mysql queries in this loop and it approximately sends 245.000 queries to mysql. Mysql status is like below:

Aborted_connects - 179391
Connections - 15673040
Max_used_connections - 59
Threads_connected - 6

May it be problem that Connections tab is 15 million, but I can't kill them. Can I solve this problem adding mysql_close to end of while() ?

set_time_limit(60);
mysql_connect("...");
while(true){
  usleep(60000);
  clearstatcache();
  mysql_query("...");
  mysql_close();//<= this one
}

Answer 1

There are few things.

1. Your code somehow connects to mysql way too much. Debug your code.
2. Try to pool your connections. Read this Connection pooling in PHP

Answer 2

Seeing your code and your problem, we should take into account and differentiate between connection and query.

It's useless to close the conection on the while true loop and it's an error.

Your code should be something like this:

mysql_connect("...");
while(true){
  //do stuff like:
  mysql_query("...");
}
//in case of we exit while true...
mysql_close();//<= this one

If we read the mysql_close manual, we can find this:

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

So, for now, don't take into account when to close the mysql_connection. We should see how the connections are being created and where.

I recomend you to check inside your MySQL and debbug it. You can with this commands:

show status where `variable_name` = 'Threads_connected';

With this you can check the current connections. Also you could check with:

show processlist;

And you will see all the process list.

Maybe with that we can start to debug and find the problem :)