Skip site navigation (1) Skip section navigation (2)

Re: Memory Allocation

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Ryan Hansen" <ryan(dot)hansen(at)brightbuilders(dot)com>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>,"Scott Carey" <scott(at)richrelevance(dot)com>
Subject: Re: Memory Allocation
Date: 2008-11-28 22:28:42
Message-ID: 49301C3A.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
I'm hoping that through compare/contrast we might help someone start
closer to their own best values....
 
>>> Scott Carey <scott(at)richrelevance(dot)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

In response to

pgsql-performance by date

Next:From: AndrusDate: 2008-11-29 17:43:50
Subject: Re: Increasing GROUP BY CHAR columns speed
Previous:From: AndrusDate: 2008-11-28 20:43:57
Subject: Re: Increasing GROUP BY CHAR columns speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group