Date: Thu, 24 Jun 2010 07:47:34 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Janning <ml@planwerk6.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Write performance
Message-ID: <20100624124734.GH23529@aart.is.rice.edu>
References: <201006241443.33296.ml@planwerk6.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <201006241443.33296.ml@planwerk6.de>
User-Agent: Mutt/1.5.16 (2007-06-09)

On Thu, Jun 24, 2010 at 02:43:33PM +0200, Janning wrote:
> Hi,
> 
> at the moment we encounter some performance problems with our database server.
> 
> We have a 12 GB RAM machine with intel i7-975 and using
> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)" 
> One disk for the system and WAL etc. and one SW RAID-0 with two disks for  
> postgresql data. Our database is about 24GB.
> 
> Our munin graph reports at 9:00 a clock writes of 3000 blocks per second and 
> reads of about 1000 blocks per second on our disk which holds the data 
> directories of postgresql (WAL are on a different disk)
> 
> 3000 blocks ~ about 3 MB/s write
> 1000 blocks ~ about 1 MB/s read
> 
> At the same time we have nearly 50% CPU I/O wait and only 12% user CPU load 
> (so 4 of 8 cpu cores are in use for io wait)
> 
> We know, its a poor man disk setup (but we can not find a hoster with rather 
> advanced disk configuration at an affordable price). Anyway, we ran some tests 
> on it:
> 
> 
> # time sh -c "dd if=/dev/zero of=bigfile bs=8k count=3000000 && sync"
> 3000000+0 records in
> 3000000+0 records out
> 24576000000 bytes (25 GB) copied, 276.03 s, 89.0 MB/s
> 
> real	4m48.658s
> user	0m0.580s
> sys	0m51.579s
> 
> # time dd if=bigfile of=/dev/null bs=8k
> 3000000+0 records in
> 3000000+0 records out
> 24576000000 bytes (25 GB) copied, 222.841 s, 110 MB/s
> 
> real	3m42.879s
> user	0m0.468s
> sys	0m18.721s
> 
> 
> 
> Of course, writing large chunks is quite a different usage pattern. But I am 
> wondering that writing 3MB/s and reading 1 MB/s seams to be a limit if i can 
> run a test with 89 MB/s writing and 110MB/s reading.
> 
> Can you give some hints, if this numbers seems to be reasonable? 
> 
> kind regards
> Janning
> 

Yes, these are typical random I/O versus sequential I/O rates for
hard drives. Your I/O is extremely under-powered relative to your
CPU/memory. For DB servers, many times you need much more I/O
instead.

Cheers,
Ken