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