Hi! We've got a Postgres 8.1.5 installation with a 60GBish database:
=# select version();
version
------------------------------------------------------------------------
---------------
PostgreSQL 8.1.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 (Gentoo 4.1.1)
(1 row)
=# select pg_size_pretty(pg_database_size('xxx'));
pg_size_pretty
----------------
59 GB
(please don't freak over the word Gentoo! This is Postgres compiled,
by us, using the sources from postgresql.org, not some patched-up
Gentoo version)
Anyways, we run a VACUUM FULL ANALYZE VERBOSE every night, and it's
now taking 3+ hours to complete. We also have autovacuum turned on
with default settings. This 3+ hour vacuum time is cutting into our
production hours. :(
Note that while we're vacuuming Postgres is running in a single user
mode and not listening. There's no other connections to the database.
The hardware itself is a dual-core Intel 2gHz w/ 4G ram and adequate
space in a hardware Raid 5 configuration. I realize Raid 5 isn't
ideal, but in the general use cases of our database it doesn't
noticeably impact performance.
How can we begin to cut down the vacuum time? My first thought is
simply change the schedule to run weekly (or biweekly) since we're
also running autovacuum. Are there any other Postgres configuration
changes that might help to improve vacuum performance?
The settings we've explicitly set are:
max_connections = 256
shared_buffers = 40000
temp_buffers = 5000
work_mem = 32768
maintenance_work_mem = 65535
max_fsm_pages = 120000
fsync = on
wal_buffers = 16
effective_cache_size = 5000
log_connections = on
log_duration = off
log_line_prefix = '%m [xid=%x] [%p]: '
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
autovacuum = on
Thanks in advance for any suggestions!
eric
