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.