Data Area Grown Too Large

[lonegroover]

Hi there.

I'm a Unix sysadmin and have recently taken over responsibilities for a number of boxes running Progress databases following the departure of a colleague. I have some notes on clearing out full extents, and I've been able to do that successfully. But apart from that, unfortunately I know next to nothing about Progress.

Sorry about that.

Anyway Nagios flagged up a warning at the weekend for a production server, complaining that a data area had crossed a threshold. Having spent a few minutes googling, and following a brief chat with our database developer, I still don't know how to fix it. Here's the pertinent bit of the log file:

[16/01/12 10:00:01 Area Status Check for DB: /db/production/production.db                                              
   OpenEdge 10

Storage Areas violating capacity thresholds ( 20%/ 70%):

         Area Name                    %Used       Suggest %Projected
         ------------------------- -------- ------------- ----------
         Schema Area                    78%         2,496        50%
         Data Area 1                    73%     1,523,760        50%
         Data Area 2                    86%    15,536,176        50%
         Data Area 3                    81%     1,703,984        50%
         Index Area 1                   83%     5,083,952        50%

.. Data Area 2 is the one Nagios complained about, having crossed the 85% mark.

Two questions then.

Firstly - is there likely to be an impact on reliability or performance as the data areas grow closer to 100%?

And secondly, how to fix this?

I appreciate that these probably seem naive questions, but I'm grateful for any help or advice.

Thanks,
James

 

[RealHeavyDude]

Database storage areas are split into at least one or more extents (files) in the file system. All but the last extent are fixed size. The last extent of a storage area may be a fixed or variable extent. If the last extent is variable one, given that you are running an enterprise database license - not a work group one, it can grow until the file system is full. If the last extent is a fixed one, the database will shut down automatically when it becomes full and it can not write to it anymore. Therefore you should add another extent to the storage area before that happens. Depending on the version of OpenEdge and some other restrictions you might be able to do this online (10.1A+ IIRC and all processes connected to the shared memory will have access rights to the newly created file on the OS level), otherwise you must add it offline.

The prostrct add command is your friend. But, you should make yourself familiar with the database administration of an OpenEdge database in order to avoid serious trouble. I would recommend you to start with http://dbappraise.com. It contains lots of excellent information. Plus, the documentation in PDF form that comes with the product also contains valuable information.

Heavy Regards, RealHeavyDude.

 

[RealHeavyDude]

Forgot, other than the shut down there will be no impact.

Plus, if you could post the structure description file which is located in the same directory where the .db file resides, we might be able to advise you better. The structure description is an ASCII file which should be in sync with the database structure stored in the .db file. If you are not sure if it is in sync you can run the prostrct list command to have the latest information.

Heavy Regards, RealHeavyDude.

 

[lonegroover]

Heavy thanks.

The .db file is production.db, and resides in /db/production.

There's a file called production.st which looks like it might be the structure description? I'll transcribe it below. I tried "prostrct list" but it gave a "too few arguments" error. But I guess it wants a DB name or something like that. Will do a bit of research into prostrct anyway (though as always, any advice is helpful and gratefully received).

#
b /db2/production/production.b1 f 1048576
b /db2/production/production.b2
#
d "Schema Area":6,32;1 /db/production/production.d1
#
d "Data Area 1":7,64;512 /db/production/production_7.d1 f 1048576
d "Data Area 1":7,64;512 /db/production/production_7.d2
#
d "Data Area 2":8,32;512 /db/production/production_8.d1 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d2 f 2785280
d "Data Area 2":8,32;512 /db/production/production_8.d3 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d4 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d5 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d6 f 2048
d "Data Area 2":8,32;512 /db/production/production_8.d7 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d8 f 2048
d "Data Area 2":8,32;512 /db/production/production_8.d9 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d10
#
d "Data Area 3":9,64;512 /db/production/production_9.d1 f 1048576
d "Data Area 3":9,64;512 /db/production/production_9.d2
#
d "Index Area 1":10,1;8 /db/production/production_10.d1 f 1048576
d "Index Area 1":10,1;8 /db/production/production_10.d2 f 960896
d "Index Area 1":10,1;8 /db/production/production_10.d3 f 1048576
d "Index Area 1":10,1;8 /db/production/production_10.d4
#
a /db2/production/production.a1
#
a /db2/production/production.a2
#
a /db2/production/production.a3
#
a /db2/production/production.a4
#
a /db2/production/production.a5
#
a /db2/production/production.a6
#
a /db2/production/production.a7
#
a /db2/production/production.a8
#
a /db2/production/production.a9
#
a /db2/production/production.a10
#
a /db2/production/production.a11
#
a /db2/production/production.a12
#
a /db2/production/production.a13
#
a /db2/production/production.a14
#
a /db2/production/production.a15
#
a /db2/production/production.a16
#
a /db2/production/production.a17
#
a /db2/production/production.a18
#
a /db2/production/production.a19
#
a /db2/production/production.a20

 

[RealHeavyDude]

What I can see from the structure description file is that the last extent of the storage area "Data Area 2" is variable (/db/production/production_8.d10). Plus, you have at least one extent (/db/production/production_8.d2 f 2785280) that is larger than 2GB which means large files (bigger than 2GB) is enabled on your database.

Therefore, if you have enough space in the file system the last extent can grow until the file system full and you don't need to add an extent - maybe extend the file system the database is on. I don't know how Nagios determines the utilization and how and what rule is applied - so I can't say anything what you can do to quiet Nagios.

You are right - the prostrct list needs the database. Exactly the path to the .db file without the .db extension.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

You can also see from the provided .st file that extents have been added in the past in an uneven manner. So your predecessor probably didn't worry too much about this. (He likely ignored the warning that you are looking at until the variable extent started to grow.)

The main issue is just being aware of the space being used and making sure that you don't run out. It is nice to add the extents ahead of time and it is nice to be orderly about it but there isn't usually much urgency if you have plenty of disk space on the filesystem.

... you should make yourself familiar with the database administration of an OpenEdge database in order to avoid serious trouble. I would recommend you to start with http://dbappraise.com. It contains lots of excellent information.

Thanks for the plug RHD!

We do training too if "lonegroover" (or anyone else for that matter) is interested.

 

[lonegroover]

Very grateful for this advice from both - thanks.

The Nagios error only cropped up this weekend, so I imagine the data area has been growing slowly but steadily for a while. It sounds from what you're saying as it's only going to be a problem when the filesystem starts to run out of space. This is actually a virtual (KVM) machine and it's not using LVM, so growing the partition isn't an option. Apart from a small boot partition, there's only one partition and it has about 12G free so there's clearly no urgency.

However I may be able to add disk at some point and mount /var or /usr from it to reclaim some space.

Another plan is to see if data can actually be deleted from the database, I suppose.

But to be clear - while I won't really gain anything from it, will adding an extent make the data area 2 capacity figure dip below 85%?

Thanks,
James

 

[lonegroover]

.. this is the output from 'prostrct list /db/production'

PROSTRCT LIST: file /db/production.db does not exist, command ignored. (6921)
[tms@bsi production]$ prostrct list /db/production/production
Area Name: Control Area, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 1
   Ext # 1, Type VARIABLE, Size 32 KByte, Name: /db/production/production.db

Area Name: Primary Recovery Area, Type 3, Block Size 8192, Extents 2
   Ext # 1, Type FIXED   , Size 1048576 KByte, Name: /db2/production/production.b1
   Ext # 2, Type VARIABLE, Size 8 KByte, Name: /db2/production/production.b2

Area Name: Schema Area, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 1
   Ext # 1, Type VARIABLE, Size 1600 KByte, Name: /db/production/production.d1

Area Name: Data Area 1, Type 6, Block Size 4096, Extents 2, Records/Block 64, Cluster Size 512
   Ext # 1, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_7.d1
   Ext # 2, Type VARIABLE, Size 2048 KByte, Name: /db/production/production_7.d2

Area Name: Data Area 2, Type 6, Block Size 4096, Extents 10, Records/Block 32, Cluster Size 512
   Ext # 1, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_8.d1
   Ext # 2, Type FIXED   , Size 2785280 KByte, Name: /db/production/production_8.d2
   Ext # 3, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_8.d3
   Ext # 4, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_8.d4
   Ext # 5, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_8.d5
   Ext # 6, Type FIXED   , Size 2048 KByte, Name: /db/production/production_8.d6
   Ext # 7, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_8.d7
   Ext # 8, Type FIXED   , Size 2048 KByte, Name: /db/production/production_8.d8
   Ext # 9, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_8.d9
   Ext # 10, Type VARIABLE, Size 2048 KByte, Name: /db/production/production_8.d10

Area Name: Data Area 3, Type 6, Block Size 4096, Extents 2, Records/Block 64, Cluster Size 512
   Ext # 1, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_9.d1
   Ext # 2, Type VARIABLE, Size 2048 KByte, Name: /db/production/production_9.d2

Area Name: Index Area 1, Type 6, Block Size 4096, Extents 4, Records/Block 1, Cluster Size 8
   Ext # 1, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_10.d1
   Ext # 2, Type FIXED   , Size 960896 KByte, Name: /db/production/production_10.d2
   Ext # 3, Type FIXED   , Size 1048576 KByte, Name: /db/production/production_10.d3
   Ext # 4, Type VARIABLE, Size 64 KByte, Name: /db/production/production_10.d4

Area Name: After Image Area 1, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a1

Area Name: After Image Area 2, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a2

Area Name: After Image Area 3, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a3

Area Name: After Image Area 4, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a4

Area Name: After Image Area 5, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a5

Area Name: After Image Area 6, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a6

Area Name: After Image Area 7, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a7

Area Name: After Image Area 8, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a8

Area Name: After Image Area 9, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a9

Area Name: After Image Area 10, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a10

Area Name: After Image Area 11, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a11

Area Name: After Image Area 12, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a12

Area Name: After Image Area 13, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a13

Area Name: After Image Area 14, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a14

Area Name: After Image Area 15, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a15

Area Name: After Image Area 16, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a16

Area Name: After Image Area 17, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a17

Area Name: After Image Area 18, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a18

Area Name: After Image Area 19, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a19

Area Name: After Image Area 20, Type 7, Block Size 8192, Extents 1
   Ext # 1, Type VARIABLE, Size 128 KByte, Name: /db2/production/production.a20

 

[RealHeavyDude]

I would not go as far as to call the plug shameless ... credit where credit is due!

Heavy Regards, RealHeavyDude

 

[RealHeavyDude]

Personally, I can't say anything about Nagios - so I don't know. But if I would be forced to speculated I would say yes.

Heavy Regards, RealHeavyDude.

 

[lonegroover]

Thanks. Sorry for the misunderstanding, but I wasn't really asking about Nagios. I was really asking about the figure in the log file to which Nagios refers. This bit:

[16/01/12 10:00:01 Area Status Check for DB: /db/production/production.db                                                  OpenEdge 10  Storage Areas violating capacity thresholds ( 20%/ 70%):           Area Name                    %Used       Suggest %Projected          ------------------------- -------- ------------- ----------          Schema Area                    78%         2,496        50%          Data Area 1                    73%     1,523,760        50%          Data Area 2                    86%    15,536,176        50%          Data Area 3                    81%     1,703,984        50%          Index Area 1                   83%     5,083,952        50%

Ideally, I'd like that 86% figure for data area 2 to decrease somewhat.

Currently, I have the following production.delta* files:

proenv>ls -l production.delta*
-rw-rw-r-- 1 tms tms 651 Oct 27  2009 production.delta-1.st
-rw-r--r-- 1 tms tms 378 Oct 11  2010 production.delta-2.st
-rw-rw-r-- 1 tms tms 122 Mar 14  2011 production.delta.3.st
-rw-rw-r-- 1 tms tms 123 Mar 14  2011 production.delta.4.st
proenv>

The last one looks like this:

d "Data Area 2":8,32;512 /db/production/production_8.d9 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d10

I believe - or at least suspect - that creating a production.delta.5.st file with the following contents:

d "Data Area 2":8,32;512 /db/production/production_8.d11 f 1048576
d "Data Area 2":8,32;512 /db/production/production_8.d12

.. then doing

prostrct addonline production production.delta.5.st

... will sort that out for me. But I'd love someone to confirm that. I'd also be interested to know if doing that will hit performance while it reorganises the structure.

Thanks,
James

 

[TomBascom]

That report looks very familiar

Yes, if you add an extent (properly) the percentage will go down.

 

[RealHeavyDude]

Adding an extent - be it variable or fixed - will always convert the extent that was the last one when it was variable to a fixed one with the size that it had at that time. Only the last extent can be variable.

Therefore it is good practice to a variable extent as the last one and your approach from a technical point of view will work.

But, your pattern, to add a fixed and a variable extent when the utilization of the storage area is above the threshold but the variable extent has not started to grow yet (which will not be the case until the last fixed one in sequence before the variable extent has filled up) seems odd to me. That way you will create a bunch of small extents in your database structure that stay almost empty and are not necessary. Again, this won't impact performance or stability - it just seems odd to me. If you do these for a long, long, long time you may end up having a database structure consisting of much more files than needed - which, at some point in time, may cause problems with file handles in the OS and makes handling the database with OS tools more complicated than need be.

The good news is that you can fix that problem with backup/restore. Which you should do on a regular basis anyway because there are only two good backups (I think this quote is from Tom): A tested and a deleted one. In a nutshell: Whenever you restore a database with Progress restore command it will use the structure description file residing in the directory where you restore the database to. This structure description file must contain the same storage areas, but the number of extents, their types and sizes may differ as long as their is enough space in the storage area.

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

As you're new to OpenEdge you will want to familiarize yourself with the product documentation, as RHD said. I don't believe you mentioned the exact version you're on, aside from the fact that it is in version 10 somewhere. If you don't have the documentation PDFs that came with Progress, you can access them online in PDF or HTML format, or you can download them.

Links to 10.2B docs:
http://communities.progress.com/pcom/docs/DOC-103525

Other versions:
http://communities.progress.com/pcom/docs/DOC-16074

Start with the Database Essentials manual, and then move on to the Database Administration manual.

 

[lonegroover]

Thanks again for all the help.

I added an extent last night as transcribed above, and the Nagios itch has been scratched.

As for the particular version we're running, I don't know. I have a database administration course guide that is labelled Progress OpenEdge 10, but that could be out of date. Can I type proadsv --version or something similar?

James

 

[lonegroover]

.. I guess it must be 10.1A or later, as I added an extent online (just read that that's only possible post-10.1A).

 

[Cringer]

The first few lines of the db log after the last db start will tell you what version you're running.

eg:

[2012/01/17@09:32:26.593+0000] P-5260       T-5268  I BROKER  0: (333)   Multi-user session begin. 
[2012/01/17@09:32:27.515+0000] P-5260       T-5268  I BROKER  0: (5326)  Begin Physical Redo Phase at 0 . 
[2012/01/17@09:32:27.984+0000] P-5260       T-5268  I BROKER  0: (7161)  Physical Redo Phase Completed at blk 124 off 3133 upd 0. 
[2012/01/17@09:32:27.984+0000] P-5260       T-5268  I BROKER  0: (13547) At end of Physical redo, transaction table size is 128. 
[2012/01/17@09:32:28.156+0000] P-5260       T-5268  I BROKER  0: (452)   Login by SYSTEM on CON:. 
[2012/01/17@09:32:28.187+0000] P-5260       T-5268  I BROKER  0: (5644)  Started for jp-icmasliv using TCP IPV4 address 0.0.0.0, pid 5260. 
[2012/01/17@09:32:28.187+0000] P-5260       T-5268  I BROKER  0: (8836)  Connecting to Admin Server on port 7841. 
[2012/01/17@09:32:28.187+0000] P-5260       T-5268  I BROKER  0: (14262) Successfully connected to AdminServer on port 7841 using TCP/IP IPV4 address 10.20.4.128. 
[2012/01/17@09:32:28.203+0000] P-5260       T-5268  I BROKER  0: (8846)  Registered with Admin Server. 
[2012/01/17@09:32:28.203+0000] P-5260       T-5268  I BROKER  0: (4234)  Progress OpenEdge Release 10.2A build 1390 SP03 on WINNT .

 

[RealHeavyDude]

The installation directory contains a text file called version that is used by each Progress shell command to display the version. Just have a look into it - given that you know where your installation directory is ...

Heavy Regards, RealHeavyDude.

 

[lonegroover]

Thanks!

[root@tpn ~]# cat /app/openedge/version
OpenEdge Release 10.1C04 as of Fri May 29 21:51:17 EDT 2009
[root@tpn ~]#

 

[TomBascom]

That is better than v9 -- but only by the thinnest of margins.

You should be thinking about upgrading before you become a poster child for "ancient, obsolete and unsupported".