pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven Flatt <steven.flatt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insertion to temp table deteriorating over time
Date: Fri, 15 Dec 2006 14:09:48 -0500
Message-ID: <13947.1166209788@sss.pgh.pa.us> (raw)
In-Reply-To: <357fa7590612151026t4ae033f6w5367cac9d0b67064@mail.gmail.com>
References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com>
	<9315.1166030958@sss.pgh.pa.us>
	<357fa7590612131517q375fc0by50cb4257bcce7a94@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>

"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



view thread (22+ messages)  latest in thread

Message-ID: <13947.1166209788@sss.pgh.pa.us>
Permalink:  ../13947.1166209788@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13947.1166209788@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, steven.flatt@gmail.com
  Subject: Re: Insertion to temp table deteriorating over time
  In-Reply-To: <13947.1166209788@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox