Re: Now I am back, next thing. Final PGS tuning.

On Wed, Apr 8, 2009 at 10:23 AM, Bill Moran <wmoran(at)potentialtech(dot)com>wrote:

> In response to Jennifer Trey <jennifer(dot)trey(at)gmail(dot)com>:
>
>
> > *maintenance_work_mem = 16384 *

If your vacuums and / or create index are taking ages, considering a
higher value here may be useful. I would need to know more about the
database before suggesting though. I have a gut feeling that this may be a
good starting place.

> >
> > *work_mem = 1024 # I think this is kb. Way to low, right? What is a
> better
> > value?*
>
> Be careful with work_mem. For every connection to the database, it is
possible to consume up to work_mem.... so:

If your application makes 100 connections to the database and your
work_mem =1GB, IF you are running big nasty order by's... you would be
swapping 100 GB. This is a pretty extreme example, but I think it's
important.

As a rule of thumb, I like to start with 1 - 2 Mb and almost always leave
it there. If you're doing joins and order by's on many many gigs later on,
then it could be an issue.

>
> > *shared_buffers = 1024 # min 128kB or max_connections*16kB ## Also to
> low.
> > Right? I've got 3GB to work with!*
>
> Assuming that's equating to 1G, then the value is about right. Common
> best practice is to set this value to 1/4 - 1/3 of the memory available
> for PostgreSQL. You're saying you'll have ~3G for PG, so 1G is about
> right to start with.

The idea here is to be conservative with shared_buffers and then use
effective_cache_size to tell the optimizer how much ram the OS can use for
buffering data. 1 GB is a good start place.

>
>
> Once the system is up and running, you can install pg_buffercache to
> monitor usage and help tune it.

Good advice

>
>
> > *wal_buffers = 256 # Also kB...*
> >
> > Please give your thoughts. I was also wondering about the Vacuum, force
> > reindex and stuff. Are those things good to run once in a while? Force
> > sounds a little brutal though!
>
> Turn on autovacuum. I've found it's the best way to go in 99% of installs
> (the corner cases being servers that have _very_ predictable workloads ...
> in which case explicit, scheduled vacuums are better).

+ 1

>
>
> REINDEXing is an occasional topic of discussion. Doing it occasionally
> definitely saves disk space on frequently updated databases, but the
> impact (if any) on performance is a subject for debate. I've yet to see
> any drastic performance improvement from REINDEXing, but if you've got
> obvious off-peak times (i.e., if nobody uses the system over weekends or
> something) it probably doesn't hurt to reindex everything on a regular
> schedule. Don't obsess over it, though.

Just remember that the REINDEX command is a locking command, so using
'create index concurrently' is recommended.

You can also use the pg_stat_all_indexes table to look at index scans vs.
tuples being read, this can sometimes hint at index 'bloat'. I would also
recommend pg_stattuple which has a pg_statindex function for looking at
index fragmentation.

--Scott