From: | Eric E <whalesuit(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Preventing or controlling runaway queries |
Date: | 2005-12-05 21:33:59 |
Message-ID: | 4394B247.5080609@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
Tom Lane wrote:
>Eric E <whalesuit(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ed L. | 2005-12-05 21:53:15 | feature: dynamic DB cache resizing |
Previous Message | Greg Stark | 2005-12-05 21:01:21 | Re: Selecting Large Object and TOAST |
From | Date | Subject | |
---|---|---|---|
Next Message | Lane Van Ingen | 2005-12-05 22:37:00 | Re: Database with "override" tables |
Previous Message | Tom Lane | 2005-12-05 20:57:27 | Re: Preventing or controlling runaway queries |