pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWrite performance
9+ messages / 7 participants
[nested] [flat]
* Write performance
@ 2010-06-24 12:43 Janning <ml@planwerk6.de>
2010-06-24 12:47 ` Re: Write performance Kenneth Marshall <ktm@rice.edu>
2010-06-24 12:53 ` Re: Write performance Matthew Wakeling <matthew@flymine.org>
2010-06-24 13:57 ` Re: Write performance Greg Smith <greg@2ndquadrant.com>
0 siblings, 3 replies; 9+ messages in thread
From: Janning @ 2010-06-24 12:43 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
@ 2010-06-24 12:47 ` Kenneth Marshall <ktm@rice.edu>
2010-06-24 13:30 ` Re: Write performance Janning <ml@planwerk6.de>
2 siblings, 1 reply; 9+ messages in thread
From: Kenneth Marshall @ 2010-06-24 12:47 UTC (permalink / raw)
To: Janning <ml@planwerk6.de>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
2010-06-24 12:47 ` Re: Write performance Kenneth Marshall <ktm@rice.edu>
@ 2010-06-24 13:30 ` Janning <ml@planwerk6.de>
0 siblings, 0 replies; 9+ messages in thread
From: Janning @ 2010-06-24 13:30 UTC (permalink / raw)
To: pgsql-performance
thanks for your quick response, kenneth
On Thursday 24 June 2010 14:47:34 you wrote:
> 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.
[...]
> Your I/O is extremely under-powered relative to your
> CPU/memory. For DB servers, many times you need much more I/O
> instead.
So at the moment we are using this machine as our primary database server:
http://www.hetzner.de/en/hosting/produkte_rootserver/eq9/
Sadly, our hoster is not offering advanced disk setup. Now we have two options
1. buying a server on our own and renting a co-location.
I fear we do not know enough about hardware to vote for this option. I think
for co-locating your own server one should have more knowledge about hardware.
2. renting a server from a hoster with an advanced disk setup.
Can anybody recommend a good hosting solution in germany with a good disk
setup for postgresql?
kind regards
Janning
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
@ 2010-06-24 12:53 ` Matthew Wakeling <matthew@flymine.org>
2010-06-24 13:16 ` Re: Write performance Janning <ml@planwerk6.de>
2 siblings, 1 reply; 9+ messages in thread
From: Matthew Wakeling @ 2010-06-24 12:53 UTC (permalink / raw)
To: Janning <ml@planwerk6.de>; +Cc: pgsql-performance
On Thu, 24 Jun 2010, Janning wrote:
> We have a 12 GB RAM machine with intel i7-975 and using
> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
Those discs are 1.5TB, not 1.5GB.
> One disk for the system and WAL etc. and one SW RAID-0 with two disks for
> postgresql data. Our database is about 24GB.
Beware of RAID-0 - make sure you can recover the data when (not if) a disc
fails.
> 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)
Not quite sure what situation you are measuring these figures under.
However, as a typical figure, let's say you are doing random access with
8kB blocks (as in Postgres), and the access time on your drive is 8.5ms
(as with these drives).
For each drive, you will be able to read/write approximately 8kB /
0.0085s, giving 941kB per second. If you have multiple processes all doing
random access, then you may be able to utilise both discs and get double
that.
> 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.
That's quite right, and typical performance figures for a drive like that.
Matthew
--
Don't criticise a man until you have walked a mile in his shoes; and if
you do at least he will be a mile behind you and bare footed.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
2010-06-24 12:53 ` Re: Write performance Matthew Wakeling <matthew@flymine.org>
@ 2010-06-24 13:16 ` Janning <ml@planwerk6.de>
2010-06-24 13:45 ` Re: Write performance Janning Vygen <vygen@kicktipp.de>
2010-06-25 17:30 ` Re: Write performance Scott Carey <scott@richrelevance.com>
0 siblings, 2 replies; 9+ messages in thread
From: Janning @ 2010-06-24 13:16 UTC (permalink / raw)
To: pgsql-performance
On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
> On Thu, 24 Jun 2010, Janning wrote:
> > We have a 12 GB RAM machine with intel i7-975 and using
> > 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
>
> Those discs are 1.5TB, not 1.5GB.
sorry, my fault.
> > One disk for the system and WAL etc. and one SW RAID-0 with two disks for
> > postgresql data. Our database is about 24GB.
>
> Beware of RAID-0 - make sure you can recover the data when (not if) a disc
> fails.
oh sorry again, its a raid-1 of course. shame on me.
> > 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)
>
> Not quite sure what situation you are measuring these figures under.
> However, as a typical figure, let's say you are doing random access with
> 8kB blocks (as in Postgres), and the access time on your drive is 8.5ms
> (as with these drives).
>
> For each drive, you will be able to read/write approximately 8kB /
> 0.0085s, giving 941kB per second. If you have multiple processes all doing
> random access, then you may be able to utilise both discs and get double
> that.
So with your calculation I have a maximum of 2MB/s random access. So i really
need to upgrade my disk configuration!
> > 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.
>
> That's quite right, and typical performance figures for a drive like that.
thanks for your help.
kind regards
Janning
> Matthew
>
> --
> Don't criticise a man until you have walked a mile in his shoes; and if
> you do at least he will be a mile behind you and bare footed.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
2010-06-24 12:53 ` Re: Write performance Matthew Wakeling <matthew@flymine.org>
2010-06-24 13:16 ` Re: Write performance Janning <ml@planwerk6.de>
@ 2010-06-24 13:45 ` Janning Vygen <vygen@kicktipp.de>
2010-06-24 17:02 ` Re: Write performance Jesper Krogh <jesper@krogh.cc>
1 sibling, 1 reply; 9+ messages in thread
From: Janning Vygen @ 2010-06-24 13:45 UTC (permalink / raw)
To: pgsql-performance
On Thursday 24 June 2010 15:16:05 Janning wrote:
> On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
> > On Thu, 24 Jun 2010, Janning wrote:
> > > We have a 12 GB RAM machine with intel i7-975 and using
> > > 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 TB)"
> > >
> > For each drive, you will be able to read/write approximately 8kB /
> > 0.0085s, giving 941kB per second. If you have multiple processes all
> > doing random access, then you may be able to utilise both discs and get
> > double that.
>
> So with your calculation I have a maximum of 2MB/s random access. So i
> really need to upgrade my disk configuration!
i was looking at tomshardware.com and the fastest disk is
Maxtor Atlas 15K II * 8K147S0,SAS,147 GB, 16 MB Cache,15000 rpm
with 5.5 ms random access time.
So even if i switch to those disks i can only reach a perfomace gain of 1.5,
right?
To achieve a better disk performance by factor of ten, i need a raid-10 setup
with 12 disks (so i have 6 raid-1 bundles). Or are there other factors with
high end disks?
kind regards
Janning
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
2010-06-24 12:53 ` Re: Write performance Matthew Wakeling <matthew@flymine.org>
2010-06-24 13:16 ` Re: Write performance Janning <ml@planwerk6.de>
2010-06-24 13:45 ` Re: Write performance Janning Vygen <vygen@kicktipp.de>
@ 2010-06-24 17:02 ` Jesper Krogh <jesper@krogh.cc>
0 siblings, 0 replies; 9+ messages in thread
From: Jesper Krogh @ 2010-06-24 17:02 UTC (permalink / raw)
To: Janning Vygen <vygen@kicktipp.de>; +Cc: pgsql-performance
On 2010-06-24 15:45, Janning Vygen wrote:
> On Thursday 24 June 2010 15:16:05 Janning wrote:
>
>> On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
>>
>>> On Thu, 24 Jun 2010, Janning wrote:
>>>
>>>> We have a 12 GB RAM machine with intel i7-975 and using
>>>> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 TB)"
>>>>
>>>>
>>> For each drive, you will be able to read/write approximately 8kB /
>>> 0.0085s, giving 941kB per second. If you have multiple processes all
>>> doing random access, then you may be able to utilise both discs and get
>>> double that.
>>>
>> So with your calculation I have a maximum of 2MB/s random access. So i
>> really need to upgrade my disk configuration!
>>
> i was looking at tomshardware.com and the fastest disk is
>
> Maxtor Atlas 15K II * 8K147S0,SAS,147 GB, 16 MB Cache,15000 rpm
>
> with 5.5 ms random access time.
>
> So even if i switch to those disks i can only reach a perfomace gain of 1.5,
> right?
>
> To achieve a better disk performance by factor of ten, i need a raid-10 setup
> with 12 disks (so i have 6 raid-1 bundles). Or are there other factors with
> high end disks?
>
Well. On the write-side, you can add in a Raid controller with Battery
backed
write cache to not make the writes directly hit disk. This improves
the amount of writing you can do.
On the read-side you can add more memory to your server so a significant
part of your most active dataset is cached in memory.
It depends on the actual sizes and workload what gives the most benefit
for you.
--
Jesper
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
2010-06-24 12:53 ` Re: Write performance Matthew Wakeling <matthew@flymine.org>
2010-06-24 13:16 ` Re: Write performance Janning <ml@planwerk6.de>
@ 2010-06-25 17:30 ` Scott Carey <scott@richrelevance.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Carey @ 2010-06-25 17:30 UTC (permalink / raw)
To: Janning <ml@planwerk6.de>; +Cc: pgsql-performance
On Jun 24, 2010, at 6:16 AM, Janning wrote:
> On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
>> On Thu, 24 Jun 2010, Janning wrote:
>>> We have a 12 GB RAM machine with intel i7-975 and using
>>> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
>>
>> Those discs are 1.5TB, not 1.5GB.
>
> sorry, my fault.
>
>>> One disk for the system and WAL etc. and one SW RAID-0 with two disks for
>>> postgresql data. Our database is about 24GB.
>>
>> Beware of RAID-0 - make sure you can recover the data when (not if) a disc
>> fails.
>
> oh sorry again, its a raid-1 of course. shame on me.
If your WAL is not on RAID but your data is, you will lose data if the WAL log drive dies. You will then have a difficult time recovering data from the data drives even though they are RAID protected. Most likely indexes and some data will be corrupted since the last checkpoint. I have lost a WAL before, and the result was a lot of corrupted system indexes that had to be rebuilt in single user mode, and one system table (stats related) that had to be purged and regenerated from scratch. This was not fun. Most of the data was fine, but the cleanup is messy if you lose WAL, and there is no guarantee that your data is safe if you don't have the WAL available.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Write performance
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
@ 2010-06-24 13:57 ` Greg Smith <greg@2ndquadrant.com>
2 siblings, 0 replies; 9+ messages in thread
From: Greg Smith @ 2010-06-24 13:57 UTC (permalink / raw)
To: Janning <ml@planwerk6.de>; +Cc: pgsql-performance
As others have already pointed out, your disk performance here is
completely typical of a single pair of drives doing random read/write
activity. So the question you should be asking is how to reduce the
amount of reading and writing needed to run your application. The
suggestions at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server address
that. Increases to shared_buffers and checkpoint_segments in particular
can dramatically reduce the amount of I/O needed to run an application.
On the last server I turned, random reads went from a constant stream of
1MB/s (with default value of shared_buffers at 32MB) to an average of
0.1MB/s just by adjusting those two parameters upwards via those guidelines.
If you haven't already made large increases to those values, I'd suggest
starting there before presuming you must get a different disk setup.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-06-25 17:30 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-24 12:43 Write performance Janning <ml@planwerk6.de>
2010-06-24 12:47 ` Kenneth Marshall <ktm@rice.edu>
2010-06-24 13:30 ` Janning <ml@planwerk6.de>
2010-06-24 12:53 ` Matthew Wakeling <matthew@flymine.org>
2010-06-24 13:16 ` Janning <ml@planwerk6.de>
2010-06-24 13:45 ` Janning Vygen <vygen@kicktipp.de>
2010-06-24 17:02 ` Jesper Krogh <jesper@krogh.cc>
2010-06-25 17:30 ` Scott Carey <scott@richrelevance.com>
2010-06-24 13:57 ` Greg Smith <greg@2ndquadrant.com>
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