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 5E1B252AC0 for ; Thu, 11 Aug 2005 11:30:21 -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 49682-02 for ; Thu, 11 Aug 2005 14:30:14 +0000 (GMT) Received: from brmea-mail-4.sun.com (brmea-mail-4.Sun.COM [192.18.98.36]) by svr1.postgresql.org (Postfix) with ESMTP id 5D6D9528D7 for ; Thu, 11 Aug 2005 11:30:13 -0300 (ADT) Received: from phys-bur-1 ([129.148.9.72]) by brmea-mail-4.sun.com (8.12.10/8.12.9) with ESMTP id j7BEUDx9010848 for ; Thu, 11 Aug 2005 08:30:14 -0600 (MDT) Received: from conversion-daemon.bur-mail1.east.sun.com by bur-mail1.east.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0IL200801ATJW4@bur-mail1.east.sun.com> (original mail from J.K.Shah@Sun.COM) for pgsql-performance@postgresql.org; Thu, 11 Aug 2005 10:30:13 -0400 (EDT) Received: from bur-mail1.east.sun.com (phys-bur-1 [129.148.9.72]) by bur-mail1.east.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0IL200AOPAYDEM@bur-mail1.east.sun.com>; Thu, 11 Aug 2005 10:30:13 -0400 (EDT) Received: from [129.148.168.2] by bur-mail1.east.sun.com (mshttpd); Thu, 11 Aug 2005 10:30:13 -0400 Date: Thu, 11 Aug 2005 10:30:13 -0400 From: Jignesh Shah Subject: Re: [Fwd: PG8 Tuning] To: paul@oxton.com, pgsql-performance@postgresql.org Cc: Robert Lor , Donald Courtney Message-id: <7833474d78.74d7878334@bur-mail1.east.sun.com> MIME-version: 1.0 X-Mailer: iPlanet Messenger Express 5.2 HotFix 1.24 (built Dec 19 2003) Content-type: multipart/mixed; boundary="Boundary_(ID_EpePzI99TlR0Dh/+EexxvA)" Content-language: en X-Accept-Language: en X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200508/144 X-Sequence-Number: 13891 This is a multi-part message in MIME format. --Boundary_(ID_EpePzI99TlR0Dh/+EexxvA) Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Content-disposition: inline Hi Paul, I was passed your message... regarding DSS workload with Postgres on Solaris. (I am not in the alias). Performance is relative to your workload. Can you actually send us what you are doing in your queries, updates etc? I have been running few tests myself and here are my rules of thumbs, your mileage can vary.. http://blogs.sun.com/roller/page/jkshah?entry=tuning_postgresql_8_0_2 * Increasing checkpoint certainly helps. (I went as far as actually going to increase LOGFILE size from 16MB to 256MB and recompiling it and then using lower number of checkpoints (appropriately).. (file rotations also decreases performance) * Moving pg_xlog to a different file system and mounting that file system with "forcedirectio" also helps a lot (This increases the througput by another 2x to 5x or more.) (This can be done either by adding forcedirectio in your /etc/vfstab mount options or for existing mounts as follows: mount -o remount,forcedirectio /filesystem (Note: Database files should not be using forcedirectio otherwise file system cache will not be used for it) * I actually reduced the PG Bufferpool to 1G or less since it seemed to decrease performance as I increased its bufferpool size (depending on your workload) * If you are using SPARC then following etc commands will help.. set segmap_percent=60 set ufs:freebehind=0 This will allocate 60% of RAM for file system buffer (database files) and also cache all files (since PostgreSQL files are 1G by default) This will help your repeat queries significantly. Other things depends on what you queries you are running? If you send me few samples, I can send you appropriate DTrace scripts (Solaris 10 or higher) to run to figure out what's happening Regards, Jignesh ____________________________________________________ Jignesh K. Shah MTS Software Engineer Sun Microsystems, Inc MDE-Horizontal Technologies Email: J.K.Shah@sun.com Phone: (781) 442 3052 http://blogs.sun.com/jkshah ____________________________________________________ ----- Original Message ----- From Paul Johnson Date Thu, 11 Aug 2005 13:23:21 +0100 (BST) To pgsql-performance@postgresql.org Subject [PERFORM] PG8 Tuning Hi all, we're running PG8 on a Sun V250 with 8GB RAM and 2*1.3GHz SPARC CPUs running Solaris 10. The DB cluster is on an external fibre-attached Sun T3 array that has 9*36GB drives configured as a single RAID5 LUN. The system is for the sole use of a couple of data warehouse developers, hence we are keen to use 'aggressive' tuning options to maximise performance. So far we have made the following changes and measured the impact on our test suite: 1) Increase checkpoint_segments from 3 to 64. This made a 10x improvement in some cases. 2) Increase work_mem from 1,024 to 524,288. 3) Increase shared_buffers from 1,000 to 262,143 (2 GB). This required setting SHMMAX=4294967295 (4 GB) in /etc/system and re-booting the box. Question - can Postgres only use 2GB RAM, given that shared_buffers can only be set as high as 262,143 (8K pages)? So far so good... 4) Move /pg_xlog to an internal disk within the V250. This has had a severe *negative* impact on performance. Copy job has gone from 2 mins to 12 mins, simple SQL job gone from 1 min to 7 mins. Not even run long SQL jobs. I'm guessing that this is because pg_xlog has gone from a 9 spindle LUN to a single spindle disk? In cases such as this, where an external storage array with a hardware RAID controller is used, the normal advice to separate the data from the pg_xlog seems to come unstuck, or are we missing something? Cheers, Paul Johnson. --Boundary_(ID_EpePzI99TlR0Dh/+EexxvA) Content-type: message/rfc822; name="[PERFORM] PG8 Tuning" Return-path: Received: from conversion-daemon.bur-mail1.east.sun.com by bur-mail1.east.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0IL200C015ME3X@bur-mail1.east.sun.com> (original mail from pgsql-performance-owner+M13863@postgresql.org) for djc@bur-mail1.East.Sun.COM; Thu, 11 Aug 2005 08:38:53 -0400 (EDT) Received: from eastmail2bur.East.Sun.COM (eastmail2bur.East.Sun.COM [129.148.13.40]) by bur-mail1.east.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0IL200MH15STJS@bur-mail1.east.sun.com> for djc@bur-mail1.East.Sun.COM (ORCPT donald.courtney@sun.com); Thu, 11 Aug 2005 08:38:53 -0400 (EDT) Received: from sunmail1brm.Central.Sun.COM (sunmail1brm.Central.Sun.COM [129.147.62.17]) by eastmail2bur.East.Sun.COM (8.12.10+Sun/8.12.10/ENSMAIL,v2.2) with ESMTP id j7BCcrlM025431 for ; Thu, 11 Aug 2005 08:38:53 -0400 (EDT) Received: from nwkea-mail-2.sun.com (nwkea-mail-2.Sun.COM [192.18.42.14]) by sunmail1brm.Central.Sun.COM (8.11.7p1+Sun/8.11.7/ENSMAIL,v2.2) with ESMTP id j7BCcpb07056 for ; Thu, 11 Aug 2005 06:38:52 -0600 (MDT) Received: from relay23.sun.com (relay23.sun.com [192.12.251.54] (may be forged)) by nwkea-mail-2.sun.com (8.12.10/8.12.9) with ESMTP id j7BCcp1d027727 for ; Thu, 11 Aug 2005 05:38:51 -0700 (PDT) Received: from mms21es.sun.com (mms21es.sun.com [150.143.232.14]) by relay23.sun.com with ESMTP for donald.courtney@sun.com; Thu, 11 Aug 2005 12:38:51 +0000 (Z) Received: from relay21.sun.com (relay21.sun.com [192.12.251.14]) by mms21es.sun.com with ESMTP for donald.courtney@sun.com; Thu, 11 Aug 2005 12:38:50 +0000 (Z) Received: from hosting.commandprompt.com ([207.173.200.128] [207.173.200.128]) by relay21.sun.com with ESMTP for donald.courtney@sun.com; Thu, 11 Aug 2005 12:38:50 +0000 (Z) Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id j7BCPn0Q014340; Thu, 11 Aug 2005 05:26:04 -0700 Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BCF1752A19 for ; Thu, 11 Aug 2005 09:23:24 -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 74763-06 for ; Thu, 11 Aug 2005 12:23:20 +0000 (GMT) Received: from lon-mail-2.gradwell.net (lon-mail-2.gradwell.net [193.111.201.126]) by svr1.postgresql.org (Postfix) with ESMTP id 5166052A06 for ; Thu, 11 Aug 2005 09:23:19 -0300 (ADT) Received: from www.gradwell.com ([193.111.200.100]) by lon-mail-2.gradwell.net with smtp (Gradwell gwh-smtpd 1.190) id 42fb4339.14d23.f; Thu, 11 Aug 2005 13:23:21 +0100 (envelope-sender ) Received: from 217.45.209.171 (SquirrelMail authenticated user paul@pop3.oxton.com) by www.gradwell.com with HTTP; Thu, 11 Aug 2005 13:23:21 +0100 (BST) Date: Thu, 11 Aug 2005 13:23:21 +0100 (BST) From: Paul Johnson Subject: [PERFORM] PG8 Tuning Sender: pgsql-performance-owner@postgresql.org To: pgsql-performance@postgresql.org Reply-to: paul@oxton.com Message-id: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 8BIT Importance: Normal X-Priority: 3 Precedence: bulk User-Agent: SquirrelMail/1.4.2 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-1.6 (hosting.commandprompt.com [192.168.1.101]); Thu, 11 Aug 2005 05:26:08 -0700 (PDT) X-Mailing-List: pgsql-performance List-Owner: List-Post: List-Subscribe: List-Unsubscribe: List-Archive: List-Help: List-Id: Original-recipient: rfc822;donald.courtney@sun.com Hi all, we're running PG8 on a Sun V250 with 8GB RAM and 2*1.3GHz SPARC CPUs running Solaris 10. The DB cluster is on an external fibre-attached Sun T3 array that has 9*36GB drives configured as a single RAID5 LUN. The system is for the sole use of a couple of data warehouse developers, hence we are keen to use 'aggressive' tuning options to maximise performance. So far we have made the following changes and measured the impact on our test suite: 1) Increase checkpoint_segments from 3 to 64. This made a 10x improvement in some cases. 2) Increase work_mem from 1,024 to 524,288. 3) Increase shared_buffers from 1,000 to 262,143 (2 GB). This required setting SHMMAX=4294967295 (4 GB) in /etc/system and re-booting the box. Question - can Postgres only use 2GB RAM, given that shared_buffers can only be set as high as 262,143 (8K pages)? So far so good... 4) Move /pg_xlog to an internal disk within the V250. This has had a severe *negative* impact on performance. Copy job has gone from 2 mins to 12 mins, simple SQL job gone from 1 min to 7 mins. Not even run long SQL jobs. I'm guessing that this is because pg_xlog has gone from a 9 spindle LUN to a single spindle disk? In cases such as this, where an external storage array with a hardware RAID controller is used, the normal advice to separate the data from the pg_xlog seems to come unstuck, or are we missing something? Cheers, Paul Johnson. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org --Boundary_(ID_EpePzI99TlR0Dh/+EexxvA)--