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 ???
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.
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
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.
That is good stuff (FYI PROD is only 1 RPB) , but why is the LOB area growing out of control?
> create limit 300 bytes
Default is 150
> 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.
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.
> I will have to do a dump and load every month on the LOB area.
Why?
The LOB area growth is out of control, in two months the area grew from 0 to 14 GB.
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.
I don't think the CLOB fields work that way. They purged last Wednesday and the LOB area continues to grow.
> 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.
Well the CLOB fields are not actually records in the database - pointers to external location. I hope your theory is correct.
> 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).
> 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.