pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPreventing or controlling runaway queries
5+ messages / 2 participants
[nested] [flat]
* Preventing or controlling runaway queries
@ 2005-12-05 19:02 Eric E <whalesuit@gmail.com>
2005-12-05 19:35 ` Re: Preventing or controlling runaway queries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Eric E @ 2005-12-05 19:02 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi all,
I've recently had a couple worrisome experiences with runaway
queries in postgres 7.4 and 8.0, and I'm wondering what I can do to
control or prevent these. When I ran a query against a large table, I
accidentally omitted mistyped a table name, and a table was
automatically added, resulting in a massive result set that crash my
postmaster instance. I know that the automatic addition of missing
tables to queries can be disabled, so I'm not so worried about that.
However, I am worried about generic queries being able to crash the
entire server instance, which in my case occurs because the query
process keeps allocating more memory until the machine is out of memory,
then Linux' memory manager kills the process (I learned this latter part
thanks to the list archives). Is there some way to limit the amount of
memory a single query process can use? If it reaches that limit, what
happens to the query? What I'm looking for is a way to have the query
be ended or suspended without the OS (Linux) killing the server
process). Does anyone had suggestions on how to accomplish this?
Thanks,
Eric
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Preventing or controlling runaway queries
2005-12-05 19:02 Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
@ 2005-12-05 19:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 20:12 ` Re: Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-12-05 19:35 UTC (permalink / raw)
To: Eric E <whalesuit@gmail.com>; +Cc: pgsql-general@postgresql.org
Eric E <whalesuit@gmail.com> writes:
> I've recently had a couple worrisome experiences with runaway
> queries in postgres 7.4 and 8.0, and I'm wondering what I can do to
> control or prevent these. When I ran a query against a large table, I
> accidentally omitted mistyped a table name, and a table was
> automatically added, resulting in a massive result set that crash my
> postmaster instance.
If it crashed your backend (as opposed to your client), we'd like to
see more details.
> Is there some way to limit the amount of
> memory a single query process can use?
See ulimit.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Preventing or controlling runaway queries
2005-12-05 19:02 Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
2005-12-05 19:35 ` Re: Preventing or controlling runaway queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-05 20:12 ` Eric E <whalesuit@gmail.com>
2005-12-05 20:57 ` Re: Preventing or controlling runaway queries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Eric E @ 2005-12-05 20:12 UTC (permalink / raw)
To: ; +Cc: pgsql-general@postgresql.org
Hi Tom,
It was a backend crash on my production 7.4 instance, running on Suse
Linux 9.1 Pro, installed from Suse's precompiled binaries, on what turns
out to be questionable hardware.
I wrote a PL/PERL function specifically to crash the database in this
way, and ran it against a test instance on Suse Linux 9.2 Pro running
under Xen, on 8.0.2 from the precompiled binaries supplied by Suse.
Here's the test function:
CREATE OR REPLACE FUNCTION internal.test_crash()
RETURNS SETOF "varchar" AS
$BODY$# This function is intended to crash the test server. DO NOT USE
in production
my $current_output;
my $retval = [];
my $data_handle = spi_exec_query('SELECT * FROM
schema1."table_of_approximately 30000 rows";');
my $nrows = $data_handle->{processed};
foreach my $tt (0 .. 10000) {
foreach my $rn (0 .. $nrows - 1) { # Loop over rows of data
my $row = $data_handle->{rows}[$rn]; # Fetch a row from the view
$current_output->{"storageRowNumber"} = $row ->{"storageRowNumber"};
my %output_copy = %{$current_output};
push @$retval, \%output_copy;
}
}
return $retval;$BODY$
LANGUAGE 'plperl' VOLATILE;
ALTER FUNCTION internal.test_crash() OWNER TO testdbowner;
Before running the above function I logged into from my workstation
using pgAdmin (the on that ships w/ v8.1) and ran a simple SELECT query,
which worked fine. I then ran the function from inside a PSQL shell on
the server itself. Watching top, the postmaster process associated with
that query just grew until it exhausted the available memory (a couple
hundred megabytes) and was killed:
Dec 5 14:54:33 dbtestserver kernel: Out of Memory: Killed process 3294
(postmaster).
The rest of the /var/log/messages output is below.
Here's the process tree after the process was killed:
2379 ? S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
3298 ? S 0:00 \_ postgres: writer process
3299 ? S 0:00 \_ postgres: stats buffer process
3300 ? S 0:00 \_ postgres: stats collector process
In pgAdmin I then attempted to rerun my previous simple SELECT query and
got the error message:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Which makes me believe that all clients would be disconnected (which did
occur when I accidentally ran the large query on my production server).
Now when I run a query with a large result set from pgAdmin3:
SELECT * FROM internal.audit_log INNER JOIN audit_tables.cases
ON cases.audit_log_id = audit_log."auditID", parts
ORDER BY audit_log.ts DESC
LIMIT 200;
(audit_log has about 500K rows, cases about 1000 and parts about 10000),
top shows memory uses jumping around at about 297MB, and PSQL from an
SSH connection to the server remains responsive. It's quite possible
that that other large query crash was due to bad memory/MOBO on the
other server. Before this query completes, pgAdmin3 returns:
ERROR: could not write block 122159 of temporary file: No space left
on device
HINT: Perhaps out of disk space?
Which seems correct, as the query is exhausting the available disk
space. So I guess the problem is limited to PL/PERL functions, and the
other case occurred due to bad hardware. Thanks for pointing me in the
right direction.
Cheers,
Eric
Tom Lane wrote:
>Eric E <whalesuit@gmail.com> writes:
>
>
>> I've recently had a couple worrisome experiences with runaway
>>queries in postgres 7.4 and 8.0, and I'm wondering what I can do to
>>control or prevent these. When I ran a query against a large table, I
>>accidentally omitted mistyped a table name, and a table was
>>automatically added, resulting in a massive result set that crash my
>>postmaster instance.
>>
>>
>
>If it crashed your backend (as opposed to your client), we'd like to
>see more details.
>
>
>
>>Is there some way to limit the amount of
>>memory a single query process can use?
>>
>>
>
>See ulimit.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Preventing or controlling runaway queries
2005-12-05 19:02 Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
2005-12-05 19:35 ` Re: Preventing or controlling runaway queries Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 20:12 ` Re: Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
@ 2005-12-05 20:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 21:33 ` Re: Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-12-05 20:57 UTC (permalink / raw)
To: Eric E <whalesuit@gmail.com>; +Cc: pgsql-general@postgresql.org
Eric E <whalesuit@gmail.com> writes:
> Here's the test function:
> ...
> my $data_handle = spi_exec_query('SELECT * FROM
> schema1."table_of_approximately 30000 rows";');
Well, the plperl documentation does point out that spi_exec_query should
only be used when you know that the result set will be relatively
small. I think the alternative (spi_query/spi_fetchrow) is new in 8.1,
but you might be able to use a cursor with FETCH instead in older
versions.
> Dec 5 14:54:33 dbtestserver kernel: Out of Memory: Killed process 3294
> (postmaster).
This, however, is just plain administrator error. You should have the
OOM killer disabled completely (memory overcommit off) on any server
machine, or indeed any machine at all that runs processes you would not
like to see killed at random. I believe PG could recover from this,
if the kernel gave it a polite "out of memory" error and not a kill -9.
But the truly nasty part of the OOM killer is that it may kill a totally
innocent process.
Linux's memory-overcommit behavior was invented by someone accustomed to
Windows standards of reliability. Personally I don't think there is any
good reason to have it enabled ever.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Preventing or controlling runaway queries
2005-12-05 19:02 Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
2005-12-05 19:35 ` Re: Preventing or controlling runaway queries Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 20:12 ` Re: Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
2005-12-05 20:57 ` Re: Preventing or controlling runaway queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-05 21:33 ` Eric E <whalesuit@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Eric E @ 2005-12-05 21:33 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Tom Lane wrote:
>Eric E <whalesuit@gmail.com> writes:
>
>
>>Here's the test function:
>>...
>>my $data_handle = spi_exec_query('SELECT * FROM
>>schema1."table_of_approximately 30000 rows";');
>>
>>
>
>Well, the plperl documentation does point out that spi_exec_query should
>only be used when you know that the result set will be relatively
>small. I think the alternative (spi_query/spi_fetchrow) is new in 8.1,
>but you might be able to use a cursor with FETCH instead in older
>versions.
>
>
I did in fact notice that, as I ran into problems exhausting memory with
spi_exec_query before creating this crash function. I just wanted to
see what would happen if I allowed someone to naively write a PL/Perl
function using it. Obviously I'll need to make sure that doesn't
happen. :)
>>Dec 5 14:54:33 dbtestserver kernel: Out of Memory: Killed process 3294
>>(postmaster).
>>
>>
>
>This, however, is just plain administrator error. You should have the
>OOM killer disabled completely (memory overcommit off) on any server
>machine, or indeed any machine at all that runs processes you would not
>like to see killed at random. I believe PG could recover from this,
>if the kernel gave it a polite "out of memory" error and not a kill -9.
>But the truly nasty part of the OOM killer is that it may kill a totally
>innocent process.
>
>
I gathered from the list archives that you can turn the OOM killer off.
For those in the dark like myself on how to do so:
Simply setting the sysctl parameter vm/overcommit_memory to 2 turns off
the overcommit behavior and keeps the OOM killer forever at bay.
<http://lwn.net/Articles/104179/;
>Linux's memory-overcommit behavior was invented by someone accustomed to
>Windows standards of reliability. Personally I don't think there is any
>good reason to have it enabled ever.
>
>
Thanks, I'll definitely turn it off.
Cheers,
Eric
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-05 21:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-05 19:02 Preventing or controlling runaway queries Eric E <whalesuit@gmail.com>
2005-12-05 19:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 20:12 ` Eric E <whalesuit@gmail.com>
2005-12-05 20:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 21:33 ` Eric E <whalesuit@gmail.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