From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Kenji Morishige <kenjim@juniper.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: utilizing multiple disks for i/o performance
Date: Mon, 22 May 2006 10:14:51 -0500
Message-ID: <1148310891.30414.54.camel@state.g2switchworks.com> (raw)
In-Reply-To: <20060520023745.GA14877@juniper.net>
References: <20060520023745.GA14877@juniper.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, kenjim@juniper.net
Subject: Re: utilizing multiple disks for i/o performance
In-Reply-To: <1148310891.30414.54.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox