The following bug has been logged online:
Bug reference: 5774
Logged by: Bala Murugan
Email address: b2m@a-cti.com
PostgreSQL version: 8.3.7
Operating system: openSUSE 10.3 (X86-64) - Kernel \r (\l).
Description: VACCUM & REINDEX kills production environement
Details:
Iam running postgres 8.3 version for more than 2 yrs on Amazon EC2 Instance,
in recent days Vaccum and reindex make the application down for more than
2hrs. I am not sure this because of my configuration or postgres.
My hardware details : High-CPU Extra Large Instance
7 GB of memory
20 EC2 Compute Units (8 virtual cores with 2.5 EC2 Compute Units each)
1690 GB of instance storage
64-bit platform
conf :
## Connections
port = 5432
max_connections = 1000
listen_addresses = '*'
## Memory usage
shared_buffers = '1 GB'
maintenance_work_mem = '448 MB'
max_fsm_pages = 220000
max_fsm_relations = 1000
max_prepared_transactions = 0
## Performance
checkpoint_segments = 16
effective_cache_size = '5 GB'
## To fix bad plans caused by partitioning 3/8/2010
random_page_cost = 6
## Logging
# Where and how:
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
#log_line_prefix = '%t %r[%p]: [%l-1]'
log_line_prefix = 'user=%u,db=%d '
# What:
log_min_duration_statement = 200
log_connections = on
log_disconnections = on
log_duration = off
log_statement = 'ddl'
# Cleanup
log_rotation_age = '1d'
log_rotation_size = 0
log_temp_files = 0
## Statistics
#default_statistics_target = 700
default_statistics_target = 100
## Autovacuum
#autovacuum = on
#autovacuum_vacuum_scale_factor = 0.1
## Bulkloading - keep these on!
fsync = on
full_page_writes = on
# We're partitioning the historysystem.history table
constraint_exclusion = on
checkpoint_completion_target = 0.9
wal_buffers = '8 MB'
# Changed 3/2/2010 in response to historySystem.history queries
work_mem = '44 MB'
statement_timeout = 0
thanks,
Bala