From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Ryan Hansen <ryan.hansen@brightbuilders.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
To: Scott Carey <scott@richrelevance.com>
Subject: Re: Memory Allocation
Date: Fri, 28 Nov 2008 16:28:42 -0600
Message-ID: <49301C3A.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <BDFBB77C9E07BE4A984DAAE981D19F961ACA1F1AED@EXVMBX018-1.exch018.msoutlookonline.net>
References: <011101c95013$b6fca0a0$24f5e1e0$@hansen@brightbuilders.com>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA1F1ADC@EXVMBX018-1.exch018.msoutlookonline.net>
<492D82BB.EE98.0025.0@wicourts.gov>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA1F1AED@EXVMBX018-1.exch018.msoutlookonline.net>
I'm hoping that through compare/contrast we might help someone start
closer to their own best values....
>>> Scott Carey <scott@richrelevance.com> wrote:
> Tests with writes can trigger it earlier if combined with bad
dirty_buffers
> settings.
We've never, ever modified dirty_buffers settings from defaults.
> The root of the problem is that the Linux paging algorithm estimates
that
> I/O for file read access is as costly as I/O for paging. A
reasonable
> assumption for a desktop, a ridiculously false assumption for a large
> database with high capacity DB file I/O and a much lower capability
swap
> file.
Our swap file is not on lower speed drives.
> If you do have enough other applications that are idle that take up
RAM that
> should be pushed out to disk from time to time (perhaps your programs
that
> are doing the bulk loading?) a higher value is useful.
Bulk loading was ssh cat | psql.
> The more RAM you have and the larger your postgres memory usage, the
lower
> the swappiness value should be.
I think the test environment had 8 GB RAM with 256 MB in
shared_buffers. For the conversion we had high work_mem and
maintenance_work_mem settings, and we turned fsync off, along with a
few other settings we would never using during production.
> I currently use a value of 1, on a 32GB machine, and about 600MB of
'stuff'
> gets paged out normally, 1400MB under heavy load.
Outside of bulk load, we've rarely seen anything swap, even under
load.
> ***For a bulk load database, one is optimizing for _writes_ and extra
page
> cache doesn't help writes like it does reads.***
I'm thinking that it likely helps when indexing tables for which data
has recently been loaded. It also might help minimize head movement
and/or avoid the initial disk hit for a page which subsequently get
hint bits set .
> Like all of these settings, tune to your application and test.
We sure seem to agree on that.
-Kevin
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: Kevin.Grittner@wicourts.gov, ryan.hansen@brightbuilders.com, scott@richrelevance.com
Subject: Re: Memory Allocation
In-Reply-To: <49301C3A.EE98.0025.0@wicourts.gov>
* 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