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

[Martin Langhoff]

On Mon, Sep 15, 2008 at 6:42 PM, Greg Smith <gsmith at gregsmith.com> 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.

Nothing uses prepared transactions, so I'll set it to 0.

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

I'm somewhat hesitant on completely avoiding sync. Moodle for example
rarely writes to the DB (as most web content mgmt) except for a log
table that gets an insert per pageview. For the time being, I plan to
get _that_ insert in a async commit --  I think this will give me 99%
of the advantage, and keep the transactional sanity for the data that
matters.

Is that a reasonable approach?

> Other than that little bit of tweaking, it looks like you've got a good
> handle on the memory allocation model.

Thanks for confirming that :-)

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

Ok. Will set that one too.

And *thanks* -- these are great hints.





martin
-- 
 martin.langhoff at gmail.com
 martin at laptop.org -- School Server Architect
 - ask interesting questions
 - don't get distracted with shiny stuff - working code first
 - http://wiki.laptop.org/go/User:Martinlanghoff