X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9CAA9D1BA98 for ; Fri, 23 Apr 2004 10:31:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16875-01 for ; Fri, 23 Apr 2004 10:31:21 -0300 (ADT) Received: from mail.astudios.com (unknown [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 1EBB5D1B98D for ; Fri, 23 Apr 2004 10:31:16 -0300 (ADT) thread-index: AcQpN7xPOA4ilzVLQ6yUbo1GCcCURw== Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132 Received: from [10.4.2.155] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.0); Fri, 23 Apr 2004 09:34:42 -0400 From: "Chris Hoover" To: Subject: Help with performance problems Date: Fri, 23 Apr 2004 09:31:17 -0400 User-Agent: KMail/1.6.1 Cc: MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message-ID: <200404230931.17850.revoohc@sermonaudio.com> X-OriginalArrivalTime: 23 Apr 2004 13:34:43.0156 (UTC) FILETIME=[BC416940:01C42937] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/403 X-Sequence-Number: 6703 I need some help. I have 5 db servers running our database servers, and th= ey=20 all are having various degrees of performance problems. The problems we ar= e=20 experiencing are: 1. General slowness 2. High loads All of our db's are running on Dell Poweredge 2650 with 2 P4 Xeons (2.8 ->= =20 3.06 GHz) with 8 to 12 GB of memory. The databases are running on attached= =20 Dell Powervault 220s running raid5. The databases were created and taken into production before I started worki= ng=20 here and are very flat. Most of the major tables have a combined primary k= ey=20 using an int field and a single char field. There are some additional=20 indexes on some tables. Most queries I see in the logs are running at les= s=20 than .01 seconds with many significantly slower. We are trying to narrow down the performance problem to either the db or th= e=20 hardware. As the dba, I need to try and get these db's tuned to the best= =20 possible way considering the current db state. We are in the beginning of = a=20 complete db redesign and application re-write, but the completion and=20 deployment of the new db and app are quite a ways off. Anyway, we are running the following: PE 2650 w/ 2 cpus (2.8-3.06) - HT on 8-12 GB memory OS on raid 0 DB's on Powervaults 220S using raid 5 (over 6 disks) Each Postgresql cluster has 2 db up to almost 170db's (project to level out= =20 the num of db's/cluster is being started) DB's are no bigger than a few GB in size (largest is about 11GB according t= o a=20 du -h) Running RH ES 2.1 Here is the postgresql.conf from the server with the 11GB db: max_connections =3D 64 shared_buffers =3D 32768 # 256MB=3D32768(buffs)*8192(bytes/buff) max_fsm_relations =3D 1000 # min 10, fsm is free space map, ~40 bytes max_fsm_pages =3D 10000 # min 1000, fsm is free space map, ~6 bytes sort_mem =3D 4096 # 256MB=3D4096(bytes/proc)*64(procs or conns) checkpoint_segments =3D 16 # in logfile segments, min 1, 16MB each checkpoint_timeout =3D 30 # range 30-3600, in seconds effective_cache_size =3D 131072 # typically 8KB each log_connections =3D true log_pid =3D true log_statement =3D true log_duration =3D true log_timestamp =3D true stats_start_collector =3D true stats_reset_on_server_start =3D true stats_command_string =3D true stats_row_level =3D true stats_block_level =3D true LC_MESSAGES =3D 'en_US' LC_MONETARY =3D 'en_US' LC_NUMERIC =3D 'en_US' LC_TIME =3D 'en_US' Here is top (server running pretty good right now) 9:28am up 25 days, 16:02, 2 users, load average: 0.54, 0.33, 0.22 94 processes: 91 sleeping, 3 running, 0 zombie, 0 stopped CPU0 states: 64.0% user, 0.1% system, 0.0% nice, 34.0% idle CPU1 states: 29.0% user, 9.0% system, 0.0% nice, 60.0% idle CPU2 states: 2.0% user, 0.1% system, 0.0% nice, 96.0% idle CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle Mem: 7720072K av, 7711648K used, 8424K free, 265980K shrd, 749888K bu= ff Swap: 2096440K av, 22288K used, 2074152K free 6379304K=20 cached Here is top from another server (with the most db's):=20 9:31am up 25 days, 16:05, 5 users, load average: 2.34, 3.39, 4.28 147 processes: 145 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 6.0% user, 1.0% system, 0.0% nice, 91.0% idle CPU1 states: 9.0% user, 4.0% system, 0.0% nice, 85.0% idle CPU2 states: 9.0% user, 3.0% system, 0.0% nice, 86.0% idle CPU3 states: 9.0% user, 4.0% system, 0.0% nice, 85.0% idle Mem: 7721096K av, 7708040K used, 13056K free, 266132K shrd, 3151336K bu= ff Swap: 2096440K av, 24208K used, 2072232K free 3746596K=20 cached Thanks for any help/advice, Chris