excessive CLOB growth - Forum - OpenEdge RDBMS - Progress Community

excessive CLOB growth

 Forum

excessive CLOB growth

  • Defect or not!

    OE 11.4

    HP-UX 11.32

    I do have an opened case with the great Richard Shulman.

    All my CLOB fields are in there own AREA.

    This month my CLOB area growth is up to 8 GB, why????

    info:

    I dumped the CLOB - the size for the most part is really small - 1KB Records: 413852 size : 1K raw : 9 or 10 in length

    Database Block Usage for Area: Lobs Active blocks: 990207 Data blocks: 989702 Free blocks: 505 Empty blocks: 193 Total blocks: 990400 Extent blocks: 4 Records/Block: 1 Cluster size: 512

     

  • because you have maximum rows per block set to 1 ???

  • Besides the recbit value, the create/toss limit could also cause extra space waste.
    Dapeng
     
  • Well that is a problem.  

    That was best practice, I guess it does not work for us.

    so my block size is 8K and the the max size for the CLOB fields are 100M

    what are the recommendation for the RPB, cluster size, and CLOB field size?

    Thanks in advance!

  • That is NOT best practice. Ever. For /any/ situation.

    Set maximum rows per block to 64.

    Set cluster size to 8 or 64. 512 is rarely needed.

    The maximum clob size is irrelevant to sapce allocation. It is there to help applications detect errors. By setting a maximum size for blobs that are know to be always small (for example, a thumbnail or icon or other small image), you can prevent errors. in such cases a 1 GB blob is always wrong.

  • Thank you Gus!  I read that in KB 000022014 "There is a strong argument in defining 1 rpb for Lob areas (Type I or Type II)" .

  • > I dumped the CLOB - the size for the most part is really small - 1KB Records

    Then why they are defined as Large Objects? It's just an artificial record fragmentation = performance degradation. The limits for the sizes of Progress records is 32K. 1KB field could be a small part of a record.

  • I agree 100 percent.. they (developers) did not know what size the CLOB would be.  Or they could of program some Boolean code,,,, something like if the data is less then 32k the put data in a normal field.

  • I changed the RPB to 64, and dumped and loaded the records, however still same size.  Why are the CLOBs reserving more space than is necessary?  

  • Check dbrpr/13. Display Block Contents

    for any data block in lob area.

  • how much space is necessary?

    how do you know this?

  • I created a new LOB area with 64 rpb (test db) and dumped (from Production) and reloaded the new AREA (Test db) and still the same size as before.  I know this by post database growth and pre database growth (ABL program internal size - FOR EACH _AreatStatus etc..).

  • I can't do the dbrpr right now - I will need to restore a fresh copy of Production soon.

  • > I can't do the dbrpr right now

    You can ;-)

    ftp.progress-tech.ru/.../viewdbblock.sh

  • Awesome!  I don't know what all this means

    /dy/db/data->sudo sh ./viewdbblock.sh carolina 72

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

    Database: carolina

    Area Info: "Lobs":72,1;512

       Dbkey: 2 (Block 2)

    Blocksize: 8 (KB)

      Extent: /dy/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:     0x00000002         2

        bk_type:      0x0c               12 (Object Block)

        bk_frchn:     0x7f               127 (NOCHN)

        bk_incr:      0x0001             1

        bk_nextf:     0x00000000         0

        bk_updctr:    0x00001559         5465

    0010 bkCheckSum:   0x8ad9             -29991

        bkHeaderSize: 0x0040             64

        objectId:     0x0000             0

        partitionId:  0x0000             0

        objectType:   0x0008             8

        bkObjDbkey:   0x0000000000000002 2

    0020 bkDbkey:      0x0000000000000002 2

        bkNextf:      0x0000000000000000 0

    0030 bkLastBiNote: 0x0000000000000000 0

        partitionId:  0x0000             0

        Reserved:     0x0000             0

        Reserved:     0x00000000         0

    0040 nextCluster:  0x0000000000000000 0

        prevCluster:  0x0000000000000007 7

    OBJBLK:

    0040 totalBlocksOld:               0x0000000000000000 0

        hiWaterBlockOld:              0x0000000000000000 0

        chainFirst[FREECHN]:          0x0000000000000007 7

    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]:           0x0000000000000007 7

        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:                  0x00000000001b28bc 1779900

    00B0 hiWaterBlock:                 0x00000000001b27ff 1779711

        numBlocksOnChain[FREECHN]:    0x00000000000001f9 505

    00C0 numBlocksOnChain[RMCHN]:      0x0000000000000000 0

        numBlocksOnChain[LOCKCHN]:    0x0000000000000000 0

    00D0 partitionId:                  0x0000             0

    Block Info: carolina.area.72.dbkey.2.160429_150523.txt