Exponential Database Growth

bodom131286 · Jun 12, 2020

Hi All,

In Openedge 11.6.4 DB, I have only one table which stores blobs (scanned PDF & documents). (maximum file size 90KB).
This DB is used for now 10+ years and it grew to 44 GB tin 10 years, but we are seeing an exponential growth where it has grown by 30+GB to 75GB in last 17 days.
I am in a situation where data growth in table is 60-70 MB in a day, where as database growth is 3/4GB since last 17 days.

May 24 Database stats:
RM chain utilization : 96%
tablesize : 43.7GB
DB size : 44GB

June 07 Database Stats:
RM Chain utilization is now 60%.
Table Size : 44.2 GB
DB size : 75GB

There is no application / code changes accessing /working on this database.
Where should i look further to find the root cause / correct.

------------------------------------------------------------------
23rd April
More details :

LOB SUMMARY FOR SHARED TABLES:
----------------------------------------------

--- LOB Size ---
Table LOBs Size Min Max Mean
PUB.xxblobs:1
xxblob_content:1 476099 40.2G 930.0B 53.9M 88.5K
-----------------------------------------------------------------------------
Subtotals: 476099 40.2G 930.0B 53.9M 88.5K

Summary for AREA "BLOBS": 10
-----------------------------------------------------------------------------
Subtotals: 476099 40.2G 930.0B 53.9M 88.5K

-----------------------------------------------------------------------------
Totals: 476099 40.2G 930.0B 53.9M 88.5K

5489721 RM block(s) found in the database.
96.93% of the RM block space is used.
------------------------------------------------------------------------

05th June

LOB SUMMARY FOR SHARED TABLES:
----------------------------------------------

--- LOB Size ---
Table LOBs Size Min Max Mean
PUB.xxblobs:1
xxblob_content:1 507745 43.3G 952.0B 53.9M 89.4K
-----------------------------------------------------------------------------
Subtotals: 507745 43.3G 952.0B 53.9M 89.4K

Summary for AREA "BLOBS": 10
-----------------------------------------------------------------------------
Subtotals: 507745 43.3G 952.0B 53.9M 89.4K

-----------------------------------------------------------------------------
Totals: 507745 43.3G 952.0B 53.9M 89.4K

8923985 RM block(s) found in the database.
64.51% of the RM block space is used.
---------------------------------------------------------

Thanks! for you help.
Rahul

jdpjamesp · Jun 12, 2020

How are you measuring DB size?

TomBascom · Jun 12, 2020

As Cringer asks - how are you measuring “DB size”?

The overall size of the LOB data has increased but not nearly enough to account for the growth. If your db blocksize is 8k then the increase in the sheer number of RM blocks looks like it could be enough to account for most of the difference.

RM “blocks” are not, however, the same as “RM chain” so your terminology is a bit puzzling.

What are the characteristics of the storage area holding the data? In particular, how many rows per block?

Your maximum document size is 59MB - not 90k as you state above. 90k is your average.

Have you recently updated or purged a large amount of data? Or perhaps you have added a large number of very small items?

Rob Fitzpatrick · Jun 13, 2020

bodom131286 said:
In Openedge 11.6.4 DB, I have only one table which stores blobs

Does this mean the database contains exactly one table? Or does it contain many tables, only one of which has BLOB columns?

As the others said, determining what you mean by database size is important. For example, if you are looking at the size of the files in the database directory, you may be including the BI and AI extents. A long-running transaction during your normal activity could easily grow a variable BI extent to many gigabytes. Or a failure of AI extent emptying (e.g. AI file management daemon runs out of space in -aiarcdir, or OE Replication target DB is down) could cause a variable AI extent to grow. Each of these scenarios would be problematic but they are not database-growth problems. Or it could even be some unexpected application or database problems that are causing significantly increased writes to the database log file.

If those aren't your issues, you need to look more closely at your dbanalys reports. Here are your blob line items:

Code:

xxblob_content:1 476099 40.2G 930.0B 53.9M 88.5K            april 23
xxblob_content:1 507745 43.3G 952.0B 53.9M 89.4K            june 5

bodom131286 said:
I am in a situation where data growth in table is 60-70 MB in a day, where as database growth is 3/4GB since last 17 days.

This BLOB grew (in logical size) by 3.1 GB in 43 days, or on average 73.8 MB per day. This table's growth hasn't changed appreciably so it looks to me like the wrong place to look for your unusual growth.

