Subject: Re: utilizing multiple disks for i/o performance
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Kenji Morishige <kenjim@juniper.net>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060520023745.GA14877@juniper.net>
References: <20060520023745.GA14877@juniper.net>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1148310891.30414.54.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Mon, 22 May 2006 10:14:51 -0500

On Fri, 2006-05-19 at 21:37, Kenji Morishige wrote:
> Where can I find any documentation to partition the tablespace disk files onto
> different physical arrays for improved performance?

There have been quite a few posts to this list in the past about this,
so searching it might be a good start.

Firstly, you need to defined "improved performance".  Are we talking
transactional throughput (OLTP), or batch updates (ETL), or report
generation (OLAP stuff)???  Or some other scenario.

For write performance, the general rules are:

You can only commit 1 transaction per rotation of a properly fsynced
disc that holds the WAL file (i.e. the pg_xlog directory).  So, putting
that on it's own fast spinning disc is step one for improved
performance.

A battery backed cache unit (BBU) on a RAID controller is a must.

RAID 1+0 is a good choice for your data partition.

For many hardware RAID controllers with the above mentioned BBU moving
the pg_xlog to another partition is no real help.

Cheap RAID controllers are often worse than no RAID controller.  If you
can't afford a good RAID controller, you're probably better off with
software RAID than using a cheapie.

For READ performance:

Often settings in postgresql.conf are far more important than the drive
layout.  

Lots of RAM is a good thing.

Assuming you've got lots of RAM, making shared_buffers anywhere from 10
to 25% of it is a pretty good size.

work_mem usually works well at around 16 meg or so.

drop random_page_cost to about 2 for most systems.

Lastly, read this:

http://www.varlena.com/GeneralBits/Tidbits/perf.html