pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerl DBD and an alarming problem
7+ messages / 3 participants
[nested] [flat]
* Perl DBD and an alarming problem
@ 2005-11-16 20:59 Craig A. James <cjames@modgraph-usa.com>
2005-11-16 23:30 ` Re: Perl DBD and an alarming problem Greg Sabino Mullane <greg@turnstep.com>
2005-11-17 00:23 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 7+ messages in thread
From: Craig A. James @ 2005-11-16 20:59 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
I am mystified by the behavior of "alarm" in conjunction with Postgres/perl/DBD. Here is roughly what I'm doing:
eval {
local $SIG{ALRM} = sub {die("Timeout");};
$time = gettimeofday;
alarm 20;
$sth = $dbh->prepare("a query that may take a long time...");
$sth->execute();
alarm 0;
};
if ($@ && $@ =~ /Timeout/) {
my $elapsed = gettimeofday - $time;
print "Timed out after $elapsed seconds";
}
Now the mystery: It works, but it hardly matters what time I use for the alarm call, the actual alarm event always happens at 26 seconds. I can set "alarm 1" or "alarm 20", and it almost always hits right at 26 seconds.
Now if I increase alarm to anything in the range of about 25-60 seconds, the actual alarm arrives somewhere around the 90 second mark. It seems as though there are "windows of opportunity" for the alarm, and it is ignored until those "windows" arrive.
Anyone have a clue what's going on and/or how I can fix it?
A secondary question: It appears that $sth->cancel() is not implemented in the Pg DBD module. Is that true?
Thanks,
Craig
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Perl DBD and an alarming problem
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
@ 2005-11-16 23:30 ` Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 0 replies; 7+ messages in thread
From: Greg Sabino Mullane @ 2005-11-16 23:30 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I am mystified by the behavior of "alarm" in conjunction with
> Postgres/perl/DBD. Here is roughly what I'm doing:
> Anyone have a clue what's going on and/or how I can fix it?
Not really, but alarm has never worked well (or claimed to) with
DBI. The DBI docs recommend trying out Sys::Sigaction:
http://search.cpan.org/~lbaxter/Sys-SigAction/
> A secondary question: It appears that $sth->cancel() is not
> implemented in the Pg DBD module. Is that true?
Correct. DBD::Pg does not support asynchronous connections. It's
possible it may in the future, but there has not been much of a
perceived need. Feel free to enter a request on CPAN:
http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBD-Pg
There may be another way around it, if you can tell us some more
about what exactly it is you are trying to do.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200511161830
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDe8ErvJuQZxSWSsgRAoZ6AJ9h6gV5U7PyLDJIqXLpSB6r7NWaaQCdESSR
CdNexfvYvSQjOLkEdPXd26U=
=/W5F
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Perl DBD and an alarming problem
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
@ 2005-11-17 00:23 ` Michael Fuhr <mike@fuhr.org>
2005-11-17 21:04 ` Re: Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-11-17 00:23 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
On Wed, Nov 16, 2005 at 12:59:21PM -0800, Craig A. James wrote:
> eval {
> local $SIG{ALRM} = sub {die("Timeout");};
> $time = gettimeofday;
> alarm 20;
> $sth = $dbh->prepare("a query that may take a long time...");
> $sth->execute();
> alarm 0;
> };
> if ($@ && $@ =~ /Timeout/) {
> my $elapsed = gettimeofday - $time;
> print "Timed out after $elapsed seconds";
> }
>
> Now the mystery: It works, but it hardly matters what time I use for the
> alarm call, the actual alarm event always happens at 26 seconds. I can set
> "alarm 1" or "alarm 20", and it almost always hits right at 26 seconds.
High-level languages' signal handlers don't always work well with
low-level libraries. I haven't dug into the Perl source code but
I'd guess that since only certain things are safe to do in a signal
handler, Perl's handler simply sets some kind of state that the
interpreter will examine later during normal execution. If you're
using only Perl facilities then that probably happens fairly timely,
but if you're stuck in a low-level library (e.g., libpq) then you
might have to wait until that library returns control to Perl before
Perl recognizes that a signal occurred.
As an example, if I run code such as yours with alarm(2) and a query
that takes 5 seconds, I see the following in a process trace (from
ktrace/kdump on FreeBSD):
55395 perl 0.000978 CALL poll(0xbfbfe1b8,0x1,0xffffffff)
55395 perl 1.996629 RET poll -1 errno 4 Interrupted system call
55395 perl 0.000013 PSIG SIGALRM caught handler=0x281be22c mask=0x0 code=0x0
55395 perl 0.000050 CALL sigprocmask(0x1,0,0x805411c)
55395 perl 0.000005 RET sigprocmask 0
55395 perl 0.000020 CALL sigreturn(0xbfbfde60)
55395 perl 0.000007 RET sigreturn JUSTRETURN
55395 perl 0.000019 CALL poll(0xbfbfe1b8,0x1,0xffffffff)
55395 perl 3.004065 RET poll 1
55395 perl 0.000024 CALL recvfrom(0x3,0x81c6000,0x4000,0,0,0)
55395 perl 0.000016 GIO fd 3 read 60 bytes
The poll() call is interrupted by SIGALRM after 2 seconds but then
it starts again and doesn't return until the query completes after
the remaining 3 seconds. Only sometime later does Perl invoke the
ALRM handler I installed, presumably because it can't do so until
the low-level code returns control to Perl.
Is there a reason you're using alarm() in the client instead of
setting statement_timeout on the server?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Perl DBD and an alarming problem
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-17 00:23 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
@ 2005-11-17 21:04 ` Craig A. James <cjames@modgraph-usa.com>
2005-11-17 23:28 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Craig A. James @ 2005-11-17 21:04 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance
Thanks for the info on alarm and timeouts, this was a big help. One further comment:
Michael Fuhr wrote:
>> eval {
>> local $SIG{ALRM} = sub {die("Timeout");};
>> $time = gettimeofday;
>> alarm 20;
>> $sth = $dbh->prepare("a query that may take a long time...");
>> $sth->execute();
>> alarm 0;
>> };
>> if ($@ && $@ =~ /Timeout/) {
>> my $elapsed = gettimeofday - $time;
>> print "Timed out after $elapsed seconds";
>> }
>>
>>Now the mystery: It works, but it hardly matters what time I use for the
>>alarm call, the actual alarm event always happens at 26 seconds...
>
>
> High-level languages' signal handlers don't always work well with
> low-level libraries...
>
> Is there a reason you're using alarm() in the client instead of
> setting statement_timeout on the server?
statement_timeout solved the problem, thanks VERY much for the pointer. To answer your question, I use alarm() because all the books and web references said that was how to do it. :-). I've used alarm() with Perl (with a 3rd-party C lib that had a potential infinite loop) very successfully.
So thanks for the pointer to statement_timeout. But...
When I set statement_timeout in the config file, it just didn't do anything - it never timed out (PG 8.0.3). I finally found in the documentation that I can do "set statement_timeout = xxx" from PerlDBI on a per-client basis, and that works.
Craig
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Perl DBD and an alarming problem
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-17 00:23 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
2005-11-17 21:04 ` Re: Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
@ 2005-11-17 23:28 ` Michael Fuhr <mike@fuhr.org>
2005-11-18 03:46 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-11-17 23:28 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
On Thu, Nov 17, 2005 at 01:04:21PM -0800, Craig A. James wrote:
> When I set statement_timeout in the config file, it just didn't do anything
> - it never timed out (PG 8.0.3). I finally found in the documentation that
> I can do "set statement_timeout = xxx" from PerlDBI on a per-client basis,
> and that works.
You probably shouldn't set statement_timeout on a global basis
anyway, but did you reload the server after you made the change?
Setting statement_timeout in postgresql.conf and then reloading the
server works here in 8.0.4.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Perl DBD and an alarming problem
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-17 00:23 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
2005-11-17 21:04 ` Re: Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-17 23:28 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
@ 2005-11-18 03:46 ` Michael Fuhr <mike@fuhr.org>
2005-11-19 20:42 ` Re: Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-11-18 03:46 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
[Please copy the mailing list on replies.]
On Thu, Nov 17, 2005 at 05:38:13PM -0800, Craig A. James wrote:
> >You probably shouldn't set statement_timeout on a global basis
> >anyway
>
> The server is a "one trick pony" so setting a global timeout value is
> actually appropriate.
Beware that statement_timeout also applies to maintenance commands
like VACUUM; it might be more appropriate to set per-user timeouts
with ALTER USER. If you do set a global timeout then you might
want to set a per-user timeout of 0 for database superusers so
maintenance activities don't get timed out.
> >... but did you reload the server after you made the change?
> >Setting statement_timeout in postgresql.conf and then reloading the
> >server works here in 8.0.4.
>
> Yes. By "reload" I assume you mean restarting it from scratch.
Either a restart or a "pg_ctl reload", which sends a SIGHUP to the
server. You can effect some changes by sending a signal to a running
server without having to restart it entirely.
> In this case, I use
>
> /etc/init.d/postgresql restart
>
> It definitely had no effect at all. I tried values clear down to 1
> millisecond, but the server never timed out for any query.
Did you use "SHOW statement_timeout" to see if the value was set
to what you wanted? Are you sure you edited the right file? As a
database superuser execute "SHOW config_file" to see what file the
server is using. What exactly did the line look like after you
changed it?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Perl DBD and an alarming problem
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-17 00:23 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
2005-11-17 21:04 ` Re: Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-17 23:28 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
2005-11-18 03:46 ` Re: Perl DBD and an alarming problem Michael Fuhr <mike@fuhr.org>
@ 2005-11-19 20:42 ` Craig A. James <cjames@modgraph-usa.com>
0 siblings, 0 replies; 7+ messages in thread
From: Craig A. James @ 2005-11-19 20:42 UTC (permalink / raw)
To: pgsql-performance
>> When I set statement_timeout in the config file, it just didn't
>> do anything - it never timed out (PG 8.0.3).
>
>... but did you reload the server after you [changed statement_timeout]?
Mystery solved. I have two servers; I was reconfiguring one and restarting the other. Duh.
Thanks,
Craig
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-11-19 20:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-16 20:59 Perl DBD and an alarming problem Craig A. James <cjames@modgraph-usa.com>
2005-11-16 23:30 ` Greg Sabino Mullane <greg@turnstep.com>
2005-11-17 00:23 ` Michael Fuhr <mike@fuhr.org>
2005-11-17 21:04 ` Craig A. James <cjames@modgraph-usa.com>
2005-11-17 23:28 ` Michael Fuhr <mike@fuhr.org>
2005-11-18 03:46 ` Michael Fuhr <mike@fuhr.org>
2005-11-19 20:42 ` Craig A. James <cjames@modgraph-usa.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox