Re: Manual vacs 5x faster than autovacs?

The autovac may have done most of the work before you killed it ...
I'm new to Postgres, but from limited subjective experience, it seems
it's a lot faster to vaccum ranges of blocks that are were recently
vacuumed (at minimum, a good chunk of table will have been brought
into buffer cache by both Postgres and the OS during the prior pass).

I've found that with very large data tables, the auto-vaccum on
default settings isn't as aggressive as I'd like ... I find running a
VACUUM ANALYZE isn't at all intrusive, though I prefer to do it once a
day at 3am.

Beware that VACUUM FULL locks an entire table at a time :-)

Cheers
Dave

On Thu, Nov 12, 2009 at 8:33 AM, Wayne Beaver <wayne(at)acedsl(dot)com> wrote:
> Hi All,
>
> Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I know
> that's old). I have seen *really* long-running autovacs eating up system
> resources. While the below is not an example of *really* long, it shows how
> I killed an autovac which had been running for more than 10 minutes, then
> ran a VAC FULL ANALYZE on same exact table in about ~2 min. Any wisdom here?
> Attributable to autovac_worker settings? Or Pg version? Other?
>
> Any insight appreciated.
>
> wb
>
> ++++++++++++++++++++++++++
>
> $ psql template1 -c "SELECT procpid, current_query, to_char (now() -
> backend_start, 'HH24:MI:SS') AS connected_et, to_char (now() -
> query_start,'HH24:MI:SS') AS query_et FROM pg_stat_activity WHERE
> datname='mydb' ORDER BY query_et DESC LIMIT 1"
>
>  procpid |                   current_query            | connected_et |
> query_et
> ---------+--------------------------------------------+--------------+----------
>    9064 | autovacuum: VACUUM ANALYZE myschema.mytable    | 00:12:07     |
> 00:11:38
>
>
>
> $ kill 9064
>
>
> $ date; psql mydb -c "VACUUM FULL ANALYZE myschema.mytable"; date
> Wed Nov 11 17:25:41 UTC 2009
> VACUUM
> Wed Nov 11 17:27:59 UTC 2009
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>