pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
VACUUM ANALYZE suddenly taking forever
4+ messages / 4 participants
[nested] [flat]

* VACUUM ANALYZE suddenly taking forever
@ 2006-07-27 15:28  Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Nolan Cafferky @ 2006-07-27 15:28 UTC (permalink / raw)
  To: pgsql-admin

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?

-- 
Nolan Cafferky
Software Developer
IT Department
RBS Interactive
nolan.cafferky@rbsinteractive.com




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: VACUUM ANALYZE suddenly taking forever
@ 2006-07-27 15:31  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Alvaro Herrera @ 2006-07-27 15:31 UTC (permalink / raw)
  To: Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>; +Cc: pgsql-admin

Nolan Cafferky wrote:

> 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

* Check whether there are open, idle transactions and close them

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: VACUUM ANALYZE suddenly taking forever
@ 2006-07-27 15:36  plongeur@arcor.de
  parent: Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>
  2 siblings, 0 replies; 4+ messages in thread

From: plongeur@arcor.de @ 2006-07-27 15:36 UTC (permalink / raw)
  To: pgsql-admin


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?



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: VACUUM ANALYZE suddenly taking forever
@ 2006-07-27 21:32  adey <adey11@gmail.com>
  parent: Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>
  2 siblings, 0 replies; 4+ messages in thread

From: adey @ 2006-07-27 21:32 UTC (permalink / raw)
  To: Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>; +Cc: pgsql-admin

Try running pgstattuple on some of the tables that have not had vacuum full,
and some of those that have. It will tell you how many dead tuples there are
in the table, which is an indicator of how seriously the table needs a full
vacuum.

Run IPCS on the Unix command line to see how much of your SHMMAX is being
used? You may have reached a threshold in one of the 6 Postgres parameters
that use SHMMAX causing a slowdown, like max_fsm_pages (see the report at
the end of a database vacuum full output).


On 7/28/06, Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com> 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?
>
> --
> Nolan Cafferky
> Software Developer
> IT Department
> RBS Interactive
> nolan.cafferky@rbsinteractive.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2006-07-27 21:32 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-27 15:28 VACUUM ANALYZE suddenly taking forever Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>
2006-07-27 15:31 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-07-27 15:36 ` plongeur@arcor.de
2006-07-27 21:32 ` adey <adey11@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox