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 55FB2567E0 for ; Mon, 14 Mar 2005 18:33:00 +0000 (GMT) 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 17641-01 for ; Mon, 14 Mar 2005 18:32:56 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 4F201567F8 for ; Mon, 14 Mar 2005 18:32:53 +0000 (GMT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 14 Mar 2005 12:32:53 -0600 Subject: Re: Performance problem... From: Scott Marlowe To: Marcin Giedz Cc: pgsql-admin@postgresql.org In-Reply-To: <200503141903.08793.marcin.giedz@eulerhermes.pl> References: <200503141903.08793.marcin.giedz@eulerhermes.pl> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1110825173.28555.79.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 14 Mar 2005 12:32:53 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/216 X-Sequence-Number: 16714 On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote: > Hello... > > > Our company is going to change SQL engine from MySQL to PSQL. Of course some > performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB RAM + > RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software - two > 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as follows: > > max_connections = 150 > shared_buffers = 50000 # min 16, at least max_connections*2, 8KB each > work_mem = 2048 # min 64, size in KB 50,000 shared buffers may or may not be too much. Try it at different sizes from 5,000 or so up to 50,000 and find the "knee". It's usually closer to 10,000 than 50,000, but ymmv... On the other hand, for a machine with 8 gigs of ram, 2 meg of work_mem is pretty small. Try bumping it up to 8 or 16 megs. You can change this one "on the fly" for testing, so just do: set work_mem=16384; and then run the query again and see if that helps. The hash aggregate method uses sort/work mem to do it's work, and if it doesn't think it can hold the result set in that space the planner will pick another method, like the merge left join. In your explain analyze output, look for gross mismatches between estimated and actual rows. Most of yours here look pretty good in the areas where the data is being collected, but during the merges, the numbers are WAY off, but i'm not sure what to do to change that.