Question Choosing The Right Storage Area For Tables

BigSlick

Member
Yes, that's one of the issues I tried to mention! Thought it was in 11.3 also, maybe i'm just confused,

I'll see what the boss says regarding 11.5.1. makes sense to me; they'll be lots of new toys!
 

BigSlick

Member
While i'm on this, what about tables within the Schema Area? Can/Should these be moved to conform with a more comfortable RPB or due to their nature do we leave them alone?

Would they benefit from being moved Type 2 storage area?
 

Cringer

ProgressTalk.com Moderator
Staff member
The only tables in the schema area should be the tables created by Progress (system tables). Any of your tables/indexes should be in type2 areas.
 

Cringer

ProgressTalk.com Moderator
Staff member
I don't think you can do anything with them even if you wanted to - yes leave them alone.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Would you recommend this? I've seen an issue with both 11.3 and 11.4 (don't ask as i'll have to dig them out) so is 11.5 the best option?
If it's an option, or even preferred, then yes I would recommend getting to 11.x. It puts you in a better position for both long-term support and getting access to newer features and add-on products. It also puts you in a better place in terms of security standards support.

My personal belief is that all sufficiently-complex software has bugs. So yes, there are issues in 11.3 and 11.4 and every other release. The question is how the issues will affect you and whether there are viable workarounds if the impacts are meaningful.

In terms of restructuring your DB, any release from 11.3 onward will have all the performance enhancements I referenced. It's worth noting that 11.3 and 11.4 have a nasty prorest bug that could prevent you from successfully restoring an online backup unless it was created with the bibackup all parameter; that bug (PSC00317988) is fixed in 11.5 but not in 11.4.

My view is that 11.5 is a better platform for the long term than 11.4. 11.4 was only the current release of OpenEdge for four months (August - December 2014). By contrast, 11.5 has already been the current release for eight months and that will likely be close to a year by the time of the release of 11.6. That means that in the final analysis, 11.5 will have gained a much larger install base than 11.4 and will likely be better supported in the long run. And it has already received a service pack as Cringer noted, whereas 11.4 still has not.

So my recommendation is to go with 11.5.1.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Sorry I was talking about System tables / VST's. Should these be left alone?
You can't do anything about the location of almost all system tables (note VSTs, being virtual, don't have a physical location) but there is new system table and VST schema in v11.

Moving a DB to v11 requires a DB version change from 10 to 11. So this is accomplished either by upgrading the DB with proutil -C conv1011 or by dumping and loading into a newly-created v11 DB.

If you do the former it does the proutil -C updateschema and updatevst for you (although in the past there have been bugs in this). Doing the latter, you already have v11 schema in the new DB so there is nothing you need to do to update it.
 

BigSlick

Member
Thanks Rob. Much appreciated. I'll see if we can get to 11.5.1.

I'll see how long > 1Tb takes to dump and load or convert.
 

Cringer

ProgressTalk.com Moderator
Staff member
The convert doesn't take long, and as you're not migrating OS or anything you're probably ok to do that. But then as you need to D&L anyway it might be the easiest way to crack the nut.
 

BigSlick

Member
I've written a program which goes through the dbanalys and takes the table and index data and outputs them to a more manageable spreadsheet. Later to be stored in a database :) so help record growth.

The program does some calculations based on the info i've kindly been given or read; as shown below.

I've just been reading Tom's sos.ppt and have got a little confused here:

Sort the tables within the rows per block grouping by size and assign any tables whose total size would require is less than 256 blocks into another group. The tables requiring more than 256 blocks should be assign to a storage area with a cluster size of 512. The smaller tables should be assigned to an area with a cluster size of 8.

Is he saying anything less than 256 RPB go into there own storage area, or shouldn't I worry about this?

Is the clusters per block advice, still advised?

Indexes should always be assigned to a storage area whose rows per block is set to 1. So long as you never make a mistake and put data in that area

I've seen somewhere 8RPB just in case and a cluster size of 8 or 64. I think 64 is now the agreed size.

Is it good practice to separate tables and indexes? Or just indexes on tables where the table requires it's own storage area?

upload_2015-8-28_10-26-47.png
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Is he saying anything less than 256 RPB go into there own storage area, or shouldn't I worry about this?
It was 256 blocks, not RPB. He's saying large (i.e. fast-growing) tables should be in an area with the largest cluster size. Fast-growing tables will require more allocations/extends, so large cluster size will help by doing allocations less often and will provide larger runs of contiguous disk space.

I've seen somewhere 8RPB just in case and a cluster size of 8 or 64. I think 64 is now the agreed size.
For index areas I use 128 RPB, just in case. The old RPB 1 guidance was from the pre-10.1B days where you wanted to conserve rowids. RPB for an index area only matters now if you screw up and put a table in the area. Higher RPB prevents the rapid growth and bad performance that would result if you put a growing table into an RPB 1 area.
I use 64 blocks per cluster for index areas.

Is it good practice to separate tables and indexes? Or just indexes on tables where the table requires it's own storage area?
If I give the table its own area I give its indexes their own area. If the table is in a multi-table area I put its indexes in a multi-index area. But I never put more than one object type (tables, indexes, LOBs) in a given area.
 

TheMadDBA

Active Member
Just seconding what Rob said and adding my strategy for object to area mapping...

- Match RPB for the table
- Size and activity of the table (larger and/or more update/delete active tables usually get their own area)
- LOBs get their own area
- If a table deserves its own area, so do the indexes for that table. Very large indexes may deserve their own area.

Having a huge cluster size can help or hurt depending on the speed of your disk subsystem. In theory if everything is perfect larger cluster sizes help. If your disk is slower it can cause longer pauses.
 

BigSlick

Member
Thanks for the info; I'm well away to improving the systems! One stumbling block i'm having though is convincing my boss to move to 8K with the Windows standard being 4K.

According to Progress; "In Windows, the operating system assumes that files and memory are handled in 4KB chunks. This means that all transfers from disk to memory are 4KB in size. The Windows operating system has been highly optimized for 4KB and often the performance at an 8KB setting is not as good as 4KB."

I've had a scour and all i can find it that increasing the blocksize can reduce IO operations, but this isn't enough to convince. How can i win this argument? What benefits will 8K give to Windows?

Thanks in advance.
BS
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Also, 4KB page and cluster sizes aren't limited to Windows. Linux uses them too. I still use 8 KB database blocks.

Blocks contain more than application data (records or index keys). They also contain overhead, e.g. block headers (all block types) and commit limit (RM blocks). As that overhead is the same size between 4 KB and 8 KB blocks it represents a greater percentage of the block size in 4 KB blocks versus 8 KB blocks. So doing a given amount of logical I/O in a 4 KB database requires more physical I/Os than in an 8 KB database, all other things being equal.

It's also important to bear in mind that not everything in the docs was written at the same time. Some info is relatively modern. Other info was written years ago and has been copied & pasted from one version to the next. In the past hardware was different, OSes were different, and the thinking on optimization was different. Not all old advice is bad, but not all is good either.

Also, docs are just like software: written by fallible humans and can contain bugs. Some of it is just wrong.
 
Top