>>> in our db system (for a website), i notice performance boosts after
>>> a vacuum
>>> full. but then, a VACUUM FULL takes 50min+ during which the db is
>>> not really
>>> accessible to web-users. is there another way to perform
>>> maintenance tasks
>>> AND leaving the db fully operable and accessible?
>>
>> You're not doing regular vacuums often enough.
By the way, you can get that VACUUM FULL to be "less injurious" if you
collect a list of tables:
pubs=# select table_schema, table_name from information_schema.tables
where table_type = 'BASE TABLE';
And then VACUUM FULL table by table. It'll take the same 50 minutes;
it'll be more sporadically "unusable" which may turn out better. But
that's just one step better; you want more steps :-).
> well, shouldn't autovacuum take care of "regular" vacuums? in addition
> to autovacuum, tables with data changes are vacuumed and reindexed
> once a day -
> still performance seems to degrade slowly until a vacuum full is
> initiated... could an additional daily vacuum over the entire db (even
> on tables that only get data added, never changed or removed) help?
Tables which never see updates/deletes don't need to get vacuumed very
often. They should only need to get a periodic ANALYZE so that the
query optimizer gets the right stats.
There are probably many tables where pg_autovacuum is doing a fine
job. What you need to do is to figure out which tables *aren't*
getting maintained well enough, and see about doing something special
to them.
What you may want to do is to go table by table and, for each one, do
two things:
1) VACUUM VERBOSE, which will report some information about how much
dead space there is on the table.
2) Contrib function pgstattuple(), which reports more detailed info
about space usage (alas, for just the table).
You'll find, between these, that there are some tables that have a LOT
of dead space. At that point, there may be three answers:
a) PG 8.1 pg_autovacuum allows you to modify how often specific tables
are vacuumed; upping the numbers for the offending tables may clear
things up
b) Schedule cron jobs to periodically (hourly? several times per
hour?) VACUUM the "offending" tables
c) You may decide to fall back to VACUUM FULL; if you do so just for a
small set of tables, the "time of pain" won't be the 50 minutes you're
living with now...
Try a), b), and c) in order on the "offending" tables as they address
the problem at increasing cost...
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxdatabases.info/info/x.html
"Listen, strange women, lyin' in ponds, distributin' swords, is no
basis for a system of government. Supreme executive power derives
itself from a mandate from the masses, not from some farcical aquatic
ceremony." -- Monty Python and the Holy Grail