Database does not seem to use free space from deleted data

[aasold]

I have a database that has been running since May 2010, when it was dumped and reloaded in OE10.1A, WinSrv2008R2 (later upgraded to OE10.2B). The ST-file gives the db about 10GB of space in several areas (Type II areas), the area "db-rapp" about 7GB. (extent def. looks like this: ...d "db-rapp":9,64;512...). Yesterday the db shut down and reported "(10601) SYSTEM ERROR: Attempted to exceed maximum size on file ... " on the area "db-rapp" (the variable overflow extent had exceeded 2GB). When I check the _dbstatus._dbstatus-emptyblks, I find that I have very little free space in the db. However, when I run proutil/dbanalys, it reports that the entire db only uses a total of about 1.5GB, and about 1.2GB in the area "db-rapp". My question is: how can the area db-rapp go full when it has 7GB of space to use, and the data only takes up 1.2GB? (Indexes are not in the same area). I can see that dump/load probably solves the problem, but probably only temporarliy? I might add that the area has a lot of data created and deleted all the time - very high turnover. Best regards Aasold Ekelund Orgsoft as, Norway

 

[Cringer]

It sounds like your data has a very high amount of fragmentation. That's why the dump and load fixes the issue.

Your first job is to turn on large files as that will mean the 2GB limit on the variable extent is gone. That will mean the db won't crash so easily.

You might also find some useful info here: http://dbappraise.com/ppt/sos.pptx

 

[TomBascom]

It could also be that your rows per block setting is inappropriate.

Or you may have an issue with the create and toss limits. i.e. You might have lots of blocks with free space -- but not enough to add a record.

The detailed dbanalys metrics for this area might shed some light on the problem.

 

[Rob Fitzpatrick]

Agreed. Dbanalys/tabanalys will show you how much data resides in your DB, not how much space it consumes on disk. If there is a large variance there I would look at the area's RPB and the mean record sizes of the table(s) in it. If you have a particularly large or fast-growing table it would make sense to put it in its own area (and its indexes in another) so you can better tailor the area settings to the data. If it has very small records then you area storing "digital air", so it will consume more disk space and grow your extents more quickly than it should.

 

[TomBascom]

Are there any LOBs in your database?

proutil doesn't report anything about them -- they can take up all sorts of room while remaining invisible to dbanalys.

 

[aasold]

Thanks for your answers so far. There are no LOBs in this area (only in another area, which does not show problems). The area in question has 4 tables, only one of them (rapport) with a lot of data and high turnover. Below are some glimpses from dbanalys.

However, I found the following in the "Database Administration" documentation:
"The OpenEdge RDBMS reserves space within each area for recovery purposes. The reserved space allows room for database growth during recovery. A database area can grow up to its maximum size, less the reserve recovery space indicated in Table 2–4."

Table 2-4 says an area with block size 4k or 8k always will reserve 5GB of space for recovery. My db has block size 8k.

Could this be the answer? Does it mean that I always have to do my space-reqired-calculation after reserving 5GB for each area for crash recovery? If so, many of my dbs will need to be a lot bigger.

-Aasold


******************* parts of dbanalys result: *******************

AREA "db-data" : 8 BLOCK ANALYSIS
-------------------------------------------------


375084 block(s) found in the area.


Current high water mark: 46591


57 free block(s) found in the area
46352 record block(s) found in the area
0 index block(s) found in the area
328493 empty block(s) found in the area
45 object block(s) found in the area
45 cluster list block(s) found in the area
1 object list block(s) found in the area
45 cluster map block(s) found in the area


[...]




RECORD BLOCK SUMMARY FOR AREA "db-rapp" : 9
-------------------------------------------------------
-Record Size (B)- ---Fragments--- Scatter
Table Records Size Min Max Mean Count Factor Factor
PUB.botteidnr 502513 43.4M 81 97 90 502513 1.0 1.0
PUB.hendlogg 0 0.0B 0 0 0 0 0.0 0.0
PUB.rapport 2624576 1.0G 164 1447 418 2624576 1.0 1.7
PUB.statistikk 2130 414.3K 191 436 199 2130 1.0 1.3


------------------------------------------------------------
Subtotals: 3129219 1.1G 81 1447 365 3129219 1.0 1.7

 

[Rob Fitzpatrick]

Thanks for your answers so far. There are no LOBs in this area (only in another area, which does not show problems). The area in question has 4 tables, only one of them (rapport) with a lot of data and high turnover. Below are some glimpses from dbanalys.

However, I found the following in the "Database Administration" documentation:
"The OpenEdge RDBMS reserves space within each area for recovery purposes. The reserved space allows room for database growth during recovery. A database area can grow up to its maximum size, less the reserve recovery space indicated in Table 2–4."

Table 2-4 says an area with block size 4k or 8k always will reserve 5GB of space for recovery. My db has block size 8k.

Could this be the answer? Does it mean that I always have to do my space-reqired-calculation after reserving 5GB for each area for crash recovery? If so, many of my dbs will need to be a lot bigger.

I interpret the documentation as:

• Determine the area's theoretical maximum size, based on
  • area RPB
  • DB block size
  • area type (I or II)
  • recid length
  • large file support
• Take that number in GB and subtract the value in the "Reserved recovery space" column in Table 2-4 for your DB block size, and that is your actual theoretical maximum area size.

I don't interpret it as "the size of your DB is size in promon plus 5 GB". That's my non-expert opinion.

RECORD BLOCK SUMMARY FOR AREA "db-rapp" : 9
-------------------------------------------------------
-Record Size (B)- ---Fragments--- Scatter
Table Records Size Min Max Mean Count Factor Factor
PUB.botteidnr 502513 43.4M 81 97 90 502513 1.0 1.0
PUB.hendlogg 0 0.0B 0 0 0 0 0.0 0.0
PUB.rapport 2624576 1.0G 164 1447 418 2624576 1.0 1.7
PUB.statistikk 2130 414.3K 191 436 199 2130 1.0 1.3


------------------------------------------------------------
Subtotals: 3129219 1.1G 81 1447 365 3129219 1.0 1.7

I would break this data into separate areas. You have one area that contains a large table with large records, a medium-size table with tiny records, a tiny table with medium-sized records, and a completely empty table. You can't optimize for all of them. By the way, were you aware that the empty table hendlogg and its indexes consume 4 MB of disk space each when you use cluster size 512 and 8 KB blocks?

The ideal RPB for these tables, based on their mean record sizes and 8 KB blocks, is 128 for botteidnr, 64 for statistikk, and maybe 32 for rapport although it seems to have some deviation from the mean. You may want to adjust the cluster sizes as well; I doubt you need 512 for a very small table like statistikk. And given the record size of rapport, and depending on how you access it (e.g. write once, versus create then update), you may want to increase its toss limit from the default of 300 KB. Look at your dbanalys output and see how many blocks are in its RM chain. If you're in the thousands of blocks you may benefit from a change.

Can you post the full area 9 definition from your structure file, a directory listing from your DB directory, and a proutil describe?

Also, rapport and statistikk may be in need of an index compact or rebuild. Have a look at the utilization stats in the index section of dbanalys.

 

[aasold]

Here is the area 9 definition from my local copy of the database (after adding an extent):

d "db-rapp":9,64;512 c:\baser\dkk\medlem_9.d1 f 1000064
d "db-rapp":9,64;512 c:\baser\dkk\medlem_9.d2 f 1000064
d "db-rapp":9,64;512 c:\baser\dkk\medlem_9.d3 f 1000064
d "db-rapp":9,64;512 c:\baser\dkk\medlem_9.d4 f 1000064
d "db-rapp":9,64;512 c:\baser\dkk\medlem_9.d5 f 1000064
d "db-rapp":9,64;512 c:\baser\dkk\medlem_9.d6

To split up into several areas is something i hesitate to do for several reasons. First it's the recovery space: if each area needs a lot of space for crash recovery, it will consume too much space. Second: this is a db-structure that we have in 25 different dbs (different locations/different customers). Most of our customers use the statistikk and hendlogg tables extensively (unlike the one shown in this particular case). We regurlarly upgrade the db schema, and to have different db/area structures for different dbs has a high administrative cost.

I sure appreciate your help!

-Aasold