Greetings all,
I'm trying to wrap my head around updating my configuration files, which have been probably fairly static since before 8.4.
I've got some beefy hardware but have some tables that are over 57GB raw and end up at 140GB size after indexes are applied. One index creation took 7 hours today. So it's time to dive in and see where i'm lacking and what I should be tweaking.
I looked at pgtune again today and the numbers it's spitting out took me back, they are huge. From all historical conversations and attempts a few of these larger numbers netted reduced performance vs better performance (but that was on older versions of Postgres).
So I come here today to seek out some type of affirmation that these numbers look good and I should look at putting them into my config, staged and or in one fell swoop.
I will start at the same time migrating my config to the latest 9.3 template...
Postgres Version: 9.3.4, Slony 2.1.3 (migrating to 2.2).
CentOS 6.x, 2.6.32-431.5.1.el6.x86_64
Big HP Boxen.
32 core, 256GB of Ram DB is roughly 175GB in size but many tables are hundreds of millions of rows.
The pgtune configurations that were spit out based on the information above;
max_connections = 300
shared_buffers = 64GB
effective_cache_size = 192GB
work_mem = 223696kB
maintenance_work_mem = 2GB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
my current configuration:
max_connections = 300
shared_buffers = 2000MB
effective_cache_size = 7GB
work_mem = 6GB
maintenance_work_mem = 10GB <-- bumped this to try to get my reindexes done
checkpoint_segments = 100
#wal_buffers = 64kB
#default_statistics_target = 10
Here is my complete configuration (This is my slon slave server, so fsync is off and archive is off, but on my primary fsync=on and archive=on).
listen_addresses = '*'
max_connections = 300
shared_buffers = 2000MB
max_prepared_transactions = 0
work_mem = 6GB
maintenance_work_mem = 10GB
fsync = off
checkpoint_segments = 100
checkpoint_timeout = 10min
checkpoint_warning = 3600s
wal_level archive
archive_mode = off
archive_command = 'tar -czvpf /pg_archives/%f.tgz %p'
archive_timeout = 10min
random_page_cost = 2.0
effective_cache_size = 7GB
log_destination = 'stderr'
logging_collector = on
log_directory = '/data/logs'
log_filename = 'pgsql-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_min_messages = info
log_min_duration_statement = 15s
log_line_prefix = '%t %d %u %r %p %m'
log_lock_waits = on
log_timezone = 'US/Pacific'
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 2000
datestyle = 'iso, mdy'
timezone = 'US/Pacific'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
deadlock_timeout = 5s
Also while it doesn't matter in 9.3 anymore apparently my sysctl.conf has
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
And PGTune recommended;
kernel.shmmax=137438953472
kernel.shmall=33554432
Also of note in my sysctl.conf config:
vm.zone_reclaim_mode = 0
vm.swappiness = 10
Thanks for the assistance, watching these index creations crawl along when you know you have so many more compute cycles to provide makes one go crazy.'
Tory
