Dangerous viewB2 utility - Forum - OpenEdge RDBMS - Progress Community

Dangerous viewB2 utility

 Forum

Dangerous viewB2 utility

  • Since V11.6 the viewB2 utility became the fast way to estimate the sizes of all database objects in type II storage areas. But it’s turned out not to be fast and it might lock a database.

    Story: the customer has deleted the unused index (its size was 100 GB). After that the viewB2 utility runs on this database for 13-15 hours. At the end a lot of processes that tried to update the table were blocked by viewB2. Fortunately, it was a test environment.

    Result before the index was deleted:

    Object Enablement    Size     Type   Object Name
    -----------------  -------- -------  ------------
    Default                   7  Master   Area.Control-Object:0
    Default           109264768   Index   PUB.table.index1:8
    Default            21103776   Index   PUB.table.index2:9
    Default            11941256   Index   PUB.table.index3:10
    Default            26215864   Index   PUB.table.index4:11
                       --------
                      168525671
    

    After the index was deleted:

    Object Enablement    Size     Type   Object Name
    -----------------  -------- -------  ------------
    Default            26215735  Master   Area.Control-Object:0
    Default           109264920   Index   PUB.table.index1:8
    Default            21103840   Index   PUB.table.index2:9
    Default            11941304   Index   PUB.table.index3:10
                       --------
                      168525783
    

    First of all, viewB2 scans the free cluster chains. The utility adds the blocks on the chain to the number of blocks owned by Master object. The reason seems to be obvious - the Area Control Object (ACO) block contains the firstFreeCluster and lastFreeCluster fields but it does not store the number of the number of clusters (or blocks) on the free cluster chain. Though the utility can easy get the number: the last row in the viewB2 output is the hiWaterBlock value stored in ACO block. The sizes of the objects are the totalBlocks values stored in the correspondent object blocks. So the number of the blocks owned by Master block can be got as the difference of these values.

    By the way, the utility also scans the free chain owned by Master block but this chain used to be short - shorter than a cluster size.

    Second, the viewB2 holds SHARE lock on ACO block while the utility is scanning the free cluster chain. ACO blocks are the most frequently updated blocks in each area (the largest value in bk_updctr). In our case the lock was hold during 13-15 hours. So be careful if you’re going to run viewB2 when there were the large objects deleted in database.

    Finally, the utility seems to scan the free cluster chains too slowly. Disk I/O is 5 times less than the sequential disk reads. The viewB2 utility scans the chains exactly as chanalys does: they read only one block per cluster - the last block in each cluster. The extended header of this block contains the nextCluster and prevCluster fields. In our case the clusters on the chain were mainly sequential. Cluster size in this area is 8. Disk I/O is 5 times slower than expected. Is it a coincidence?

  • I forgot to mention one more feature of viewB2: the utility is unstoppable. It ignores all signals except SIGUSR1. If you'll use kill -9 the database will crash.

  • I run viewB2 utility on a copy of our 1.5 TB database. It run for 11 minutes. We do not have any indexes close to 100 GB of course.

    My biggest index is 7.5 GB and my biggest table is 200 GB.

    Version 11.7.1.

    Dmitri Levin

    Alphabroder

  • Dmitri, I think the customers will not be happy when your production database hangs for 11 minutes. And you will be unable to find out what's going on - promon, protop etc will hang as well. The only solution is NOT to run viewB2 in production environment.

  • For our company it will be a complete disaster if our production will hang for 2 minutes. Of course I will never run viewB2 in production environment.

    Dmitri Levin

    Alphabroder

  • The issue will be fixed in 11.7.6.