From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: R: slow seqscan after vacuum analize
Date: Thu, 05 Feb 2004 08:15:24 -0500
Message-ID: <m3fzdpmzv7.fsf@wolfe.cbbrowne.com> (raw)
References: <2s28b9$kqpmo@mailr-1.tiscali.it>
<006401c3ebb1$d4237c60$7201a8c0@mst1x5r347kymb>
Centuries ago, Nostradamus foresaw when iain@mst.co.jp ("Iain") would write:
> I'd like to know more about the possibility of plain vacuums harming
> performance. This is the first I've heard of it. Vacuum full is not always
> an option in a production environment.
There certainly are known cases where systems where the I/O bus is
already fairly much saturated will suffer BADLY when a big vacuum is
thrown at them.
The problem in such cases is that the vacuum draws the pages that it
is working on into the buffer cache, pushing out data that is actually
useful to cache.
There are experimental patches for 7.4, 7.3, and even, I believe, 7.2,
for a "sleeping vacuum" that tries to limit the damage by sleeping
every so often so that the vacuum does not dominate, and so that
"ordinary traffic" gets a chance to reclaim cache. And there are
efforts in 7.5 to improve cache management, so that pages brought in
by VACUUM would be put at the opposite end of the "LRU" queue. That
way, instead of them being treated as Most Recently Used, pushing
everything the least bit older towards being dropped from the buffer
cache, the vacuumed pages would be treated as if they were LRU, so
they would get evicted FIRST.
But if the Original Poster is encountering that the database is doing
Seq Scans when it would be better to do an Index Scan, that is a
separate problem, and focusing on the VACUUM may distract from the
_real_ problem...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/advocacy.html
Rules of the Evil Overlord #195. "I will not use hostages as bait in a
trap. Unless you're going to use them for negotiation or as human
shields, there's no point in taking them."
<http://www.eviloverlord.com/;
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: R: slow seqscan after vacuum analize
In-Reply-To: <m3fzdpmzv7.fsf@wolfe.cbbrowne.com>
References: <2s28b9$kqpmo@mailr-1.tiscali.it> <006401c3ebb1$d4237c60$7201a8c0@mst1x5r347kymb> <m3fzdpmzv7.fsf@wolfe.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