Cluster Size 64 for Index Areas

I deleted my first answer because I get mixed up with RPM, a higher value than 1 should be better to avoid large index areas if somebody adds an table by accident.

And I think the enhancement request was (wish):

Areas which are reserved by the DB engine for indexes only to avoid the mix index/table by a database setting.

As a result my first result was OT, sorry.

Thanks Stefan,

Yes I remember that point too. The Cluster Size of 64 for index areas was to do with splitting of indexes, but the exact explanation escapes me.

RE: Cluster Size 64 for Index Areas

Reply by Stefan Marquardt

To avoid large index areas when somebody adds a table by accident

Stop receiving emails on this subject.

Flag this post as spam/abuse.

-- 
Tom Bascom
603 396 4886
tom@greenfieldtech.com

If there are not enough free blocks in the cluster for an index split a new cluster must be added before the index split operation can finish. . This results in a couple of latches being held for a loooong time including MTX IIRC.

Thanks for the answers. And what scenario would trigger an index split?

RE: Cluster Size 64 for Index Areas

Reply by James Palmer

Thanks for the answers. And what scenario would trigger an index split?

Stop receiving emails on this subject.

Flag this post as spam/abuse.

-- 
Tom Bascom
603 396 4886
tom@greenfieldtech.com

Thanks Tom. Much appreciated. Makes sense again!

@palmer: index entries are sorted. when you add a new one, there is only one place to put it. it normally has to go between two existing entries and if that block has insufficient room, it must be split into two and entry for new block added to parent. if parent has insufficient room then /it/ must be split too. but i see no need to have at least 16 free blocks.

As I recall it wasn't so much "16" as "more than 8". The maximum depth
of index blocks being 6 plays into it. You need to reserve that many
"just in case" (on both sides?) when a block splits. Rich explained it
much better than me.

--
Tom Bascom
603 396 4886
tom@greenfieldtech.com

[mention:9617a07f61934bc98f6fccb3b7fabfae:e9ed411860ed4f2ba0265705b8793d05] Thanks for replying. That helps to make sense of it.

If I recall correctly "64" was mentioned as the max number of the index tree levels. But documentation says the max value is defined by the -cs parameter and its maximum is 256. BTW, the largest single index I saw was 0.5 TB in size and consisted of 5 levels only. Also Richard has mentioned a relation to some bug that was recently fixed. It could be the bug PSC00262763 fixed in V10.2B08 but I can be wrong.

The maximum number of index b-tree levels currently supported in OpenEdge 11.5 is 16.  It has been this way for a very long time.  With the compression algorithms in our index b-trees, I'd be very interested to hear who has the deepest index b-tree to see if this should be improved on in the future.

The change requiring pre-allocation of free space just prior to an index block split was introduced 9 years ago in 10.0B04 and 10.1A to solve a bug that would cause the database to hang waiting on a buffer lock. The hang occurred due to a buffer locking protocol inconsistency/conflict when additional free space is required by an index object when in the middle of a block split operation.

Due to the highly concurrent optimistic locking protocol in place when traversing down the index b-tree (a technique  called "crabbing") there is currently no way to know how deep the index really is when beneath the root block since another user could have performed a split along a different path in the index b-tree which cascaded upward, above where you currently are in the tree and not involving any index buffers you have a lock on.  Of course this could be improved upon since at some point you have all the index blocks you need locked to perform the split operation but it is more complicated than just that.

There are many ways to solve this problem, some riskier than others, and I have no plans to debate the approach previously taken in this forum.  Suffice it to say that ensuring there are at least 16 blocks available to the index object just prior to the split operation ensures that it will complete without needing to request additional space from the storage area (or the file system).  You wouldn't want to be allocating new space when in the middle of split operation anyway because of the performance impact on the rest of the application while in the middle of a multi-block index split operation.

This explains why the suggestion for using a cluster size of 64 for index areas was made.  Allocation of space just prior to an index split operation will occur much less frequently than with a cluster size of 8.  How much it improves your performance is really dependent on your application and for many deployments as is often the case with such things, we are probably just splitting hairs here anyway.  However, changing the cluster size in an area containing data requires some type of data movement so it is best to use decent values to start with.

> With the compression algorithms in our index b-trees, I'd be very interested to hear who has the deepest index b-tree to see if this should be improved on in the future.

I don't think that the number of index levels can be higher than 7.

For large tables we will get new level in index tree when the number of records will reach:

recs ~(1000-10000)^levels

where 1000-10000 is the average number of index keys per leaf block.

Max number of records (per SAT2) is 2^64.

2^64=1024^level => max level=6.4 and it's an overestimated value.

Probably me being dense but how do I see how many levels an index has?

Ignore me - I've found the relevant section in a DBAnalys.

[mention:7dc6ee8d33b54aa696ccb0bdd581e3b1:e9ed411860ed4f2ba0265705b8793d05] Our deepest level is 4 (based on a visual scan of the DBANALYS output). We've got a heck of a lot at 3. 

PUB.InternetPerformance
  InternetBudgetDataKeyIdx
                             612       2      4          23546   68.0M    37.1     2.3
  InternetPerformanceKeyIdx
                             611       1      3          11928   70.1M    75.5     1.5
  IpeRunDateIdx              613       3      4          27482   82.5M    38.6     2.2

A few large tables and their indexes:

                                            -Record Size (B)-           ---Fragments--- Scatter

Found db with 6 levels in an index tree:

-Record Size (B)- ---Fragments--- Scatter
Table Records Size Min Max Mean Count Factor Factor
PUB.table3 8057647115 1.3T 47 25481 173 8057759806 2.1 1.0

Table Index Fields Levels Blocks Size % Util Factor
PUB.table3
Index1 9 3 5 13190287 18.4G 36.8 2.3
Index2 10 3 5 8302850 16.0G 50.8 2.0
Index3 8 6 6 92560806 219.1G 62.5 1.8

It's the different instance of the same table as Table3 above where:
Index3 8 6 5 52376577 241.2G 60.6 1.8

The difference is db blocksize: 4K vs 8K.

good that we have plenty of headroom so the practical worst case can increase.