Thread: drop tempoary table VERY slow

drop tempoary table VERY slow

From
"Sam Liddicott"
Date:
I have a DB where this:

select 1 into temporary table x;
runs quickly, but

drop table x;

takes many seconds to run.

I don't know why.

But:

begin;
select 1 into temporary table x;
abort;

is very quick.

Note the slow dropping applies to automatic dropping of temporary tables
when the connection is closed.
I now "abort" all my query sessions for speed.

I cannot reproduce this on "template1" and it has not always been the case
for my DB.

Sam


  _____


Samuel Liddicott
Support Consultant
sam@ananova.com <mailto:sam@ananova.com>
Direct Dial: +44 (0)113 367 4523
Fax: +44 (0)113 367 4680
Switchboard: +44 (0)113 367 4600

Ananova Limited
Marshall Mill
Marshall Street
Leeds
LS11 9YJ

http://www.ananova.com

Registered Office:
St James Court
Great Park Road
Almondsbury Park
Bradley Stoke
Bristol BS32 4QJ
Registered in England No.2858918

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you receive
this in error, please contact the sender and delete the material from any
computer.

Re: drop tempoary table VERY slow

From
Andrew McMillan
Date:
On Wed, 2002-06-05 at 21:02, Sam Liddicott wrote:
> >
> > When did you last do a vacuum?  If you are adding and
> > dropping temporary
> > tables a lot, perhaps you should vacuum pg_class and
> > pg_attribute often
> > as well.
>
> I do a vacuum analyse every night on that whole DB, cron logs show pg_
> tables are also vacummed, taking 97 seconds for pg_class and 463 seconds for
> pg_attribute.
>
> The DB size is about 10G and we do about 16,000 temporary tables per day.
> The whole thing has become enourmously faster since we enclosed the queries
> in an aborting transaction.
> (If you are interested it serves Ananova TV listings at
> http://www.ananova.com/tv_listings/_tv_full_listings.html)

Interesting.  Those are pretty long times to take for a vacuum on those
tables - if you are using 7.2.x have you tried more frequent vacuum?
Perhaps with a vacuum full each night?

I think that the aborting transaction approach, since it works, is most
likely to be your best bet in general, however.

It would be interesting to see the 'vacuum full analyze' results for the
system tables in that DB, although perhaps less interesting while you
are running your current solution - maybe a comparison would be
worthwhile.

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

Re: drop tempoary table VERY slow

From
"Sam Liddicott"
Date:
> -----Original Message-----
> From: Andrew McMillan [mailto:andrew@catalyst.net.nz]
> Sent: 02 June 2002 11:52
> To: Sam Liddicott
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] drop tempoary table VERY slow
>
>
> On Fri, 2002-05-31 at 22:28, Sam Liddicott wrote:
> > And when I do drop a table CPU usage goes to 99% on one CPU.
>
> When did you last do a vacuum?  If you are adding and
> dropping temporary
> tables a lot, perhaps you should vacuum pg_class and
> pg_attribute often
> as well.

I do a vacuum analyse every night on that whole DB, cron logs show pg_
tables are also vacummed, taking 97 seconds for pg_class and 463 seconds for
pg_attribute.

The DB size is about 10G and we do about 16,000 temporary tables per day.
The whole thing has become enourmously faster since we enclosed the queries
in an aborting transaction.
(If you are interested it serves Ananova TV listings at
http://www.ananova.com/tv_listings/_tv_full_listings.html)

Sam

Re: drop tempoary table VERY slow

From
"Sam Liddicott"
Date:
> -----Original Message-----
> From: Andrew McMillan [mailto:andrew@catalyst.net.nz]
> Sent: 05 June 2002 12:58
> To: Sam Liddicott
> Cc: pgsql-bugs@postgresql.org
> Subject: RE: [BUGS] drop tempoary table VERY slow
>
> Interesting.  Those are pretty long times to take for a
> vacuum on those
> tables - if you are using 7.2.x have you tried more frequent vacuum?
> Perhaps with a vacuum full each night?

Hmmm.

> I think that the aborting transaction approach, since it
> works, is most
> likely to be your best bet in general, however.
>
> It would be interesting to see the 'vacuum full analyze'
> results for the
> system tables in that DB, although perhaps less interesting while you
> are running your current solution - maybe a comparison would be
> worthwhile.

Alas we won't be able to downgrade as it affected the service seriously.
In doing a full vacuum I notice such errors as:

NOTICE:  Index pg_index_indrelid_index NUMBER OF INDEX' TUPLES (92) IS NOT
THE SAME AS HEAP' (86). Recreate the index

Hmm.  It's not my index (of course) I'm not sure how to go about re-creating
it.

Sam

Re: drop tempoary table VERY slow

From
Andrew McMillan
Date:
On Thu, 2002-06-06 at 01:54, Sam Liddicott wrote:

> >
> > It would be interesting to see the 'vacuum full analyze'
> > results for the
> > system tables in that DB, although perhaps less interesting while you
> > are running your current solution - maybe a comparison would be
> > worthwhile.
>
> Alas we won't be able to downgrade as it affected the service seriously.
> In doing a full vacuum I notice such errors as:
>
> NOTICE:  Index pg_index_indrelid_index NUMBER OF INDEX' TUPLES (92) IS NOT
> THE SAME AS HEAP' (86). Recreate the index
>
> Hmm.  It's not my index (of course) I'm not sure how to go about re-creating
> it.

To reindex the system tables you need to shut down your database, then
run a standalone postgres backend:

postgres -O -P <dbname>

Inside that you then:

REINDEX DATABASE <dbname>;

Once it is done, quit and restart your database.

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

Re: drop tempoary table VERY slow

From
"Sam Liddicott"
Date:
Sorry for the delays on this that machine actually died recently and had to
be rebuit before I could do any more tests.

> > > It would be interesting to see the 'vacuum full analyze'
> > > results for the
> > > system tables in that DB, although perhaps less
> interesting while you
> > > are running your current solution - maybe a comparison would be
> > > worthwhile.

Are you very interested in the comparison?  I could fake a load of the
non-transactioned tempoary table queries if you are really interested.

I also noted we sometimes get a load of temporary tables left lying around
that look "global" and we have to drop by hand.

After rebuilding the machine I did a dump from the other machine and
inserted on the new machine (schema, data and all) and the new machine is
VERY slow at queries; taking 4 seconds at 100% cpu at times instead of
0.2-0.5 seconds or so.

Yet if I copy over the binary files when the DB's are stopped the new
machine is very fast at queries.
Could this be because the new machine started on 7.2.1 with a different
optimiser and so never generated query stats the the old box did while it
was still on 7.2 ?

Sam