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 D0ACD5567D for ; Tue, 15 Mar 2005 16:08:32 +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 85690-09 for ; Tue, 15 Mar 2005 16:08:31 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id E60A252A57 for ; Tue, 15 Mar 2005 16:08:30 +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; 15 Mar 2005 10:08:27 -0600 Subject: Re: Performance problem... From: Scott Marlowe To: Marcin Giedz Cc: pgsql-admin@postgresql.org In-Reply-To: <200503150959.39708.marcin.giedz@eulerhermes.pl> References: <200503141903.08793.marcin.giedz@eulerhermes.pl> <1110825173.28555.79.camel@state.g2switchworks.com> <200503150959.39708.marcin.giedz@eulerhermes.pl> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1110902907.28555.126.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 15 Mar 2005 10:08:27 -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/225 X-Sequence-Number: 16723 On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote: > Dnia poniedzia=C5=82ek, 14 marca 2005 19:32, Scott Marlowe napisa=C5=82: > > 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 cour= se > > > some performance problems occured. Our server is Dual Xeon 3.0GHz + 8= GB > > > RAM + RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(softw= are > > > - two 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as > > > follows: > > > > > > max_connections =3D 150 > > > shared_buffers =3D 50000 # min 16, at least max_connections*= 2, 8KB > > > each work_mem =3D 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... >=20 > Playing with shared_buffers from 10000 to 50000 doesn't change anything i= n=20 > total time for this query :( But when I change work_mem a little higher t= o=20 > 10000 total runtime decreases a little about 10% but when I change=20 > random_page_cost to 0.2 (I know that almost all papers say it should be=20 > higher then 1.0) total runtime decreases almost 3 times and lasts about 9= 00ms=20 > - earlier with random_page_cost=3D1.2 it took 2.7s. Is it possible to hav= e=20 > random_page_cost on this value?=20 IF random_page_cost needs to be that low, then it's likely that the query planner is either getting bad statistics and making a poor decision, or that you've got a corner case that it just can't figure out. What does explain analyze say with random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed full lately?