Обсуждение: sequence indexes
Is it safe to drop and recreate an index used by a sequence? Over three databases I have these key indexes taking up about a gig of disk space and I need to free it up (since the partition is getting rather full). Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Vince Vielhaber <vev@michvhf.com> writes:
> Is it safe to drop and recreate an index used by a sequence?
Uh, sequences haven't got indexes. You mean an index on a "serial"
column, no? Sure, there's no magic there. Don't forget it's a
unique index, though, if you want to have the same error checking
as before.
regards, tom lane
On Fri, 25 Jan 2002, Tom Lane wrote: > Vince Vielhaber <vev@michvhf.com> writes: > > Is it safe to drop and recreate an index used by a sequence? > > Uh, sequences haven't got indexes. You mean an index on a "serial" > column, no? Sure, there's no magic there. Don't forget it's a > unique index, though, if you want to have the same error checking > as before. It's a serial column. | vev | newclaim_newclaimid_key | index || vev | newclaim_newclaimid_seq |sequence | 577527808 Jan 25 14:50 newclaim_newclaimid_key select count(*) from newclaim; count ----- 53747 (1 row) select max(newclaimid) from newclaim; max ------- 9907663 (1 row) A bit much diskspace for that, isn't it? The data turns over alot. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Fri, 25 Jan 2002, Tom Lane wrote: > Vince Vielhaber <vev@michvhf.com> writes: > > I'm guessing reindex wasn't in 6.5.3. :( > > Vince, surely you know better than to still be running 6.5.3 :-( Actually until just a few minutes ago I thought it was 7.1.3, guess this thing will be getting upgraded next month. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Vince Vielhaber <vev@michvhf.com> writes:
> I'm guessing reindex wasn't in 6.5.3. :(
Vince, surely you know better than to still be running 6.5.3 :-(
regards, tom lane
Vince Vielhaber <vev@michvhf.com> writes:
> A bit much diskspace for that, isn't it? The data turns over alot.
Yeah, this is one of the scenarios where we desperately need index
compaction. The index pages holding the lower serial numbers are
no doubt empty or nearly so, but there's no mechanism for reclaiming
that space short of rebuilding the index. (BTW you might consider
REINDEX instead of a manual drop/recreate.)
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.
regards, tom lane
On Fri, 25 Jan 2002, Tom Lane wrote: > Vince Vielhaber <vev@michvhf.com> writes: > > A bit much diskspace for that, isn't it? The data turns over alot. > > Yeah, this is one of the scenarios where we desperately need index > compaction. The index pages holding the lower serial numbers are > no doubt empty or nearly so, but there's no mechanism for reclaiming > that space short of rebuilding the index. (BTW you might consider > REINDEX instead of a manual drop/recreate.) I'm guessing reindex wasn't in 6.5.3. :( Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
"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
> 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
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
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.
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
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
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
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