You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Mike Blazer <bl...@peterlink.ru> on 2004/05/27 21:20:45 UTC
Apache/mod_perl/mysql - threads at startup
Hello, guys!
I use this configuration for quite a while and under a hard traffic:
Apache 1.3.29
mod_perl 1.29
mysql 4.0.3 (a rather old one)
for sometime I have this minor problem - each time I restart servers
there are few failures right after the restart. It's always "Lost
connection" error. As it's a hits counter I can live with it. After the
startup this problem never appears again.
But today I finally decided to explore what happens. Here is from my log
right immediatelly after restart:
(nnnnn) is child's pid, thread is $thread_id = $dbh->{mysql_thread_id}
[Thu May 27 08:10:33 2004] [error] reinit (11154) thread:133
[Thu May 27 08:10:33 2004] [error] reinit (11155) thread:133
[Thu May 27 08:10:33 2004] [error] reinit (11162) thread:133
[Thu May 27 08:10:33 2004] [error] reinit (11156) thread:134
[Thu May 27 08:10:33 2004] [error] reinit (11166) thread:136
[Thu May 27 08:10:33 2004] [error] ListTables: Lost connection to MySQL
server d
uring query
at /host/counter/lib/Counter/ShowCounter.pm line 204
[Thu May 27 08:10:33 2004] [error] reinit (11168) thread:137
[Thu May 27 08:10:33 2004] [error] reinit (11167) thread:142
[Thu May 27 08:10:33 2004] [error] reinit (11159) thread:135
[Thu May 27 08:10:33 2004] [error] reinit (11158) thread:139
[Thu May 27 08:10:33 2004] [error] reinit (11157) thread:140
[Thu May 27 08:10:33 2004] [error] reinit (11160) thread:144
[Thu May 27 08:10:33 2004] [error] reinit (11165) thread:146
[Thu May 27 08:10:33 2004] [error] SQL execute error: SELECT VERSION()
Lost connection to MySQL server during query
at /host/counter/lib/SQL/BuilderExt.pm line 147
[Thu May 27 08:10:33 2004] [error] server_version: could not determine
version
at /host/counter/lib/SQL/BuilderExt.pm line 147
See, in the very beginning - 3 child processes started and connected to
the same mysql thread_id
None of my processes ever disconnects, they all use Apache:DBI and
everything is checked to be correct.
Seems like it's mysql error in dispatching threads?
I scanned mysql changelog - nothing that would look like this was fixed
up to the current version.
I tried all kinds of adjusting from
StartServers => 15,
MinSpareServers => 5,
MaxSpareServers => 50,
to
StartServers => 0,
MinSpareServers => 0,
MaxSpareServers => 50,
But at least 2 childs always connect to the same thread.
Any ideas?
Thanks!
--
==================================
Mike Blazer
blazer@peterlink.ru
==================================
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache/mod_perl/mysql - threads at startup
Posted by Mike Blazer <bl...@peterlink.ru>.
Perrin Harkins wrote:
> You could still be foiling it
> by stashing database handles in globals or closures though.
Thanks a lot, Perrin, in fact this helped a lot. You convinced me that
it's my error :)
The idea that one db handle is being inherited by the forked childs
helped. I finally found one place where I forgot to add connect_method
=> "connect" to override Apache::DBI back to DBI. Everything is fine,
thanks!
> > # here's a workaround to override DBI methods with Apache::DBI ones
> > {
> > package DBI;
> > $connect_via = "Apache::DBI::connect";
> > }
>
> Ack! Don't do that! Just load Apache::DBI at the top of your
> httpd.conf like it says to in the docs.
Nope. Look inside DBI.pm - this is a fair hack.
> > $Counter::db and $Counter::db->reconnect;
> >
> > unless ($Counter::db) {
> > $Counter::db ||= MySQL::Tools->dbopen( @Counter::INI::DBopendata )
> > or croak MySQL::Tools->errstr;
> > }
>
> That looks like a problem to me. Don't put database handles in
> globals. You will regret it. If any code like this gets run outside of
> a sub in a module that is use()ed during startup, you would get sharing
> of handles between processes.
No, it works perfect, just you should be sure you initialise handles
after childs are forked - i.e. in sub handler - not in BEGIN or any
early stage.
These globals let me have just one DB handle per child, instead of 7
handles! :) Having 10-15 childs active this saves a lot of mysql
threads.
Actually even if you have few PerlHandler modules with their sub
handler's - each time the child executes just one of them and so it's
safe to reuse the same connection.
Thanks! It's solved!
--
==================================
Mike Blazer
blazer@peterlink.ru
==================================
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache/mod_perl/mysql - threads at startup
Posted by Perrin Harkins <pe...@elem.com>.
On Thu, 2004-05-27 at 21:34, Mike Blazer wrote:
> It's hard to extract the part of the code because my configs look wild
> :)
Holy cow, this is a lot of code. The simplest thing I can think of to
see what's going on is to turn on Apache::DBI debugging and then watch
your error_log. If you see a bunch of stuff about "skipping connection
cache during server startup" that's good. You could still be foiling it
by stashing database handles in globals or closures though.
> # here's a workaround to override DBI methods with Apache::DBI ones
> {
> package DBI;
> $connect_via = "Apache::DBI::connect";
> }
Ack! Don't do that! Just load Apache::DBI at the top of your
httpd.conf like it says to in the docs.
> because of the 2nd pass. Dunno, may be there is some problem with this
> 2nd pass...
It's the standard "server starts twice on startup" issue. It's in the
docs.
> OK, and after that the whole stuff starts, few different PerlHandler
> modules that use the same dbh like this
>
> sub handler {
> ...
>
> $Counter::db and $Counter::db->reconnect;
>
> unless ($Counter::db) {
> $Counter::db ||= MySQL::Tools->dbopen( @Counter::INI::DBopendata )
> or croak MySQL::Tools->errstr;
> }
That looks like a problem to me. Don't put database handles in
globals. You will regret it. If any code like this gets run outside of
a sub in a module that is use()ed during startup, you would get sharing
of handles between processes.
> MySQL::Tools->dbopen returns $db and $db->dbh is DBI::db object (or
> Apache::DBI::db in case it overrides DBI)
>
> dbopen() finishes with
>
> $self->reconnect or return;
>
> $self;
> }
>
> and reconnect() actually opens the connection:
>
> sub reconnect {
> my $self = shift;
> my $old_dbh = $self->{dbh} ? "$self->{dbh}" : "";
Okay, so it looks like you are stashing database handles in this object
too, which could also be the problem if this object ever gets put into a
global or a closure.
I'm not sure why you need all this code just to connect to the
database. Usually you only need a few lines to deal with the config and
the rest is left to Apache::DBI.
> $self->{"dbh"} and $self->{"dbh"}->disconnect;
> in 'reconnect' sub makes nothing in case of Apache::DBI and is supposed
> to do reconnection if called in non-mod_perl environment. It's actually
> added for some persistency, to mean 'reconnect' even not under mod_perl
I'm not following this, but if you want database persistence outside of
mod_perl, use DBI->connect_cached(). It's safe under mod_perl too,
since it will just delegate to Apache::DBI if Apache::DBI is loaded.
> END { eval{ $Counter::db->dbclose } if $Counter::db }
Those don't run until the server shuts down.
> To remind the problem - few first Apache childs manage to connect the
> same mysql thread at startup. Then they all fail, reconnect and live
> happily ever after :)
Actually, I would say that the apache parent connects to MySQL, you
store that connection somewhere, and then apache forks and multiple
children end up with that same connection.
- Perrin
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache/mod_perl/mysql - threads at startup
Posted by Mike Blazer <bl...@peterlink.ru>.
Thanks, Perrin, I'm sure you're right just don't see where this happens.
It's hard to extract the part of the code because my configs look wild
:)
One sh script starts/stops/restarts 3 servers with 1 config for proxy
and 1 config full of <Perl> sections - for two back-end servers.
Yes, I do call mysql from <Perl> section of conf-file.
One "thread" of this execution looks like
require "$counter_root/startup-image.pl";
inside this file:
@Counter::INI::DBopendata = (
base => 'ranks',
host => "localhost",
port => 3306,
"username" => "....",
"password" => "....",
);
...
# this is because I use the same startup-image.pl for standalone scripts
# with $no_GATEWAY_INTERFACE = 1;
require DBI;
unless ($no_GATEWAY_INTERFACE) {
# Apache::DBI should be before DBI but after Apache::Status
require Apache;
require Apache::Status;
require Apache::DBI;
# here's a workaround to override DBI methods with Apache::DBI ones
{
package DBI;
$connect_via = "Apache::DBI::connect";
}
.... lots more
}
then back to conf:
my $db = MySQL::Tools->dbopen(@Counter::INI::DBopendata,
connect_method => "connect") or die $DBI::errstr;
print "image startup thread:".$db->dbh->{mysql_thread_id}."
".scalar($db->dbh)."\n";
# this is just an object-oriented interface to SQL language
my $res = $db->select(
Tables => Counter::Folders->table_name,
Columns => "ICON",
DISTINCT => 1,
SQL_CACHE => 1,
) or die "reading counter images: ".MySQL::Tools->errstr;
ref($res) && @$res or die "reading counter images: no images found\n";
...stuff...
$db->dbclose;
undef $db;
I'm calling this with connect_method => "connect" and really, I see this
result of the upper print:
image startup thread:717 DBI::db=HASH(0x8746500)
- means I really use DBI and dbclose is just a simple $dbh->disconnect
in this case. And I really don't see thread 717 any more
Actually there should've been a 2nd line (like I saw while printed to
file, I won't change thread id's here)
image startup thread:718 DBI::db=HASH(0x8746500)
because of the 2nd pass. Dunno, may be there is some problem with this
2nd pass... I don't actually know why it happens. But it's still DBI and
disconnects as DBI::db handler should disconnect.
OK, and after that the whole stuff starts, few different PerlHandler
modules that use the same dbh like this
sub handler {
...
$Counter::db and $Counter::db->reconnect;
unless ($Counter::db) {
$Counter::db ||= MySQL::Tools->dbopen( @Counter::INI::DBopendata )
or croak MySQL::Tools->errstr;
}
MySQL::Tools->dbopen returns $db and $db->dbh is DBI::db object (or
Apache::DBI::db in case it overrides DBI)
dbopen() finishes with
$self->reconnect or return;
$self;
}
and reconnect() actually opens the connection:
sub reconnect {
my $self = shift;
my $old_dbh = $self->{dbh} ? "$self->{dbh}" : "";
my $old_thr = $self->{dbh} ? ($self->{dbh}->{mysql_thread_id} || 0) :
0;
$self->{"dbh"} and $self->{"dbh"}->disconnect;
$self->{"dbh"} = DBI->connect(
sprintf("dbi:%s:database=%s;host=%s;port=%s",
$self->{"driver"}, $self->{"name"}, $self->{"host"}, $self->{"port"}),
$self->{"username"}, $self->{"password"}, {
AutoCommit => 1, # 0 makes error in mysql
PrintError => 0,
RaiseError => 0,
($self->{"connect_method"} ? (
dbi_connect_method => $self->{"connect_method"}
) : ()),
}) or return;
my $new_thr = $self->{dbh} ? ($self->{dbh}->{mysql_thread_id} || 0) :
0;
!$old_dbh || $old_dbh eq "$self->{dbh}" or
warn "reconnect: reconnected successfully\n";
if (Apache->request && $old_thr != $new_thr) {
warn "reinit ($$) thread new: $new_thr old: $old_thr
$self->{dbh}\n";
}
... reading tables with column properties ...
$self;
}
I've just made more clear test print and the last 'warn' prints much
like I sent before but now it shows how wrong mysql thread (719 - same
for 5 server childs) is being replaced with the new threads:
old: 0 means first connection for this child, no previous thread
[Thu May 27 16:00:23 2004] [error] reinit (13339) thread new: 719 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:23 2004] [error] reinit (13341) thread new: 719 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:23 2004] [error] reinit (13340) thread new: 719 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:23 2004] [error] reinit (13342) thread new: 719 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:23 2004] [error] reinit (13343) thread new: 719 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:23 2004] [error] SQL execute error: LISTFIELDS
counter_sitegroups
Lost connection to MySQL server during query
at /host/counter/lib/Counter/Common.pm line 502
[Thu May 27 16:00:23 2004] [error] TablesProperties: Lost connection to
MySQL server during query
at /host/counter/lib/Counter/Common.pm line 502
[Thu May 27 16:00:23 2004] [error] reinit (13344) thread new: 719 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13346) thread new: 723 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13348) thread new: 725 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13351) thread new: 728 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] ListTables: Lost connection to MySQL
server during query
at /host/counter/lib/Counter/ShowCounter.pm line 204
[Thu May 27 16:00:24 2004] [error] reinit (13353) thread new: 730 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13345) thread new: 722 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13347) thread new: 724 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13349) thread new: 726 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13350) thread new: 727 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13341) thread new: 731 old:
719 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13352) thread new: 729 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13339) thread new: 732 old:
719 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] ListTables: Lost connection to MySQL
server during query
at /host/counter/lib/Counter/ShowCounter.pm line 204
[Thu May 27 16:00:24 2004] [error] reinit (13342) thread new: 733 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13340) thread new: 734 old:
0 Apache::DBI::db=HASH(0x8389fa4)
[Thu May 27 16:00:24 2004] [error] reinit (13388) thread new: 735 old:
0 Apache::DBI::db=HASH(0x8389fa4)
Sorry, I tried to simplify this but as usual with OO - things are hidden
inside the other things ad infinitum.
$self->{"dbh"} and $self->{"dbh"}->disconnect;
in 'reconnect' sub makes nothing in case of Apache::DBI and is supposed
to do reconnection if called in non-mod_perl environment. It's actually
added for some persistency, to mean 'reconnect' even not under mod_perl
DBI->connect(...) each time the PerlHandler runs is supposed to ping
(Apache::DBI::connect) and keep the previous handler if it's alive
otherwise - create a new connection
dbclose looks like
sub dbclose {
my ($self) = @_;
my $rc = $self->{"dbh"}->disconnect;
undef $_[0];
$rc;
}
and destroys MySQL::Tools object. It calls the appropriate 'disconnect'
method for dbh.
I call it only in configs (no Apache::DBI). In PerlHandler modules I
have only
END { eval{ $Counter::db->dbclose } if $Counter::db }
Guys, I'm sorry - lots of stuff here and not an easy reading.
To remind the problem - few first Apache childs manage to connect the
same mysql thread at startup. Then they all fail, reconnect and live
happily ever after :)
Thanks for any input here!
Mike Blazer
Perrin Harkins wrote:
>
> On Thu, 2004-05-27 at 15:20, Mike Blazer wrote:
> > See, in the very beginning - 3 child processes started and connected to
> > the same mysql thread_id
> >
> > None of my processes ever disconnects, they all use Apache:DBI and
> > everything is checked to be correct.
> > Seems like it's mysql error in dispatching threads?
>
> Usually it's something opening a database connection before apache
> forks. How about showing us the parts of your startup code that deal
> with database connections?
>
> - Perrin
--
==================================
Mike Blazer
blazer@peterlink.ru
==================================
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache/mod_perl/mysql - threads at startup
Posted by Perrin Harkins <pe...@elem.com>.
On Thu, 2004-05-27 at 15:20, Mike Blazer wrote:
> See, in the very beginning - 3 child processes started and connected to
> the same mysql thread_id
>
> None of my processes ever disconnects, they all use Apache:DBI and
> everything is checked to be correct.
> Seems like it's mysql error in dispatching threads?
Usually it's something opening a database connection before apache
forks. How about showing us the parts of your startup code that deal
with database connections?
- Perrin
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html