It's Time to Manage Your Extents!

"Will ProTop tell me when it is time to add a new fixed and or variable extent to my OpenEdge database?" It most certainly can.

Mind the following points

• keep your extents manageable (250GB to 500GB); moderate-sized extents are easier to move around
• don't hit the 1 TB limit for an extent (unrecoverable)
• you don't need to use fixed length (preallocated) extents
• monitor the usage of your last fixed extent and avoid growing your variable extent
• monitor the size of your variable extents as they grow

ProTop monitors all the details

ProTop monitors several application extent-related metrics to keep your extent management honest. It lets you know when you might be approaching a limit that will impair your chosen extent management approach. All of these are in etc/alert.*.cfg except where noted:

1. lrgExtent - the largest extent in the database; monitor this to prevent any extent from reaching 1 TB
2. hasVar - alerts when an area has no variable extent; there should always be a variable extent for every area whether you plan to use it or have it just for overflow; it is possible to forget to add a variable extent after adding a new fixed extent
3. pctLastX - percent used of the last fixed extent (export PCTLASTX=yes in bin/localenv to enable); if you prefer to manage fixed extents, this will alert you when the last fixed extent has reached 85% utilized so you can prepare to add a new extent
4. areaMaxPct - percent of maximum area size used; important to watch, especially when large files are not enabled and or you using Type I storage areas and small database block sizes
5. fsPctUsed - percent of disk space used (specific file systems can be monitored with alert filters)
6. varGB - this size of the storage area's variable extent in GB. This alertable metric is not in etc/alert.*.cfg by default. It is found in the StorageAreas Data Collector. Alerting on this metric is discussed below.

Using all variable extents

When using the variable extent management approach, you define a variable extent for each application data and index area in your database. Then you allow the database to grow those variable extents as needed. Typically you have a manageable extent size you prefer to work with (say ~250GB), and you want to know when any of your variable extents are around that size to stop that extent from growing further.

For this approach, we use the varGB alertable metric found in the StorageArea data collector as mentioned above. To be alerted once each day when a variable extent has reached and or passed 250GB:

1. Be sure the StorageAreas data collector name is in the ptInitDC variable in etc/pt3agent.*.cfg. It is included by default. If it has been removed for some reason, please replace it in the list. If you changed ptInitDC, restart ProTop by removing tmp/*.flg. ProTop will restart shortly.
2. Now add the following line to your etc/alert.*.cfg file:
   

   varGB  num  >=  250  ""  "daily"  "Variable Extent &1 &2 &3" alert

3. ProTop will automatically read this change to the alert configuration file, monitor the variable extents, and alert you when any grows to 250GB or more.

Add a new variable extent

When you receive the alert you defined above, add a new variable extent. This can be done online or offline with these steps:

1. create a file called "add.st" (any name will do) describing the new extent like the current variable extent.
2. add the new extent with this command:

   prostrct [add|addonline] <dbname> add.st

   The current variable extent is fixed at its current size, and the new variable extent is added. 

3. generate an updated structure definition file, and you are done:

   prostrct list <dbname>

Pros

• No maintenance window is needed; the database can remain online
• Database disk space consumption is organic

Cons

• Extents will grow in place during the business day (though users are unlikely to feel it (read the argument below)
• All connected sessions must have permission to access the new area
• Fixed extent sizes will not likely be the same size

Do I Really Need Fixed Length (preallocated) Storage Areas?

The most common argument against variable-length storage areas is that there is a performance cost every time the area needs to grow. Technically, this is true. However, in the real world, the cost is less than negligible.

Don't believe us? How much does your database grow every month? In our experience, 1-2 GB of growth per month covers more than 98% of the databases out there. For our example, let's assume 2GB of growth per month on a system that is only running Monday-Friday, 9-5. That's 8 hours per day, approximately 21 days per month, or 168 hours per month. 2 GB of growth, divided by 168 hours = 12 MB per hour.

A 512 block per cluster storage area in an 8KB block size database will grow 4 MB, or one cluster (512 X 8192 bytes = 4 MB), per extension. This means that our theoretical database will grow 3 times per hour (12 MB of growth per hour, divided by 4 MB per expansion event). An ok SAN (not too fast, not too slow), can grow a file at approximately 10 MB/sec. Pull out your calculator, and you'll see that the "performance penalty" for our 2 GB / month database growth is 400ms, three times per hour.

There are cases when fixed-length data extents are preferred, but those situations are rare. Not sure? Ask the experts.

There could be an argument against variable length AI extents as these tend to grow quickly and continuously. However, our benchmark testing has shown that even for AI extents, the benefits of variable extents outweigh the minor performance cost. Or to put it another way, it is very rare that the business will notice the effect of variable length AI extents, even if the DBA can measure the degradation. Most OpenEdge OLTP applications are 98% read versus 2% write. A small degradation on a tiny portion of database activity is by definition negligible.

Using fixed plus variable extents

When using the fixed extent approach, you still have a variable extent defined for each application storage area, but only as a backup to your fixed extent approach; this is considered an OpenEdge database best practice.  Should a last fixed extent fill (you missed the alert to add a new extent, for instance, because it grew too fast, etc.) and you do not have a variable extent defined, the database cannot grow and will shut itself down.

This approach involves monitoring the space used in the last fixed extent rather than the growth of the variable extent as described above. It relies on the pctLastX alert, which exists in etc/alert.cfg by default:

pctLastX num > 85 "" "daily" "Last fixed extent &1 &2 &3" alert # export PCTLASTX=yes in bin/localenv to enable

But, as the comment at the end of the line above indicates, it has to be enabled. 

To be alerted once a day when any last fixed extent is 85% used:

1. Be sure the StorageAreas data collector name is in the ptInitDC variable in etc/pt3agent.*.cfg.  It is included by default. If it has been removed for some reason, please replace it in the list.
2. Uncomment the line in your bin/localenv[.bat] containing PCTLASTX (if bin/localenv[.bat] is not there, copy bin/localenv.[bat]x to bin/localenv[.bat]).  It will look like this:

   export PCTLASTX=yes # enable alerts for the last fixed extent becoming full

3. Restart ProTop by removing tmp/*.flg files.  ProTop will restart shortly.
   
   

Add a new fixed and variable length extent

When you receive an alert with the subject "Last fixed extent [num] > 85", follow these steps to add new fixed and variable extents. NOTE: This must be done offline as you have to remove the existing variable extent, which cannot be done online.

1. create an "add.st" that defines a new fixed extent (like the last fixed extent) and a variable extent (like the current variable extent)
2. stop the database in a maintenance window
3. remove the unused variable extent:
   

   prostrct remove <dbname> d [area-name]

4. add the new fixed and variable extents:
   

   prostrct add <dbname> add.st

5. generate an updated structure file:

   prostrct list <dbname>

6. restart the database

Pros

• extents can be managed to a precisely consistent size
• extents will not grow in place during the business day

Cons

• must be done offline
• will pre-allocate disk space that other areas cannot use if this area stops growing

Conclusion

Yes, ProTop can tell you when to add a fixed and or variable length extent to your database areas.  Going with all variable or fixed plus variable extents is up to you.  Once you configure ProTop accordingly, ProTop will watch your extents, so you don't have to.