X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 28437D1B9BC for ; Tue, 16 Mar 2004 15:25:42 +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 12386-01 for ; Tue, 16 Mar 2004 11:25:39 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 18C7CD1B8EA for ; Tue, 16 Mar 2004 11:25:35 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2GFPZVJ024847; Tue, 16 Mar 2004 10:25:36 -0500 (EST) To: Shridhar Daithankar Cc: Rosser Schwarz , pgsql-performance@postgresql.org Subject: Re: atrocious update performance In-reply-to: <4056AF09.9030003@frodo.hserus.net> References: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com> <4056AF09.9030003@frodo.hserus.net> Comments: In-reply-to Shridhar Daithankar message dated "Tue, 16 Mar 2004 13:08:49 +0530" Date: Tue, 16 Mar 2004 10:25:35 -0500 Message-ID: <24846.1079450735@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/219 X-Sequence-Number: 6075 Shridhar Daithankar writes: > Rosser Schwarz wrote: >> shared_buffers = 4096 >> sort_mem = 32768 >> vacuum_mem = 32768 >> wal_buffers = 16384 >> checkpoint_segments = 64 >> checkpoint_timeout = 1800 >> checkpoint_warning = 30 >> commit_delay = 50000 >> effective_cache_size = 131072 > First of all, your shared buffers are low. 4096 is 64MB with 16K block > size. I would say at least push them to 150-200MB. Check. Much more than that isn't necessarily better though. shared_buffers = 10000 is frequently mentioned as a "sweet spot". > Secondly your sort mem is too high. Note that it is per sort per query. You > could build a massive swap storm with such a setting. Agreed, but I doubt that has anything to do with the immediate problem, since he's not testing parallel queries. > Similarly pull down vacuum and WAL buffers to around 512-1024 each. The vacuum_mem setting here is 32Mb, which seems okay to me, if not on the low side. Again though it's not his immediate problem. I agree that the wal_buffers setting is outlandishly large; I can't see any plausible reason for it to be more than a few dozen. I don't know whether oversized wal_buffers can directly cause any performance issues, but it's certainly not a well-tested scenario. The other setting I was going to comment on is checkpoint_warning; it seems mighty low in comparison to checkpoint_timeout. If you are targeting a checkpoint every half hour, I'd think you'd want the system to complain about checkpoints spaced more closely than several minutes. But with the possible exception of wal_buffers, I can't see anything in these settings that explains the originally complained-of performance problem. I'm still wondering about foreign key checks. regards, tom lane