X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B370952C4B for ; Fri, 12 Aug 2005 19:48: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 00546-07 for ; Fri, 12 Aug 2005 22:48:19 +0000 (GMT) Received: from web31112.mail.mud.yahoo.com (web31112.mail.mud.yahoo.com [68.142.201.74]) by svr1.postgresql.org (Postfix) with SMTP id A9D8F52C3A for ; Fri, 12 Aug 2005 19:48:17 -0300 (ADT) Received: (qmail 30548 invoked by uid 60001); 12 Aug 2005 22:48:19 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=I+vikVFp7wTj0p9OsYC41OeyPw7TpGd95Yu5EhCaFk/NdzPRylN9rEfe9u8a4PWjInNsZwefEQVJ40ujos0vlePuBcEkOTfM8iqBiGIkff49sw8GagCih/1NSBRjYy4P9FRQO+tGUzu8Zk5Bi70zFYhb+qkwtKLxmQ2zmPI6gKo= ; Message-ID: <20050812224819.30546.qmail@web31112.mail.mud.yahoo.com> Received: from [216.194.55.187] by web31112.mail.mud.yahoo.com via HTTP; Fri, 12 Aug 2005 15:48:19 PDT Date: Fri, 12 Aug 2005 15:48:19 -0700 (PDT) From: Reply-To: ogjunk-pgjedan@yahoo.com Subject: Re: postgres optimization (effective_cache_size) To: pgsql-admin@postgresql.org In-Reply-To: <1122652055.32465.56.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.459 tagged_above=0 required=5 tests=[AWL=-0.094, DNS_FROM_RFC_ABUSE=0.374, NO_REAL_NAME=0.178] X-Spam-Level: X-Archive-Number: 200508/166 X-Sequence-Number: 18485 Hello, I followed this advice for picking a good effective_cache_size value (below) from Scott Marlowe, and run into a bit of trouble: I looked at the `top' output and saw "721380k cached". So I calculated the effective cache size using Scott's formula: 721380/8 = 90172 Then I changed my effective_cache size from the previous 10000 to 90172: effective_cache_size = 90172 # typically 8KB each I restarted PG, and thought I'd see some performance improvements, but I actually saw degradation in performance. All of a sudden a query that took a second started taking a few seconds, and consumed more CPU than before. Can anyone explain this drop in performance and increase in CPU usage and tell me what I did wrong? I also noticed that the "721380k cached" number in top dropped to about 300000k (about a half). Maybe that was simply due to PG restart? If so, does that indicate the kernel had about 400,000K worth of PG data cached? Thanks, Otis --- Scott Marlowe wrote: > Effective cache size just tells the query planner about how much > memory > the OS is using to cache your dataset. > > Bring the machine up, run lots of queries, and check the cache and > buffers with top, and there's your amount. divide by 8k to get the > setting for effective cache size.