X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 20BF99FB376 for ; Tue, 5 Sep 2006 12:37:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 26426-05-4 for ; Tue, 5 Sep 2006 12:36:53 -0300 (ADT) X-Greylist: delayed 00:07:23.682253 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id DD3D49FB37B for ; Tue, 5 Sep 2006 12:36:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from buggy.javaxpert.com (jmoliere.net8.nerim.net [213.41.184.162]) by svr4.postgresql.org (Postfix) with ESMTP id 6A1F05AF882 for ; Tue, 5 Sep 2006 15:29:13 +0000 (GMT) Received: from buggy.javaxpert.com (localhost.localdomain [127.0.0.1]) by buggy.javaxpert.com (Postfix) with ESMTP id A0A4971C47; Tue, 5 Sep 2006 17:24:31 +0200 (CEST) Received: from 207.45.248.31 (proxying for unknown) (SquirrelMail authenticated user romje) by buggy.javaxpert.com with HTTP; Tue, 5 Sep 2006 17:24:31 +0200 (CEST) Message-ID: <14670.207.45.248.31.1157469871.squirrel@buggy.javaxpert.com> In-Reply-To: <20060905150853.20091.qmail@web60521.mail.yahoo.com> References: <20060905150853.20091.qmail@web60521.mail.yahoo.com> Date: Tue, 5 Sep 2006 17:24:31 +0200 (CEST) Subject: Re: DB Performance Tuning From: romje@javaxpert.com To: "nilesh khode" Cc: pgsql-admin@postgresql.org User-Agent: SquirrelMail/1.4.6 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.096 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, NO_REAL_NAME X-Spam-Level: * X-Archive-Number: 200609/27 X-Sequence-Number: 22941 On Tue, September 5, 2006 5:08 pm, nilesh khode wrote: > Hello, > > I am working on database performace tuning on Postgresql 8.1 database > build on Suse 10 Linux dedicated server. I am having 32 GB of RAM and 8 > CPUs. > nice machine :) > > I have made minimum recommended settings under postgresql.conf (i.e > shared_buffer, work_mem, and effective cache_size also kernel.shmmax) > > I have run SQL queries using pg-admin, the query execution + retrieval > time is 80% reduced over existing server. > But same I tested with JDBC, the query requires more time as compared > with existing server. > what 's the driver used ? do you use statement or preparedstatement ? the time given is made after 1 query or the same query repeated x times then dividing the whole time by x ? have you tried to use jdbc proxy (p6spy for instance) to watch the query ? to know where the time is spent (don't use it in production !!!) > Please help me out in this performance tuning. > HTH Jerome > Regards, > Nilesh > > -- Auteur cahiers du programmeur J2EE - Eyrolles 2003