From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: new to postgres (and db management) and performance already a problem :-(
Date: Mon, 16 Jan 2006 22:57:59 -0500
Message-ID: <m3hd83sdyw.fsf@mobile.int.cbbrowne.com> (raw)
References: <43CC1938.7020807@gmail.com>
<18875.1137451410@sss.pgh.pa.us>
<070501c61aee$ed450560$6402a8c0@iwing>
<22960.1137460135@sss.pgh.pa.us>
<071801c61b05$7ea50c10$6402a8c0@iwing>
>>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: new to postgres (and db management) and performance already a problem :-(
In-Reply-To: <m3hd83sdyw.fsf@mobile.int.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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