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