Re: Pg 8.3 tuning recommendations for embedded low-memory device (for OLPC :-) )

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(dot)com http://www.gregsmith.com Baltimore, MD