Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B33A49FA162 for ; Wed, 13 Dec 2006 19:17:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 98975-10 for ; Wed, 13 Dec 2006 19:17:41 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.237]) by postgresql.org (Postfix) with ESMTP id 9A07B9F9FB2 for ; Wed, 13 Dec 2006 19:17:44 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so301292wxd for ; Wed, 13 Dec 2006 15:17:43 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=RWqGo9JvRf9xN+PAW0j86QdWIKdRuvG/NDaWJDCd4hvivtPv1ENVnAYEbX27m+dfHLdODaUEYVI1fQ15uh/AhIcB6wn+8BT3u1znYtemd+zJAC0i+S1u5NoqRPOLVsSoywl9ciw9crMtnX5D5XYZdA72a7ede4atSkiVQYbbXvI= Received: by 10.70.40.1 with SMTP id n1mr313933wxn.1166051862151; Wed, 13 Dec 2006 15:17:42 -0800 (PST) Received: by 10.70.108.15 with HTTP; Wed, 13 Dec 2006 15:17:41 -0800 (PST) Message-ID: <357fa7590612131517q375fc0by50cb4257bcce7a94@mail.gmail.com> Date: Wed, 13 Dec 2006 18:17:41 -0500 From: "Steven Flatt" To: "Tom Lane" Subject: Re: Insertion to temp table deteriorating over time Cc: pgsql-performance@postgresql.org In-Reply-To: <9315.1166030958@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_147201_3188773.1166051861993" References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com> <9315.1166030958@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/349 X-Sequence-Number: 22405 ------=_Part_147201_3188773.1166051861993 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline After running some further standalone tests using temp tables, I'm not convinced the problem is specific to temp table usage. In fact it looks like generic SQL activity degrades over time. Having said that, what kinds of things should I be looking for that could deteriorate/bloat over time? Ordinarily the culprit might be infrequent vacuuming or analyzing, but that wouldn't be corrected by a restart of Postgres. In our case, restarting Postgres gives us a huge performance improvement (for a short while, anyways). By the way, we are using PG 7.4.6 on FreeBSD 5.30.0170. The temp table has 15 columns: a timestamp, a double, and the rest integers. It has no indexes. Thanks, Steve On 12/13/06, Tom Lane wrote: > > "Steven Flatt" writes: > > Any idea where the bloat is happening? I believe that if we were > dropping > > and re-creating the temp table over and over, that could result in > pg_class > > bloat (among other catalog tables), but what is going wrong if we use > the > > same table over and over and truncate it? > > That seems very strange --- I too would have expected a TRUNCATE to > bring you back to ground zero performance-wise. I wonder whether the > issue is not directly related to the temp table but is just some generic > resource leakage problem in a very long-running backend. Have you > checked to see if the backend process bloats memory-wise, or perhaps has > a huge number of files open (I wonder if it could be leaking open file > handles to the successive generations of the temp table)? Are you sure > that the slowdown is specific to inserts into the temp table, as opposed > to generic SQL activity? > > Also, which PG version is this exactly ("7.4" is not specific enough)? > On what platform? Can you show us the full schema definition for the > temp table and any indexes on it? > > regards, tom lane > ------=_Part_147201_3188773.1166051861993 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
After running some further standalone tests using temp tables, I'm not convinced the problem is specific to temp table usage.  In fact it looks like generic SQL activity degrades over time.
 
Having said that, what kinds of things should I be looking for that could deteriorate/bloat over time?  Ordinarily the culprit might be infrequent vacuuming or analyzing, but that wouldn't be corrected by a restart of Postgres.  In our case, restarting Postgres gives us a huge performance improvement (for a short while, anyways).
 
By the way, we are using PG 7.4.6 on FreeBSD 5.30.0170.  The temp table has 15 columns: a timestamp, a double, and the rest integers.  It has no indexes.
 
Thanks,
Steve

 
On 12/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Steven Flatt" <steven.flatt@gmail.com> writes:
> Any idea where the bloat is happening?  I believe that if we were dropping
> and re-creating the temp table over and over, that could result in pg_class
> bloat (among other catalog tables), but what is going wrong if we use the
> same table over and over and truncate it?

That seems very strange --- I too would have expected a TRUNCATE to
bring you back to ground zero performance-wise.  I wonder whether the
issue is not directly related to the temp table but is just some generic
resource leakage problem in a very long-running backend.  Have you
checked to see if the backend process bloats memory-wise, or perhaps has
a huge number of files open (I wonder if it could be leaking open file
handles to the successive generations of the temp table)?  Are you sure
that the slowdown is specific to inserts into the temp table, as opposed
to generic SQL activity?

Also, which PG version is this exactly (" 7.4" is not specific enough)?
On what platform?  Can you show us the full schema definition for the
temp table and any indexes on it?

                       regards, tom lane

------=_Part_147201_3188773.1166051861993--