I suggest you look at the Database Summary sections of your April 23 and June 5 reports. Start with the "Total" line from each one. Under "Combined", are they different by 30+ GB? If not then we come back to the question of how you measure database size. If they are then compare the line items from one report to another. Analyze the data (e.g. write a program to extract the numbers, or import the data into Excel and then do the necessary manipulation to remove the unit suffixes (B, K, M, G) from the Size values and multiply by the appropriate factors to convert everything to bytes. See which tables have changed and by what amounts. Remember that these are sums of record sizes; they are not the size of the objects on disk. So if you have a problem of incorrect area RPB causing huge file growth (which is certainly a possibility), it won't be reflected in this data.

Here is an example. I created an empty DB and added two Type II areas called data256 and data1, with RPB 256 and 1 respectively. In each one I created identical tables called foo256 and foo1 and created 100,000 identical records in each. Logically they are identical, physically they are not.

Structure:

Code:

d "data256":7,256;8 ./dba_7.d1
d "data1":8,1;8 ./dba_8.d1

Extents:

Code:

-rw-rw-r-- 1 robf dbadmin   4325376 Jun 13 09:10 dba_7.d1
-rw-rw-r-- 1 robf dbadmin 819593216 Jun 13 09:10 dba_8.d1

dbanalys Record Block Summary line items (note: identical):

Code:

Table                                    Records    Size   Min   Max  Mean                Count Factor  Factor
PUB.foo256                                100000    3.3M    35    35    35               100000    1.0     1.0
PUB.foo1                                  100000    3.3M    35    35    35               100000    1.0     1.0

dbanalys Database Summary line items (note: identical):

Code:

                             Records              Indexes              LOBs               Combined
NAME                        Size  Tot %         Size  Tot %         Size  Tot %          Size  Tot %
PUB.foo256                  3.3M   44.3         0.0B    0.0         0.0B    0.0          3.3M   44.3
PUB.foo1                    3.3M   44.3         0.0B    0.0         0.0B    0.0          3.3M   44.3

You will see physical size differences reflected in other types of reports, e.g.:

viewB2 report (proutil dbname -C viewb2 > dbname.b2):
Note: does not require the use of ABP!
Note: sizes are in blocks

Code:

Area "data256":7 - Primary Buffer Pool

Object Enablement    Size     Type   Object Name
-----------------  -------- -------  ------------
Default                   7  Master   Area.Control-Object:0
Default                 480   Table   PUB.foo256:1
                   --------
                        487

Area "data1":8 - Primary Buffer Pool

Object Enablement    Size     Type   Object Name
-----------------  -------- -------  ------------
Default                   7  Master   Area.Control-Object:0
Default              100008   Table   PUB.foo1:2

statistics report (prostrct statistics dbname > dbname.stat):

Code:

  Database Block Usage for Area: data256

  Active blocks: 487
    Data blocks: 486
    Free blocks: 1
   Empty blocks: 41
   Total blocks: 528
  Extent blocks: 1
  Records/Block: 256
   Cluster size: 8

  Database Block Usage for Area: data1

  Active blocks: 100015
    Data blocks: 100014
    Free blocks: 1
   Empty blocks: 33
   Total blocks: 100048
  Extent blocks: 1
  Records/Block: 1
   Cluster size: 8

dbanalys Area Block Analysis:

Code:

AREA "data256": 7  BLOCK ANALYSIS
-------------------------------------------------

527 block(s) found in the area.

Current high water mark: 487

    1 free block(s) found in the area
    476 record block(s) found in the area
    0 index block(s) found in the area
    40 empty block(s) found in the area
    2 object block(s) found in the area
    2 cluster list block(s) found in the area
    1 object list block(s) found in the area
    2 cluster map block(s) found in the area

AREA "data1": 8  BLOCK ANALYSIS
-------------------------------------------------

100047 block(s) found in the area.

Current high water mark: 100015

    1 free block(s) found in the area
    100004 record block(s) found in the area
    0 index block(s) found in the area
    32 empty block(s) found in the area
    2 object block(s) found in the area
    2 cluster list block(s) found in the area
    1 object list block(s) found in the area
    2 cluster map block(s) found in the area

If there is real physical growth of 30+ GB in your database (by "real" I mean in the data extents, not BI or AI or DB log or something else) between April 23 and June 5 then it must be reflected in the block summaries of dbanalys. Since you already have the April and June reports, that is the place to start analyzing. Once you know which areas grew physically, you can look at which objects are in those areas and drill down from there. Good luck.

dimitri.p · Jun 14, 2020

A database being used to store scanned documents will start increasing at an obscene rate for only two reasons:
A) A LOT and I mean really a lot more documents are being stored than before ,

