Who is taking all of my connections?

[Chris McAvoy]

Hi,

Every morning around 11am, we run out of database connections. We
have one user, and a bunch of scripts that use that account to connect
to the database. I believe we have a bad script out there that's
routinely chewing up our available connections. I plan on turning on
as much logging as possible tonight. Are there any other tools out
there that can help me figure out exactly which script is generating
the connections?

Thanks,
Chris

[Rob]

"Chris McAvoy" <mc******@hotmail.com> schreef in bericht
news:73**************************@posting.google.c om...

Hi,

Every morning around 11am, we run out of database connections. We
have one user, and a bunch of scripts that use that account to connect
to the database. I believe we have a bad script out there that's
routinely chewing up our available connections. I plan on turning on
as much logging as possible tonight. Are there any other tools out
there that can help me figure out exactly which script is generating
the connections?

Thanks,
Chris

Hello Chris,

I do not know of such tools.
But you can start to do a "find in all files" with a editor that support
this functionality and look for the use of the function mysql_pconnect().
Because if you use persistent connections (mysql_pconnect) it is possible
that some connections are not closed properly and your script is creating
new ones when ever it is run. This will exceed your connections limit
rapidly. Then read the information about persistent connection and determine
if you really need such a connection. Maybe mysql_connect() is all you need
and changing mysql_pconnect to mysql_connect solves your problem.

More information :

http://www.mysql.com/news-and-events...000000086.html

http://nl.php.net/manual/en/features...onnections.php

I hope this helps to solve the problem
Rob

[Rob]

"Chris McAvoy" <mc******@hotmail.com> schreef in bericht
news:73**************************@posting.google.c om...

Hi,

Every morning around 11am, we run out of database connections. We
have one user, and a bunch of scripts that use that account to connect
to the database. I believe we have a bad script out there that's
routinely chewing up our available connections. I plan on turning on
as much logging as possible tonight. Are there any other tools out
there that can help me figure out exactly which script is generating
the connections?

Thanks,
Chris

Hello Chris,

I do not know of such tools.
But you can start to do a "find in all files" with a editor that support
this functionality and look for the use of the function mysql_pconnect().
Because if you use persistent connections (mysql_pconnect) it is possible
that some connections are not closed properly and your script is creating
new ones when ever it is run. This will exceed your connections limit
rapidly. Then read the information about persistent connection and determine
if you really need such a connection. Maybe mysql_connect() is all you need
and changing mysql_pconnect to mysql_connect solves your problem.

More information :

http://www.mysql.com/news-and-events...000000086.html

http://nl.php.net/manual/en/features...onnections.php

I hope this helps to solve the problem
Rob

[Rob]

"Chris McAvoy" <mc******@hotmail.com> schreef in bericht
news:73**************************@posting.google.c om...

Hi,

Every morning around 11am, we run out of database connections. We
have one user, and a bunch of scripts that use that account to connect
to the database. I believe we have a bad script out there that's
routinely chewing up our available connections. I plan on turning on
as much logging as possible tonight. Are there any other tools out
there that can help me figure out exactly which script is generating
the connections?

Thanks,
Chris

Hello Chris,

I do not know of such tools.
But you can start to do a "find in all files" with a editor that support
this functionality and look for the use of the function mysql_pconnect().
Because if you use persistent connections (mysql_pconnect) it is possible
that some connections are not closed properly and your script is creating
new ones when ever it is run. This will exceed your connections limit
rapidly. Then read the information about persistent connection and determine
if you really need such a connection. Maybe mysql_connect() is all you need
and changing mysql_pconnect to mysql_connect solves your problem.

More information :

http://www.mysql.com/news-and-events...000000086.html

http://nl.php.net/manual/en/features...onnections.php

I hope this helps to solve the problem
Rob

[Chris McAvoy]

Hi Rob, thanks for your response.

"Rob" <reply_@news_group.please> wrote

I do not know of such tools.
But you can start to do a "find in all files" with a editor that support
this functionality and look for the use of the function mysql_pconnect().
Because if you use persistent connections (mysql_pconnect) it is possible
that some connections are not closed properly and your script is creating
new ones when ever it is run. This will exceed your connections limit
rapidly.

We definitly had some issues with pconnect vs. connect. We removed
all the pconnects we found, and still had the issue. We ended up
moving out of our shared environment to a dedicated host. Since then,
we've continued to have the issue. I turned off persistence in
php.ini, and we still have the problem.

