Re: [GENERAL] how to avoid that a postgres session eats up all the memory
Generally out of memory error persist when the memory is not enough to handle the query. When query executed without using standard plan it consum lots of memory. If shared buffer is not enough to handle this query it thrown error like out of memory. It if good practice to do "explain " before executing it, will give you information about which plan it will use. The workaround for the "Delete..." query is you can make pl/pgsql function, which will execute faster than this query. As it is not gone for sequential scan. It will directly jump to the table.bar_id where you given appropriate condition. --- Thanks & Regards Dhaval Jaiswal EnterpriseDB www.enterprisedb.com From: Clemens Schwaighofer To: pgsql-general@postgresql.org Sent: Thursday, January 22, 2009 2:21:47 PM Subject: [GENERAL] 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 ]
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
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-7706Fax: +81-(0)3-3545-7343 ]
[ http://www.e-gra.co.jp ]
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
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 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-7703Fax: +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 disclai...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
try raising work_mem before the delete; on single connection : set work_mem=512000; DELETE FROM ..; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
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-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] 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-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
try making it in two steps,using temp table maybe. how many rows does the subselect generate ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
