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