Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
o thinking there is a case for the subject of this thread, an automatic way to preserve density. Secondary points: I did not expect to see the number of table pages grow so much larger for pg-12 than for pg-9.4. The number of table pages was almost identical at the start of each run. However this was not the focus of the test. Also, although not shown in those tables, pg-12 was around 4.5 times faster in completing those 768 iterations,an enormous improvement. And one question : I notice that in some pg-11 release, a new config parameter appeared : vacuum_cleanup_index_scale_factor specifies the fraction of the total number of heap tuples counted in the previous statistics collection that can be inserted without incurring an index scan at the VACUUM cleanup stage. I have not researched this at all and nor did I set it to anything for my pg-12beta2 run, but it sounds as though maybe it could be relevant to this kind of workload - Is that so? CheersJohn Lumby
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
Thanks Peter > From: Peter Geoghegan > Sent: July 8, 2019 1:39 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > Perhaps you didn't take deleted_pages into account -- there must be > free space that is reusable by the index that has yet to be reused. > It would probably make sense to subtract that across the board. > Correct, I did not, but will do so for the next runs. > > I don't think that a test case that runs VACUUM when there are only > 4300 deletions and 4300 insertions is particularly realistic, in > general. You might see a larger difference if there was more churn > between each VACUUM run. > Actually the test workload does not run any explicit VACUUM command, it relies on autovacuum with these settings (same settings for 9.4 and 12beta2) autovacuum | on | autovacuum_analyze_scale_factor | 0.4 | autovacuum_analyze_threshold| 5 | autovacuum_max_workers | 6 | autovacuum_naptime | 20 | s autovacuum_vacuum_cost_delay| 0 | ms autovacuum_vacuum_cost_limit| | autovacuum_vacuum_scale_factor | 0 | autovacuum_vacuum_threshold | 2000| autovacuum_work_mem | 1048576 | kB To correspond to your " more churn between each VACUUM" Would you then suggest increasing autovacuum_vacuum_cost_delay and/or autovacuum_vacuum_scale_factor? Cheers, John Lumby
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
> From: Peter Geoghegan > Sent: July 8, 2019 1:39 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Mon, Jul 8, 2019 at 9:23 AM John Lumby wrote: > > > Although a welcome improvement, I think it is not enough to justify > > stopping use of setting > > a lower explicit FILLFACTOR. Which then brings me back to thinking there > > is a case > > for the subject of this thread, an automatic way to preserve density. > > I don't think that such an option would make much sense. The "waves of > misery" paper is about smoothing out the frequency of page splits > following bulk loading and a CREATE INDEX. It is not about making > splits occur less often. It's well understood that a certain amount of > free space is the overhead of B-Tree indexes, albeit an overhead that > can be avoided in certain specific instances. > Yes, I see that. But surely "making splits occur less often" is a desirable objective in itself, is it not? And I believe that a parameter to preserve the "steady-state" density in high-traffic indexes would help achieve that goal, wouldn't you agree? Cheers, John Lumby
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
> From: Peter Geoghegan
> Sent: July 9, 2019 1:47 PM
> Subject: Re: REINDEX : new parameter to preserve current average leaf density
> as new implicit FILLFACTOR
>
> On Tue, Jul 9, 2019 at 10:31 AM John Lumby wrote:
> > Yes, I see that. But surely "making splits occur less often" is a
> > desirable
> > objective in itself, is it not? And I believe that a parameter to
> > preserve the "steady-state"
> > density in high-traffic indexes would help achieve that goal, wouldn't
> > you agree?
>
> Anything that reliably reduces page splits without hurting space
> utilization is well worthwhile. I can't see how what you describe
> could have that effect, though. If you expect the leaf density to be
> the same after a REINDEX, then why bother at all? There is no reason
> to think that that will be more effective than simple vacuuming.
>
Ah, I did not explain the idea welll enough.
The scenario (simplified) is this:
Time 0 FILLFACTORs all set to default 90%
because we do not yet know what the steady-state density
will turn out to be.
{ workload runs for a few weeks }
Time N gather table and index stats, discover growth and learn density.
growth is more than autovacuum could control so
{ ALTER INDEX ??? SET (fillfactor = AUTO); }
{ REINDEX, desiring to preserve current density whatever this is }
{ workload runs for a few more weeks }
Time 2*Ngather table and index stats, discover little or no growth since
time N.
we have achieved steady-state in total number of pages.
Would this not work?
Cheers, John
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
And the point of the REINDEX at that point (below) is to remove dead tuple
keys-tids
and reorganize those split pages back into physical order without losing the
freespace.
> From: Peter Geoghegan
> Sent: July 9, 2019 1:47 PM
> Subject: Re: REINDEX : new parameter to preserve current average leaf density
> as new implicit FILLFACTOR
>
> On Tue, Jul 9, 2019 at 10:31 AM John Lumby wrote:
> > Yes, I see that. But surely "making splits occur less often" is a
> > desirable
> > objective in itself, is it not? And I believe that a parameter to
> > preserve the "steady-state"
> > density in high-traffic indexes would help achieve that goal, wouldn't
> > you agree?
>
> Anything that reliably reduces page splits without hurting space
> utilization is well worthwhile. I can't see how what you describe
> could have that effect, though. If you expect the leaf density to be
> the same after a REINDEX, then why bother at all? There is no reason
> to think that that will be more effective than simple vacuuming.
>
Ah, I did not explain the idea welll enough.
The scenario (simplified) is this:
Time 0 FILLFACTORs all set to default 90%
because we do not yet know what the steady-state density
will turn out to be.
{ workload runs for a few weeks }
Time N gather table and index stats, discover growth and learn density.
growth is more than autovacuum could control so
{ ALTER INDEX ??? SET (fillfactor = AUTO); }
{ REINDEX, desiring to preserve current density whatever this is }
{ workload runs for a few more weeks }
Time 2*Ngather table and index stats, discover little or no growth since
time N.
we have achieved steady-state in total number of pages.
Would this not work?
Cheers, John
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
> From: Peter Geoghegan > Sent: July 9, 2019 3:01 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Tue, Jul 9, 2019 at 11:27 AM John Lumby wrote: > > And the point of the REINDEX at that point (below) is to remove dead tuple > > keys-tids > > and reorganize those split pages back into physical order without losing > > the freespace. > > VACUUM already removes the tuples, accounting for all overhead. > > We could in principle come up with a way of moving pages around, > [ ... ] > That would either necessitate that the command acquire a disruptive lock > [ ... ] > Neither of which seem particularly appealing. I was not thinking of a new command, just an extension of the existing REINDEX which would apply a fillfactor equal to current average page density, by adding a preliminary step to sample that first. Of course, the user can do that for themselves by a series of steps with ANALYZE, get page_density from pgstattuple, ALTER INDEX, REINDEX so this new parameter would be a convenience, assuming that this sequence actually is beneficial, which I believe it is - see my next. > > I believe that this is a lot more important in systems that generally > use clustered indexes, such as MS SQL Server. This kind of > "fragmentation" isn't usually much of a problem when using Postgres. > We have found that, for an index which has both experienced large number of page splits and whose table has a large number of dead tuples (despite autovacuum), REINDEX with FILLFACTOR set to current page_density does produce a performance improvement, and also does reduce future growth in number of pages.I don't have numbers to hand, and in fact not sure if any catalog view or pgstattuple tells me about the proportion of dead key-tids in the index itself (do you know of any source?) as opposed to the table, but based on that recollection, yes, REINDEX can reduce fragmentation. However we did not run a VACUUM command first. Maybe if we had run VACUUM instead of the REINDEX commands, we might have obtained the same degree of improvement, I don't know. I think this was Tom's point earlier on in this thread. Correct me if I'm wrong but I believe whether an index is "clustered" or not is not relevant for this discussion because the clustering in that context is referring to ordering of the table pages, not the index pages.I believe it is quite possible to have a perfectly "clustered" table whose clustering index is itself badly disorganized. Cheers, John
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
> From: Peter Geoghegan > Sent: July 9, 2019 5:15 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Tue, Jul 9, 2019 at 12:29 PM John Lumby wrote: > > I was not thinking of a new command, just an extension of the existing > > REINDEX > > which would apply a fillfactor equal to current average page density, > > by adding a preliminary step to sample that first. > > That would be a very different thing to REINDEX no matter how you > spelt it, though. REINDEX creates a new index, from scratch, whereas > you're talking about restructuring what's already there. No, no, I really am talking about an extension to the *existing* REINDEX, and yes, yes, my extended REINDEX *would* still; create a new index from scratch. The *only* difference is that, instead of taking the FILLFACTOR it uses from whatever is set in the index attributes, it would take it from first calculating current average page density. and then build a new index with that fillfactor. > > Or, it could be that range scan performance benefitted from reduced > fragmentation, > Yes, I think so. Cheers, John
access to original-statement predicates in an INSTEAD-OF row trigger
I have an INSTEAD-OF row trigger one of whose purposes is to re-direct the original statement (which is operating on a VIEW) to a different real base table. Suppose the original statement is UPDATE myview VW set VW.counter = 11 where VW.primary_key = and AND VW.counter = 10; and my trigger constructs this statement UPDATE basetable BT set BT.counter = 11 where BT.primary_key = OLD.primary_key; based on what it finds in OLD tuple and NEW tuple. This will never update the wrong row since it specifies the primary key - good. But I have realized there is a problem concerning the returned TAG. Suppose that, *after* the backend executor started executing the statement but *before* the trigger is fired and this statement is issued, a different transaction updated BT.counter to 11 (or higher). My trigger still runs the update, but the original statement specified to do so only if the current value of counter is 10. Or rather, it specified that no row should be found for update if counter <> 10. Is there any way my trigger can discover this predicate condition and apply it to its generated statement? Or if not, (because I suppose in general such predicates could be very complex) is there some other way of doing this that avoids this problem and that does not require modification of the application? (**) I have a feeling this must have come up before but Idon't see any reference. postgresqI version 12. Cheers, John (**)I know a SHARE lock could be obtained by the application running the orginal statement but assume for this question that that is not possible. I am looking for some self-contained way in trigger or similar code.
Re: access to original-statement predicates in an INSTEAD-OF row trigger
Adrian Klaver wrote : > > On 11/15/19 10:37 AM, John Lumby wrote: > > > Suppose the original statement is > > > UPDATE myview VW set VW.counter = 11 where VW.primary_key = and > > VW.counter = 10; > > > and my trigger constructs this statement > > > UPDATE basetable BT set BT.counter = 11 where BT.primary_key = > > OLD.primary_key; > > Not following. > > Do you want OLD.primary_key set to 11 or BT.counter set to 12/both/or > > some other action? Sorry I did not make it clear. I want some way for the trigger to discover and apply any predicates *other* than primary key equality condition that were applied to the original statement, which in the example is VW.counter = 10 (the repeated AND in the original append's example was a typo, corrected above) so for this example I want the trigger to build a statement reading UPDATE basetable BT set BT.counter = 11 where BT.primary_key = and BT.counter = 10; where is the value of OLD.primary_key so that, if some other transaction had updated BT.counter to some other value such as 11 in that tiny window I described in previous append, the result of the generated statement would be no rows updated and a return TAG of 0 rows. The significance being that the original application would be able to discover that its update was not applied based on this return TAG (actually the trigger returns a null tuple to indicate this). > > > Cheers, John > > Adrian Klaver > > adrian.kla...@aklaver.com >
Re: access to original-statement predicates in an INSTEAD-OF row trigger
Adrian Klaver wrote : > > Seems you are looking for Serializable Isolation Level: > True , that would solve the race condition, but it is too drastic. We need to run with Read Committed. I am looking for a solution which does not alter the application or overall behaviour, but just addresses detecting which predicates to apply in some way. Cheers, John
Re: access to original-statement predicates in an INSTEAD-OF row trigger
On 11/15/19 17:38, Adrian Klaver wrote: > On 11/15/19 1:54 PM, John Lumby wrote: >> Adrian Klaver wrote : >>> >> We need to run with Read Committed. >> >> I am looking for a solution which does not alter the application or >> overall behaviour, >> but just addresses detecting which predicates to apply in some way. > > Not sure how that could be pulled off with Read Committed as it would > involve predicting the future from the POV of the transaction. > No need for time-travel! Let me re-phrase the question in a simpler fashion : How can a row trigger access the original SQL statement at the root of the current operation? Either in string form or any other form (e.g. parse tree). >> >> Cheers, John >> > >
REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
Background : For some workloads involving high volume of INSERT/UPDATE/DELETE, It is sometimes beneficial to schedule regular REINDEX of high-activity indexes, so as to improve performance, or restore performance levels back to what it was earlier, by removing dead keys etc. This can result in the average page density of these indexes fluctuating up and down in a saw-tooth fashion, REINDEX causing large increase in density (large drop in total number of pages) and the workload gradually decreasing density back to some "steady-state". Suggestion : it would be useful if REINDEX could , when some new parameter is set , first determine current average leaf page density in the index to be rebuilt, (e.g. the value of pgstatindex().avg_leaf_density from the pgstattuple extension ), and then adopt this density as the temporary override FILLFACTOR while rebuilding index pages, as to to minimize change in density. This would avoid the saw-tooth effect on number of pages, and also reduce the number of index page-splits which occur during the period immediately following a REINDEX done with default FILLFACTOR of 90%. In effect, it lessens the need for the physical reorganization aspect of REINDEX and focusses more on the function of removing dead keys. An admin do this for themselves by monitoring index page density and setting the FILLFACTOR to the current density before each REINDEX (and may find that this doesn't change much if the workload is truly steady-state), but I wonder if this community would agree that it would provide a useful automation of the process. Cheers, John Lumby
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
Thanks Tom and Peter for those thoughts. > > I think you've confused REINDEX with VACUUM. It seems like a pretty poor > substitute for that --- it's much more expensive and has worse locking > requirements. > I think the answer is mainly "I wish it were so" , but in practice, even with a reasonably aggressive autovacuum configuration running, eventually the number of pages and number of dead keys builds up too much.The assumption (which is the case in our particular workload) is that, eventually, unavailable-service maintenance operation must be done, and REINDEX does (today, pg-11.x) play a useful role because it addresses various aspects. And then, given we find it useful to REINDEX, we would prefer to avoid the sawtooth / "wave of misery" syndrome. > > There is a very recent research paper that discusses the idea of > varying fillfactor with a view to ameliorating page splits: > Thanks, I am chewing my way through that. As you say, it does address exactly the issues I raised. Do you happen to know if their source-code is available somewhere? ( I did see their db is MS SQL Server but it still might provide some portable ideas. ) > > I suspect that you might find that the enhancements to B-Tree indexes > that went into Postgres 12 would help with this workload, especially > if you notice that this happens with indexes that have a lot of duplicates > I had not noticed that, thanks for pointing it out. Yes , in my workload most of the indexes in question are non-unique and some have very low key card. I will try out the pg-12 beta when I get a chance. > > For the full background, you might take a look at my pgCon talk: > https://youtu.be/p5RaATILoiE > Is there a pdf or text version? > > Does it seem at all possible that you were affected by either the issue with > duplicates, > definitely > > or the issue that is addressed by the "split after new tuple" optimization? > don't know,yet more research needed.is there a module or contrib which would tell me? Thanks again John Lumby From: Peter Geoghegan Sent: June 25, 2019 6:12 PM To: John Lumby Cc: pgsql general Subject: Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR On Tue, Jun 25, 2019 at 2:45 PM John Lumby wrote: > Background : For some workloads involving high volume of > INSERT/UPDATE/DELETE, It is sometimes beneficial > to schedule regular REINDEX of high-activity indexes, so as to improve > performance, or restore performance levels back to what it was earlier, by > removing dead keys etc. This can result in the average page density of > these indexes fluctuating up and down in a saw-tooth fashion, REINDEX causing large increase in density (large drop in total number of pages) and the workload gradually decreasing density back to some "steady-state". I suspect that you might find that the enhancements to B-Tree indexes that went into Postgres 12 would help with this workload, especially if you notice that this happens with indexes that have a lot of duplicates. For the full background, you might take a look at my pgCon talk: https://youtu.be/p5RaATILoiE Fair warning: this is a very technical talk. Does it seem at all possible that you were affected by either the issue with duplicates, or the issue that is addressed by the "split after new tuple" optimization? They're both causes of index bloat that VACUUM cannot usually prevent. -- Peter Geoghegan
