excessive CLOB growth

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

All Replies