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@2ndQuadrant.com   www.2ndQuadrant.us