Re: PostgreSQL with Zabbix - problem of newbe

Off-list message that should have made it onto here, from Krzysztof:

I have changed PostgreSQL to 8.3. I think that the database is really working faster. New settings:

name | unit | current_setting
---------------------------------+------+-------------------
autovacuum | | on
autovacuum_analyze_scale_factor | | 0.1
autovacuum_analyze_threshold | | 5000
autovacuum_freeze_max_age | | 200000000
autovacuum_max_workers | | 3
autovacuum_naptime | s | 1min
autovacuum_vacuum_cost_delay | ms | 20ms
autovacuum_vacuum_cost_limit | | -1
autovacuum_vacuum_scale_factor | | 0.2
autovacuum_vacuum_threshold | | 5000
checkpoint_segments | | 32
constraint_exclusion | | off
deadlock_timeout | ms | 1min
default_statistics_target | | 100
from_collapse_limit | | 8
join_collapse_limit | | 8
log_autovacuum_min_duration | ms | 0
maintenance_work_mem | kB | 256MB
max_connections | | 400
max_fsm_pages | | 2048000
max_locks_per_transaction | | 64
max_prepared_transactions | | 100
max_stack_depth | kB | 20MB
random_page_cost | | 4
shared_buffers | 8kB | 760MB
statement_timeout | ms | 0
temp_buffers | 8kB | 32768
vacuum_cost_delay | ms | 0
vacuum_cost_limit | | 200
vacuum_cost_page_dirty | | 20
vacuum_cost_page_hit | | 1
vacuum_cost_page_miss | | 10
wal_buffers | 8kB | 16MB
work_mem | kB | 1600MB

I trimmed the above a bit to focus on the performance related
parameters. Just doing the 8.3 upgrade has switched over to sane
autovacuum settings now, which should improve things significantly.

The main problem with this configuration is that work_mem is set to an
unsafe value--1.6GB. With potentially 400 connections and about 2GB of
RAM free after starting the server, work_mem='4MB' is as large as you
can safely set this.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us