Re: swap storm created by 8.2.3 - Mailing list pgsql-general

From Joseph Shraibman
Subject Re: swap storm created by 8.2.3
Date
Msg-id 46570CD6.2030807@selectacast.net
Whole thread Raw
In response to Re: swap storm created by 8.2.3  (Richard Huxton <dev@archonet.com>)
Responses Re: swap storm created by 8.2.3
List pgsql-general

Richard Huxton wrote:
> Joseph Shraibman wrote:
>> I'm running:
>>
>> PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
>> (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3)
>>
>> My memory settings are:
>>
>> work_mem = 64MB
>> shared_buffers = 128MB
>> temp_buffers = 32MB
>>
>> I ran a query that was "SELECT field, count(*) INTO TEMP temptable"
>> and it grew to be 10gig (as reported by top)
>
> What was the real query?

First I selected 90634 rows (3 ints) into the first temp table, then I
did "select intfield1, count(intfield2) FROM realtable rt WHERE rt.id =
temptable.id and other conditions on rt here GROUP BY intfield1".  The
size of the second temp table should have been no more than 60000 rows.

> How many rows are we talking about?
>
>  > and brought the whole machine
>> to its knees.  How do I keep this from happening again?
>
> Set your per-user limits (man ulimit or man bash) to restrict PG's
> overall memory consumption.
>
What happens when PG hits that limit?  Will it start using disk space
for TEMP tables then?

pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Referencing any field in a trigger
Next
From: Richard Huxton
Date:
Subject: Re: swap storm created by 8.2.3