Message-ID: <4C5B4A0C.5080508@catalyst.net.nz>
Date: Fri, 06 Aug 2010 11:32:28 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Advice configuring ServeRAID 8k for performance
References: <op.vgy9k5ov5ru9c3@kent60.office.vivox.com>
In-Reply-To: <op.vgy9k5ov5ru9c3@kent60.office.vivox.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

On 06/08/10 06:28, Kenneth Cox wrote:
> I am using PostgreSQL 8.3.7 on a dedicated IBM 3660 with 24GB RAM 
> running CentOS 5.4 x86_64.  I have a ServeRAID 8k controller with 6 
> SATA 7500RPM disks in RAID 6, and for the OLAP workload it feels* 
> slow.  I have 6 more disks to add, and the RAID has to be rebuilt in 
> any case, but first I would like to solicit general advice.  I know 
> that's little data to go on, and I believe in the scientific method, 
> but in this case I don't have the time to make many iterations.
>
> My questions are simple, but in my reading I have not been able to 
> find definitive answers:
>
> 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 (using dd of 2xRAM per 
> http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm).
>

Normally I'd agree with the others and recommend RAID10 - but you say 
you have an OLAP workload - if it is *heavily* read biased you may get 
better performance with RAID5 (more effective disks to read from). 
Having said that, your sequential read performance right now is pretty 
low (151 MB/s  - should be double this), which may point to an issue 
with this controller. Unfortunately this *may* be important for an OLAP 
workload (seq scans of big tables).


> 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).
>

Probably wise to use noatime. If you have a heavy write workload (i.e so 
what I just wrote above does *not* apply), then you might find adjusting 
the ext3 commit interval upwards from its default of 5 seconds can help 
(I'm doing some testing at the moment and commit=20 seemed to improve 
performance by about 5-10%).

> 3) Do I just need to spend more time configuring postgresql?  My 
> non-default settings were largely generated by pgtune-0.9.3:
>
>     max_locks_per_transaction = 128 # manual; avoiding "out of shared 
> memory"
>     default_statistics_target = 100
>     maintenance_work_mem = 1GB
>     constraint_exclusion = on
>     checkpoint_completion_target = 0.9
>     effective_cache_size = 16GB
>     work_mem = 352MB
>     wal_buffers = 32MB
>     checkpoint_segments = 64
>     shared_buffers = 2316MB
>     max_connections = 32
>

Possibly higher checkpoint_segments and lower wal_buffers (I recall 
someone - maybe Greg suggesting that there was no benefit in having the 
latter > 10MB). I wonder about setting shared_buffers higher - how large 
is the database?

Cheers

Mark