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

On Mon, Sep 15, 2008 at 6:42 PM, Greg Smith <gsmith(at)gregsmith(dot)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(dot)langhoff(at)gmail(dot)com
martin(at)laptop(dot)org -- School Server Architect
- ask interesting questions
- don't get distracted with shiny stuff - working code first
- http://wiki.laptop.org/go/User:Martinlanghoff