Thread: Preventing or controlling runaway queries
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
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
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
>
>
>
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
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
This could be an old conversation for most. I've used PostgreSQL for a
while but I haven't fully use the Procedure/Functions to it fullest
until now. I need to migrate a MS-SQL 2000 database to PostgreSQL.
I've read as much as I could find but I seem to be missing something.
I did see last week something on PLPGSQL and read through that. But
there has to be something out there that goes in depth on the
SQL/Function command set(Speaking of functions/procedures).
The biggest hole that I have; seems to be on Cursors; define and
opening. I think the fetching and closing is pretty straight forward.
But the Define and opening is causing some grief. Either I'm making to
far too hard or I'm really have missing something silly.
Simple example in MS-2000:
CREATE PROCEDURE dbo.sp_RemoveDups AS
SET NOCOUNT ON
DECLARE @err int, @LastName varchar(255), @Name varchar(255), @id
bigint, @LineNum bigint
DECLARE NewListCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT Name, id
FROM MailingList
ORDER BY id
OPEN NewListCursor
SELECT @LineNum = 0
SELECT @LastName = ""
FETCH NEXT FROM NewListCursor INTO @Name, @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @LineNum = @LineNum + 1
IF @LastName = @Name
DELETE FROM MailingList WHERE id = @id
SELECT @LastName = @Name
FETCH NEXT FROM NewListCursor INTO @LastName, @id
END
CLOSE NewListCursor
DEALLOCATE NewListCursor
RETURN (0)
GO
This is an example of the simple stored procedures like the ones I'm
trying to migrate. PLPGSQL is ok but I thought it would run better in
SQL. Just not C, Perl or TK/TCL. Those are not being used with this
application and no plans to use them in the future.
Thanks for any help;
Lee Foster
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
On Sat, Dec 10, 2005 at 09:02:39PM -0600, Foster, Stephen wrote: > I did see last week something on PLPGSQL and read through that. But > there has to be something out there that goes in depth on the > SQL/Function command set(Speaking of functions/procedures). The standard functions are described in the "Functions and Operators" chapter of the documentation. Here's a link to the latest version, but use the documentation for the version you're running: http://www.postgresql.org/docs/8.1/interactive/functions.html SQL functions are documented in "Query Language (SQL) Functions": http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html > The biggest hole that I have; seems to be on Cursors; define and > opening. I think the fetching and closing is pretty straight forward. > But the Define and opening is causing some grief. Either I'm making to > far too hard or I'm really have missing something silly. What problems are you having? Without seeing what you're doing in PostgreSQL it's difficult to say what's wrong. In PL/pgSQL you can loop through query results without explicitly using a cursor; see "Looping Through Query Results": http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING [snip example] > This is an example of the simple stored procedures like the ones I'm > trying to migrate. PLPGSQL is ok but I thought it would run better in > SQL. SQL doesn't have control structures, so if you need conditionals or loops then use a procedural language like PL/pgSQL or implement the logic on the client side. For the example you posted, the following query should have the same effect (remove records with duplicate names, if I'm reading it right): DELETE FROM mailinglist WHERE id NOT IN ( SELECT DISTINCT ON (name) id FROM mailinglist ORDER BY name, id ); I don't know how well this would perform on large data sets, especially in older versions of PostgreSQL, but you could try it. I'd recommend trying it first on a test table or in a transaction that you can roll back in case it doesn't do what you want. See the SELECT documentation for a description of the non-standard DISTINCT ON clause that the above query uses: http://www.postgresql.org/docs/8.1/interactive/sql-select.html -- Michael Fuhr
Michael,
This is one of the simple ones that has me hung up. It currently errors
out on the first line. Why I have no idea. It looks right to me. One
of the harder ones that I have to work on is on the security
sub-routines to pull together the security rights for a person during
login and I'm looking forward to that one.
I'll take a look at the reference that you sent. The other ones that I
wrote had to be really simple if I'm hung on this one. It is just a
simple purge on a temp table before merging new information into the
master table. I have thought about this before hand wasn't about to
locate the "Welcome to PostreSQL Stored Procedures" or in my current
case "PostreSQL for idiots". This is embarrassing for the time I've
spend as a DBA. Getting kicked by a simple pre-process procedure.
CREATE FUNCTION sp_removedups() RETURNS void AS
$BODY$
DECLARE lastname varchar(255);
fname varchar(255);
id bigint;
DECLARE NewListCursor CURSOR FOR
SELECT Name, id
FROM MailingList
ORDER BY Name;
OPEN NewListCursor;
LineNum := 0;
LastName := "";
FETCH NEXT FROM NewListCursor INTO fname, id;
WHILE (--Lost on variable name for end of query; EmptyQueryResponse <>
0? --)
BEGIN
IF LastName = fname THEN
DELETE FROM MailingList WHERE id = id;
END IF;
LastName := fname;
FETCH NEXT FROM NewListCursor INTO fname, id;
END;
CLOSE NewListCursor;
$BODY$
LANGUAGE 'sql' VOLATILE;
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michael Fuhr
Sent: Saturday, December 10, 2005 10:41 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL
Stored Procedures
On Sat, Dec 10, 2005 at 09:02:39PM -0600, Foster, Stephen wrote:
> I did see last week something on PLPGSQL and read through that. But
> there has to be something out there that goes in depth on the
> SQL/Function command set(Speaking of functions/procedures).
The standard functions are described in the "Functions and Operators"
chapter of the documentation. Here's a link to the latest version,
but use the documentation for the version you're running:
http://www.postgresql.org/docs/8.1/interactive/functions.html
SQL functions are documented in "Query Language (SQL) Functions":
http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html
> The biggest hole that I have; seems to be on Cursors; define and
> opening. I think the fetching and closing is pretty straight forward.
> But the Define and opening is causing some grief. Either I'm making
to
> far too hard or I'm really have missing something silly.
What problems are you having? Without seeing what you're doing in
PostgreSQL it's difficult to say what's wrong.
In PL/pgSQL you can loop through query results without explicitly
using a cursor; see "Looping Through Query Results":
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structure
s.html#PLPGSQL-RECORDS-ITERATING
[snip example]
> This is an example of the simple stored procedures like the ones I'm
> trying to migrate. PLPGSQL is ok but I thought it would run better in
> SQL.
SQL doesn't have control structures, so if you need conditionals
or loops then use a procedural language like PL/pgSQL or implement
the logic on the client side.
For the example you posted, the following query should have the same
effect (remove records with duplicate names, if I'm reading it right):
DELETE FROM mailinglist
WHERE id NOT IN (
SELECT DISTINCT ON (name) id
FROM mailinglist
ORDER BY name, id
);
I don't know how well this would perform on large data sets,
especially in older versions of PostgreSQL, but you could try it.
I'd recommend trying it first on a test table or in a transaction
that you can roll back in case it doesn't do what you want.
See the SELECT documentation for a description of the non-standard
DISTINCT ON clause that the above query uses:
http://www.postgresql.org/docs/8.1/interactive/sql-select.html
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
"Foster, Stephen" <stephenlfoster@comcast.net> writes: > WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> > 0? --) > BEGIN > IF LastName = fname THEN > DELETE FROM MailingList WHERE id = id; > END IF; > LastName := fname; > FETCH NEXT FROM NewListCursor INTO fname, id; > END; > CLOSE NewListCursor; > $BODY$ > LANGUAGE 'sql' VOLATILE; You can't do any looping or other control structures in an SQL function. Use PL/pgSQL instead. -Doug
Ok, I tried it in that I'm still getting errors. Happy to do so but well is my errors? -----Original Message----- From: Douglas McNaught [mailto:doug@mcnaught.org] Sent: Sunday, December 11, 2005 10:39 AM To: Foster, Stephen Cc: 'Michael Fuhr'; pgsql-general@postgresql.org; pgsql-sql@postgresql.org Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures "Foster, Stephen" <stephenlfoster@comcast.net> writes: > WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> > 0? --) > BEGIN > IF LastName = fname THEN > DELETE FROM MailingList WHERE id = id; > END IF; > LastName := fname; > FETCH NEXT FROM NewListCursor INTO fname, id; > END; > CLOSE NewListCursor; > $BODY$ > LANGUAGE 'sql' VOLATILE; You can't do any looping or other control structures in an SQL function. Use PL/pgSQL instead. -Doug -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005
"Foster, Stephen" <stephenlfoster@comcast.net> writes:
> This is one of the simple ones that has me hung up. It currently errors
> out on the first line. Why I have no idea. It looks right to me.
You should show us the error message; most of us are not psychics.
> CREATE FUNCTION sp_removedups() RETURNS void AS
> $BODY$
> DECLARE lastname varchar(255);
> fname varchar(255);
> id bigint;
> DECLARE NewListCursor CURSOR FOR
> SELECT Name, id
> FROM MailingList
> ORDER BY Name;
> OPEN NewListCursor;
You're missing a BEGIN, and I'm not sure whether it's allowed to use the
DECLARE keyword twice in the same block (the second instance is certainly
unnecessary even if legal).
regards, tom lane
On Sun, Dec 11, 2005 at 11:38:47AM -0500, Douglas McNaught wrote:
> "Foster, Stephen" <stephenlfoster@comcast.net> writes:
>
> > WHILE (--Lost on variable name for end of query; EmptyQueryResponse <>
> > 0? --)
> > BEGIN
> > IF LastName = fname THEN
> > DELETE FROM MailingList WHERE id = id;
> > END IF;
> > LastName := fname;
> > FETCH NEXT FROM NewListCursor INTO fname, id;
> > END;
> > CLOSE NewListCursor;
> > $BODY$
> > LANGUAGE 'sql' VOLATILE;
>
> You can't do any looping or other control structures in an SQL
> function. Use PL/pgSQL instead.
And as I mentioned in my previous post, you can loop through query
results without messing around with an explicit cursor.
CREATE FUNCTION testfunc() RETURNS void AS $$
DECLARE
row record;
BEGIN
FOR row IN SELECT * FROM tablename ORDER BY whatever LOOP
-- do stuff that refers to row.column_name
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
--
Michael Fuhr
Trying a totally different approach. Simple procedure that I'm using to
use as learn opportunity in stored procedures in PostgreSQL prior to
migrating a complex web site. Call it a training example if you will.
Goal is to learn the correct process of working with cursors on complex
queries in PostgreSQL before investing hours of work migrating a web
site and moving some of the complex procedures to the database to
simplify a process.
Using this simple example,
CREATE FUNCTION sp_removedups() RETURNS void AS
$BODY$
DECLARE
lastname varchar(255);
fname varchar(255);
id bigint;
DECLARE NewListCursor CURSOR FOR
SELECT Name, id
FROM MailingList
ORDER BY Name;
BEGIN
OPEN NewListCursor;
LastName := "";
FETCH NEXT FROM NewListCursor INTO fname, id;
WHILE (--Lost on variable name for end of query;
EmptyQueryResponse <> 0? --)
BEGIN
IF LastName = fname THEN
DELETE FROM MailingList WHERE id = id;
END IF;
LastName := fname;
FETCH NEXT FROM NewListCursor INTO fname, id;
END;
CLOSE NewListCursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
On the BEGIN/END; yes you are right it was missing. I'm converting a
MS-2000 SQL example to PostgreSQL. Error according to pgadminIII is on
line 11. Which should be the "LastName := "";" line. Before someone
says it there is a better way of do this but it was a simple example
before I dived in to the real ones.
Second question/problem is how do you determine if the query has reached
the end. Mentioned on the WHILE line.
I have another project after this one I'm about to tackle that will be
even more complex. So the sooner I can grasp the store procedures in
this database the better off I will be. If there is a book somewhere
that will clearly define the command set please let me know. This way
determine what I can and can't do. The web language I use I can go back
and fore with no problems but am think some of the processing that it is
currently doing would be faster if I move it to the database side.
Thanks for any help,
Lee Foster
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
Thanks Michael! Guess I should have drunk more coffee before replying.
I must have miss that.
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Sunday, December 11, 2005 11:22 AM
To: Douglas McNaught
Cc: Foster, Stephen; pgsql-general@postgresql.org;
pgsql-sql@postgresql.org
Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL
Stored Procedures
On Sun, Dec 11, 2005 at 11:38:47AM -0500, Douglas McNaught wrote:
> "Foster, Stephen" <stephenlfoster@comcast.net> writes:
>
> > WHILE (--Lost on variable name for end of query; EmptyQueryResponse
<>
> > 0? --)
> > BEGIN
> > IF LastName = fname THEN
> > DELETE FROM MailingList WHERE id = id;
> > END IF;
> > LastName := fname;
> > FETCH NEXT FROM NewListCursor INTO fname, id;
> > END;
> > CLOSE NewListCursor;
> > $BODY$
> > LANGUAGE 'sql' VOLATILE;
>
> You can't do any looping or other control structures in an SQL
> function. Use PL/pgSQL instead.
And as I mentioned in my previous post, you can loop through query
results without messing around with an explicit cursor.
CREATE FUNCTION testfunc() RETURNS void AS $$
DECLARE
row record;
BEGIN
FOR row IN SELECT * FROM tablename ORDER BY whatever LOOP
-- do stuff that refers to row.column_name
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
--
Michael Fuhr
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
"Foster, Stephen" <stephenlfoster@comcast.net> writes:
> WHILE (--Lost on variable name for end of query;
> EmptyQueryResponse <> 0? --)
WHILE FOUND
LOOP
...
END LOOP
Although as Michael mentioned, this is all the hard way; any experienced
plpgsql programmer would forget the cursor entirely and just write a
FOR-loop.
regards, tom lane