or the more usual reason: A scanner or scanners were replaced and now the size of the scanned documents has shot from whatever it used to be to something A LOT larger due to scanner resolution settings.
Usually accidentaly by the user accepting "default" setttings or on purpose scanning at "ungodly resolution".

Rob Fitzpatrick · Jun 14, 2020

dimitri.p said:
A database being used to store scanned documents will start increasing at an obscene rate for only two reasons:
A) A LOT and I mean really a lot more documents are being stored than before ,

or the more usual reason: A scanner or scanners were replaced and now the size of the scanned documents has shot from whatever it used to be to something A LOT larger due to scanner resolution settings.
Usually accidentaly by the user accepting "default" setttings or on purpose scanning at "ungodly resolution".

I considered a resolution change as the cause but the one BLOB we know about doesn't reflect a significant increase in growth rate over the last month and a half, compared with what it was claimed to be in the past. It shows only a 3.1 GB increase in size, which is a fraction of the claimed 30 GB database size increase.

Of course, there could be other BLOBs or tables in this database that we don't know about. We need some clarification and more detail from the poster to understand the nature of the problem (which we don't really know yet) and the root cause.

bodom131286 · Jun 15, 2020

Rob Fitzpatrick said:
I considered a resolution change as the cause but the one BLOB we know about doesn't reflect a significant increase in growth rate over the last month and a half, compared with what it was claimed to be in the past. It shows only a 3.1 GB increase in size, which is a fraction of the claimed 30 GB database size increase.

Of course, there could be other BLOBs or tables in this database that we don't know about. We need some clarification and more detail from the poster to understand the nature of the problem (which we don't really know yet) and the root cause.

Thank you for your inputs. I am attaching more details herewith.
There is only one table in database. xxblobs ()table for same is attached herewith.
please find the Dbanalysis (on various days from 23rd May )& database structure file attached as well
Database size from promon is 75GB & database backup size is 75GB as well.

--------------------------------------------------
Activity - Sampled at 06/15/20 09:59 for 7:53:42.

Event Total Per Sec Event Total Per Sec
Commits 5252 0.2 Undos 53 0.0
Record Updates 31996 1.1 Record Reads 307726 10.8
Record Creates 105901 3.7 Record Deletes 10 0.0
DB Writes 850744 29.9 DB Reads 10072115 354.4
BI Writes 467138 16.4 BI Reads 170714 6.0
AI Writes 0 0.0
Record Locks 34721 1.2 Record Waits 0 0.0
Checkpoints 439 0.0 Buffs Flushed 3259 0.1

Rec Lock Waits 0 % BI Buf Waits 10 % AI Buf Waits 0 %
Writes by APW 97 % Writes by BIW 78 % Writes by AIW 0 %
Buffer Hits 33 % Primary Hits 33 % Alternate Hits 0 %
DB Size 75 GB BI Size 1023 MB AI Size 0 K
FR chain 43 blocks RM chain 8 blocks
Shared Memory 600296K Segments 1

5 Servers, 299 Users (270 Local, 29 Remote, 78 Batch),1 Apws

RETURN - repeat, U - continue uninterrupted, Q - quit:
------------------

Thanks!
Rahul

bodom131286 · Jun 15, 2020

Cringer said:
How are you measuring DB size?

Hi Cringer,

From backup we are measuring DB size. Backup size is 75GB.
1. Openedge version : 11.6 . Service pack : 4
2. OS version : redhat 7.7
3. I am checking with application team an users if there is any changes in application / scan machine.
4. There is no error we are getting.

I am sharing some data below and attached also.
Thanks!
Rahul

There is only one table in database. xxblobs ()table for same is attached herewith.
please find the Dbanalysis (on various days from 23rd May )& database structure file attached as well
Database size from promon is 75GB & database backup size is 75GB as well.

--------------------------------------------------
Activity - Sampled at 06/15/20 09:59 for 7:53:42.

Event Total Per Sec Event Total Per Sec
Commits 5252 0.2 Undos 53 0.0
Record Updates 31996 1.1 Record Reads 307726 10.8
Record Creates 105901 3.7 Record Deletes 10 0.0
DB Writes 850744 29.9 DB Reads 10072115 354.4
BI Writes 467138 16.4 BI Reads 170714 6.0
AI Writes 0 0.0
Record Locks 34721 1.2 Record Waits 0 0.0
Checkpoints 439 0.0 Buffs Flushed 3259 0.1

Rec Lock Waits 0 % BI Buf Waits 10 % AI Buf Waits 0 %
Writes by APW 97 % Writes by BIW 78 % Writes by AIW 0 %
Buffer Hits 33 % Primary Hits 33 % Alternate Hits 0 %
DB Size 75 GB BI Size 1023 MB AI Size 0 K
FR chain 43 blocks RM chain 8 blocks
Shared Memory 600296K Segments 1

