Re: Preventing or controlling runaway queries

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