drop tempoary table VERY slow

And when I do drop a table CPU usage goes to 99% on one CPU.

Sam

-----Original Message-----
From: Sam Liddicott
Sent: 31 May 2002 10:57
To: 'pgsql-bugs@postgresql.org'
Subject: drop tempoary table VERY slow

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.

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.

Regards,
Andrew.

Sam

-----Original Message-----
From: Sam Liddicott
Sent: 31 May 2002 10:57
To: 'pgsql-bugs@postgresql.org'
Subject: drop tempoary table VERY slow

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.

--
--------------------------------------------------------------------
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?

-----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

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?

-----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

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?

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