[ale] Postgresql archive logs

[Jeff Hubbs]

Oh, c'mon - *all we're really good at* is suggesting hyperoptimized 
solutions that you can't actually do! :)

It's funny you should mention that because I was just working with a 
Dell PE 2950 matching your description.

One thing I noticed:  on the 2950 in question, the drives in slots 4 and 
5 were on a different controller channel than the ones in slots 0 
through 3; that's a compartmentalization you may be able to exploit.

First, if no one really cares how fast this PostgreSQL server is, then 
leaving it as a 6-drive RAID5 as your /dev/sda is Good Enough (tm); it 
is the least cumbersome and most futureproofed path you could take.  But 
if you want to improve matters, I think the organizing principle you 
need to start from is "Keep heavy write away from RAID5."

Given that some write on RAID5 may be inevitable, if you want to improve 
that, you want more drives, not fewer.  With n drives, every nth write 
is a parity write, so the bigger n is, the less write bandwidth 
difference there is between n and n-1.  So I'd look at your PostgreSQL 
and OS disk footprints, where the heavy write takes place, and the size 
of your drives into consideration, determine just what it is you want to 
improve, and go from there.

If it turns out that the heaviest write is to the archive logs, I would 
probably RAID1 the pair of drives on their own controller channel in 
slots  4 and 5 and put the logs there, leaving the DB data and the OS on 
the RAID5, rationale being that OS operations aren't going to be tying 
up disk very much taking care of its own business.

And if you want to grease the wheels past that, well, you need to go off 
into 3xRAID1, RAID1 plus RAID10/01, etc., disk space willing, or add 
drive enclosures or other things you say you don't want to do.

- Jeff

On 10/27/11 11:54 AM, Lightner, Jeff wrote:
>
> Question:
>
> For large (~1 TB) Postgresql DB is it better to have archive logs on 
> same RAID set as the DB to maximize spindles for the DB or to setup 
> two separate RAID sets thereby reducing spindles for the DB?
>
> We currently have a Dell PowerEdge with PERC (LSI OEM) RAID 
> Controller.   Initially it was setup with all 6 internal disks in the 
> same RAID5 set.
>
> Our DBAs believe that having archive logs on same disks as DB is a 
> problem so want us to redo the layout so that we have 1 x 2 disk 
> mirror for the OS and archive longs and a 1 x 4 disk RAID 5 for the 
> DB.   This effectively reduces the spindles for DB from 6 to 4.   I'm 
> wondering if the performance implications of such a reduction would 
> outweigh the data integrity setup.
>
> Please do NOT suggest installing more disks, or attaching to an 
> external array.   These aren't options for this system.   Also please 
> don't talk about the importance of doing backups I already know 
> that.   Finally don't talk about whether RAID 5 is a good idea -- I 
> already know the pitfalls.   My questions is not to talk about how it 
> could be done better with more/different equipment but rather what is 
> best with the equipment described above.
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.ale.org/pipermail/ale/attachments/20111027/5d8febb7/attachment.html