Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7F7F29FBF07 for ; Wed, 27 Jun 2007 21:38:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 15037-05 for ; Wed, 27 Jun 2007 21:38:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by postgresql.org (Postfix) with ESMTP id 9A7D69FBE72 for ; Wed, 27 Jun 2007 21:38:13 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id k40so547101wah for ; Wed, 27 Jun 2007 17:38:12 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=iWFCsP0WtwXt/tyAk0AxJul+ZxWKq3XGi+7vkys8Ha09NVSoB/fI7io9F2vitC58zUK787f2vAOhYJm1Vhu6KSYLywJNBKwND6zu5OzYWHgsQPnI/L5Sqq2Ad0l2uIDbJ3zi1A/dp4PoU4TZmIpJXfMJwhnNGfAyZaosVghAt4o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=Z3MPk0jOz2fOuX+hIpT2Rl24PgmTC4hn/sLFAEeH8AMpcTG7WEGN/awYXz0JBgEHVtZPMu5HNn5Ho6DyUxC5m9XOZ3kTcmmYdjr+R5gcDc54PMKwGao9D48kZq6Ghql4lnn+0oyXgyc/ZUgade7Urw/mYC9JgUg96p+ZWkvllwA= Received: by 10.114.120.1 with SMTP id s1mr992908wac.1182991091982; Wed, 27 Jun 2007 17:38:11 -0700 (PDT) Received: by 10.114.123.20 with HTTP; Wed, 27 Jun 2007 17:38:11 -0700 (PDT) Message-ID: <5f33a0cb0706271738x1cd5dacdpaf925120f7a1130c@mail.gmail.com> Date: Wed, 27 Jun 2007 20:38:11 -0400 From: "Evan Reiser" To: pgsql-performance@postgresql.org Subject: High IOWAIT times, low iops? Need Help with configuration In-Reply-To: <5f33a0cb0706271614q2bd34f58qb87a307f6424fba6@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_46487_16233841.1182991091964" References: <5f33a0cb0706271614q2bd34f58qb87a307f6424fba6@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.314 tagged_above=0 required=5 tests=AWL=0.498, BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/608 X-Sequence-Number: 25641 ------=_Part_46487_16233841.1182991091964 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I was wondering if you guys have some suggested settings for our server, i think we are not hardware limited but the configureation is set up incorrectly. For some reason our database seems to have trouble handling 10+ inserts per second which seems to be a pretty trivial load for this hardware, we're seeing very high %iowait, this is a pretty typical output for #iostat -m 5 avg-cpu: %user %nice %system %iowait %steal %idle 0.41 0.00 0.41 96.28 0.00 2.90 Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn sda 90.63 0.08 0.56 0 2 sdc 0.00 0.00 0.00 0 0 sdd 94.09 0.19 1.74 0 8 sda = 2x320GB 7200rpm in RAID1 sdc = 2x150GB 10krpm in RAID1 (transaction log is on this array) sdd = 6x150GB 10krpm in RAID 10 (database is on the array) raid controller = 3ware 9650 12port - 256MB cache 8GB RAM, core 2 duo - quad core it would seem like the io subsystem is the limiting factor, but i feel like we should be barely hitting a wall, you can see from the example its writing < 2MB/s to the array Here's some of our settings shared_buffers = 256MB # min 128kB or max_connections*16kB temp_buffers = 32MB # min 800kB max_prepared_transactions = 50 # can be 0 or more work_mem = 32MB # min 64kB maintenance_work_mem = 32MB # min 1MB max_stack_depth = 7MB # min 100kB max_fsm_pages = 512000 # min max_fsm_relations*16, 6 bytes fsync = off # turns forced synchronization on or off If you guys have any suggestions it would be greatly appreciated ------=_Part_46487_16233841.1182991091964 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

I was wondering if you guys have some suggested settings for our server, i think we are not hardware limited but the configureation is set up incorrectly.  For some reason our database seems to have trouble handling 10+ inserts per second which seems to be a pretty trivial load for this hardware, we're seeing very high %iowait, this is a pretty typical output for #iostat -m 5

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
                 0.41    0.00     0.41       96.28       0.00     2.90

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
sda              90.63         0.08         0.56          0          2
sdc               0.00         0.00         0.00          0          0
sdd              94.09         0.19         1.74          0          8


sda = 2x320GB 7200rpm in RAID1
sdc = 2x150GB 10krpm in RAID1    (transaction log is on this array)
sdd = 6x150GB 10krpm in RAID 10 (database is on the array)

raid controller = 3ware 9650 12port - 256MB cache

8GB RAM, core 2 duo - quad core

it would seem like the io subsystem is the limiting factor, but i feel like we should be barely hitting a wall, you can see from the example its writing < 2MB/s to the array

Here's some of our settings

shared_buffers = 256MB                  # min 128kB or max_connections*16kB
temp_buffers = 32MB                     # min 800kB
max_prepared_transactions = 50          # can be 0 or more
work_mem = 32MB                         # min 64kB
maintenance_work_mem = 32MB             # min 1MB
max_stack_depth = 7MB                   # min 100kB

max_fsm_pages = 512000          # min max_fsm_relations*16, 6 bytes

fsync = off                             # turns forced synchronization on or off


If you guys have any suggestions it would be greatly appreciated

------=_Part_46487_16233841.1182991091964--