5 Servers, 299 Users (270 Local, 29 Remote, 78 Batch),1 Apws

RETURN - repeat, U - continue uninterrupted, Q - quit:
------------------

bodom131286 · Jun 15, 2020

TomBascom said:
As Cringer asks - how are you measuring “DB size”?

The overall size of the LOB data has increased but not nearly enough to account for the growth. If your db blocksize is 8k then the increase in the sheer number of RM blocks looks like it could be enough to account for most of the difference.

RM “blocks” are not, however, the same as “RM chain” so your terminology is a bit puzzling.

What are the characteristics of the storage area holding the data? In particular, how many rows per block?

Your maximum document size is 59MB - not 90k as you state above. 90k is your average.

Have you recently updated or purged a large amount of data? Or perhaps you have added a large number of very small items?

Hi Tom,

Thank you for going through this.
I am measuring DB size from backup file size of database.

d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d1 f 2097152

d "BLOBS_IDX":11,1;8 /ext1/prod/mfgdata/medlblobs_11.d1 f 65536

Storage area is holding scan PDF / documents.
No we have not recently purged any data as well no large number of updates.

I am attaching DBanalysis &schema file herewith, just in case if you get a chance to look at them.

Regards,
Rahul

bodom131286 · Jun 15, 2020

[/QUOTE]
Thank you for looking in this and helping.
I am trying to zero down to the root cause & checking if there is any change application:
difference between dbalaysis of April & June 15:

sdiff medlblobs.dbanalysis.15thJune medlblobs.dbanalys.24thMay | grep '|'
Database: /ext1/prod/mfgdata/medlblobs | Database: /ext3/test/mfgdata/medlblobs
Date: Mon Jun 15 09:19:23 2020 | Date: Thu May 28 08:26:51 2020
4 Table PUB.xxblobs:1 | 6 Table PUB.xxblobs:1
4975137 Blob PUB.xxblobs.xxblob_content:1 | 1031810 Blob PUB.xxblobs.xxblob_content:1
9830011 block(s) found in the area. | 5489638 block(s) found in the area.
Current high water mark: 9829959 | Current high water mark: 5489631
9829944 record block(s) found in the area | 5489616 record block(s) found in the area
52 empty block(s) found in the area | 7 empty block(s) found in the area
17 Index PUB.xxblobs.xxblob_key:8 | 15 Index PUB.xxblobs.xxblob_key:8
Current high water mark: 1351 | Current high water mark: 1271
18 free block(s) found in the area | 16 free block(s) found in the area
1323 index block(s) found in the area | 1245 index block(s) found in the area
6855 empty block(s) found in the area | 6935 empty block(s) found in the area
PUB._Area 7 328.0B 3 | PUB._Area 10 481.0B 3
PUB._AreaExtent 30 1.6K 5 | PUB._AreaExtent 22 1.2K 5
Subtotals: 37 2.0K 3 | Subtotals: 32 1.7K 3
Subtotals: 37 2.0K 3 | Subtotals: 32 1.7K 3
PUB.xxblobs 511678 20.6M 1 | PUB.xxblobs 477432 19.2M 1
Subtotals: 511678 20.6M 1 | Subtotals: 477432 19.2M 1
Subtotals: 511678 20.6M 1 | Subtotals: 477432 19.2M 1
Totals: 515062 21.4M | Totals: 480811 19.9M
xxblob_content:1 510233 43.5G 9 | xxblob_content:1 476099 40.2G 9
Subtotals: 510233 43.5G 9 | Subtotals: 476099 40.2G 9
Subtotals: 510233 43.5G 9 | Subtotals: 476099 40.2G 9
Totals: 510233 43.5G 9 | Totals: 476099 40.2G 9
9830049 RM block(s) found in the database. | 5489721 RM block(s) found in the database.
59.2% of the RM block space is used. | 96.93% of the RM block space is used.
_Area-Number 1027 1 1 | _Area-Number 1027 1 1
_AreaExtent-Area 1028 2 1 | _AreaExtent-Area 1028 2 1
Subtotals: | Subtotals:
xxblob_key 8 2 3 132 | xxblob_key 8 2 3 124
Subtotals: 132 | Subtotals: 124
Totals: 147 | Totals: 139
1476 index block(s) found in the database. | 1398 index block(s) found in the database.
56.87% of the index block space is used. | 57.32% of the index block space is used.
PUB._Area 328.0B 0.0 70.0B 0.0 | PUB._Area 481.0B 0.0 97.0B 0.0
PUB._AreaExtent 1.6K 0.0 292.0B 0.0 | PUB._AreaExtent 1.2K 0.0 225.0B 0.0
PUB.xxblobs 20.6M 0.0 0.0B 0.0 | PUB.xxblobs 19.2M 0.0 0.0B 0.0
PUB.xxblobs 0.0B 0.0 6.4M 0.0 | PUB.xxblobs 0.0B 0.0 6.1M 0.0
Total 21.4M 0.0 6.5M 0.0 | Total 19.9M 0.0 6.2M 0.0
60 free block(s) found in the database. | 58 free block(s) found in the database.
8744 empty block(s) found in the database. | 8779 empty block(s) found in the database.
9840358 total blocks found in the database. | 5499985 total blocks found in the database.
Database analysis complete Mon Jun 15 09:21:47 2020 | Database analysis complete Thu May 28 08:28:18 2020

bodom131286 · Jun 15, 2020

dimitri.p said:
A database being used to store scanned documents will start increasing at an obscene rate for only two reasons:
A) A LOT and I mean really a lot more documents are being stored than before ,

or the more usual reason: A scanner or scanners were replaced and now the size of the scanned documents has shot from whatever it used to be to something A LOT larger due to scanner resolution settings.
Usually accidentaly by the user accepting "default" setttings or on purpose scanning at "ungodly resolution".

Hi Dimitri,

Thank you for your response & suggestions.
I am checking for scanner settings change meanwhile looking at other place aswell.

Thanks!
rahul

bodom131286 · Jun 15, 2020

Rob Fitzpatrick said:
Does this mean the database contains exactly one table? Or does it contain many tables, only one of which has BLOB columns?

As the others said, determining what you mean by database size is important. For example, if you are looking at the size of the files in the database directory, you may be including the BI and AI extents. A long-running transaction during your normal activity could easily grow a variable BI extent to many gigabytes. Or a failure of AI extent emptying (e.g. AI file management daemon runs out of space in -aiarcdir, or OE Replication target DB is down) could cause a variable AI extent to grow. Each of these scenarios would be problematic but they are not database-growth problems. Or it could even be some unexpected application or database problems that are causing significantly increased writes to the database log file.

If those aren't your issues, you need to look more closely at your dbanalys reports. Here are your blob line items:

Code:

xxblob_content:1 476099 40.2G 930.0B 53.9M 88.5K april 23 xxblob_content:1 507745 43.3G 952.0B 53.9M 89.4K june 5

This BLOB grew (in logical size) by 3.1 GB in 43 days, or on average 73.8 MB per day. This table's growth hasn't changed appreciably so it looks to me like the wrong place to look for your unusual growth.

I suggest you look at the Database Summary sections of your April 23 and June 5 reports. Start with the "Total" line from each one. Under "Combined", are they different by 30+ GB? If not then we come back to the question of how you measure database size. If they are then compare the line items from one report to another. Analyze the data (e.g. write a program to extract the numbers, or import the data into Excel and then do the necessary manipulation to remove the unit suffixes (B, K, M, G) from the Size values and multiply by the appropriate factors to convert everything to bytes. See which tables have changed and by what amounts. Remember that these are sums of record sizes; they are not the size of the objects on disk. So if you have a problem of incorrect area RPB causing huge file growth (which is certainly a possibility), it won't be reflected in this data.

Here is an example. I created an empty DB and added two Type II areas called data256 and data1, with RPB 256 and 1 respectively. In each one I created identical tables called foo256 and foo1 and created 100,000 identical records in each. Logically they are identical, physically they are not.

Structure:

Code:

d "data256":7,256;8 ./dba_7.d1 d "data1":8,1;8 ./dba_8.d1

Extents:

Code:

-rw-rw-r-- 1 robf dbadmin 4325376 Jun 13 09:10 dba_7.d1 -rw-rw-r-- 1 robf dbadmin 819593216 Jun 13 09:10 dba_8.d1

dbanalys Record Block Summary line items (note: identical):

Code:

Table Records Size Min Max Mean Count Factor Factor PUB.foo256 100000 3.3M 35 35 35 100000 1.0 1.0 PUB.foo1 100000 3.3M 35 35 35 100000 1.0 1.0

dbanalys Database Summary line items (note: identical):

Code:

Records Indexes LOBs Combined NAME Size Tot % Size Tot % Size Tot % Size Tot % PUB.foo256 3.3M 44.3 0.0B 0.0 0.0B 0.0 3.3M 44.3 PUB.foo1 3.3M 44.3 0.0B 0.0 0.0B 0.0 3.3M 44.3

You will see physical size differences reflected in other types of reports, e.g.:

viewB2 report (proutil dbname -C viewb2 > dbname.b2):
Note: does not require the use of ABP!
Note: sizes are in blocks

Code:

Area "data256":7 - Primary Buffer Pool Object Enablement Size Type Object Name ----------------- -------- ------- ------------ Default 7 Master Area.Control-Object:0 Default 480 Table PUB.foo256:1 -------- 487 Area "data1":8 - Primary Buffer Pool Object Enablement Size Type Object Name ----------------- -------- ------- ------------ Default 7 Master Area.Control-Object:0 Default 100008 Table PUB.foo1:2

statistics report (prostrct statistics dbname > dbname.stat):

Code:

Database Block Usage for Area: data256 Active blocks: 487 Data blocks: 486 Free blocks: 1 Empty blocks: 41 Total blocks: 528 Extent blocks: 1 Records/Block: 256 Cluster size: 8 Database Block Usage for Area: data1 Active blocks: 100015 Data blocks: 100014 Free blocks: 1 Empty blocks: 33 Total blocks: 100048 Extent blocks: 1 Records/Block: 1 Cluster size: 8

dbanalys Area Block Analysis:

Code:

AREA "data256": 7 BLOCK ANALYSIS ------------------------------------------------- 527 block(s) found in the area. Current high water mark: 487 1 free block(s) found in the area 476 record block(s) found in the area 0 index block(s) found in the area 40 empty block(s) found in the area 2 object block(s) found in the area 2 cluster list block(s) found in the area 1 object list block(s) found in the area 2 cluster map block(s) found in the area AREA "data1": 8 BLOCK ANALYSIS ------------------------------------------------- 100047 block(s) found in the area. Current high water mark: 100015 1 free block(s) found in the area 100004 record block(s) found in the area 0 index block(s) found in the area 32 empty block(s) found in the area 2 object block(s) found in the area 2 cluster list block(s) found in the area 1 object list block(s) found in the area 2 cluster map block(s) found in the area

If there is real physical growth of 30+ GB in your database (by "real" I mean in the data extents, not BI or AI or DB log or something else) between April 23 and June 5 then it must be reflected in the block summaries of dbanalys. Since you already have the April and June reports, that is the place to start analyzing. Once you know which areas grew physically, you can look at which objects are in those areas and drill down from there. Good luck.

/QUOTE]
Thank you for looking in this and helping.
I am trying to zero down to the root cause & checking if there is any change application:
difference between dbalaysis of April & June 15:

sdiff medlblobs.dbanalysis.15thJune medlblobs.dbanalys.24thMay | grep '|'
Database: /ext1/prod/mfgdata/medlblobs | Database: /ext3/test/mfgdata/medlblobs
Date: Mon Jun 15 09:19:23 2020 | Date: Thu May 28 08:26:51 2020
4 Table PUB.xxblobs:1 | 6 Table PUB.xxblobs:1
4975137 Blob PUB.xxblobs.xxblob_content:1 | 1031810 Blob PUB.xxblobs.xxblob_content:1
9830011 block(s) found in the area. | 5489638 block(s) found in the area.
Current high water mark: 9829959 | Current high water mark: 5489631
9829944 record block(s) found in the area | 5489616 record block(s) found in the area
52 empty block(s) found in the area | 7 empty block(s) found in the area
17 Index PUB.xxblobs.xxblob_key:8 | 15 Index PUB.xxblobs.xxblob_key:8
Current high water mark: 1351 | Current high water mark: 1271
18 free block(s) found in the area | 16 free block(s) found in the area
1323 index block(s) found in the area | 1245 index block(s) found in the area
6855 empty block(s) found in the area | 6935 empty block(s) found in the area
PUB._Area 7 328.0B 3 | PUB._Area 10 481.0B 3
PUB._AreaExtent 30 1.6K 5 | PUB._AreaExtent 22 1.2K 5
Subtotals: 37 2.0K 3 | Subtotals: 32 1.7K 3
Subtotals: 37 2.0K 3 | Subtotals: 32 1.7K 3
PUB.xxblobs 511678 20.6M 1 | PUB.xxblobs 477432 19.2M 1
Subtotals: 511678 20.6M 1 | Subtotals: 477432 19.2M 1
Subtotals: 511678 20.6M 1 | Subtotals: 477432 19.2M 1
Totals: 515062 21.4M | Totals: 480811 19.9M
xxblob_content:1 510233 43.5G 9 | xxblob_content:1 476099 40.2G 9
Subtotals: 510233 43.5G 9 | Subtotals: 476099 40.2G 9
Subtotals: 510233 43.5G 9 | Subtotals: 476099 40.2G 9
Totals: 510233 43.5G 9 | Totals: 476099 40.2G 9
9830049 RM block(s) found in the database. | 5489721 RM block(s) found in the database.
59.2% of the RM block space is used. | 96.93% of the RM block space is used.
_Area-Number 1027 1 1 | _Area-Number 1027 1 1
_AreaExtent-Area 1028 2 1 | _AreaExtent-Area 1028 2 1
Subtotals: | Subtotals:
xxblob_key 8 2 3 132 | xxblob_key 8 2 3 124
Subtotals: 132 | Subtotals: 124
Totals: 147 | Totals: 139
1476 index block(s) found in the database. | 1398 index block(s) found in the database.
56.87% of the index block space is used. | 57.32% of the index block space is used.
PUB._Area 328.0B 0.0 70.0B 0.0 | PUB._Area 481.0B 0.0 97.0B 0.0
PUB._AreaExtent 1.6K 0.0 292.0B 0.0 | PUB._AreaExtent 1.2K 0.0 225.0B 0.0
PUB.xxblobs 20.6M 0.0 0.0B 0.0 | PUB.xxblobs 19.2M 0.0 0.0B 0.0
PUB.xxblobs 0.0B 0.0 6.4M 0.0 | PUB.xxblobs 0.0B 0.0 6.1M 0.0
Total 21.4M 0.0 6.5M 0.0 | Total 19.9M 0.0 6.2M 0.0
60 free block(s) found in the database. | 58 free block(s) found in the database.
8744 empty block(s) found in the database. | 8779 empty block(s) found in the database.
9840358 total blocks found in the database. | 5499985 total blocks found in the database.
Database analysis complete Mon Jun 15 09:21:47 2020 | Database analysis complete Thu May 28 08:28:18 2020

Rob Fitzpatrick · Jun 15, 2020

Can you list your database files and their file system sizes?

bodom131286 · Jun 15, 2020

Rob Fitzpatrick said:
Can you list your database files and their file system sizes?

Hi Rob,

Below are the required details : file system type XFS.

klevprdqad17ap1-/ext1/prod/mfgdata $ ls -ltrh medlblobs*
-rw-rw-r--. 1 mfgpro mfg 1.5K Oct 6 2019 medlblobs.st.old
-rw-rw-r--. 1 mfgpro mfg 1.7K Dec 8 2019 medlblobs.st
-rw-rw-r--. 1 mfgpro mfg 128K Jun 15 02:06 medlblobs.a2
-rw-rw-r--. 1 mfgpro mfg 128K Jun 15 02:06 medlblobs_11.d2
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 02:06 medlblobs_10.d20
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 02:06 medlblobs_10.d17
-rw-rw-r--. 1 mfgpro mfg 12G Jun 15 02:06 medlblobs.a1
-rw-rw-r--. 1 mfgpro mfg 128K Jun 15 02:06 medlblobs.d2
-rw-rw-r--. 1 mfgpro mfg 640K Jun 15 02:06 medlblobs.db
-rw-rw-r--. 1 mfgpro mfg 16K Jun 15 02:06 medlblobs.b2
-r--r--r--. 1 root mfg 265 Jun 15 02:06 medlblobs.lk
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 12:50 medlblobs_10.d18
-r--r--r--. 1 mfgpro mfg 306K Jun 15 13:00 medlblobs.lic
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:05 medlblobs_10.d15
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:05 medlblobs_10.d6
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d9
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d11
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d10
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d7
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d8
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d5
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d4
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d3
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d2
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d19
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d16
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d14
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d13
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:50 medlblobs_10.d12
-rw-rw-r--. 1 mfgpro mfg 16M Jun 15 13:50 medlblobs.d1
-rw-rw-r--. 1 mfgpro mfg 64M Jun 15 13:56 medlblobs_11.d1
-rw-rw-r--. 1 mfgpro mfg 2.0G Jun 15 13:57 medlblobs_10.d1
-rw-rw-r--. 1 mfgpro mfg 1.0G Jun 15 13:59 medlblobs.b1
-rw-rw-r--. 1 mfgpro mfg 1.2M Jun 15 13:59 medlblobs.lg
-rw-rw-r--. 1 mfgpro mfg 36G Jun 15 13:59 medlblobs_10.d21
klevprdqad17ap1-/ext1/prod/mfgdata $ df -h | grep ext1
/dev/mapper/vg_prdqad17--ext1-ext1 1.2T 818G 383G 69% /ext1
klevprdqad17ap1-/ext1/prod/mfgdata $

