Re: how much mem to give postgres?

>Josh Close
> I'm trying to figure out what I need to do to get my postgres server
> moving faster. It's just crawling right now. It's on a p4 HT with 2
> gigs of mem.

....and using what version of PostgreSQL are you using? 8.0beta, I hope?

> I was thinking I need to increase the amount of shared buffers, but
> I've been told "the sweet spot for shared_buffers is usually on the
> order of 10000 buffers". I already have it set at 21,078. If you have,
> say 100 gigs of ram, are you supposed to still only give postgres
> 10,000?

Thats under test currently. My answer would be, "clearly not", others
differ, for varying reasons.

> Also, do I need to up the shmmax at all? I've used the formula "250 kB
> + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity"
> at
http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html#SYSVIPC
> but it's never quite high enough, so I just make sure it's above the
> amount that the postgres log says it needs.

shmmax isn't a tuning parameter for PostgreSQL, its just a limit. If you get
no error messages, then its high enough.

> Are there any other settings I should be concerned with? I've heard
> about the effective_cache_size setting, but I haven't seen anything on
> what the size should be.

wal_buffers if the databases are heavily updated.

> Any help would be great. This server is very very slow at the moment.
>

Try *very fast disks*, especially for the logs.

Best regards, Simon Riggs