Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D0AE09FA4C5 for ; Fri, 15 Dec 2006 15:21:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 21641-07 for ; Fri, 15 Dec 2006 15:21:10 -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.231]) by postgresql.org (Postfix) with ESMTP id 098529FA485 for ; Fri, 15 Dec 2006 15:21:09 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so797275wxd for ; Fri, 15 Dec 2006 11:21:09 -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=eetQXCXgDkNcqddM9W9ZHyJmivuR+sczDFNfLi/5aePdlORw9N1YnuFYnQ78ALg9inWWovgHFgqSBJchKuonHM1ANJf/6qstYiY4Utbr5H6wlWgm/EmuTuXxHkY97fEUCgWXTsH5I1iUO6dgQ04TZZTPT3AmBkMgu0Nb6rfXKdQ= Received: by 10.70.59.17 with SMTP id h17mr1590298wxa.1166210468847; Fri, 15 Dec 2006 11:21:08 -0800 (PST) Received: by 10.70.108.15 with HTTP; Fri, 15 Dec 2006 11:21:01 -0800 (PST) Message-ID: <357fa7590612151121x5f95102fj21a96b1bdc7eb002@mail.gmail.com> Date: Fri, 15 Dec 2006 14:21:01 -0500 From: "Steven Flatt" To: "Tom Lane" Subject: Re: Insertion to temp table deteriorating over time Cc: pgsql-performance@postgresql.org In-Reply-To: <13947.1166209788@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_177119_24314674.1166210461771" References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com> <13032.1166052447@sss.pgh.pa.us> <357fa7590612141240t55e70695q770a05a68a67536c@mail.gmail.com> <25209.1166131396@sss.pgh.pa.us> <357fa7590612150821i3eca3f66ge2ced1ee20fa2dac@mail.gmail.com> <11651.1166202586@sss.pgh.pa.us> <357fa7590612150955g1b9c733duaf000c23d28aa8af@mail.gmail.com> <12508.1166206006@sss.pgh.pa.us> <357fa7590612151026t4ae033f6w5367cac9d0b67064@mail.gmail.com> <13947.1166209788@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/427 X-Sequence-Number: 22483 ------=_Part_177119_24314674.1166210461771 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I've been trying to reproduce the problem for days now :). I've done pretty much exactly what you describe below, but I can't reproduce the problem on any of our lab machines. Something is indeed special in this environment. Thanks for all your help, Steve On 12/15/06, Tom Lane wrote: > > "Steven Flatt" writes: > > Are the removable rows in pg_class even an issue? So what if 5000-6000 > dead > > tuples are generated every hour then vacuumed? Performance continues to > > steadily decline over a few days time. Memory usage does not appear to > be > > bloating. Open file handles remain fairly fixed. Is there anything > else I > > can monitor (perhaps something to do with the odbc connection) that I > could > > potentially correlate with the degrading performance? > > At this point I think the most productive thing for you to do is to try > to set up a self-contained test case that reproduces the slowdown. That > would allow you to poke at it without disturbing your production system, > and would let other people look at it too. From what you've said, I'd > try a simple little program that inserts some data into a temp table, > truncates the table, and repeats, as fast as it can, using the same SQL > commands as your real code and similar but dummy data. It shouldn't > take long to observe the slowdown if it occurs. If you can't reproduce > it in isolation then we'll know that some other part of your application > environment is contributing to the problem; if you can, I'd be happy to > look at the test case with gprof or oprofile and find out exactly what's > going on. > > regards, tom lane > ------=_Part_177119_24314674.1166210461771 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I've been trying to reproduce the problem for days now :).  I've done pretty much exactly what you describe below, but I can't reproduce the problem on any of our lab machines.  Something is indeed special in this environment.
 
Thanks for all your help,
 
Steve

 
On 12/15/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Steven Flatt" <steven.flatt@gmail.com> writes:
> Are the removable rows in pg_class even an issue?  So what if 5000-6000 dead
> tuples are generated every hour then vacuumed?  Performance continues to
> steadily decline over a few days time.  Memory usage does not appear to be
> bloating.  Open file handles remain fairly fixed.  Is there anything else I
> can monitor (perhaps something to do with the odbc connection) that I could
> potentially correlate with the degrading performance?

At this point I think the most productive thing for you to do is to try
to set up a self-contained test case that reproduces the slowdown.  That
would allow you to poke at it without disturbing your production system,
and would let other people look at it too.  From what you've said, I'd
try a simple little program that inserts some data into a temp table,
truncates the table, and repeats, as fast as it can, using the same SQL
commands as your real code and similar but dummy data.  It shouldn't
take long to observe the slowdown if it occurs.  If you can't reproduce
it in isolation then we'll know that some other part of your application
environment is contributing to the problem; if you can, I'd be happy to
look at the test case with gprof or oprofile and find out exactly what's
going on.

                       regards, tom lane

------=_Part_177119_24314674.1166210461771--