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 7EB48D1B445 for ; Sat, 6 Dec 2003 02:55:21 +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 65049-06 for ; Fri, 5 Dec 2003 22:54:54 -0400 (AST) Received: from lakemtao04.cox.net (lakemtao04.cox.net [68.1.17.241]) by svr1.postgresql.org (Postfix) with ESMTP id D3F88D1B43E for ; Fri, 5 Dec 2003 22:54:49 -0400 (AST) Received: from [192.168.0.13] ([68.105.167.134]) by lakemtao04.cox.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id <20031206025451.PPKC19895.lakemtao04.cox.net@[192.168.0.13]>; Fri, 5 Dec 2003 21:54:51 -0500 From: Robert Treat To: swampler@noao.edu, Postgres-performance Subject: Re: Help tracking down problem with inserts slowing down... Date: Fri, 5 Dec 2003 21:54:52 -0500 User-Agent: KMail/1.5 References: <1070661108.20063.86.camel@weaver.tuc.noao.edu> In-Reply-To: <1070661108.20063.86.camel@weaver.tuc.noao.edu> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312052154.52223.xzilla@users.sourceforge.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/138 X-Sequence-Number: 4998 On Friday 05 December 2003 16:51, Steve Wampler wrote: > I need some help tracking down a sudden, massive slowdown > in inserts in one of our databases. > > PG: 7.2.3 (RedHat 8.0) > > Background. We currently run nearly identical systems > at two sites: Site A is a 'lab' site used for development, > Site B is a production site. > > The databases in question have identical structure: > > A simple table with 4 columns with a trigger function > on inserts (which checks to see if the entry already > exists, and if so, changes the insert into an update...) > A simple view with 4 columns into the above table. > > All access is through jdbc (JDK 1.3.1, jdbc 7.1-1.3), > postgresql.conf's are identical. > > 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. The odd thing is that > Site B's DB now has only 60,000 records while Site A's is > up around 3 million. Inserts at A average 63ms, inserts > at B are now up at 4.5 seconds! > > EXPLAIN doesn't show any difference between the two. > > Can someone suggest ways to track this down? I don't know > much about postgresql internals/configuration. > What does explain analyze show for the insert query? Are there FK and/or Indexes involved here? Did you you reindex? A vacuum verbose could give you a good indication if you need to reindex, compare the # of pages in the index with the # in the table. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL