[Server-devel] [GENERAL] Pg 8.3 tuning recommendations for embedded low-memory device (for OLPC :-) )

[Robert Treat]

On Monday 15 September 2008 02:42:32 Greg Smith wrote:
> On Mon, 15 Sep 2008, Martin Langhoff wrote:
> > +max_prepared_transactions = 5
>
> That is the default on 8.3, am guessing you just uncommented it but didn't
> change.  If you're not actually using prepared transactions anywhere, you
> may very well be able to drive memory use down a touch more by lowering
> this to zero.  If you're not sure, the easy but somewhat harsh way to find
> out is to set it that low on a test system and see if everything still
> works.  If you're using them, 5 is actually too low; you'd want one for
> every connection to be safe.
>

neither moodle or mediawiki should be using prepared transactions. 

> > +wal_writer_delay = 1000ms
>
> Presumably your goal is to lower how often transactions get written to
> disk to lower overhead, right?  You mentioned in your first message you
> could handle some of that even if it's at the expense of robustness on
> crash.  In that case, what you also need to set here is:
>
> synchronous_commit = off
>
> When then lets wal_writer_delay do what I think you want.  See
> http://www.postgresql.org/docs/8.3/interactive/wal-async-commit.html for
> more info.
>
> Other than that little bit of tweaking, it looks like you've got a good
> handle on the memory allocation model.  The other parameter you should be
> setting is effective_cache_size, to about how much total RAM is available
> for PostgreSQL to use including the OS buffer cache.  That's probably at
> least 1/2 of the RAM in each system, you can look at what's leftover after
> the system is running to get a rough value there.  This is only used for
> estimating what size of queries could be handled by the system, it's not a
> memory allocation.
>

Call me crazy, but I think you need to drop postgres and maybe even template0 
databases from the system, just to reduce overall footprint, plus gives you 
less databases to have to keep track of wrt autovacuum and such.

Also, if your disk is limited, you might want to play with the 
autovacuum_max_freeze_age and the corresponding vacuum settings to try and 
reduce pg_clog size. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL