excessive CLOB growth - Forum - OpenEdge RDBMS - Progress Community

excessive CLOB growth

 Forum

excessive CLOB growth

  • You did not specify dbkey as a third parameter and viewdbblock shows the area's object block.

    But the problem is: Area Info: "Lobs":72,1;512

    RPB in your area is still 1.

  • ran your script on the Business copy, not the test

    I will run it on test

  • ash:/md/db/data->sudo sh ./viewdbblock.sh carolina 72

    viewdbblock.sh Release V1.4 as of Jul 21, 2012

    Database: carolina

    Area Info: "Lobs":72,64;64

       Dbkey: 128 (Block 2)

    Blocksize: 8 (KB)

      Extent: /md/db/data/ext/carolina_72.d1

        Size: 2.02803e+06 (KB)

      Offset: 16 (KB)

    ./viewdbblock.sh[378]: 2.02803e+06: Syntax error

    Creating temp database... Please, wait.

    -----------

    0000 bk_dbkey:     0x00000080         128

        bk_type:      0x0c               12 (Object Block)

        bk_frchn:     0x7f               127 (NOCHN)

        bk_incr:      0x0001             1

        bk_nextf:     0x00000000         0

        bk_updctr:    0x00006191         24977

    0010 bkCheckSum:   0xccdc             -13092

        bkHeaderSize: 0x0040             64

        objectId:     0x0000             0

        partitionId:  0x0000             0

        objectType:   0x0008             8

        bkObjDbkey:   0x0000000000000080 128

    0020 bkDbkey:      0x0000000000000080 128

        bkNextf:      0x0000000000000000 0

    0030 bkLastBiNote: 0x0000000000000000 0

        partitionId:  0x0000             0

        Reserved:     0x0000             0

        Reserved:     0x00000000         0

    0040 nextCluster:  0x0000000000000000 0

        prevCluster:  0x00000000000001c0 448

    OBJBLK:

    0040 totalBlocksOld:               0x0000000000000000 0

        hiWaterBlockOld:              0x0000000000000000 0

        chainFirst[FREECHN]:          0x00000000000001c0 448

    0050 chainFirst[RMCHN]:            0x0000000000000000 0

        chainFirst[LOCKCHN]:          0x0000000000000000 0

    0060 numBlocksOnChainOld[FREECHN]: 0x0000000000000000 0

        numBlocksOnChainOld[RMCHN]:   0x0000000000000000 0

        numBlocksOnChainOld[LOCKCHN]: 0x0000000000000000 0

    0070 chainLast[FREECHN]:           0x0000000000000fc0 4032

        chainLast[RMCHN]:             0x0000000000000000 0

    0080 chainLast[LOCKCHN]:           0x0000000000000000 0

        objectId:                     0x0000             0

        objectType:                   0x0008             8

    0090 serialNumber:                 0x0000000000000000 0

        firstFreeCluster:             0x0000000000000000 0

    00A0 lastFreeCluster:              0x0000000000000000 0

        totalBlocks:                  0x00000000000748be 477374

    00B0 hiWaterBlock:                 0x00000000000668bf 420031

        numBlocksOnChain[FREECHN]:    0x0000000000000039 57

    00C0 numBlocksOnChain[RMCHN]:      0x0000000000000000 0

        numBlocksOnChain[LOCKCHN]:    0x0000000000000000 0

    00D0 partitionId:                  0x0000             0

    Block Info: carolina.area.72.dbkey.128.160429_152026.txt

  • Specify dbkey 4416 (=(64 + 5) * 64) or higher. It should be a data block.

    viewdbblock.sh carolina 72 4416

  • Remember this is a test env with only those CLOB tables.

    ash:/md/db/data->sudo sh ./viewdbblock.sh carolina 72 4416

    viewdbblock.sh Release V1.4 as of Jul 21, 2012

    Database: carolina

    Area Info: "Lobs":72,64;64

       Dbkey: 4416 (Block 69)

    Blocksize: 8 (KB)

      Extent: /md/db/data/ext/carolina_72.d1

        Size: 2.02803e+06 (KB)

      Offset: 552 (KB)

    ./viewdbblock.sh[378]: 2.02803e+06: Syntax error

    Creating temp database... Please, wait.

    -----------

    0000 bk_dbkey:     0x00001140         4416

        bk_type:      0x03               3 (Data Block)

        bk_frchn:     0x01               1 (RMCHN)

        bk_incr:      0x0001             1

        bk_nextf:     0x00001180         4480

        bk_updctr:    0x00000015         21

    0010 bkCheckSum:   0x77ce             30670

        bkHeaderSize: 0x0040             64

        objectId:     0x0007             7

        partitionId:  0x0000             0

        objectType:   0x0003             3

        bkObjDbkey:   0x0000000000006000 24576

    0020 bkDbkey:      0x0000000000001140 4416

        bkNextf:      0x0000000000001180 4480

    0030 bkLastBiNote: 0x0000000000000000 0

        partitionId:  0x0000             0

        Reserved:     0x0000             0

        Reserved:     0x00000000         0

    0040 nextCluster:  0x093702d71cc0199b 664002592984013211

        prevCluster:  0x1661132d0ffc0cce 1612591225837980878

    RMBLK:

        numdir:    9

        freedir:   55

        free:      727

        dir:

           [  0] Offset: 0x1cc0 Size: 830   Table number: 0

                    0  0xec  CLOB_RES_2 0x03 0x3b 0x7b 0x22 0x6a 0x6f 0x

    0x63 0x74 0x69 0x6f 0x6e 0x49 0x64 0x22 0x3a 0x22 0x32 0x34 0x63 0x63

    37 0x62 0x2d 0x35 0x36 0x65 0x36 0x2d 0x32 0x33 0x32 0x31 0x61 0x39 0

    0x69 0x64 0x65 0x72 0x22 0x3a 0x22 0x68 0x79 0x62 0x72 0x69 0x73 0x2

    x22 0x3a 0x22 0x32 0x34 0x37 0x35 0x37 0x30 0x22 0x2c 0x22 0x61 0x6c

    a 0x22 0x30 0x30 0x30 0x35 0x30 0x36 0x34 0x31 0x22 0x2c 0x22 0x6a 0x

    0x4a 0x6f 0x62 0x49 0x64 0x22 0x3a 0x22 0x30 0x30 0x30 0x35 0x30 0x36

    75 0x73 0x22 0x3a 0x22 0x64 0x65 0x66 0x61 0x75 0x6c 0x74 0x22 0x2c 0

    0x41 0x56 0x45 0x53 0x20 0x43 0x4f 0x4e 0x53 0x54 0x52 0x55 0x43 0x5

  • > RMBLK:

    >     numdir:    9

    >    freedir:   55

    >     free:      727

    9 records, average rec size is 903 bytes. Only 727 bytes are free in the block. It looks good.

  • then why is my extent almost 4 GB - and I purged the other table

    TEST

    -rw-r--r--   1 root       sys        2076704768 Apr 29 10:50 carolina_72.d1

    -rw-r--r--   1 root       sys        1833959424 Apr 29 10:50 carolina_72.d2

    PRODUCTION :  14 GM

    /home/dbadmin->ll /db/data/ext/carolina_72*

    -rw-r--r--   1 root       sys        2076704768 Apr 29 15:40 /db/data/ext/carolina_72.d1

    -rw-r--r--   1 root       sys        2076704768 Apr 29 13:33 /db/data/ext/carolina_72.d2

    -rw-r--r--   1 root       sys        2076704768 Apr 29 15:44 /db/data/ext/carolina_72.d3

    -rw-r--r--   1 root       sys        8359247872 Apr 29 15:44 /db/data/ext/carolina_72.d4

    First Table count and size  

          Message

            446781

           361403108

    Second Table count and size

           Message

            51539

           34701994

  • > then why is my extent almost 4 GB

    Well, there is one strange thing in information from data block:

    > bkObjDbkey:   0x0000000000006000 24576

    > 0020 bkDbkey:      0x0000000000001140 4416

    Dbkey of Object Block for your CLOB (bkObjDbkey) has a large value. This mean that the area was not empty when you put CLOB object here.

    Check bkObjDbkey. It will tell you how many blocks are used for the object:

    viewdbblock.sh carolina 72 24576

  • PROD: bkObjDbkey:   0x0000000000000400 1024

    TEST: bkObjDbkey:   0x0000000000006000 24576

  • Then it's time for dbanalys:

    proutil carolina -C dbanalys "Lobs"

    Dbanalys does not report LOB statistics until V11.6 but at least its chanalys part will report the block count per object in the area.

  • here you go...

    /home/dbadmin->sudo proutil /db/data/carolina -C dbanalys "Lobs"

    OpenEdge Release 11.4 as of Fri Jul 25 19:00:25 EDT 2014

    PROGRESS Database Analysis

    Database: /db/data/carolina

    Blocksize: 8192

    RecSpaceSearchDepth: 5

    Options: chanalys ixanalys tabanalys area Lobs

    Date: Fri Apr 29 16:55:37 2016

    [Warning] database in use - reported statistics are approximations. (2486)

    CHAIN ANALYSIS FOR AREA "Lobs": 72

    ----------------------------------------------------------

    FREE CLUSTER CHAIN ANALYSIS

    ---------------------------

    0 cluster(s) found in the free cluster chain.

    FREE CHAIN ANALYSIS

    -------------------

    Number of          Object    Object

    Blocks             Type

    ----------------------------------------------------------------------------------------------------------------

    505                Master    --:0

    0                  Table     PUB.zsaztdl:624

    0                  Blob      PUB.zsaztdl.querydata:2

    0                  Blob      PUB.zsaztdl.returndata:3

    0                  Blob      PUB.zsaztdl.reqheader:4

    0                  Blob      PUB.zsazaudit.rqst:9

    0                  Blob      PUB.zsazaudit.rspons:10

    RM CHAIN ANALYSIS

    ---------------------------

    Number of          Object    Object

    Blocks             Type

    ----------------------------------------------------------------------------------------------------------------

    182160             Table     PUB.zsaztdl:624

    182162             Blob      PUB.zsaztdl.querydata:2

    239                Blob      PUB.zsaztdl.returndata:3

    248                Blob      PUB.zsaztdl.reqheader:4

    457                Blob      PUB.zsazaudit.rqst:9

    323                Blob      PUB.zsazaudit.rspons:10

    INDEX DELETE CHAIN ANALYSIS

    ---------------------------

    AREA "Lobs": 72  BLOCK ANALYSIS

    -------------------------------------------------

    1781948 block(s) found in the area.

    Current high water mark: 1781759

                               RECORD BLOCK SUMMARY

    RECORD BLOCK SUMMARY FOR AREA "Lobs": 72

    -------------------------------------------------------

    RECORD BLOCK SUMMARY FOR SHARED TABLES

    --------------------------------------------

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

    Table                                    Records    Size   Min   Max  Mean                Count Factor  Factor

    PUB.zsaztdl                               451692   44.8M   103   124   103               451692    1.0     1.0

                                    -----------------------------------------------------------------------------

    Subtotals:                                451692   44.8M   103   124   103               451692    1.0     1.1

                                    -----------------------------------------------------------------------------

    Summary for AREA "Lobs": 72

    Subtotals:                                451692   44.8M   103   124   103               451692    1.0     1.1

                                    -----------------------------------------------------------------------------

    Totals:                                   451692   44.8M   103   124   103               451692    1.0     1.1

    1781224 RM block(s) found in the storage area.

    26.53% of the RM block space is used.

    0 index block(s) found in the storage area.

    0.0% of the index block space is used.

                                DATABASE SUMMARY

    SUMMARY FOR AREA "Lobs": 72

    -------------------------------------------------------

    SUMMARY FOR SHARED OBJECTS:

    --------------------------------------------

                                Records              Indexes            Combined

    NAME                        Size  Tot %         Size  Tot %         Size  Tot %

    PUB.zsaztdl                44.8M  100.0         0.0B    0.0        44.8M  100.0

                       -----------------------------------------------------------

    Total                      44.8M  100.0         0.0B    0.0        44.8M  100.0

    Size key:

    B = bytes

    K = kilobytes

    M = megabytes

    G = gigabytes

    T = terabytes

    AREA BLOCK ANALYSIS:

    -----------------------

    1 area block(s) found in the storage area.

    7 cluster list block(s) found in the storage area.

    7 cluster allocation block(s) found in the storage area.

    7 object block(s) found in the storage area.

    1 object list block(s) found in the storage area.

    7 object allocation block(s) found in the storage area.

    0 row allocation block(s) found in the storage area.

    505 free block(s) found in the storage area.

    189 empty block(s) found in the storage area.

    1781948 total blocks found in the storage area.

    [Warning] database in use - reported statistics are approximations. (2486)

    Database analysis complete Fri Apr 29 16:57:13 2016

  • Number of          Object    Object

    Blocks             Type

    182160             Table     PUB.zsaztdl:624

    Table                                    Records    Size   Min   Max  Mean                Count Factor  Factor

    PUB.zsaztdl                               451692   44.8M   103   124   103               451692    1.0     1.0

    451692 records in 182160 blocks: 2.5 records per block. Mean rec size: 103 bytes. In other words, only 255 bytes are used in 8K block.

    Did you load and then delete a lot of the zsaztdl's records?

  • Our application admin (frontend function) do a lot of load and purge routines.

    Man, 99% of the records are less then 1K.

  • BTW, LOB area with RPB 64 is not a good place for the zsaztdl table with 100 byte records.

  • > George Potemkin [https://community.progress.com/members/georgep12]

    > BTW, LOB area with RPB 64 is not a good place for the zsaztdl table with 100 byte records.

    why not?

    the overhead in a block that contains 64 records consist of the following:

    data block header 64 bytes

    record block specific extra header 12 bytes

    row directory (2 bytes each) 128 bytes

    create limit 300 bytes

    per row overhead (~17 bytes each) 1088 bytes

    subtotal 1592 bytes

    for an 8192 byte data block, that leaves us with 6600 bytes for data. 64 100 bytes records is 6400. so we waste a litle but not all that much. not terrible either.

    setting max rows per block to 128 would be slightly better. or not, since rows are not all the same size.