Andrew Sullivan wrote:
On Thu, Jun 01, 2006 at 01:54:08PM +0200, Antoine wrote:
Hi,
We just don't seem to be getting much benefit from autovacuum. Running
a manual vacuum seems to still be doing a LOT, which suggests to me
that I should either run a cron job and disable autovacuum, or just
run a cron job on top of autovacuum.
Don't know if this was covered in an earlier thread. Bear with me if so.
I'm working with 7.4.8 and 8.0.3 systems, and pg_autovacuum does have some
glitches ... in part solved by the integrated autovac in 8.1:
- in our env, clients occasionally hit max_connections. This is a known and
(sort of) desired pushback on load. However, that sometimes knocks pg_autovacuum
out.
- db server goes down for any reason: same problem.
Just restarting pg_autovacuum is not good enough; when pg_autovacuum terminates,
it loses its state, so big tables that change less than 50% between such
terminations may never get vacuumed (!)
For that reason, it's taken a switch to a Perl script run from cron every 5
minutes, that persists state in a table. The script is not a plug-compatible
match for pg_autovacuum (hardcoded rates; hardcoded distinction between user and
system tables), but you may find it useful.
--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.
#!/usr/bin/env perl
use strict;
use warnings;
use Carp;
use DBI;
use POSIX qw(strftime);
# Hardcoded (aggressive) autovacuum parameters:
my ($VAC_BASE, $VAC_RATE) = (1000, 0.8);
my ($ANA_BASE, $ANA_RATE) = ( 500, 0.4);
my $VERBOSE = 'VERBOSE';
my $start = time;
my $stamp = strftime "==== %FT%T autovac: ", localtime;
open STDERR, ">&STDOUT"; # Redirect PG "VERBOSE" output.
my $dbh = DBI->connect("dbi:Pg(PrintError=1,RaiseError=1):");
# REVISIT: move this to schema:
my $oid = $dbh->selectall_arrayref(<<"__SQL__")->[0][0];
SELECT oid FROM pg_class WHERE relname = 'autovac_state';
__SQL__
$dbh->do(<<"__SQL__") if !defined $oid;
CREATE TABLE public.autovac_state(
relid oid NOT NULL PRIMARY KEY,
name text NOT NULL,
analyze_time timestamptz,
vacuum_time timestamptz,
analyze_tups bigint, -- (n_tup_ins+n_tup_upd+n_tup_del) at last ANALYZE
vacuum_tups bigint -- (n_tup_upd+n_tup_del) at last VACUUM
);
__SQL__
# Calculate updates/additions to autovac_state:
my $anavac = join ";", map {$_->[0]} @{$dbh->selectall_arrayref(<<"__SQL__")};
SELECT * INTO TEMP new_state FROM (
SELECT
relid, -- identify tables by ID, so that (re)created tables always
-- are treated as fresh tables.
name, -- for constructing the vacuum/analyze command
old_relid, -- NULL means this will need a new state table entry
analyze_tups,-- _tups are used to update autovac_state
vacuum_tups,
CASE WHEN analyze_tups - prev_analyze_tups NOT BETWEEN 0 AND analyze_point
OR old_relid IS NULL
THEN now()
END AS analyze_time,
CASE WHEN vacuum_tups - prev_vacuum_tups NOT BETWEEN 0 AND vacuum_point
THEN now()
END AS vacuum_time
FROM (
SELECT N.nspname || '.' || C.relname AS name,
A.relid AS old_relid,
C.oid AS relid,
S.n_tup_ins + S.n_tup_upd + S.n_tup_del AS analyze_tups,
S.n_tup_upd + S.n_tup_del AS vacuum_tups,
COALESCE(A.analyze_tups,0) AS prev_analyze_tups,
COALESCE(A.vacuum_tups,0) AS prev_vacuum_tups,
CASE WHEN nspname ~ '^pg_' THEN 1.0 ELSE $ANA_RATE END
* C.reltuples + $ANA_BASE AS analyze_point,
CASE WHEN nspname ~ '^pg_' THEN 2.0 ELSE $VAC_RATE END
* C.reltuples + $VAC_BASE AS vacuum_point
FROM pg_class AS C
JOIN pg_namespace AS N ON N.oid = C.relnamespace
JOIN pg_stat_all_tables AS S ON S.relid = C.oid
LEFT JOIN autovac_state AS A ON A.relid = S.relid
WHERE N.nspname NOT LIKE 'pg_temp%'
) AS X
) AS X
WHERE analyze_time IS NOT NULL OR vacuum_time IS NOT NULL;
SELECT CASE WHEN vacuum_time IS NOT NULL
THEN 'VACUUM ANALYZE $VERBOSE ' || name
ELSE 'ANALYZE $VERBOSE ' || name
END
FROM new_state;
__SQL__
if ($anavac) {
print STDERR $stamp."start\n";
$dbh->do(<<"__SQL__");
$anavac;
UPDATE autovac_state
SET analyze_tups = N.analyze_tups,
vacuum_tups = CASE WHEN N.vacuum_time IS NULL
THEN autovac_state.vacuum_tups
ELSE N.vacuum_tups
END,
analyze_time = COALESCE(N.analyze_time, autovac_state.analyze_time),
vacuum_time = COALESCE(N.vacuum_time, autovac_state.vacuum_time)
FROM new_state AS N
WHERE N.relid = autovac_state.relid;
INSERT INTO autovac_state
SELECT relid, name, analyze_time, vacuum_time, analyze_tups, vacuum_tups
FROM new_state
WHERE old_relid IS NULL;
DELETE FROM autovac_state
WHERE analyze_time < now() - '1 day'::INTERVAL
AND relid NOT IN (SELECT oid FROM pg_class);
__SQL__
print STDERR $stamp.(time - $start)." secs\n";
}
$dbh->do("DROP TABLE new_state");
1;
__END__
=head1 SYNOPSIS
autovac - autovacuum with persistent state.
=head1 DESCRIPTION
C<autovac> is a replacement for C<pg_autovacuum>.
C<autovac> does a single C<pg_autovacuum> step, then saves its state in
the C<public.autovac_state> table. It should be run from cron, say,
every 5 minutes.
C<autovac> runs more aggressively for user tables
(pg_autovacuum -V 0.8 -v 1000) than for system tables.
When pg_stats_user_tables.(ins/upd/del) counts have been zeroed,
C<autovac> vacuums all tables.
To check when C<autovac> last analyzed/vacuumed a given table:
SELECT vacuum_time, analyze_time FROM autovac_state
WHERE name = 'public.message'
=head1 OUTPUT
If any action is taken, C<autovac> prints:
==== yyyy-mm-dd hh:mm:ss autovac start
... VACUUM/ANALYZE VERBOSE output ...
==== yyyy-mm-dd hh:mm:ss autovac <n> secs
=head1 PG_AUTOVACUUM
=cut
