On Wed, Jun 3, 2015 at 6:18 PM, Scott Marlowe <scott.marlowe@gmail.com> 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
>>>>
>>>>
>>>>
>>>> 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.
>
> 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.
In my experience, dumping the buffer cache as heavily as that counts
as thrashing. Either concurrent or future queries will have to go to
disk and that will throw performance out the window, which is never
quite so ok.
It is generally better to let pg use that temporary file (unless the
in-memory strategy happens to be much faster, say using a hash instead
of sort, which usually doesn't happen in my experience for those sizes
anyway) and let the OS handle the pressure those dirty buffers cause.
The OS will usually handle it better than work_mem.
