Kenneth Cox wrote:
1) Should I switch to RAID 10 for performance? I see things like "RAID 5 is bad for a DB" and "RAID 5 is slow with <= 6 drives" but I see little on RAID 6. RAID 6 was the original choice for more usable space with good redundancy. My current performance is 85MB/s write, 151 MB/s reads
RAID6 is no better than RAID5 performance wise, it just has better fault tolerance. And the ServeRAID 8k is a pretty underpowered card as RAID controllers go, so it would not be impossible for it computing RAID parity and the like to be the bottleneck here. I'd expect a 6-disk RAID10 with 7200RPM drives to be closer to 120MB/s on writes, so you're not getting ideal performance there. Your read figure is more competative, but that's usually the RAID5 pattern--decent on reads, slugging on writes.
2) Should I configure the ext3 file system with noatime and/or data=writeback or data=ordered? My controller has a battery, the logical drive has write cache enabled (write-back), and the physical devices have write cache disabled (write-through).
data=ordered is the ext3 default and usually a reasonable choice. Using writeback instead can be dangerous, I wouldn't advise starting there. noatime is certainly a good thing, but the speedup is pretty minor if you have a battery-backed write cache.
3) Do I just need to spend more time configuring postgresql? My non-default settings were largely generated by pgtune-0.9.3
Those look reasonable enough, except no reason to make wal_buffers bigger than 16MB. That work_mem figure might be high too, that's a known concern with pgtune I need to knock out of it one day soon. When you are hitting high I/O wait periods, is the system running out of RAM and swapping? That can cause really nasty I/O wait.
Your basic hardware is off a bit, but not so badly that I'd start there. Have you turned on slow query logging to see what is hammering the system when the iowait climbs? Often tuning those by looking at the EXPLAIN ANALYZE output can be much more effective than hardware/server configuration tuning.
* I know "feels slow" is not scientific. What I mean is that any single query on a fact table, or any 'rm -rf' of a big directory sends disk utilization to 100% (measured with iostat -x 3).
"rm -rf" is really slow on ext3 on just about any hardware. If your fact tables aren't in RAM and you run a query against them, paging them back in again will hammer the disks until it's done. That's not necessarily indicative of a misconfiguration on its own.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
