Question Fixed Extents

Hi All,
The existing DB (Size 2 TB) on 9.1E version has got all variable extents (963 tables split across 55 data and 55 index area) where the segregation of tables are based on functionality which doesn't seem to be a good design. We are migrating to 11.6 version; as part of our migration we have segregated the tables based on the # of rows, mean record size and transaction load. I have to make a decision to either go with either variable or fixed extent; based on what parameters should I take my decision (what all I should take into consideration)?

I have hand picked some 20+ tables out of the 963 tables put it in a separate excel sheet and have attached to this thread for your reference (replaced the table/db names with dummy names - sorry for that).

Overall there are,
23 Large Size Tables (separate area)
10 Medium Size Tables (separate area)
88 Small Size Tables (segregated to 3 different areas)
842 Very Small Size Tables (segregated to 3 different areas)

If in case you suggest fixed extents and 1 variable extent for each area, what could be the size of each extent? How do I decide the size of each extent?

Regards,
Saravanakumar B
 

Attachments

  • Sample_Data.xlsx
    13 KB · Views: 5

Cringer

ProgressTalk.com Moderator
Staff member
The nice thing about a modern and supported version of Progress is that you can add additional extents online, so as long as you are monitoring how full your extents are it doesn't matter if you get it a bit wrong on the small side. That being said, as Tom says, you want to have a reasonably buffer. The factor that will determine how much free space you need is the speed the tables are growing. You should be able to get an idea of which tables are growing fast by comparing dbanalys output on your current system. Those will be the ones you want to look out for in particular after you migrate.
The most important thing though is to monitor how full your extents are regularly and take action when necessary.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
For ease of management I prefer all variable extents. If you have some tables with extremely fast growth then there might be a measurable benefit to using fixed extents for them. Some would also argue that you don't want any one database file to get too large (say, 100+ GB), as that could make certain maintenance/repair activities more time-consuming.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I also use variable extents for AI and use the AI file management daemon to switch extents based on -aiarcinterval.
 
@TomBascom @Cringer @Rob Fitzpatrick - Thank you!

As my current db on 9.1E is using variable extents and users/developers are fine with performance; can I stick with the same? with one exception - we have 4 Fast Growing Tables which are more than 100 GB. Can I go with 32 GB fixed extents (number of extents will be depending on the size of the table and as Tom suggested would be giving it a 50% free space or bit less as we can add fixed extents online)? Is this approach advisable?

Also as per this design it will be a mix of fixed and variable extents, i mean 4 tables will have multiple fixed extent followed by 1 variable extent and rest will be all variable extents. I assume the dump and load process is not going to be different because of this design. Is that right?

If we go with this design, will there be any operation in my migration process where I have to handle the area's (fixed vs variable extent) in a different way?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
This shouldn't affect your dump and load. Take Tom's advice and do a full test load and use the resulting database as your starting point. Procopy the empty database overtop of it after the first test load and the result is a database that is pre-grown to the size of the data you want to load, but logically empty. Then you can load schema, users, sequence values, data, SQL permissions, etc.
 
@Rob Fitzpatrick - Quick question, in this case as we have the db pre-grown but pretty much with no data the initial load becomes faster even though its using variable extent. is my understanding right?

Sequence of actions;
1. Use prostrct and create the db with all variable extents area
2. Do complete dump and load of all tables that are currently on the system
3. Apply empty database (in out case an 8K db) [We will get a DB that is pre-grown but logically no data]
4. Load schema, users, sequence values
5. Do a complete dump and load that is equal to current production on this db
6. followed by rest of the activities

Is my understanding right? And on step 2 I have to do dump and load of all tables and not just few.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Essentially, yes. As discussed earlier you might want a mix of variable areas and fixed areas, based on table growth rate and size.

I suggest you take a backup of the DB after step 3, as you may want to test the load multiple times with different settings. Restoring that backup before each test run would be faster than repeating the process from the start each time.

Yes, dump and load all tables in step 2.
 
@Rob Fitzpatrick - we started the test today.

Meanwhile there was one more idea that came up during the meeting which we aren't sure it will work the way we think (basically to avoid fixed extent but still get db performance which will be more or less same as that of having fixed extent). Below is the step by step process;
1. Use prostrct and create the db with all variable extents area
2. Load the DF
3. Use BUFFER-COPY (or any technique) and try to mimic the data for top 50 tables which are considered to be Large/Medium/Fast Growing (say if a table size is 110 GB with 326258926 records, we will create records 2 times or more to double the table size)
3. Apply empty database (in our case an 8K db) [We will get a DB that is pre-grown but logically no data]
4. Load schema, users, sequence values
5. Do a complete dump and load that is equal to current production on this db
6. followed by rest of the activities

So I expect the Large/Medium/Fast Growing tables to have their extents pre-grown which will act similar to that of having a fixed extent. Is my understanding right?

If this could also be considered as a suggestion then we can run this test as well in parallel. Please advise.
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
Use BUFFER-COPY (or any technique) and try to mimic the data for top 50 tables which are considered to be Large/Medium/Fast Growing (say if a table size is 110 GB with 326258926 records, we will create records 2 times or more to double the table size)
Even if you write double the amount of data to your "template" database than you actually expect to load, you won't prevent the database from someday growing the variable extents in those areas. You're just delaying the day when that starts happening. This seems like a lot of work to just postpone the inevitable: extending the variable extent and writing data to it. Also, in the process you're making your new database physically larger on disk than it needs to be given its logical size.

I would suggest that you start with a strategy of loading to a pre-grown database and then monitoring the rate at which various areas grow, the responsiveness of the client application(s), the frequency of extend operations, the perceived stall of the application(s) during extends, if any, etc. If you decide that your transaction volume in certain tables is too high, you can always switch them to a strategy where you write to fixed extents. There's really not much penalty in trying the variable approach first. Just be aware that you'll need to add HWM monitoring for those areas and add new fixed extents and a new variable before you need them.

If you do decide to load data into a table twice, don't try to rebuild any of its unique indexes.
 
For the data portion you could just load the .bd twice ;)
True, I agree Tom.

This seems like a lot of work to just postpone the inevitable: extending the variable extent and writing data to it.
I agree Rob but when I brought up this point they said tables to grow to this point will take another 4 to 6 years before which we will have db maintenance and we can plan by then.

Also, in the process you're making your new database physically larger on disk than it needs to be given its logical size.
This point needs to be seriously taken into consideration. I hope they don't say me that they have enough space and not to worry about. :) I guess it's not a good idea to have such a large pre-grown db as baseline version.

If you decide that your transaction volume in certain tables is too high, you can always switch them to a strategy where you write to fixed extents.
I agree, Rob. As we have an option to add fixed extents at any given point of time to an area, I guess we can go with the first approach and monitor the area's that has fast growing tables to see if adding fixed extent will improve the performance at some later stage.
 
I have a question in continuation to the above one, (hope it can be on the same thread as it's inter related)

Based on old design I saw the below piece of log on promon;

Database extends 16640 99840 1664.00 2.80

What does 2.80 represent?

In our process will I see this value as 0.00 or smaller value than 2.80 when I load the data for the second time as part of step 5?
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
Based on old design I saw the below piece of log on promon;

Database extends 16640 99840 1664.00 2.80

What does 2.80 represent?
I assume this is from promon R&D | 2 | 10 (Activity, Space Allocation). This is the screen (in 11.3.2):

Code:
10/12/16        Activity: Space Allocation
23:26:42        10/12/16 23:26 to 10/12/16 23:26 (6 sec)

                                    Total         Per Min          Per Sec          Per Tx

Database extends                        0               0             0.00            0.00
Take free block                         0               0             0.00            0.00
Return free block                       0               0             0.00            0.00
Alloc rm space                          0               0             0.00            0.00
Alloc from rm                           0               0             0.00            0.00
Alloc from free                         0               0             0.00            0.00
Bytes allocated                         0               0             0.00            0.00
rm blocks examined                      0               0             0.00            0.00
Remove from rm                          0               0             0.00            0.00
Add to rm, front                        0               0             0.00            0.00
Add to rm, back                         0               0             0.00            0.00
Move rm front to back                   0               0             0.00            0.00
Remove locked rm entry                  0               0             0.00            0.00

Enter <return>, A, L, R, S, U, Z, P, T, or X (? for help):

So the number in the last column is extends per transaction.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
In our process will I see this value as 0.00 or smaller value than 2.80 when I load the data for the second time as part of step 5?
Your extends should be close to zero in your second load. You might have a few, depending on the transaction activity in production between your obtaining the copy with which to build the template DB and you actually shutting down production to begin the D&L.

One other point that's always worth mentioning in a D&L, I can't remember if it's already been said; take a tabanalys or dbanalys from your source DB after it is shut down and from your target after the load/rebuild and compare the record counts in all tables to ensure they match exactly. Do this before bringing up the target as new production and committing transactions to it.
 

Cringer

ProgressTalk.com Moderator
Staff member
One other point that's always worth mentioning in a D&L, I can't remember if it's already been said; take a tabanalys or dbanalys from your source DB after it is shut down and from your target after the load/rebuild and compare the record counts in all tables to ensure they match exactly. Do this before bringing up the target as new production and committing transactions to it.
This is really important. 11.2.1 had a bug in it whereby if you did a multi-threaded binary dump and the table didn't have the indexes necessary for multi-threading it told you it started a single threaded dump but in fact it dumped nothing. We found this out the hard way!
 
@Rob Fitzpatrick @Cringer - Thank you!

Your extends should be close to zero in your second load. You might have a few, depending on the transaction activity in production between your obtaining the copy with which to build the template DB and you actually shutting down production to begin the D&L.
Just wanted to make sure the extends doesn't get recalculated during the second time load (I understand there may still be few) as its pre-grown.
 
Top