Postgresql – Postgres 8.4 VACUUM FULL performance

postgresql

I am trying to run a vacuum full analyze on a postgres 8.4 machine where it appears that autovacuum has not been able to keep up. The database is very large and under heavy load.

The problem started with postgres denying updates because it is out of transactions. My only option was to take our business offline and do a manual vacuum full.

The issue is that it is taking a really long time and I have been looking for a dependable way to speed it up. I don't want to arbitrarily make changes to the configuration files while the database is in an unknown state.

Here is the postgres.conf configuration for the server.

listen_addresses = '*'
port = 5476
max_connections = 1000

### logging
log_destination = 'syslog'

constraint_exclusion = partition

### work_mem for vacuuming
maintenance_work_mem = 128MB        # min 1MB
                                    # DEFAULT 16MB

# -----------------------------
# CHANGE THESE DEPENDING ON SYSTEM
# VALUES BASED ON 2G RAM
# -----------------------------

### RAM * 1/4
shared_buffers = 512MB              # min 128kB or max_connections*16kB
                                  # DEFAULT: 24MB
#### very import. all work happens here, needs to be as big as possible

temp_buffers = 64MB
#### session-local buffers used to access temporary tables on a per-session basis

### not more than RAM / connections
work_mem = 20MB                   # min 64kB
                                  # DEFAULT: 1MB
#### used for sorting in memory

### raise this if log complains or to reduce checkpoints
wal_buffers = 3MB                 # min 32kB
                                  # DEFAULT: 64kB
checkpoint_segments = 16          # in logfile segments, min 1, 16MB each
                                  # DEFAULT: 3
#### stores changes to shared buffer. higher value = fewer checkpoints/flushes

### RAM * 2/3
effective_cache_size = 1365MB
                                  # DEFAULT: 128MB
#### size of largest possible object

### multiplied defaults by 0.2
cpu_tuple_cost = 0.002                  # (same)
cpu_index_tuple_cost = 0.001            # (same)
cpu_operator_cost = 0.0005              # (same)
#### do this. based on older slower hardware.

### milliseconds vacuum will delay when its cost reaches threshold
vacuum_cost_delay = 20            # default = 0

# -----------------------------
# OPTIONAL DEBUG/LOGGING PARAMS
# -----------------------------

### uncomment the following 6 lines to enable query logging
#silent_mode = on
#log_min_duration_statement = 0
#log_duration = on
#log_statement = 'none'

Any ideas on what I can do to get this server back up asap would be greatly appreciated.

Edit

Here is the exact error message we are receiving, sorry about that.

ERROR:  database is not accepting commands to avoid wraparound data loss in database "live"

Best Answer

This particular error comes with a hint mentioned in the doc, in Routine vacuuming (8.4):

ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb" HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".

It's a critical situation which normally should not occur if autovacuum can do its job. When you're back online, you should try to find out why this could have happened (wrong vacuum settings?).

In the meantime, what must be done is simply what the HINT tells: VACUUM without additional parameter. As mentioned in the comments , VACUUM FULL is a different command, even though it's not obvious by its name. It rewrites entirely the tables, is much slower and not desirable in this situation.

More details at https://wiki.postgresql.org/wiki/VACUUM_FULL