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

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 ...)

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.

--
[ 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                                     ]

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.

> 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?

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

--
  Richard Huxton
  Archonet Ltd

try making it in two steps,using temp table maybe.
how many rows does the subselect generate ?

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                                     ]

On 01/22/2009 07:19 PM, Grzegorz Jaśkiewicz wrote:
> try making it in two steps,using temp table maybe.
> how many rows does the subselect generate ?

the subselect returns 57.000 rows.

--
[ 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                                     ]

try raising work_mem before the delete; on single connection :

set work_mem=512000; DELETE FROM ......;

But if I have my work mem small, shouldn't it then just end with "out
of memory" and not use up all the memory ...

On Fri, Jan 23, 2009 at 18:48, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
> try raising work_mem before the delete; on single connection :
>
> set work_mem=512000; DELETE FROM ......;
>



--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager                                        ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group         ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp                                      ]




This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is
privileged, confidential and/or otherwise protected from disclosure.
Dissemination, distribution or copying of this e-mail or the
information herein by anyone other than the intended recipient, or
an employee or agent responsible for delivering the message to the
intended recipient, is strictly prohibited.  All contents are the
copyright property of TBWA Worldwide, its agencies or a client of
such agencies. If you are not the intended recipient, you are
nevertheless bound to respect the worldwide legal rights of TBWA
Worldwide, its agencies and its clients. We require that unintended
recipients delete the e-mail and destroy all electronic copies in
their system, retaining no copies in any media.If you have received
this e-mail in error, please immediately notify us via e-mail to
disclaimer@tbwaworld.com.  We appreciate your cooperation.

We make no warranties as to the accuracy or completeness of this
e-mail and accept no liability for its content or use.  Any opinions
expressed in this e-mail are those of the author and do not
necessarily reflect the opinions of TBWA Worldwide or any of its
agencies or affiliates.

Clemens Schwaighofer wrote:
> 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.

Can we establish that it really is psql? Can you check with "ps aux" or
"top" that it's psql and not a backend ("postgres") running the query?

Also, can we establish that it is memory that we're running out of -
again, "ps", "top" or "free -m" should show that.

--
  Richard Huxton
  Archonet Ltd

On 01/24/2009 12:42 AM, Richard Huxton wrote:
> Clemens Schwaighofer wrote:
>> 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.
>
> Can we establish that it really is psql? Can you check with "ps aux" or
> "top" that it's psql and not a backend ("postgres") running the query?

yes, I checked it with top and ps aux, it is only psql, not the
connecting postgres worker

> Also, can we establish that it is memory that we're running out of -
> again, "ps", "top" or "free -m" should show that.

it is the physical memory.

I will run the process again, and send in results for confirmation

--
[ 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                                     ]