Re: shared_buffers and shmmax what are the max recommended values?

On Fri, 7 Mar 2008, Anton Melser wrote:

> There is actually quite a bit of write (at least the dump is increasing
> far more than what is being added manually by content writers... and I'm
> not even certain where it is coming from but that is another story!)

If you look at pg_stat_user_tables regularly that should give you an idea
what's being added/updated/deleted.

> yes checkpoint_segments is at the default... if I increase to 10 or so
> will that be better?

There will be less checkpoints, which may be better for you. But each
checkpoint could be doing more work, so they will be more disruptive,
which can be worse. It's not a parameter you can increase and that will
always be an improvement.

Normally people doing write-heavy work set that into the 30-100 range.
You will use more disk space for the WAL files used by the server, and
recovery from a crash will take longer as well. The default of 3 keeps
WAL files at a total of about 112MB; increasing to 10 raises that to
336MB, and at 30 you can expect to have 1GB of WAL files around on the
primary server.

> btw, we have a warm standby via wal copies if that makes a difference...

Changing checkpoint_segments doesn't alter how often WAL files are moved
over, but it will increase how many of them you need to keep around on the
secondary in order to rebuild the server after a crash. You should be
careful making changes here until you understand how all that fits
together.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD