Hello, I'm trying to understand why my database extents are continually growing. For example, I have an area containing +- 300 tables. One of this tables is storing lobs. On disk, the total size of extents is approx. 5Gb. When I run a tabanalys, it returns a size af 2.2Gb. The last extent is a variable one and is still growing. The only way I found to reclaim unused space is to reorg (dump & load) the database. Can someone tell me if the space freed by deleted records (even lobs records) is supposed to be used again and what could be the reason of this behavior ? Regards.
I'm trying to understand why my database extents are continually growing.
For example, I have an area containing +- 300 tables.
One of this tables is storing lobs.
On disk, the total size of extents is approx. 5Gb.
When I run a tabanalys, it returns a size af 2.2Gb.
The last extent is a variable one and is still growing.
The only way I found to reclaim unused space is to reorg (dump & load) the database.
Can someone tell me if the space freed by deleted records (even lobs records) is supposed to be used again and
what could be the reason of this behavior ?
Free space is re-used when records are deleted, but there are rules in place which control when (such as, will the record fit in the block that has free space).
If you are using Type II storage areas, with a large (512 blocks) cluster size and 300 tables in a single area, then this growth is not extraordinary, in my opionion. Even with smaller cluster sizes, you would expect to see a fair amount of growth due to the cluster size. I would suggest you post the details of the structure of the area in question to stop the guessing..
Thanks for your help.
Here is the detailed structure of the area :
Area Name: Data Area 64, Type 6, Block Size 4096, Extents 6, Records/Block 64, Cluster Size 64
Ext # 1, Type FIXED , Size 2097088 KByte, Name: /driver/base/driver/driver_8.d1
Ext # 2, Type FIXED , Size 787200 KByte, Name: /driver/base/driver/driver_8.d2
Ext # 3, Type FIXED , Size 810496 KByte, Name: /driver/base/driver/driver_8.d3
Ext # 4, Type FIXED , Size 769280 KByte, Name: /driver/base/driver/driver_8.d4
Ext # 5, Type FIXED , Size 801536 KByte, Name: /driver/base/driver/driver_8.d5
Ext # 6, Type VARIABLE, Size 3328 KByte, Name: /driver/base/driver/driver_8.d6
Do you know if there's a way to limit this problem or at least to predict the database growth ?
A cluster size of 64 means Progress is allocating 64 blocks for the table whose record is first created in that cluster.
With 300 tables in this area, the only way to prevent the type of growth you are seeing is to split the tables into multiple storage areas. Potentially, a number of tables could be using a cluster of 64 blocks for a single record.
I would also suggest you read Practical OpenEdge RDBMS Internals and Storage Area configuration (discussion) and setting cluster size - best approach (discussion) for more information (from people who have way more knowledge than me).
You don't mention version, which is often important in providing a detailed response.
What Kevin says is just the tip of the iceberg, really. The right answer, for best performance even more than for conserving disk space, is to organize tables into areas with tables of similar characteristics in the same area and indices in their own area. Not only does this allow you to match the storage characteristics to the characteristics of the data, but it enables you to do selective reorganization. E.g., if you have blobs in your database, those especially you would like in their own storage area since they are very different from other data.
One problem that you are probably having is that LOB size isn't reflected in any of proutil's output.
TABANALYS only shows the size of "normal" field data and indexes. LOBs are ignored.
The version is 10.1C.
In the past, this database was only one Area.
When I started to work on it, I followed the "Progress Advanced DB Course Guide" book advising that records should be split into differents areas by :
1. Type (Index or Data)
2. Records per block
3. Blocks per cluser
4. Related data
So, I created 3 data areas with 32,64 or 128 recs/block and one Index area.
The OS block size is 4KB and, still following this guide, the available space is 3866 bytes.
Then I divided this space by the mean record size of each table to determine where it should fit.
Example : Mean record size = 115. Records per block = 3866/115 = 34. Rounded up -> 64.
Is this approach correct?
I should probably have split the lobs tables into another Storage Area.
I didn't know it.
Do you know if there's another way to produce report on lobs?
There is no simple and direct method.
You either write some 4gl to loop through them all and collect the stats (that's a lot of work and a lot of IO) or you put them in a storage area that is dedicated to LOBs and simply look at the size of that area.