Re: Vacuums on large busy databases

On Thu, 2006-09-14 at 11:23 -0400, Francisco Reyes wrote:
> My setup:
> Freebsd 6.1
> Postgresql 8.1.4
> Memory: 8GB
> SATA Disks
>
> Raid 1 10 spindles (2 as hot spares)
> 500GB disks (16MB buffer), 7200 rpm
> Raid 10
>
> Raid 2 4 spindles
> 150GB 10K rpm disks
> Raid 10
>
> shared_buffers = 10000

Why so low? You have a lot of memory, and shared_buffers are an
important performance setting. I have a machine with 4GB of RAM, and I
found my best performance was around 150000 shared buffers, which is a
little more than 1GB.

The default value of 1000 was chosen so that people who use PostgreSQL
only incidentally among many other programs do not notice an impact on
their system. It should be drastically increased when using PostgreSQL
on a dedicated system, particularly with versions 8.1 and later.

Also, a VACUUM helps a table that gets UPDATEs and DELETEs. If you're
doing mostly inserts on a big table, there may be no need to VACUUM it 3
times per day. Try VACUUMing the tables that get more UPDATEs and
DELETEs more often, and if a table has few UPDATEs/DELETEs, VACUUM it
only occasionally. You can run ANALYZE more frequently on all the
tables, because it does not have to read the entire table and doesn't
interfere with the rest of the operations.

Regards,
Jeff Davis