Message-ID: <556F7CAA.2080003@2ndquadrant.com>
Date: Thu, 04 Jun 2015 00:16:10 +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: Scott Marlowe <scott.marlowe@gmail.com>
CC: "pgsql-performance@postgresql.org" <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>
	<556F0649.9000402@2ndquadrant.com>
	<9DED5F42-2C51-4CDE-A179-56A8A9370338@playrion.com>
	<556F15AD.4040801@2ndquadrant.com>
	<CAOR=d=20bFbC-LnrzqCL2-c79q-8hmHAf6zv0Roh8r=tmxafrA@mail.gmail.com>
	<556F547D.5040206@2ndquadrant.com>
 <CAOR=d=2bMfcv9d5Lok5WddVm7ztM4a1QNcxm9Tcpx+PChjQunQ@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=2bMfcv9d5Lok5WddVm7ztM4a1QNcxm9Tcpx+PChjQunQ@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 23:18, Scott Marlowe wrote:
> On Wed, Jun 3, 2015 at 1:24 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
>>
>> On 06/03/15 17:09, Scott Marlowe wrote:
>>>
>>> On Wed, Jun 3, 2015 at 8:56 AM, Tomas Vondra
>>>
>> Well, except that 15GB of that is shared_buffers, and I wouldn't call that
>> 'free'. Also, I don't see page cache as entirely free - you probably want at
>> least some caching at this level.
>>
>> In any case, even if all 64GB were free, this would not be enough for the
>> query that needs >95GB for temp files.
>
> You can argue all you want, but this machine has plenty of free memory
> right now, and unless the OP goes crazy and cranks up work_mem to some
> much higher level it'll stay that way, which is good. There's far far
> more than 300MB free here. At the drop of a hat there can be ~60G
> freed up as needed, either for shared_buffers or work_mem or other
> things to happen. Cache doesn't count as "used" in terms of real
> memory pressure. IE you're not gonna start getting swapping becase you
> need more memory, it'll just come from the cache.

Please, could you explain how you free 60GB 'as need' when 15GB of that 
is actually used for shared buffers? Also, we don't know how much of 
that cache is 'dirty' which makes it more difficult to free.

What is more important, though, is the amount of memory. OP reported the 
query writes ~95GB of temp files (and dies because of full disk, so 
there may be more). The on-disk format is usually more compact than the 
in-memory representation - for example on-disk sort often needs 3x less 
space than in-memory qsort. So we can assume the query needs >95GB of 
data. Can you explain how that's going to fit into the 64GB RAM?

> Cache is free memory. If you think of it any other way when you're
> looking at memory usage and pressure on theings like swap you're
> gonna make some bad decisions.

Cache is not free memory - it's there for a purpose and usually plays a 
significant role in performance. Sure, it may be freed and used for 
other purposes, but that has consequences - e.g. it impacts performance 
of other queries etc. You generally don't want to do that on production.


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