Re: Conservative postgresql.conf made by initdb?

On Wednesday 05 December 2007 01:45, rihad wrote:
> In postgresql.conf generated by initdb shared_buffers is set to 32MB
> even though there was more available (see below; also ipcs shows
> postgres is (and will be) the only shared memory user). Is this enough
> or maybe it's less than ok?

Chances are it's to low, but the installer doesnt make assumptions that it
will be the sole user of RAM on the box.

> I don't know. What I do know is that MySQL
> 4.0.x uses 500-550 mb RAM for similar access patterns.

Unfortunatly this doesn't mean anything, since depending on the table types
you're using you have to configure MySQL ram usage in significantly different
ways.

> Also,
> max_connections preset to 40 is more than ok for our case. Is
> max_fsm_pages = 204800 OK ? Should I increase it if I expect 15-20 gb
> disk usage by db?
>

You probably need to increase it, the problem is that the amount of data
modification your database will have effects just how high you need this.

> FreeBSD 7.0-BETA3 PostgreSQL 8.3 beta2
>

Beta2? Get with the times man! Beta4 has been out for at least... several
hours at this point.

> sysctl settings at the time initdb ran more or less resembled the
> recommendations given here:
> http://www.postgresql.org/docs/8.3/static/kernel-resources.html#SYSVIPC
> namely:
>
> kern.ipc.shmmax: 134217728
> kern.ipc.shmmin: 1
> kern.ipc.shmall: 32768
> kern.ipc.shmseg: 128
> kern.ipc.shmmni: 192
> kern.ipc.semmns: 60
> kern.ipc.semmsl: 60
> kern.ipc.semmap: 256
> kern.ipc.semvmx: 32767
>
> Maybe I should increase shmall/shmmax and rerun initdb to maximize
> performance of expectedly 25-30 concurrent connections, which are
> "persistent" and hence many of which are idle at the low hours?
>

Nah, initdb is not a tuning device. I'd recommend setting your shared_buffers
to 20% of total RAM for starters. You'll also want to set your
expected_cache_size accordingly. After that, google and benchmarking are
your friends. HTH.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL