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?