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 B7D35D1B580 for ; Wed, 17 Mar 2004 18:42:33 +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 23245-03 for ; Wed, 17 Mar 2004 14:42:26 -0400 (AST) Received: from www.totalcardinc.com (unknown [64.33.232.186]) by svr1.postgresql.org (Postfix) with ESMTP id 8E541D1E85C for ; Wed, 17 Mar 2004 14:42:23 -0400 (AST) Received: from RSCHWARZW2K ([10.250.0.37]) (authenticated bits=0) by www.totalcardinc.com (8.12.8/8.12.8) with ESMTP id i2HIgMAl024345; Wed, 17 Mar 2004 12:42:22 -0600 From: "Rosser Schwarz" To: "'Tom Lane'" Cc: Subject: Re: atrocious update performance Date: Wed, 17 Mar 2004 12:42:22 -0600 Message-ID: <002b01c40c4f$95d8a750$2500fa0a@CardServices.TCI.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 In-Reply-To: <26451.1079546289@sss.pgh.pa.us> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-MailScanner-From: rschwarz@totalcardinc.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/242 X-Sequence-Number: 6098 while you weren't looking, Tom Lane wrote: > Hm. It looks like you mistakenly traced psql rather than the backend, > but since the delay went away we wouldn't have learned > anything anyhow. > Have you got any idea what conditions may have changed between seeing > delay and not seeing delay? None, offhand. I have noticed that when a large query is running, the machine can sporadically just freeze--or at least take inordinately long for some other process, be it top or ls, another query, or whatever to start. Nothing looks saturated when it happens, and, while you can count on it to happen, it's not consistent enough to reproduce. > This is pretty odd too. It looks like it's doing checkpoints every so > often (look for the writes to pg_control), which a backend engaged in > a long-running query surely ought not be doing. Need to think about > why that might be... Does the fact that all the reads and writes are 32K mean anything out of the ordinary? $PGSRC/src/include/pg_config_manual.h has BLCKSZ #defined to 16384. I was running previously with a 32K BLCKSZ, but that turned out to be rather sub-optimal for as heavily indexed as our tables are. I've dumped and rebuilt several times since then. /rls -- Rosser Schwarz Total Card, Inc.