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 ]
Attachment:
signature.asc
Description: OpenPGP digital signature
