X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CBB419DCAB8 for ; Mon, 5 Dec 2005 17:34:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56844-05 for ; Mon, 5 Dec 2005 17:34:08 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.200]) by postgresql.org (Postfix) with ESMTP id 1AF419DCAB1 for ; Mon, 5 Dec 2005 17:34:03 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i20so751519wra for ; Mon, 05 Dec 2005 13:34:05 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding; b=muJT66Wogcb6TcWSrWr0NRHv3B6TsQoClM2qdgLJ56jdIC39mCCXZ5TknEplQTBCMKwkPc8jOwrM6krSwrAFQYjsMEGezp4cYjVkEHkCPUGqxeQqX6KzRaMZCjZrFaNM/K4RzOsIlc6/GYiNDZxU8ZDYxE3/y+XRyy6NPaDwihk= Received: by 10.54.127.3 with SMTP id z3mr4744512wrc; Mon, 05 Dec 2005 13:34:05 -0800 (PST) Received: from ?192.168.130.103? ( [69.17.59.217]) by mx.gmail.com with ESMTP id 45sm6367277wri.2005.12.05.13.34.05; Mon, 05 Dec 2005 13:34:05 -0800 (PST) Message-ID: <4394B247.5080609@gmail.com> Date: Mon, 05 Dec 2005 16:33:59 -0500 From: Eric E User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: Preventing or controlling runaway queries References: <43948EC3.4030407@gmail.com> <1744.1133811345@sss.pgh.pa.us> <43949F37.4000000@gmail.com> <2416.1133816247@sss.pgh.pa.us> In-Reply-To: <2416.1133816247@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.047 required=5 tests=[AWL=0.047] X-Spam-Score: 0.047 X-Spam-Level: X-Archive-Number: 200512/272 X-Sequence-Number: 87835 Tom Lane wrote: >Eric E 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. >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