pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Evan Reiser <evan.reiser@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: High IOWAIT times, low iops? Can't figure out what the bottleneck is...
Date: Tue, 3 Jul 2007 13:44:49 -0400
Message-ID: <5f33a0cb0707031044k71811735ye51a3b0dc25638e2@mail.gmail.com> (raw)
In-Reply-To: <5f33a0cb0707031019n7ffd4929p4b558304d47c10d@mail.gmail.com>
References: <5f33a0cb0707031019n7ffd4929p4b558304d47c10d@mail.gmail.com>

Here is the table structure for the table with all the inserts

id(PK)        integer
dateinserted  datetime without timezone
dateexamined  datetime without timezone
lockedby      integer
done          boolean
failed        boolean
invalid       boolean


>select * from table limit 1;
id      | done | failed |     dateinserted    | dateexamined   | lockedby |
invalid
--------+------+--------+---------------------+----------------+----------+---------
3130902 | f    | f      | 1900-01-01 00:00:00 |                |        0 |
f

There is a clustered index on dateinserted,
+ a non clustered index on id
+ a non clustered index on done,failed,invalid
+ a non clustered index on lockedby

There are no triggers



On 7/3/07, Evan Reiser <evan.reiser@gmail.com> wrote:
>
> 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
> 5-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.97      0.00    0.82         89.69     0.00      8.52
>
> Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
> sda              85.40         0.18         0.72
> 0                3
> sdb               0.00         0.00         0.00
> 0                0
> sdc              30.43         0.00         0.76
> 0                3
> sdd              93.91         0.71         0.12                 3
>        0
>
>
>
>
> sda = 2x320GB 7200rpm in RAID1 (operating system)
> sdc = 2x150GB 10krpm in RAID1    (transaction log is on this array)
> sdd = 6x150GB 10krpm in RAID 10 (database is on the array)
>
> we're using ReiserFS on each of the arrays as the file system
> OS is debian Etch x64
>
> raid controller = 3ware 9650 12port - 256MB cache
>
> 8GB RAM, core 2 duo - quad core 2.4ghz 8MB L2 Cache
>
> 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.  If i try to copy a big file (1-2GB) over to
> the array, the array hits 300MB/s sustained, it can burst write files <
> 256mb AT 800MB/S
>
> Here's some of our settings
>
>
> shared_buffers = 1024MB
> temp_buffers = 32MB
> max_prepared_transactions = 50
> work_mem = 64MB
> maintenance_work_mem = 256MB
> max_stack_depth = 7MB
> max_fsm_pages = 512000
>
> vacuum_cost_delay = 20ms
>
> fsync = on
> wal_buffers = 1MB
> checkpoint_segments = 32
>
> effective_cache_size = 4128MB
>
> If you guys have any suggestions it would be greatly appreciated
>
> -Evan Reiser
>

view thread (2+ messages)

Message-ID: <5f33a0cb0707031044k71811735ye51a3b0dc25638e2@mail.gmail.com>
Permalink:  ../5f33a0cb0707031044k71811735ye51a3b0dc25638e2@mail.gmail.com/
Also on:    postgresql.org/message-id/5f33a0cb0707031044k71811735ye51a3b0dc25638e2@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: evan.reiser@gmail.com
  Subject: Re: High IOWAIT times, low iops? Can't figure out what the bottleneck is...
  In-Reply-To: <5f33a0cb0707031044k71811735ye51a3b0dc25638e2@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox