X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 15FA5D1B4C1 for ; Thu, 4 Dec 2003 21:25:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 26873-09 for ; Thu, 4 Dec 2003 17:24:44 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 1A3DDD1B4B9 for ; Thu, 4 Dec 2003 17:24:42 -0400 (AST) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4023430; Thu, 04 Dec 2003 13:25:33 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Jack Coates , Richard Huxton Subject: Re: tuning questions Date: Thu, 4 Dec 2003 13:24:37 -0800 User-Agent: KMail/1.4.3 Cc: pgsql-performance References: <1070553983.6498.52.camel@cletus.lyris.com> <200312042027.22223.dev@archonet.com> <1070570264.13923.88.camel@cletus.lyris.com> In-Reply-To: <1070570264.13923.88.camel@cletus.lyris.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200312041324.37889.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/101 X-Sequence-Number: 4961 Jack, > latest changes: > shared_buffers =3D 35642 This is fine, it's about 14% of available RAM. Though the way you calculat= ed=20 it still confuses me. It's not complicated; it should be between 6% and 1= 5%=20 of available RAM; since you're doing a data-transformation DB, yours should= =20 be toward the high end.=20 > max_fsm_relations =3D 1000 > max_fsm_pages =3D 10000 You want to raise this a whole lot if your data transformations involve lar= ge=20 delete or update batches. I'd suggest running "vacuum analyze verbose"= =20 between steps to see how many dead pages you're accumulating. > wal_buffers =3D 64 > sort_mem =3D 32768 > vacuum_mem =3D 32768 > effective_cache_size =3D 10000 This is way the heck too low. it's supposed to be the size of all availabl= e=20 RAM; I'd set it to 2GB*65% as a start. > IO is active, but hardly saturated. CPU load is hefty though, load > average is at 4 now. Unless you're doing huge statistical aggregates (like radar charts), or hea= vy=20 numerical calculations-by-query, high CPU and idle I/O usually indicates a= =20 really bad query, like badly mismatched data types on a join or unconstrain= ed=20 joins or overblown formatting-by-query. --=20 -Josh Berkus Aglio Database Solutions San Francisco