excessive CLOB growth

Posted by ctoman on 28-Apr-2016 10:51

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

 

All Replies

Posted by gus on 28-Apr-2016 11:10

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

Posted by Dapeng Wu on 28-Apr-2016 11:20

Besides the recbit value, the create/toss limit could also cause extra space waste.
Dapeng
 

Posted by ctoman on 28-Apr-2016 11:27

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!

Posted by gus on 28-Apr-2016 11:36

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.

Posted by ctoman on 28-Apr-2016 11:42

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)" .

Posted by George Potemkin on 28-Apr-2016 12:36

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

Posted by ctoman on 28-Apr-2016 12:42

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.

Posted by ctoman on 29-Apr-2016 12:53

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?  

Posted by George Potemkin on 29-Apr-2016 12:58

Check dbrpr/13. Display Block Contents

for any data block in lob area.

Posted by gus on 29-Apr-2016 13:14

how much space is necessary?

how do you know this?

Posted by ctoman on 29-Apr-2016 13:45

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

Posted by ctoman on 29-Apr-2016 13:46

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

Posted by George Potemkin on 29-Apr-2016 13:49

> I can't do the dbrpr right now

You can ;-)

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

Posted by ctoman on 29-Apr-2016 14:07

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

Posted by George Potemkin on 29-Apr-2016 14:17

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.

Posted by ctoman on 29-Apr-2016 14:19

ran your script on the Business copy, not the test

I will run it on test

Posted by ctoman on 29-Apr-2016 14:21

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

Posted by George Potemkin on 29-Apr-2016 14:25

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

viewdbblock.sh carolina 72 4416

Posted by ctoman on 29-Apr-2016 14:33

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

Posted by George Potemkin on 29-Apr-2016 14:41

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

Posted by ctoman on 29-Apr-2016 14:45

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

Posted by George Potemkin on 29-Apr-2016 14:53

> 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

Posted by ctoman on 29-Apr-2016 14:59

PROD: bkObjDbkey:   0x0000000000000400 1024

TEST: bkObjDbkey:   0x0000000000006000 24576

Posted by George Potemkin on 29-Apr-2016 15:19

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.

Posted by ctoman on 29-Apr-2016 15:58

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

Posted by George Potemkin on 29-Apr-2016 16:23

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?

Posted by ctoman on 29-Apr-2016 16:28

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

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

Posted by George Potemkin on 29-Apr-2016 16:32

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

Posted by gus on 29-Apr-2016 17:04

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

Posted by ctoman on 29-Apr-2016 17:07

That is good stuff (FYI PROD is only 1 RPB) , but why is the LOB area growing out of control?

Posted by Rob Fitzpatrick on 29-Apr-2016 17:16

> create limit 300 bytes

Default is 150

Posted by George Potemkin on 30-Apr-2016 01:13

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

Storing a table not in the same area as the LOB field would make at least it's possible to set the optimal RPB value. Table's records and LOB's fields are too different "animals" to be locked in one "cage".

Having 64-bit dbkeys and the create limit that can be changed at any time I'd always set RPB for the /table/'s areas higher than the value based on mean record size. Maybe I just a coward and I don't say: set it always to 256 . ;-)

CToman's case seems to be rather specific. Look at the names of CLOB fields in table zsazaudit: querydata, returndata, reqheader, rqst, rspons. I guess the table is a part of system that is used to monitor the execution of application itself. The values of these CLOB fields except the "querydata" are mostly empty and they are not using any slots in the "LOB" area. But they can be used in the future. And it might create the additional problems.

I guess the table has the huge numbers of the creates/deletes:
> RM CHAIN ANALYSIS
> 182160 Table PUB.zsaztdl:624

With 64 records per block it means that at some point in time the table had more than ten million records. Dbanalys reported only 451,692 records. This explains why the "LOB" area is large while its current contents is rather small.

Posted by ctoman on 30-Apr-2016 05:39

I'm going to move that table to another area and change the RBP for the LOB area from 1 to 64.  I will have to do a dump and load every month on the LOB area.  

Posted by George Potemkin on 30-Apr-2016 05:46

> I will have to do a dump and load every month on the LOB area.

Why?

Posted by ctoman on 30-Apr-2016 05:49

The LOB area growth is out of control, in two months the area grew from 0 to 14 GB.

Posted by George Potemkin on 30-Apr-2016 05:55

IMHO, it's a load and purge procedure that is out of control.

Progress will re-use space released after the purge. If 14 GB is a pick for your data then the area will not grow above this limit.

Posted by ctoman on 30-Apr-2016 05:59

I don't think the CLOB fields work that way.  They purged last Wednesday and the LOB area continues to grow.

Posted by George Potemkin on 30-Apr-2016 06:19

> They purged last Wednesday and the LOB area continues to grow.

Theory says it's impossible - unless if RM chain(s) is broken. BTW, 'proutil carolina -C chanalys "LOB" -scan' will report the chain's corruptions if they exist.

Viewdbblock says the blocks at the beginning of area are full of LOB data. This means the space is re-used.

You can use the script to check the blocks just below area's HWM:

> 1781948 total blocks found in the storage area.

It's dbkey 114044672 (= 1781948 * 64) or lower.

BTW, I'd rise the toss limit for the zsaztdl.querydata field to 900 bytes or so. There are no reasons to have the long RM chains for LOB fields. On other hand you will be unable to use dbanalys to estimate the number of blocks allocated for LOB (untill V11.6). But 'viewdbblock db area bkObjDbkey' will report the accurate number with your Progress version.

Posted by ctoman on 30-Apr-2016 06:25

Well the CLOB fields are not actually records in the database -  pointers to external location.  I hope your theory is correct.

Posted by George Potemkin on 30-Apr-2016 06:43

> the CLOB fields are not actually records in the database -  pointers to external location.

As a part of the table's records - yes.

As the database objects the LOB fields are stored in the blocks exactly as the records. You have got the dump of block with dbkey 4416. And it's objectType 3 (LOB).

Posted by gus on 02-May-2016 08:52

> On Apr 30, 2016, at 7:44 AM, George Potemkin wrote:

>

> As the database objects the LOB fields are stored in the blocks exactly as the records. You have got the dump of block with dbkey 4416. And it's objectType 3 (LOB).

right. George didn't mention that it is /pieces/ of the LOB.

Max LOB size is 1 GB. A huge LOB can get chopped into many pieces and span many blocks.

Don't forget that creating, updating, and deleting LOBs can generate a lot of transaction log activity.

This thread is closed