Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3EF342E022E for ; Wed, 9 Jan 2008 08:53:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 64941-08 for ; Wed, 9 Jan 2008 08:53:23 -0400 (AST) Received: from cptwks26.careerjunction.local (unknown [196.212.105.173]) by postgresql.org (Postfix) with ESMTP id 018FF2E020D for ; Wed, 9 Jan 2008 08:53:18 -0400 (AST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by cptwks26.careerjunction.local (Postfix) with ESMTP id C7794B7A5C; Wed, 9 Jan 2008 14:53:10 +0200 (SAST) Message-ID: <4784C3B5.9060108@careerjunction.co.za> Date: Wed, 09 Jan 2008 14:53:09 +0200 From: Adrian Moisey User-Agent: Thunderbird 2.0.0.6 (X11/20071022) MIME-Version: 1.0 To: "Joshua D. Drake" CC: pgsql-performance@postgresql.org Subject: Re: big database performance References: <4784835A.8070604@careerjunction.co.za> <47848575.8000007@commandprompt.com> In-Reply-To: <47848575.8000007@commandprompt.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.101 tagged_above=0 required=5 tests=BAYES_50=0.001, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200801/41 X-Sequence-Number: 28280 Hi >> We recently converted to postgres (from mssql) and we're having >> performance issues. Not all the issues are related to postgres, but >> we're trying to sort everything out. >> >> The server is running ubuntu Gutsy with the database stored on a IBM >> SAN. It is a Dell box with dual quad core 2.00GHz Xeons and 8GB RAM. >> >> >> The database is about 71GB in size. >> >> I've looked at the postgres config files and we've tweaked as much as >> our knowledge allows. >> >> Can someone shed some light on the settings I should use ? > > Umpf that isn't quite enough info :) but assuming you are running 8.2.x: Sorry :/ Yes, we are running 8.2.x > Start with 1GB shared_buffers (you may be able to go hire), 4MB > work_mem, wal_sync_method = open_sync, checkpoint_segments = 30, > default_statistics_target = 150, effective_cache_size = 6GB . Our shared_buffers is 1GB. work_mem is 32MB I changed wal_sync_method to open_sync (which helped a ton!) Can someone please explain effective_cache_size. what cache does it want to know about? Linux cache? Also, we're running the db on ext3 with noatime. Should I look at changing or getting rid of journaling ? > 64bit Gutsy? Yes > How is the SAN connected? fibre > What does mpstat 5 (3 iterations) say? > Even better what does sar -A say over a 24 hour period? I'll get these for you -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842