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 79B24D1B496 for ; Fri, 5 Dec 2003 23:39:34 +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 46967-09 for ; Fri, 5 Dec 2003 19:39:06 -0400 (AST) Received: from bob.samurai.com (bob.samurai.com [205.207.28.75]) by svr1.postgresql.org (Postfix) with ESMTP id 5B260D1B480 for ; Fri, 5 Dec 2003 19:39:03 -0400 (AST) Received: from tokyo.samurai.com (d226-89-59.home.cgocable.net [24.226.89.59]) by bob.samurai.com (Postfix) with ESMTP id 0BAAF1E36; Fri, 5 Dec 2003 18:38:49 -0500 (EST) To: swampler@noao.edu Cc: Postgres-performance Subject: Re: Help tracking down problem with inserts slowing From: Neil Conway In-Reply-To: <1070661108.20063.86.camel@weaver.tuc.noao.edu> (Steve Wampler's message of "Fri, 05 Dec 2003 14:51:48 -0700") References: <1070661108.20063.86.camel@weaver.tuc.noao.edu> Date: Fri, 05 Dec 2003 18:38:47 -0500 Message-ID: <87fzfy96ns.fsf@mailbox.samurai.com> User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/136 X-Sequence-Number: 4996 Steve Wampler writes: > PG: 7.2.3 (RedHat 8.0) You're using PG 7.2.3 with the PG 7.1 JDBC driver; FWIW, upgrading to newer software is highly recommended. > The two sites were performing at comparable speeds until a few days > ago, when we deleted several million records from each database and > then did a vacuum full; analyze on both. Now inserts at Site B are > several orders of magnitude slower than at Site A. Two thoughts: (1) Can you confirm that the VACUUM FULL on site B actually removed all the tuples you intended it to remove? Concurrent transactions can limit the amount of data that VACUUM FULL is able to reclaim. If you run contrib/pgstattuple (or compare the database's disk consumption with the number of live rows in it), you should be able to tell. (2) Look at the EXPLAIN for the SELECTs generated by the ON INSERT trigger -- is there any difference between site A and B? -Neil