On 06/04/15 01:54, Yves Dorfsman wrote:
> On 2015-06-03 16:29, Joshua D. Drake wrote:
>>
>> On 06/03/2015 03:16 PM, Tomas Vondra wrote:
>>
>>> 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.
>>
>> Exactly. If your cache is reduced your performance is reduced because less
>> things are in cache. It is not free memory. Also the command "free" is not
>> useful in this scenario. It is almost always better to use sar so you can see
>> where the data points are that free is using.
>>
>
> It's one thing to consciously keep free memory for the OS cache, but
> you should not take the "free" column from the first line output of
> the program free as meaning that's all there is left, or that you
> need allthat memory.
No one suggested using the 'free' column this way, so I'm not sure what
you're responding to?
> You should look at "used" from the second line ("-/+ buffers/cache").
> That value is what the kernel and all the apps are using on your
> machine. Add whatever you want to have for OS cache, and this is the
> total amount ofmemory you want in your machine.
Except that the second line is not particularly helpful too, because it
does not account for the shared buffers clearly, nor does it show what
part of the page cache is dirty etc.
> Note that for a machine that has run long enough, and done enough
> I/O ops, "free" from the first line will always be close to 0,
> because the OS tries to use as much memory as possible for caching,
> do enough I/O and you'll fill that up.
That's generally true, but the assumption is that on a 300GB database
the page cache has a significant benefit for performance. What however
makes this approach utterly futile is the fact that OP has only 64GB of
RAM (and only ~45GB of that in page cache), and the query writes >95GB
temp files on disk (and then fails). So even if you drop the whole page
cache, the query will fail anyway.
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