After turning on query logging, I see that the processes that go to
sleep aren't consistent, as in, they're very common queries, and there
doesn't appear to be an obvious pattern to them.

It seems like during times of high stress, either PHP or mysql isn't
releasing open connections efficiently. We don't explicitly close
connections, which _shouldn't_ be a big deal, but it appears that it
may be.

My current thinking is this, we have a lot of scripts, and sometimes
we'll unneccessarily mysql_connect, even though the script is really
only being included in another file that has an existing
mysql_connect. The second, unnecessary, mysql_connect isn't closed.

In the log file, the connections sleep for a while, and then they're
all closed in one big shot.

Our primary application is PHPBB. Before we left the shared host,
they stated that several PHPBB sites were having similar issues.

As a workaround, I've increased max_connections from 100 to 500. I
think we'll be stable for a while, but I'd really like to clean it up
for good.

I know this is a MySQL group, and not a PHP group. If this is an
inappropriate post, let me know, and I'll move along.

Thanks very much,
Chris

[Chris McAvoy]

Hi Rob, thanks for your response.

"Rob" <reply_@news_group.please> wrote

I do not know of such tools.
But you can start to do a "find in all files" with a editor that support
this functionality and look for the use of the function mysql_pconnect().
Because if you use persistent connections (mysql_pconnect) it is possible
that some connections are not closed properly and your script is creating
new ones when ever it is run. This will exceed your connections limit
rapidly.

We definitly had some issues with pconnect vs. connect. We removed
all the pconnects we found, and still had the issue. We ended up
moving out of our shared environment to a dedicated host. Since then,
we've continued to have the issue. I turned off persistence in
php.ini, and we still have the problem.

After turning on query logging, I see that the processes that go to
sleep aren't consistent, as in, they're very common queries, and there
doesn't appear to be an obvious pattern to them.

It seems like during times of high stress, either PHP or mysql isn't
releasing open connections efficiently. We don't explicitly close
connections, which _shouldn't_ be a big deal, but it appears that it
may be.

My current thinking is this, we have a lot of scripts, and sometimes
we'll unneccessarily mysql_connect, even though the script is really
only being included in another file that has an existing
mysql_connect. The second, unnecessary, mysql_connect isn't closed.

In the log file, the connections sleep for a while, and then they're
all closed in one big shot.

Our primary application is PHPBB. Before we left the shared host,
they stated that several PHPBB sites were having similar issues.

As a workaround, I've increased max_connections from 100 to 500. I
think we'll be stable for a while, but I'd really like to clean it up
for good.

I know this is a MySQL group, and not a PHP group. If this is an
inappropriate post, let me know, and I'll move along.

Thanks very much,
Chris

[Chris McAvoy]

Hi Rob, thanks for your response.

"Rob" <reply_@news_group.please> wrote

I do not know of such tools.
But you can start to do a "find in all files" with a editor that support
this functionality and look for the use of the function mysql_pconnect().
Because if you use persistent connections (mysql_pconnect) it is possible
that some connections are not closed properly and your script is creating
new ones when ever it is run. This will exceed your connections limit
rapidly.

We definitly had some issues with pconnect vs. connect. We removed
all the pconnects we found, and still had the issue. We ended up
moving out of our shared environment to a dedicated host. Since then,
we've continued to have the issue. I turned off persistence in
php.ini, and we still have the problem.

After turning on query logging, I see that the processes that go to
sleep aren't consistent, as in, they're very common queries, and there
doesn't appear to be an obvious pattern to them.

It seems like during times of high stress, either PHP or mysql isn't
releasing open connections efficiently. We don't explicitly close
connections, which _shouldn't_ be a big deal, but it appears that it
may be.

My current thinking is this, we have a lot of scripts, and sometimes
we'll unneccessarily mysql_connect, even though the script is really
only being included in another file that has an existing
mysql_connect. The second, unnecessary, mysql_connect isn't closed.

In the log file, the connections sleep for a while, and then they're
all closed in one big shot.

Our primary application is PHPBB. Before we left the shared host,
they stated that several PHPBB sites were having similar issues.

As a workaround, I've increased max_connections from 100 to 500. I
think we'll be stable for a while, but I'd really like to clean it up
for good.

I know this is a MySQL group, and not a PHP group. If this is an
inappropriate post, let me know, and I'll move along.

Thanks very much,
Chris