From: Greg Smith <greg@2ndquadrant.com>
To: Krzysztof Kardas <krzychk2@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL with Zabbix - problem of newbe
Date: Fri, 09 Apr 2010 12:03:41 -0400
Message-ID: <4BBF4FDD.3090008@2ndquadrant.com> (raw)
In-Reply-To: <v2w4ff8a7a31004080223mec26e053n69a2b80f24328f37@mail.gmail.com>
References: <v2w4ff8a7a31004080223mec26e053n69a2b80f24328f37@mail.gmail.com>
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@2ndQuadrant.com www.2ndQuadrant.us
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, krzychk2@gmail.com
Subject: Re: PostgreSQL with Zabbix - problem of newbe
In-Reply-To: <4BBF4FDD.3090008@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox