Re: how to avoid that a postgres session eats up all the memory

On 01/22/2009 07:11 PM, Richard Huxton wrote:
> Clemens Schwaighofer wrote:
>> Hi,
>>
>> I just literally trashed my test server with one delete statement
>> because the psql used up all its memory and started to swap like crazy.
>>
>> my delete looked like this
>>
>> DELETE FROM table_foo WHERE bar_id IN (SELECT DISTINCT bar_id FROM bar
>> LEFT JOIN ... LEFT JOIN ... LEFT JOIN .... WHERE table.bar_id IS NULL
>> AND ...)
>
> Is it your psql client or PostgreSQL backend that used up all the
> memory? I can't see how a DELETE can use up memory in psql.

psql used up all my physical memory. I didn't thought so too, but I
learned something new. a DELETE can use up all my memeory.

>
>> so basically it runs a select to see what entries do not have any
>> reference data and then should delete them all.
>>
>> Now, my question is. How can I setup postgres to not use up all the
>> memory and then make the server useless. How can I set it up, so it dies
>> with "out of memory" before I have to restart the server.
>
> You shouldn't need to restart the server at all - what operating system
> are you running?

i run Linux, Debian/Testing with a 2.6.25.1 kernel. And once he started
to swap, or whatever, the load skyrocketed (last was 78) and I couldn't
do anything.

> What memory settings are you using? How much RAM do you have? How much
> do you want to keep for other applications?

As this is just a test/development box, I have no special settings.

shared_buffers = 24MB
max_fsm_pages = 153600

The server itself has 1GB of memory.

All I want, is that psql client does not use up all the memory and make
the system unresponsive.

--
[ Clemens Schwaighofer -----=====:::::~ ]
[ IT Engineer/Web Producer/Planning/Manager ]
[ E-Graphics Communications SP Digital ]
[ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706 Fax: +81-(0)3-3545-7343 ]
[ http://www.e-gra.co.jp ]