Message-ID: <556F0649.9000402@2ndquadrant.com>
Date: Wed, 03 Jun 2015 15:51:05 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)
References: <1433334092106-5852321.post@n5.nabble.com>
	<1433337082772-5852328.post@n5.nabble.com>
 <CA+RSxMiSwse+YfMoFT8128PoWG3kifmrvg4rhdBHxUTE3HPH-g@mail.gmail.com>
In-Reply-To: 
 <CA+RSxMiSwse+YfMoFT8128PoWG3kifmrvg4rhdBHxUTE3HPH-g@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 06/03/15 15:27, chiru r wrote:
> Hi Benjamin,
>
> It looks you are facing disk space issue for queries.
> In order to avid the disk space issue you can do the following.
> 1) Increase the work_mem parameter session level before executing the
> queries.
> 2) If you observe diskspace issue particular user queries,increase the
> work_mem parameter user level.

The suggestion to increase work_mem is a bit naive, IMHO. The query is 
writing ~95GB to disk, it usually takes more space to keep the same data 
in memory. They only have 64GB of RAM ...

In the good case, it will crash just like now. In the worse case, the 
OOM killer will intervene, possibly crashing the whole database.


> 3) Check with developer to tune the query.

That's a better possibility. Sadly, we don't know what the query is 
doing, so we can't judge how much it can be optimized.

--
Tomas Vondra                   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance