Re: VACUUM ANALYZE suddenly taking forever

Did you try to reset the statistics ? select pg_stat_reset();


On Thursday 27 July 2006 17:28, Nolan Cafferky wrote:
> Synopsis: VACUUM ANALYZE on full database used to take just a few
> minutes, now it takes several hours, with no apparant improvement in
> successive runs.
>
> Details:
>
> I have a production database server hosting two heavily used databases
> and not much else.  We're currently running postgres 8.0.8.  Normally we
> have a VACUUM ANALYZE run nightly on both databases, which only takes a
> couple of minutes each to complete.  We also have a report that runs
> hourly on one of the databases and dumps a large amount of data into a
> materialized view.  It normally takes 10-20 minutes (we could probably
> optimize it, but it's never made it up the priority list).
>
> Anyway, about two nights ago, the hourly report started running
> indefinitely, and we've had to turn it off, after having 16 copies of it
> waiting in line for the first to finish.  Since then, VACUUM ANALYZE has
> been taking several hours instead of several minutes on both databases.
> Yesterday I ran the VACUUM ANALYZE manually on both databases, hoping
> that there was just some transient cleanup problem, but we've had the
> same results today.
>
> What would cause this, and what can I do to fix it?  For the moment, I'm
> going to claim the "we didn't change anything!" mantra - no development
> we've done in the past few days seems like it would significantly
> influence both databases.  The so far untried ideas I've had are:
>
> * Try out the autovacuum service
> * Re-index tables (this hasn't been done for at least months, maybe never)
> * Do some selective VACUUM FULL on high-use tables (materialized view
> for report seems like a likely culprit, but also seems like it wouldn't
> influence both databases)
> * Restart postgres, restart the machine itself, and other useless
> handwaving
>
> \begin{more-background-information}
>
> * The database server is a quad Opteron, about 2GHz each.  8 GB of RAM,
> and a several hard disk RAID. It's burly. I believe we're running on a
> Gentoo linux installation, although postgres was installed from source.
> Again, we're running postgres 8.0.8.  Here's some sample output from a
> "vmstat 1 5" that I just ran:
> procs -----------memory---------- ---swap-- -----io---- --system--
> ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy
> id wa
>  0  0   1208 5658464      0 2256384    0    0   554   344    1     1 10
> 2 83  6
>  1  0   1208 5640272      0 2273928    0    0    24   476 1405  1885 12
> 3 83  2
>  1  0   1208 5652368      0 2258628    0    0     0   560 1194   663  6
> 1 91  2
>  0  0   1208 5653392      0 2259104    0    0    16   750 1979  4362 15
> 4 78  2
>  1  0   1208 5649744      0 2259716    0    0    24   661 1651  3114 21
> 4 73  2
> * Yes, so far we've been doing a direct VACUUM ANALYZE on everything,
> plus VACUUM FULL ANALYZE on a few tables, instead of using the
> autovacuum service like we should. It seems like there wouldn't be such
> an abrupt change in performace because of that.
> * Shortly after killing the 16 or so backed-up reports, the partition
> postgres had the data/subtrans directory in filled up, and we had a
> bunch of "No space left on device" errors for a minute or two.  The
> partitions do deserve some rearranging, but for now we've made some
> adjustments and postgres is wallowing in free disk space.
>
> \end{more-background-information}
>
> Suggestions?