Disk Tuning Guide

NOTE:  This document is a bit out of date. You are encouraged to update it and remove this notice :-) 

Disk Optimisation Strategies for PostgreSQL

Suggested topics thus far

Software related

• Operating System Specific
• Filesystem recommendations
• Partititioning recommendations
• Where to put Indexes, Tables, and other things
• NFS

 

Hardare related

• RAID Configuration
• SCSI vs IDE
• SCSI Contoller types
• Drive spindle speeds

Software related

Operating System Specific

• ?

Filesystem recommendations

• Software RAID
• Journalling types recommended/required/etc
• etc

Partititioning recommendations

• ?

Where to put Indexes, Tables, and other things

• Multiple disk drives
• Putting WAL log files on an otherwise idle drive
• etc

NFS

• v2 vs v3 vs v4
• Locking issues
• Speed considerations

 

 

Hardare related

RAID Configuration

RAID: Redundant Array of Independant Disks. RAID allows a computer to use two or more physical drives as a larger, faster, more dependable single logical drive. By striping information redundantly across several drives, a RAID array can survive the failure of one or more disk drives and continue to operate.

RAID configuration consists of two primary choices:

• Hardware versus Software RAID
• RAID level

Hardware versus Software

While hardware RAID is often considered superior to software based RAID, there are circumstances where a software RAID array represents a better value. Hardware's primary advantage over software is that it allows the RAID controller to cache writes with battery backed memory, thus ensuring better crash recovery, while maintaining good speed. Be careful choosing a hardware RAID card to ensure it has both a solid driver for your operating system, and that if it has cache that it is battery backed and can be set to write back mode.

Software RAID is often a good choice for report databases, where write performance isn't a critical issue.

RAID array levels

• RAID level 0: RAID level 0 simply stripes information across the disks with no parity. This method aggregates the bandwidth of several drives at once with little or no overhead. While this level provides the fastest possible performance, it isn't technically "RAID", since the failure of any one disk results in the loss of all data stored on that drive, with no recoverability. This level requires at least 2 drives, but gets faster as more drives are added. The chance of failure increases as you increase the number of drives, so large RAID0 arrays are not a good idea. While not commonly used alone, it is often used with other RAID levels to provide both fast performance and redundancy. Common usage: batch file processing database where the input data is well defined and can be replaced easily.
• RAID level 1: RAID level 1 writes everything to two or more disks. RAID level 1 is well suited for small to medium workloads. It provides the failsafe of a mirrored drive set with the minimum number of drives needed, two. For heavily read databases, RAID level 1 with more than 2 drives can provide very good throughput, by allowing for greater aggregate read bandwidth than just 2 drives. Note that there is a penalty of having to write every update to all the drives in the array. Common useage: workgroup / intranet, OLAP, batch processing.
• RAID level 5: RAID level 5 is well suited for medium to large workloads. RAID5 requires at least three disk drives, but the more drives you place in a RAID5 array the faster it generally runs and the better it handles heavy parallel load. Common usage: intranet, OLAP, OLTP.
• RAID level 1+0, 5+0 While RAID level 0 has no redundancy of its own, it can be added by building a RAID level 1 or 5 array on top of a set of RAID level 0 arrays. This combination often provides the best speed for the least number of drives. At least 4 drives are required for RAID 1+0, and at least 6 drives are required for RAID 5+0. Common usage: high performance OLTP.
• RAID level 0+1, 0+5 This level is much like level 1+0 or 5+0, but instead of being a set of RAID1 or 5 built on top of a set of RAID0, it is a set of RAID0 built on top of multiple RAID1 or 5 arrays. It too is commonly used for OLTP.

SCSI vs IDE

• Where each is best suited
• Data security issues with IDE drives and write cache enabled
• Speed expectations from them

SCSI Contoller types

• U160 vs U320, etc.
• Adaptec 29160 (not bad)
• Advansys UW (sucks due to BIOS incompatibilities with some drives)
• etc

Drive spindle speeds

• Performance when database is on a 15000 RPM SCSI disk vs 10000 RPM SCSI disk vs 7200 RPM IDE disk

Contributors thus far

Justin Clift

Shridhar Daithankar

Lee Kindness

Scott Marlowe