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

[Greg Smith]

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.

> +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.

--
* Greg Smith gsmith at gregsmith.com http://www.gregsmith.com Baltimore, MD