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

On 06/03/15 23:18, Scott Marlowe wrote:
> On Wed, Jun 3, 2015 at 1:24 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)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