Thanks!
Rahul

jdpjamesp · Jun 15, 2020

Thanks for all the info. It would also help to have the medlblobs.st contents listed. I suspect you have a lot of unused space in medlblobs_10.d21

bodom131286 · Jun 15, 2020

Cringer said:
Thanks for all the info. It would also help to have the medlblobs.st contents listed. I suspect you have a lot of unused space in medlblobs_10.d21

Hi Cringer,

below are the content of DB structure file:
b /ext1/prod/mfgdata/medlblobs.b1 f 1048576
b /ext1/prod/mfgdata/medlblobs.b2
#
d "Schema Area":6,64;1 /ext1/prod/mfgdata/medlblobs.d1 f 16384
d "Schema Area":6,64;1 /ext1/prod/mfgdata/medlblobs.d2
#
a /ext1/prod/mfgdata/medlblobs.a1
#
a /ext1/prod/mfgdata/medlblobs.a2
#
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d1 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d2 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d3 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d4 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d5 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d6 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d7 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d8 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d9 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d10 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d11 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d12 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d13 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d14 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d15 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d16 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d17 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d18 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d19 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d20 f 2097152
d "BLOBS":10,64;8 /ext1/prod/mfgdata/medlblobs_10.d21
#
d "BLOBS_IDX":11,1;8 /ext1/prod/mfgdata/medlblobs_11.d1 f 65536
d "BLOBS_IDX":11,1;8 /ext1/prod/mfgdata/medlblobs_11.d2

Thanks!
rahul

jdpjamesp · Jun 15, 2020

Is /ext1/prod/mfgdata/medlblobs_10.d21 growing? I suspect not. I'm guessing this variable extent has grown for some reason but the records have gone again leaving empty space behind.

TomBascom · Jun 15, 2020

According to medlblobs.stat.june05:

Files in Area: After Image Area 1
/ext1/prod/mfgdata/medlblobs.a1 11977490432

That's 12GB which sounds like a pretty big chunk of the unexpected growth.

I don't know how you are managing ai extents but it sure seems as if whatever process you are using to archive and switch extents is not working properly.

If you are using the "switch extents when nightly backups occur" method then it seems like you might have 12GB of new data every day.

TomBascom · Jun 15, 2020

Some additional insight:

Code:

                #Blobs  increase increase%      #RM  increase  increase%  increaseGB  avgBLOB
24-May  sun     476099                      5489721                                        92
28-May  thu     504699     28600     6.01%  7407345   1917624     34.93%       14.63      536
02-Jun  tue     506040      1341     0.27%  8331889    924544     12.48%        7.05     5516
05-Jun  fri     507745      1705     0.34%  8923985    592096      7.11%        4.52     2778
12-Jun  fri     510012      2267     0.45%  9725281    801296      8.98%        6.11     2828
15-Jun  mon     510233       221     0.04%  9830049    104768      1.08%        0.80     3793

There was a big growth spurt between May 24 and May 28 but a lot less, relatively speaking, since then.

The "avgBLOB" column is the average space increase (# of RM blocks added) associated with an *added* BLOB. There was, apparently, a mix of sizes in the 5/24 to 5/28 timeframe.

To me this sure looks like there was a major change in the size of the BLOBs being added.

TomBascom · Jun 15, 2020

It is unfortunate that the BLOB records lack any date information. Maybe there is a table in another db that points to the blobs and has a record that you can join on? It would be helpful to run pseudo-code such as:

Code:

for each xxblob no-lock where createDate >= 01/01/2020 break by createDate:

  accumulate record-length( xxblob ) ( sub-count sub-minimum sub-average sub-maximum by createDate ).

  if last-of( createDate ) then
    display
      createDate
      ( accum sub-count   by createDate record-length( xxblob ))
      ( accum sub-minimum by createDate record-length( xxblob ))
      ( accum sub-average by createDate record-length( xxblob ))
      ( accum sub-maximum by createDate record-length( xxblob ))
    .

end.

(also - after 33+ years I have *finally* found a reason to use "accumulate"!)

Exponential Database Growth

New Member

ProgressTalk.com Moderator

Curmudgeon

ProgressTalk.com Sponsor

Member

ProgressTalk.com Sponsor

New Member

Attachments

New Member

Attachments

New Member

Attachments

New Member

Attachments

New Member

New Member

Attachments

ProgressTalk.com Sponsor

New Member

ProgressTalk.com Moderator

New Member

ProgressTalk.com Moderator

Curmudgeon

Curmudgeon

Curmudgeon