Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D4A61633F70 for ; Fri, 9 Apr 2010 13:04:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65556-09 for ; Fri, 9 Apr 2010 16:03:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9]) by mail.postgresql.org (Postfix) with ESMTP id 6270B633F01 for ; Fri, 9 Apr 2010 13:03:47 -0300 (ADT) Received: from [192.168.0.105] (pool-96-244-14-10.bltmmd.fios.verizon.net [96.244.14.10]) by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis) id 0M21ZH-1NfhUp1njf-00tK2I; Fri, 09 Apr 2010 18:03:45 +0200 Message-ID: <4BBF4FDD.3090008@2ndquadrant.com> Date: Fri, 09 Apr 2010 12:03:41 -0400 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Krzysztof Kardas CC: pgsql-performance@postgresql.org Subject: Re: PostgreSQL with Zabbix - problem of newbe References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V01U2FsdGVkX1+or9bpSe2nczIYLgDnuDtVoyGS+yLpSneedio N5sw08OqQ8WbDc6RIPEl+UioM60cabnc+0FsYkVr9RWl740XOU HVfQ/3mofVK1aLyHPFB1U7ajfzu59nO X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=none X-Spam-Level: X-Archive-Number: 201004/117 X-Sequence-Number: 38367 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