Re: Problem with Mysql 4.0.18 + Debian
That sounds like a typical mod_perl-problem. The script is making new
connections and doesn't close the old ones.
You should add debug-code to your script and add
* * * * * root mysql -e 'show processlist'
/tmp/mysql_processlist_debug_`date +%s`.txt
to your /etc/crontab in order to log the processlist once every minute
in a txt-file in /tmp
Jan
[EMAIL PROTECTED] wrote:
Thanks for the two responses.
William Mussatto said:
Are you running mod_perl?
Yes I am, with Perl 5.8.3.
Victor Pendleton said:
What does mysql show processlist look like?
Here is what it looks like currently, but the system is not in its
unresponsive phase right now. I can't force it to go all wonky on me,
it will probably be tomorrow before the process count explodes again.
++-+---+-+-+--+---+--+
| Id | User| Host | db | Command | Time | State | Info
|
++-+---+-+-+--+---+--+
| 8 | citidel | localhost | citidel | Sleep | 0| | NULL
|
| 71 | citidel | localhost | citidel | Sleep | 2192 | | NULL
|
| 72 | citidel | localhost | citidel | Sleep | 2141 | | NULL
|
| 78 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 79 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 87 | citidel | localhost | citidel | Sleep | 741 | | NULL
|
| 88 | citidel | localhost | citidel | Sleep | 730 | | NULL
|
| 89 | citidel | localhost | citidel | Sleep | 607 | | NULL
|
| 95 | citidel | localhost | citidel | Query | 0| NULL | show
processlist |
++-+---+-+-+--+---+-
Ryan Richardson said:
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18. I am running Apache 1.3.29 + perl and using mysql as the
backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens.
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mysql 4.0.18 + Debian
Thanks for the two responses.
William Mussatto said:
Are you running mod_perl?
Yes I am, with Perl 5.8.3.
Victor Pendleton said:
What does mysql show processlist look like?
Here is what it looks like currently, but the system is not in its
unresponsive phase right now. I can't force it to go all wonky on me,
it will probably be tomorrow before the process count explodes again.
++-+---+-+-+--+---+--+
| Id | User| Host | db | Command | Time | State | Info
|
++-+---+-+-+--+---+--+
| 8 | citidel | localhost | citidel | Sleep | 0| | NULL
|
| 71 | citidel | localhost | citidel | Sleep | 2192 | | NULL
|
| 72 | citidel | localhost | citidel | Sleep | 2141 | | NULL
|
| 78 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 79 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 87 | citidel | localhost | citidel | Sleep | 741 | | NULL
|
| 88 | citidel | localhost | citidel | Sleep | 730 | | NULL
|
| 89 | citidel | localhost | citidel | Sleep | 607 | | NULL
|
| 95 | citidel | localhost | citidel | Query | 0| NULL | show
processlist |
++-+---+-+-+--+---+-
Ryan Richardson said:
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files),
Mysql 4.0.18. I am running Apache 1.3.29 + perl and using mysql as
the backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm
line 25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable,
so the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem
is not that it is being overloaded. I have tried this scenario at
least a dozen times, and the same thing always happens.
Ryan
While I am not a mod_perl expert it does create a connection for each
child. I'm not clear on the pooling of these connections but you might
check the number of apache children next time it goes un-responsive. Do
each of your perl scripts include an explicit ' $dbh-disconnect;'? That
may be needed to return things to the pool. You might also make sure that
the $dbh is 'my'ed and that its scope is less than the entire program.
For example putting it inside of set of { } and ensuring that the program
flow falls out the bottom so the $dbh goes out of scope. I run in a
multi-virtual host environment so mod_perl is conter productive for us so
my experience is limited. I'd look at mod_perl and $dbh connection
pooling on google. I'd be interested in what you find. We have a php
mail system and I've had to increase the number of connections because of
its connection pooling.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mysql 4.0.18 + Debian
Hi,
A quick fix would be to set the wait_timeout variable in the my.cnf to a
much smaller value than 28800 (default value).
Try to add wait_timeout=60 in the my.cnf for example, the connections should
be automatically closed after 60 secondes if there are not used anymore.
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Jan Kirchhoff [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 7:43 PM
Subject: Re: Problem with Mysql 4.0.18 + Debian
That sounds like a typical mod_perl-problem. The script is making new
connections and doesn't close the old ones.
You should add debug-code to your script and add
* * * * * root mysql -e 'show processlist'
/tmp/mysql_processlist_debug_`date +%s`.txt
to your /etc/crontab in order to log the processlist once every minute
in a txt-file in /tmp
Jan
[EMAIL PROTECTED] wrote:
Thanks for the two responses.
William Mussatto said:
Are you running mod_perl?
Yes I am, with Perl 5.8.3.
Victor Pendleton said:
What does mysql show processlist look like?
Here is what it looks like currently, but the system is not in its
unresponsive phase right now. I can't force it to go all wonky on me,
it will probably be tomorrow before the process count explodes again.
++-+---+-+-+--+---+
--+
| Id | User| Host | db | Command | Time | State | Info
|
++-+---+-+-+--+---+
--+
| 8 | citidel | localhost | citidel | Sleep | 0| | NULL
|
| 71 | citidel | localhost | citidel | Sleep | 2192 | | NULL
|
| 72 | citidel | localhost | citidel | Sleep | 2141 | | NULL
|
| 78 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 79 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 87 | citidel | localhost | citidel | Sleep | 741 | | NULL
|
| 88 | citidel | localhost | citidel | Sleep | 730 | | NULL
|
| 89 | citidel | localhost | citidel | Sleep | 607 | | NULL
|
| 95 | citidel | localhost | citidel | Query | 0| NULL | show
processlist |
++-+---+-+-+--+---+
-
Ryan Richardson said:
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18. I am running Apache 1.3.29 + perl and using mysql as the
backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens.
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mysql 4.0.18 + Debian
Jocelyn Fournier wrote: Hi, A quick fix would be to set the wait_timeout variable in the my.cnf to a much smaller value than 28800 (default value). Try to add wait_timeout=60 in the my.cnf for example, the connections should be automatically closed after 60 secondes if there are not used anymore. But as you wrote its only a quick fix and when the load on the server increases the problem will show up again... sooner or later. Better find the bug in the perl-script, it's probably just a missing $dbh-disconnect (or it's in the wrong place) as somebody else here suggested already. we did a lot of bigger web-projects with heavy load and load-balancing on linux/apache/mysql/mod_perl - environments and it works perfectly, and all problems I've had before that were like Ryan's were just because of mistakes in my database-connection/disconnection functions. Try google, there are lots of webpages dealing with the traps of mod_perl and what one has to watch out for! Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with Mysql 4.0.18 + Debian
What does mysql show processlist look like?
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18. I am running Apache 1.3.29 + perl and using mysql as the
backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens.
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with Mysql 4.0.18 + Debian
Victor Pendleton said:
What does mysql show processlist look like?
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18. I am running Apache 1.3.29 + perl and using mysql as the
backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens.
Ryan
Are you running mod_perl?
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mysql 4.0.18 + Debian
Ryan,
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm
line 25
Are you running Perl from CGI or mod_perl? If you run it from mod_perl,
maybe you have problem with persistent connection. Maybe you forgot to
disconnect DB handler.
--Batara
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
