Message-ID: <556F547D.5040206@2ndquadrant.com>
Date: Wed, 03 Jun 2015 21:24:45 +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>
In-Reply-To: 
 <CAOR=d=20bFbC-LnrzqCL2-c79q-8hmHAf6zv0Roh8r=tmxafrA@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 17:09, Scott Marlowe wrote:
> On Wed, Jun 3, 2015 at 8:56 AM, Tomas Vondra
>>
>>
>> I don't see why you think you have less than 3GB used. The output you posted
>> clearly shows there's only ~300MB memory free - there's 15GB shared buffers
>> and ~45GB of page cache (file system cache).
>
> Because you subtract cached from used to see how much real spare
> memory you have. The kernel will dump cached mem as needed to free up
> space for memory usage. So 64141-61761=2380MB used.

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.


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