Thread

1. Re: drop tempoary table VERY slow

   Sam Liddicott <sam.liddicott@ananova.com> — 2002-06-05T09:02:53Z

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

2. Re: drop tempoary table VERY slow

   Andrew McMillan <andrew@catalyst.net.nz> — 2002-06-05T11:57:47Z

   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?