From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Scott Marlowe <scott.marlowe@gmail.com>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.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 13:19:44 +1200
Message-ID: <556FA7B0.6050108@catalyst.net.nz> (raw)
In-Reply-To: <CAOR=d=3WjFfjwK-ne2P-J4senyoLwxUKy5GSX3+wmXSKzwH8Sg@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>
<556F7CAA.2080003@2ndquadrant.com>
<556F7FD8.5070802@commandprompt.com>
<CAOR=d=2vMYtYfJhRQoHMPzPBi22omk46XRDa2EcTVJYT8YCFpw@mail.gmail.com>
<CAOR=d=3WjFfjwK-ne2P-J4senyoLwxUKy5GSX3+wmXSKzwH8Sg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 04/06/15 12:58, Scott Marlowe wrote:
> On Wed, Jun 3, 2015 at 6:53 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Wed, Jun 3, 2015 at 4:29 PM, Joshua D. Drake <jd@commandprompt.com> 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.
>>
>> But if that WAS happening he wouldn't still HAVE 60G of cache! That's
>> my whole point. He's NOT running out of memory. He's not even having
>> to dump cache right now.
>
> Further if he started using a few gig here for this one it wouldn't
> have a big impact on cache (60G-1G etc) but might make it much faster,
> as spilling to disk is a lot less intrusive when you've got a bigger
> chunk of ram to work in. OTOH doing something like setting work_mem to
> 60G would likely be fatal.
>
> But he's not down to 3GB of memory by any kind of imagination. Any
> working machine will slowly, certainly fill its caches since it's not
> using the memory for anything else. That's normal. As long as you're
> not blowing out the cache you're fine.
>
>
I agree with Scott's analysis here.
It seems to me that the issue is the query(s) using too much disk space.
As others have said, it may not be practical to up work_mem to the point
where is all happens in memory...so probably need to:
- get more disk or,
- tweak postgres params to get a less disk hungry plan (need to see that
explain analyze)!
Cheers
Mark
--
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: mark.kirkwood@catalyst.net.nz, scott.marlowe@gmail.com, jd@commandprompt.com, tomas.vondra@2ndquadrant.com
Subject: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)
In-Reply-To: <556FA7B0.6050108@catalyst.net.nz>
* 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