Thread: Insertion to temp table deteriorating over time

On 12/13/06, Steven Flatt <steven.flatt@gmail.com> wrote:
> Hi,
>
> Our application is using Postgres 7.4 and I'd like to understand the root
> cause of this problem:
>
> To speed up overall insert time, our application will write thousands of
> rows, one by one, into a temp table

1. how frequently are you commiting the transaction ?
    if you commit less frequetly it will be faster.

2. If you use COPY instead of INSERT it will be faster.
    using COPY is easy with DBD::Pg (perl). In versions
    8.x i think there has been major speed improvements
   in COPY.

I do not know the root cause of slowdown though.

Regds
mallah.



>

"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

"Steven Flatt" <steven.flatt@gmail.com> writes:
> 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).

Do you actually need to restart the postmaster, or is just starting a
fresh session (fresh backend) sufficient?  And again, have you monitored
the backend process to see if it's bloating memory-wise or open-file-wise?

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
> 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.

Hm, *are* you vacuuming only infrequently?  In particular, what is your
maintenance policy for pg_class?

Some experimentation with TRUNCATE and VACUUM VERBOSE shows that in 7.4,
a TRUNCATE of a temp table with no indexes and no toast table generates
three dead row versions in pg_class.  (I'm surprised that it's as many
as three, but in any case the TRUNCATE would certainly have to do one
update of the table's pg_class entry and thereby generate one dead row
version.)

If you're being sloppy about vacuuming pg_class, then over time the
repeated-truncate pattern would build up a huge number of dead rows
in pg_class, *all with the same OID*.  It's unsurprising that this
would create some slowness in looking up the temp table's pg_class
entry.

If this theory is correct, the reason that starting a fresh backend
makes it fast again is that the new backend creates a whole new temp
table with a new OID assigned, and so the adjacent litter in pg_class
doesn't matter anymore (or not so much anyway).

Solution would be to institute regular vacuuming of the system
catalogs...

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Regarding your other email -- interesting -- but we are vacuuming pg_class
> every hour.  So I don't think the answer lies there...

That's good, but is the vacuum actually accomplishing anything?  I'm
wondering if there's also a long-running transaction in the mix.
Try a manual "VACUUM VERBOSE pg_class;" after the thing has slowed down,
and see what it says about removable and nonremovable rows.

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Here's the output of "VACUUM VERBOSE pg_class".  I think it looks fine.  I
> even did it three times in a row, each about 10 minutes apart, just to see
> what was changing:

Hm, look at the numbers of rows removed:

> INFO:  "pg_class": found 5680 removable, 3263 nonremovable row versions in
> 625 pages
> DETAIL:  0 dead row versions cannot be removed yet.

> INFO:  "pg_class": found 24 removable, 3263 nonremovable row versions in 625
> pages
> DETAIL:  0 dead row versions cannot be removed yet.

> INFO:  "pg_class": found 0 removable, 3263 nonremovable row versions in 625
> pages
> DETAIL:  0 dead row versions cannot be removed yet.

The lack of unremovable dead rows is good, but why were there so many
dead rows the first time?  You didn't say what the cycle time is on your
truncate-and-refill process, but the last two suggest that the average
rate of accumulation of dead pg_class rows is only a couple per minute,
in which case it's been a lot longer than an hour since the previous
VACUUM of pg_class.  I'm back to suspecting that you don't vacuum
pg_class regularly.  You mentioned having an hourly cron job to fire off
vacuums ... are you sure it's run as a database superuser?

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Our application is such that there is a great deal of activity at the
> beginning of the hour and minimal activity near the end of the hour.

OK ...

> The truncate and re-fill process is done once per hour, at the end of the
> high-load cycle, so I doubt that's even a big contributor to the number of
> removable rows in pg_class.

Oh, then where *are* the removable rows coming from?  At this point I
think that the truncate/refill thing is not the culprit, or at any rate
is only one part of a problematic usage pattern that we don't see all of
yet.

            regards, tom lane

"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

"Steven Flatt" <steven.flatt@gmail.com> writes:
> 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.

Yuck.  You could try strace'ing the problem backend and see if anything
is visibly different between fast and slow operation.  I don't suppose
you have oprofile on that machine, but if you did it'd be even better.

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
> I can fix this problem by using CREATE TEMP TABLE ... LIKE instead of CREATE
> TEMP TABLE ... AS.

That seems ... um ... bizarre.  Now are you able to put together a
self-contained test case?  Seems like we could have two independent bugs
here: first, why (and how) is the temp table different, and second how
does that result in the observed performance problem.

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Issue #1:

> (I'm assuming there's a reasonable explanation for this.)  If I create a
> temp table with a single varchar column (or text column), do 100 inserts to
> that table, copy to a permanent table, truncate the temp table and repeat,
> the time required for the 100 inserts grows almost linearly.

I still can't reproduce this.  Using 7.4 branch tip, I did

create temp table foo(f1 varchar);
create table nottemp(f1 varchar);
\timing
insert into foo select stringu1 from tenk1 limit 100; insert into nottemp select * from foo; truncate foo;
insert into foo select stringu1 from tenk1 limit 100; insert into nottemp select * from foo; truncate foo;
... repeat several thousand times ...

and couldn't see any consistent growth in the reported times.  So either
it's been fixed since 7.4.6 (but I don't see anything related-looking in
the CVS logs), or you haven't provided all the details.

> Now the varchar columns that end up in the perm view come from the tbl
> table, but in tbl, they are defined as varchar(40).  Somehow the 40 limit is
> lost when constructing the view.

Yeah, this is a known issue with UNIONs not preserving the length info
--- which is not entirely unreasonable: what will you do with varchar(40)
union varchar(50)?  There's a hack in place as of 8.2 to keep the
length if all the union arms have the same length.

            regards, tom lane

"Steven Flatt" <steven.flatt@gmail.com> writes:
>  I can't reproduce the problem that way either (or when using a server-side
> PLpgSQL function to do similar).  It looks like you have to go through an
> ODBC connection, with the looping done on the client side.  Each individual
> insert to the temp table needs to be sent over the connection and this is
> what degrades over time.  I can reproduce on 7.4.6 and 8.1.4.  I have a
> small C program to do this which I can send you offline if you're
> interested.

Please.

            regards, tom lane