
> I've looked at the problem a little bit --- there's literature more
> recent than Lehmann-Yao that talks about how to do btree compaction
> without losing concurrency.  But it didn't get done for 7.2.

Yes, there must be. Informix handles this case perfectly.
(It uses a background btree cleaner)

Andreas

Fri, 25 Jan 2002 22:55:58 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: sequence indexes 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> I've looked at the problem a little bit --- there's literature more
>> recent than Lehmann-Yao that talks about how to do btree compaction
>> without losing concurrency.  But it didn't get done for 7.2.

> Yes, there must be. Informix handles this case perfectly.
> (It uses a background btree cleaner)

Right, I had hoped to fold it into lazy VACUUM, but ran out of time.
(Of course, had I known in August that we'd still not have released
7.2 by now, I might have kept after it :-()

			regards, tom lane

Fri, 25 Jan 2002 17:05:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: sequence indexes 
Tom Lane wrote:
> 
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> >> I've looked at the problem a little bit --- there's literature more
> >> recent than Lehmann-Yao that talks about how to do btree compaction
> >> without losing concurrency.  But it didn't get done for 7.2.
> 
> > Yes, there must be. Informix handles this case perfectly.
> > (It uses a background btree cleaner)

As an idle thought, I wonder what other maintenance tasks we could have
a process in the background automatically doing when system activity is
low ?

Maintenance
***********
- Index compaction
- Vacuum of various flavours

Tuning
******
- cpu_tuple costings (and similar) recalculation(s)


Can't think of anything else off the top of my head though.

Regards and best wishes,

Justin Clift

> 
> Right, I had hoped to fold it into lazy VACUUM, but ran out of time.
> (Of course, had I known in August that we'd still not have released
> 7.2 by now, I might have kept after it :-()
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Sat, 26 Jan 2002 19:08:22 +1100
Justin Clift <justin@postgresql.org>
Re: sequence indexes
Justin Clift wrote:
> 
> Tom Lane wrote:
> >
> > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > >> I've looked at the problem a little bit --- there's literature more
> > >> recent than Lehmann-Yao that talks about how to do btree compaction
> > >> without losing concurrency.  But it didn't get done for 7.2.
> >
> > > Yes, there must be. Informix handles this case perfectly.
> > > (It uses a background btree cleaner)
> 
> As an idle thought, I wonder what other maintenance tasks we could have
> a process in the background automatically doing when system activity is
> low ?
> 
> Maintenance
> ***********
> - Index compaction
> - Vacuum of various flavours


I had a couple thoughts about index compaction and vacuum in the
background:

Could one run a postgresql process in a lower priority process and
perform lazy vacuums without affecting performance all that much?

A live index compaction can be done by indexing the table with a
temporary name rename the old index, rename the new index to the old
name, and drop the old index.

Mon, 28 Jan 2002 15:30:31 -0500
mlw <markw@mohawksoft.com>
Re: sequence indexes
mlw wrote:
> 
> 
> Could one run a postgresql process in a lower priority process and
> perform lazy vacuums without affecting performance all that much?

One must be very careful not to introduce reverse priority problems -
i.e. a 
lower priority process locking some resource and then not letting go
while 
higher priority processes are blocked from running due to needing that
lock.

In my tests 1 vacuum process slowed down 100 concurrent pgbench
processes 
by ~2 times.

> A live index compaction can be done by indexing the table with a
> temporary name rename the old index, rename the new index to the old
> name, and drop the old index.

Isn't this what REINDEX command does ?

---------------
Hannu

Tue, 29 Jan 2002 09:34:04 +0200
Hannu Krosing <hannu@tm.ee>
Re: sequence indexes
Hannu Krosing wrote:
> 
> mlw wrote:
> >
> >
> > Could one run a postgresql process in a lower priority process and
> > perform lazy vacuums without affecting performance all that much?
> 
> One must be very careful not to introduce reverse priority problems -
> i.e. a
> lower priority process locking some resource and then not letting go
> while
> higher priority processes are blocked from running due to needing that
> lock.
I understand that, hmm. I wonder if the lock code could boost the priority of a
process which owns a lock.

> 
> In my tests 1 vacuum process slowed down 100 concurrent pgbench
> processes
> by ~2 times.

Is that good or bad?

> 
> > A live index compaction can be done by indexing the table with a
> > temporary name rename the old index, rename the new index to the old
> > name, and drop the old index.
> 
> Isn't this what REINDEX command does ?

REINDEX can't be run on a live system, can it?


> 
> ---------------
> Hannu

Tue, 29 Jan 2002 07:43:52 -0500
mlw <markw@mohawksoft.com>
Re: sequence indexes
mlw wrote:
> 
> Hannu Krosing wrote:
> >
> > mlw wrote:
> > >
> > >
> > > Could one run a postgresql process in a lower priority process and
> > > perform lazy vacuums without affecting performance all that much?
> >
> > One must be very careful not to introduce reverse priority problems -
> > i.e. a
> > lower priority process locking some resource and then not letting go
> > while
> > higher priority processes are blocked from running due to needing that
> > lock.
> I understand that, hmm. I wonder if the lock code could boost the priority of a
> process which owns a lock.
> 
> >
> > In my tests 1 vacuum process slowed down 100 concurrent pgbench
> > processes
> > by ~2 times.
> 
> Is that good or bad?

I had hoped it to take somewhat proportional time, i.e. slow other
backends 
down by 1/100.

> > > A live index compaction can be done by indexing the table with a
> > > temporary name rename the old index, rename the new index to the old
> > > name, and drop the old index.
> >
> > Isn't this what REINDEX command does ?
> 
> REINDEX can't be run on a live system, can it?

It will probably lock something, but otherways I don't say why it can't.

You may have to add FORCE to the end of command thus:

reindex table tablename force;

-------------
Hannu

Tue, 29 Jan 2002 15:17:19 +0200
Hannu Krosing <hannu@tm.ee>
Re: sequence indexes
On Tue, Jan 29, 2002 at 07:43:52AM -0500, mlw wrote:
> Hannu Krosing wrote:
> > 
> > mlw wrote:
> > 
> > One must be very careful not to introduce reverse priority problems -
> > i.e. a
> > lower priority process locking some resource and then not letting go
> > while
> > higher priority processes are blocked from running due to needing that
> > lock.
> I understand that, hmm. I wonder if the lock code could boost the priority of a
> process which owns a lock.
>

The classic approach to solving priority inversion is to allow for
priority inheritance: that is, the low-priority process stays low
priority, even when it locks a resource, until there is contention for
that resource from a higher priority process: then it inherits the higher
priority of the waiting process.

Ross

Tue, 29 Jan 2002 10:28:04 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: sequence indexes