From: Paul Johnson <paul@oxton.com>
To: pgsql-performance@postgresql.org
Subject: Data Warehousing Tuning
Date: Wed, 6 Jul 2005 18:15:47 +0100 (BST)
Message-ID: <3688.217.45.209.171.1120670147.squirrel@www.gradwell.com> (raw)
Hi all, we have the following setup:
- Sun V250 server
- 2*1.3GHz Sparc IIIi CPU
- 8GB RAM
- 8*73GB SCSI drives
- Solaris 10
- Postgres 8
Disks 0 and 1 are mirrored and contain the OS and the various software
packages, disks 2-7 are configured as a 320GB concatenation mounted on
/data, which is where load files and Postgres database and log files live.
The box is used by a small number of developers doing solely
Postgres-based data warehousing work. There are no end-users on the box,
and we are aiming for the maximum IO throughput.
Questions are as follows:
1) Should we have set the page size to 32MB when we compiled Postgres?
We mainly do bulk loads using 'copy', full-table scans and large joins so
this would seem sensible. Tables are typically 10 million rows at present.
2) What are the obvious changes to make to postgresql.conf?
Things like shared_buffers, work_mem, maintenance_work_mem and
checkpoint_segments seem like good candidates given our data warehousing
workloads.
3) Ditto /etc/system?
4) We moved the pg_xlog files off /data/postgres (disks 2-7) and into
/opt/pg_xlog (disks 0-1), but it seemed like performance decreased, so we
moved them back again.
Has anyone experienced real performance gains by moving the pg_xlog files?
Thanks in anticipation,
Paul.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: paul@oxton.com
Subject: Re: Data Warehousing Tuning
In-Reply-To: <3688.217.45.209.171.1120670147.squirrel@www.gradwell.com>
References: <3688.217.45.209.171.1120670147.squirrel@www.gradwell.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