From: Eric E <whalesuit@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: Preventing or controlling runaway queries
Date: Mon, 05 Dec 2005 16:33:59 -0500
Message-ID: <4394B247.5080609@gmail.com> (raw)
In-Reply-To: <2416.1133816247@sss.pgh.pa.us>
References: <43948EC3.4030407@gmail.com>
<1744.1133811345@sss.pgh.pa.us>
<43949F37.4000000@gmail.com>
<2416.1133816247@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: whalesuit@gmail.com, pgsql-general@postgresql.org
Subject: Re: Preventing or controlling runaway queries
In-Reply-To: <4394B247.5080609@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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