X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 817D2528B9 for ; Wed, 6 Jul 2005 14:15:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30927-10 for ; Wed, 6 Jul 2005 17:15:49 +0000 (GMT) Received: from lon-mail-1.gradwell.net (lon-mail-1.gradwell.net [193.111.201.125]) by svr1.postgresql.org (Postfix) with ESMTP id DC1935289B for ; Wed, 6 Jul 2005 14:15:47 -0300 (ADT) Received: from www.gradwell.com ([193.111.200.100]) by lon-mail-1.gradwell.net with smtp (Gradwell gwh-smtpd 1.186) id 42cc11c3.f44.c7; Wed, 6 Jul 2005 18:15:47 +0100 (envelope-sender ) Received: from 217.45.209.171 (SquirrelMail authenticated user paul@pop3.oxton.com) by www.gradwell.com with HTTP; Wed, 6 Jul 2005 18:15:47 +0100 (BST) Message-ID: <3688.217.45.209.171.1120670147.squirrel@www.gradwell.com> Date: Wed, 6 Jul 2005 18:15:47 +0100 (BST) Subject: Data Warehousing Tuning From: "Paul Johnson" To: pgsql-performance@postgresql.org Reply-To: paul@oxton.com User-Agent: SquirrelMail/1.4.2 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200507/47 X-Sequence-Number: 13288 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.