Cluster Size 64 for Index Areas - Forum - OpenEdge RDBMS - Progress Community

Cluster Size 64 for Index Areas

 Forum

Cluster Size 64 for Index Areas

  • Could someone who was at the DBA Cage Match (or someone who knows why) please explain again the reasoning behind setting the cluster size on index areas to 64? I was trying to explain to a colleague and realise it's not gone in and stayed in my head! 

  • 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.

  • Accomodating the possibility for accidentally adding tables to an index area is the reasoning for setting rows per block > 1.  That is a somewhat different discussion.

    The cluster size discussion was that when Progress needs to split an index block the engine needs to be certain that there are at least 16 free blocks.  With the cluster size set to 8 you might end up allocating 2 clusters on disk in the middle of a task that is already very time sensitive.

    That would be unfortunate for the users who have to wait for that ;)


    On 11/24/14, 7:27 AM, Stefan Marquardt wrote:
    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

    --
    Tom Bascom
    tom@wss.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.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • Thanks for the answers. And what scenario would trigger an index split?

  • As you add records indexes grow...  as they grow the blocks need to occasionally split to keep the b-tree balanced.

    On 11/24/14, 8:03 AM, James Palmer wrote:
    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

    --
    Tom Bascom
    tom@wss.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

    --
    Tom Bascom
    tom@wss.com

  • 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?