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
}

It's very strange... You are connecting a single time in this code, so please, specify the part of code is failing. — Víctor López, Jul 21 '17 at 12:30
No,this codes runs mysql 245.000 times due to while loop in 60 seconds — Fuad Paşayev, Jul 21 '17 at 12:44

score 1 · Answer 1 · answered Jul 21 '17 at 12:32

1

There are few things.

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

answered Jul 21 '17 at 12:32

kawadhiya21

2,458
21
34

No need to close the mysql connection. You can use the same connection over and over. No need to connect to mysql each time. Unfortunately, MySQL pooling is not directly available on PHP – kawadhiya21 Jul 21 '17 at 12:40
Before I haven't used mysql_close bu after some time I get error `max_user_connections to many` and it stops mysql,and asked in this question that "Can I solve this problem adding `mysql_close` to end of `while()` ?" – Fuad Paşayev Jul 21 '17 at 12:43
You connect only once. Then you use the same instance to update/insert. See at what point you call mysql_connect and check if it gets called repetitively . – kawadhiya21 Jul 21 '17 at 12:45
May problem be that I require("db.php") and while runs a lot of times,Is that problem? – Fuad Paşayev Jul 21 '17 at 12:49
In every page I used `require("db.php");` – Fuad Paşayev Jul 21 '17 at 12:56
You can check if mysql connection is open https://stackoverflow.com/questions/5148033/how-do-i-check-if-php-is-connected-to-a-database-already – kawadhiya21 Jul 21 '17 at 13:05

score 1 · Answer 2 · answered Jul 21 '17 at 20:08

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 :)

Mysql connections too many

2 Answers2