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 26B7ED1B54F for ; Fri, 17 Oct 2003 15:00:35 +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 58034-07 for ; Fri, 17 Oct 2003 12:00:04 -0300 (ADT) Received: from pass.bivio.com (pass.bivio.com [216.87.82.238]) by svr1.postgresql.org (Postfix) with ESMTP id F0309D1B54C for ; Fri, 17 Oct 2003 11:59:58 -0300 (ADT) Received: from LIFT.bivio.com (lift.bivio.com [216.87.82.233]) by pass.bivio.com (8.9.3/8.9.3) with ESMTP id IAB21227 for ; Fri, 17 Oct 2003 08:59:49 -0600 From: Rob Nagler MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16272.318.870000.93584@gargle.gargle.HOWL> Date: Fri, 17 Oct 2003 08:48:30 -0600 To: pgsql-performance@postgresql.org Subject: vacuum locking X-Mailer: VM 6.96 under Emacs 21.1.1 Organization: bivio Software Artisans, Inc. X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/478 X-Sequence-Number: 4226 It seems a simple "vacuum" (not full or analyze) slows down the database dramatically. I am running vacuum every 15 minutes, but it takes about 5 minutes to run even after a fresh import. Even with vacuuming every 15 minutes, I'm not sure vacuuming is working properly. There are a lot of updates. The slowest relation is the primary key index, which is composed of a sequence. I've appended a csv with the parsed output from vacuum. The page counts are growing way too fast imo. I believe this is caused by the updates, and index pages not getting re-used. The index values aren't changing, but other values in the table are. Any suggestions how to make vacuuming more effective and reducing the time it takes to vacuum? I'd settle for less frequent vacuuming or perhaps index rebuilding. The database can be re-imported in about an hour. Rob ---------------------------------------------------------------- Spacing every 15 minutes Pages,Tuples,Deleted 7974,1029258,1536 7979,1025951,4336 7979,1026129,52 7979,1025618,686 7979,1025520,152 7980,1025583,28 7995,1028008,6 8004,1030016,14 8010,1026149,4965 8012,1026684,6 8014,1025910,960 8020,1026812,114 8027,1027642,50 8031,1027913,362 8040,1028368,784 8046,1028454,1143 8049,1029155,6 8053,1029980,10 8065,1031506,24 8084,1029134,4804 8098,1031004,346 8103,1029412,3044 8118,1029736,1872 8141,1031643,1704 8150,1032597,286 8152,1033222,6 8159,1029436,4845 8165,1029987,712 8170,1030229,268 8176,1029568,1632 8189,1030136,1540 8218,1030915,3963 8255,1033049,4598 8297,1036583,3866 8308,1031412,8640 8315,1031987,1058 8325,1033892,6 8334,1030589,4625 8350,1031709,1040 8400,1033071,5946 8426,1031555,8368 8434,1031638,2240 8436,1031703,872 8442,1031891,612