Skip site navigation (1) Skip section navigation (2)

Re: Vacuum time degrading

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Wes <wespvp(at)syntegra(dot)com>
Cc: Postgresql-General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Vacuum time degrading
Date: 2005-04-04 14:50:25
Message-ID: 25735.1112626225@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Wes <wespvp(at)syntegra(dot)com> writes:
> On 3/2/05 10:50 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It wouldn't be easy --- there are some locking considerations that say
>> btbulkdelete needs to scan the index in the same order that an ordinary
>> scan would do.  See the nbtree README for details.

> Just a follow-up on this..

> The vacuum time has been steadily increasing at a seemingly increasing rate,
> although there are no deletes or updates to the database.  The current DB
> size is just over 500 million rows.  Last week it was up to 6.84 hours to do
> a vacuum.  Over the weekend I reindexed all the major indexes.  The two
> largest indexes took about 10 hours to reindex both.  After the reindexing,
> the vacuum took only 1.44 hours.  This is pretty much a linear scaling from
> the original vacuum time I reported.

> So, the increasing vacuum times would appear to be as Tom suggested - due to
> the fact that vacuum processes indexes in index order, not physical disk
> order.  I guess we add a periodic reindex to our maintenance procedures...

That doesn't follow from what you said.  Did you check that the physical
sizes of the indexes were comparable before and after the reindex?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2005-04-04 15:02:30
Subject: Re: [HACKERS] plPHP in core?
Previous:From: Tom LaneDate: 2005-04-04 14:46:41
Subject: Re: BuildFarm status: recent check failures

pgsql-general by date

Next:From: Janning VygenDate: 2005-04-04 14:54:46
Subject: invalid input syntax for type bytea
Previous:From: Karl O. PincDate: 2005-04-04 14:44:09
Subject: Re: Strange plpgsql performance -- arithmetic, numeric()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group