Question Choosing The Right Storage Area For Tables

BigSlick

Member
Hello all,

Were using OE10.2B on Windows server 2008 64bit.

Firstly, i'm probably going about this the wrong way so a nudge in the right direction would be nice. I'm trying to determine whether a table is sat in the wrong storage area and would benefit by being moved to something a little more homely.

My .st file looks a little like this:

Code:
#
b C:\db\test.b1 f 1024000
b C:\db\test.b2
#
d "Schema Area":6,32;1 C:\db\test.d1 f 128000
d "Schema Area":6,32;1 C:\db\test.d2
#
d "Data Area":7,64;64 C:\db\test_7.d1 f 2048000
d "Data Area":7,64;64 C:\db\test_7.d2
#
d "Area8":8,32;64 C:\db\test_100.d1 f 2048000
d "Area8":8,32;64 C:\db\test_100.d2
#
d "Area9":9,64;64 C:\db\test_101.d1 f 2048000
d "Area9":9,64;64 C:\db\test_101.d2
#
d "Area10":10,128;64 C:\db\test_102.d1 f 2048000
d "Area10":10,128;64 C:\db\test_102.d2

and my tabanalys looks like this:

Code:
RECORD BLOCK SUMMARY FOR AREA "Data Area" : 7
-------------------------------------------------------
                                                           -Record Size (B)-           ---Fragments--- Scatter
Table                                    Records    Size   Min   Max  Mean                Count Factor  Factor
PUB.table1                                      1   57.0B    57    57    57                    1    1.0     1.0
PUB.table2                                 95890    6.5M    64   276    71                95890    1.0     1.0
PUB.table3                                101789   18.2M   113   317   187               101789    1.0     1.0
PUB.table4                                 57    3.8K    50    81    68                   57    1.0     1.3
PUB.table5                                     95   10.6K    65   236   114                   95    1.0     1.0
PUB.table6                                   511   23.3K    32    50    46                  511    1.0     1.0
PUB.table7                                   39    1.7K    29    69    44                   39    1.0     1.0
PUB.table8                                     1   35.0B    35    35    35                    1    1.0     1.0
PUB.table9                                    11    1.5K   132   151   144                   11    1.0     1.0
PUB.table10                                 1646  358.3K   196   255   222                 1646    1.0     1.0
PUB.table11                                   33   52.3K   216 15099  1624                   36    1.0     1.0

From my misguided understanding;

Area 7 has 64 records per block
Area 7 has 64 blocks per cluster
We have a 4K block size

Does that mean each record has an average size of 4096/64 ? 64bytes? or even 62.5?

If, by some miracle i'm on the right track here, does that mean that at least half of the tables above are in the wrong area.

Thanks in advance.
BS
 

BigSlick

Member
Thanks Cringer; appreciate the rapid response.

I'd like a more hands-on approach though in order to start learning. - I know re-inventing the wheel. But it would be nice to get my head around the outputs and be able to notice issues when presented with such data.

Thanks
 

Cringer

ProgressTalk.com Moderator
Staff member
Completely understand, and applaud the approach. The best thing about that tool is it's quick and easy, and you can then use it to verify your own understanding.
As I understand it, as a rule of thumb, take the blocksize (-100 for the overhead). Then divide that by the mean record size. I don't think cluster size comes into it, but I am open to be shouted down! :)
Table1 for eg works out at 71.79 records per block, so round that up to the next power of two - 128.
Table2 works out at 57.63 so is in the right area.
 

Cringer

ProgressTalk.com Moderator
Staff member
I should point out, it's a rule of thumb, it's not an exact approach, and as you add more data to the tables your mean record size could fluctuate, so it's not a hard and fast solution.
 

BigSlick

Member
Thanks :)

That's a starting point. Hopefully my understanding isn't too far away from reality then.

My main concern it that our database is > 1TB and is growing rapidly. I'm hoping the majority of this is down to poor structure and too many under utilised or unused indexes.
 

TheMadDBA

Active Member
The cluster size and the records per block don't really impact the average record size, although improper settings can cause some amount of wasted space and potentially large performance issues. The cluster size determines the minimum size for an object (table, index or lob) and how many blocks are added to the area when it is extended (grown).

Records per block basically splits the block into slots.. so for 64 RPB there will be 64 slots in a block. The mean record size is the key since there will always be those random records that are larger or smaller that you just can't tune for.

For a quick start use (4096 - 100) / RPB to determine the slot size for a record and then compare that to the mean record size from the tabanalys/dbanalys. It looks like most of the tables in that area are using the wrong RPB, but they are so small that you aren't really wasting that much space. Since you are looking into fixing the area setup you should strongly consider converting to an 8K blocksize as well.

As the author of the suggested tool I would second the recommendation to give it a try... if only to steal some or all of the code for your own use :D. One benefit of the tool is that can merge the VST activity from your production database with the dbanalys to make sure you focus on the high volume tables first.
 

BigSlick

Member
Thanks for the reply and the info, all sounds pretty good to me.

My next question was going to be regarding the blocksize. I've read everywhere that 4K in the size for windows, but i read something of Tom's recently that kind of suggested that it's the case.

I'll happy move to 8K then :) Finding the time for a dump and load may be tricky though. I can see me working over Christmas :(
 

TheMadDBA

Active Member
Such is the life of a DBA lol... I would sort out all of the storage area issues, index rebuilds, etc before switching to 8k.

Depending on your outage window and access to the source code there are quite a few ways to speed up the migration to 8k blocks. Multi threading the dumps and the loads is key to any brute force dump and load.
 

BigSlick

Member
TheMadDBA said:
I would sort out all of the storage area issues, index rebuilds, etc before switching to 8k

Agreed. If I were to do a dump and load, I'd get everything done at once. Make a job of it :)

Am I right in thinking that you can do a multi-threaded dump, but not a load? From what I remember the dump was way slower than a load, but not since the multi-thread.

Cringer said:
I'm going to be doing D&L work over Christmas so we can keep each other entertained! It's certainly more appealing than entertaining the mother-in-law! :eek:

It's a date; I'll provide the virtual candle!
 

TheMadDBA

Active Member
You can multi thread the dumps and the loads. How many threads and which ones should run at the same time depends on your storage area layout and hardware. The general concept is to keep your hardware as busy as possible at all times.

For large tables I turn on threading for the dump or use dumpspecified to thread it myself.

When I multi thread loads I usually don't run multiple threads for the same storage area. Basically one active load process/script per target area at a time.

It requires some testing to get the window down, but usually you sort out the problem tables (huge ones) first and the smaller ones usually don't matter as much.
 

BigSlick

Member
Thanks for the advice, greatly appreciated.

I'll set off a few practice runs in the coming weeks and see how long it takes!
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Looks like SP05.
You would want to be on a minimum of SP07 for the index rebuild improvements; technically, SP06 but the memory-allocation algorithm was tuned for SP07. I also seem to recall a Windows Server-specific memory allocation fix that I think was in SP08. I'll check the SP release notes.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Yes, SP08 added the -freeTF idxbuild parameter which affects how merge buffer memory is freed when idxbuild finishes. It defaults to 0 on 64-bit Windows and 1 on other platforms.

This was bug PSC00256208. More info:
Index rebuild process does not exit after reported as completed on Windows 64-bit
Knowledge Article
 

BigSlick

Member
Thanks Rob, the only issue is that the powers that be want to Upgrade to 11 (time-scales unknown)

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?
 

Cringer

ProgressTalk.com Moderator
Staff member
Yes, moving to 11 is a very good idea. Not least of all meaning you're on a modern version.
11.5 is very good. And there's a service pack for 11.5.1 which is even better.
Whatever the case, 11.2.1 should be avoided in your case as there's a big bug for multi threaded binary dumps.
 
Top