Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5A46462E0FE for ; Tue, 6 Jan 2009 12:51:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97514-08 for ; Tue, 6 Jan 2009 12:51:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from a.mail.sonomait.com (a.mail.sonomait.com [64.142.123.30]) by mail.postgresql.org (Postfix) with ESMTP id 6A81363228D for ; Tue, 6 Jan 2009 12:51:12 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by a.mail.sonomait.com (Postfix) with ESMTP id 536AC16457E for ; Tue, 6 Jan 2009 08:51:09 -0800 (PST) X-Virus-Scanned: amavisd-new at Received: from a.mail.sonomait.com ([127.0.0.1]) by localhost (a.mail.sonomait.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id eK-IjSnFY7HO for ; Tue, 6 Jan 2009 08:51:07 -0800 (PST) Received: from [192.168.1.33] (ppp-217-198.20-151.libero.it [151.20.198.217]) by a.mail.sonomait.com (Postfix) with ESMTP id 13CD21644EE for ; Tue, 6 Jan 2009 08:51:05 -0800 (PST) Message-ID: <49638BE9.2030701@gmail.com> Date: Tue, 06 Jan 2009 17:50:49 +0100 From: Stefano Nichele User-Agent: Thunderbird 2.0.0.19 (Windows/20081209) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: understanding postgres issues/bottlenecks Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/12 X-Sequence-Number: 32291 Hi list, I would like to ask your help in order to understand if my postgresql server (ver. 8.2.9) is well configured. It's a quad-proc system (32 bit) with a 6 disk 1+0 RAID array and 2 separate disks for the OS and write-ahead logs with 4GB of RAM. I don't know what is the best info to help me and so I start with some vmstat information: > vmstat -n 30 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 0 23 84 129968 25060 3247860 0 0 78 50 0 2 17 5 33 45 0 1 24 84 124204 25136 3257100 0 0 3037 1154 3359 7253 25 7 6 62 0 2 30 84 124704 25136 3256344 0 0 3004 1269 3553 7906 33 9 7 51 0 0 25 84 125784 24956 3253344 0 0 3357 773 3163 6454 17 4 10 68 0 0 2 84 125744 25236 3258996 0 0 3186 567 3125 6425 24 6 21 50 0 3 7 84 124948 25500 3260088 0 0 1829 535 2706 4625 18 3 54 25 0 5 0 84 124976 25624 3259112 0 0 2067 647 3050 6163 26 6 41 27 0 0 7 84 123836 25644 3260760 0 0 2239 1065 3289 8654 27 7 38 28 0 These are gathered loadavg info for the same period: 29.57 29.53 33.52 1/231 12641 29.54 29.63 33.31 1/226 12678 24.43 28.45 32.69 1/223 12696 12.31 24.17 30.95 4/223 12706 At the moment as average there are about 120/150 connections and this is my postgresql.conf file listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 400 # (change requires restart) ssl = off # (change requires restart) password_encryption = on shared_buffers = 32MB # min 128kB or max_connections*16kB max_prepared_transactions = 0 # can be 0 or more work_mem = 1MB # min 64kB maintenance_work_mem = 256MB # min 1MB max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each fsync = on # turns forced synchronization on or off full_page_writes = on # recover from partial page writes wal_buffers = 8MB # min 32kB checkpoint_segments = 56 # in logfile segments, min 1, 16MB each enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on cpu_tuple_cost = 0.003 # same scale as above cpu_index_tuple_cost = 0.001 # same scale as above cpu_operator_cost = 0.0005 # same scale as above effective_cache_size = 3GB geqo_threshold = 14 log_destination = 'stderr' # Valid values are combinations of redirect_stderr = on # Enable capturing of stderr into log log_directory = 'pg_log' # Directory where log files are written log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern. log_truncate_on_rotation = on # If on, any existing log file of the same log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements log_statement = 'none' # none, ddl, mod, all autovacuum = on # enable autovacuum subprocess? stats_start_collector = on # must be 'on' for autovacuum stats_row_level = on # must be 'on' for autovacuum statement_timeout = 150000 datestyle = 'iso, mdy' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting deadlock_timeout = 5s escape_string_warning = off standard_conforming_strings = on Cheers and thanks a lot in advance. Let me know if other info is useful. Ste -- Stefano Nichele Funambol Chief Architect Funambol :: Open Source Mobile'We' for the Mass Market :: http://www.funambol.com