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