Skip site navigation (1) Skip section navigation (2)

Re: What exactly is postgres doing during INSERT/UPDATE ?

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Luke Koops <luke(dot)koops(at)entrust(dot)com>
Cc: Joseph S <jks(at)selectacast(dot)net>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: What exactly is postgres doing during INSERT/UPDATE ?
Date: 2009-08-29 08:46:15
Message-ID: 407d949e0908290146o687152b6ge8e349587132b20b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, Aug 29, 2009 at 5:20 AM, Luke Koops<luke(dot)koops(at)entrust(dot)com> wrote:
> Joseph S Wrote
>> If I have 14 drives in a RAID 10 to split between data tables
>> and indexes what would be the best way to allocate the drives
>> for performance?
>
> RAID-5 can be much faster than RAID-10 for random reads and writes.  It is much slower than RAID-10 for sequential writes, but about the same for sequential reads.  For typical access patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of sequential scans.

That's pretty much exactly backwards. RAID-5 will at best slightly
slower than RAID-0 or RAID-10 for sequential reads or random reads.
For sequential writes it performs *terribly*, especially for random
writes. The only write pattern where it performs ok sometimes is
sequential writes of large chunks.

> Always put the transaction logs (WAL Files) on RAID-10 (or RAID-1 if you don't want to dedicate so many drives to the logs).  The only significant performance difference between RAID-10 and RAID-1 is that RAID-1 is much slower (factor of 4 or 5) for random reads.

no, RAID-10 and RAID-1 should perform the same for reads. RAID-10 will
be slower at writes by about a factor equal to the number of mirror
sides.

> I think the ratio of random reads from the transaction logs would typically be quite low.

During normal operation the logs are *never* read, neither randomly
nor sequentially.

> You don't want your transaction logs or any swapfiles on RAID-5.  The slow sequential write performance can be a killer.

As i mentioned sequential writes are the only write case when RAID-5
sometimes ok. However the picture is complicated by transaction
syncing which would make RAID-5 see it more as random i/o. In any case
wal normally doesn't take much disk space so there's not much reason
to use anything but RAID-1.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2009-08-29 13:26:27
Subject: Re: What exactly is postgres doing during INSERT/UPDATE ?
Previous:From: Luke KoopsDate: 2009-08-29 04:20:30
Subject: Re: What exactly is postgres doing during INSERT/UPDATE ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group