Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)

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