From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
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)
Date: Thu, 04 Jun 2015 00:16:10 +0200
Message-ID: <556F7CAA.2080003@2ndquadrant.com> (raw)
In-Reply-To: <CAOR=d=2bMfcv9d5Lok5WddVm7ztM4a1QNcxm9Tcpx+PChjQunQ@mail.gmail.com>
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>
<556F547D.5040206@2ndquadrant.com>
<CAOR=d=2bMfcv9d5Lok5WddVm7ztM4a1QNcxm9Tcpx+PChjQunQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/03/15 23:18, Scott Marlowe 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
>>>
>> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, scott.marlowe@gmail.com
Subject: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)
In-Reply-To: <556F7CAA.2080003@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox