binary load with idxbuild

[MikeHorn]

Hello gurus.

I have a 94+ GB database that I am dump and loading onto a new server. I can d&l the vast majority of tables within an 18 hour time frame and am proposing to d&l the two or three largest history tables off-line, using some 4GL to load the last week or so just prior to go-live.

One such history table crashed on loading, the error indicating that I'd exceeded a 2GB limit. I resolved this by adding another 10 volumes in the multi-volume sort file. However, the load now seems to be taking a significantly longer time.

I may be imagining things, but does anybody else have experience of this? I will try the load and idxbuild separately - but don't hold your breath, this could take a few more days.

TIA
Mike

 

[ron]

Hello, mate!

We're rather war-torn from the experience of D&L with a 290GB DB where there is a small number of "huge" tables.

You'll have to provide a few more details about your environment. What servers? What Progress? And especially -- what disc config? Are you using FC or SCSI? If SCSI, is it "fast"? (160 or 320?) Are you using RAID? What kind?

If you're using Solaris, you can (effectively) put the srt files in memory, which will speed-up idxbuild by a big margin.

Ron.

 

[MikeHorn]

Yes, I was a little light on details wasn't I?

64 bit 10.1A02 AIX 5.3
IBM p550, 4 processors, 16GB RAM, DB split over 4 filesystems, AI & BI also have their own filesystems. Type I & Type II storage areas in use.

nmon reports system 75% idle, promon reports 99% buffer hits. No major paging to report.

I have arrived in work this morning to find that one of my build scripts is STILL running (I created one script per storage area, so one script serially loads multiple tables)

My conclusion is that creating too many srt files has slowed my load/build down, my main question is why would this be?

I share the same srt file for all loads, should I look at having a sort file per storage area? I can't see that making a difference but I am clutching at straws now.

Thanks
Mike

 

[MikeHorn]

and to answer your other questions Ron

Using fibre channel connection to a SAN for disk storage, RAID 1+0

 

[ron]

Hello, Mike.

You're using 10.1 - we're still using 9.1D. However, I think what I say is still relevant (enough).

Our last D&L was last Christmas. I tried loading/indexing in multiple streams -- but had to crash it and re-start with our old scripts that did all the loading and indexing in one big serial stream. The reason was simply that it was so slow I just couldn't believe. I didn't have an opportunity to do any reasearch, but there is definately some kind of performance issue with multiple streams.

Tom Bascomb, however, has developed a sophisticated process to do parallel dumps, loads and indexes very quickly - so it must be possible!

I notice that you have 16GB of RAM. It's a while since I've used AIX so I don't know how you use memory "as a disc" in AIX. In Solaris the /tmp directory is automatically memory. We put our srt files in /tmp and it speeds-up the idxbuild by a dazzling factor. I recommend that you see if you can do something similar in AIX. There must be a way.

Otherwise, I don't think I can help you much.

Ron.

 

[TomBascom]

Yes, I was a little light on details wasn't I?

64 bit 10.1A02 AIX 5.3
IBM p550, 4 processors, 16GB RAM,

That sounds like a machine that should be capable of getting the work done.

DB split over 4 filesystems,

I do not recommend this practice. People tend to do it in an attempt to balance IO but, IMHO, it does not work. And it wastes RAM too.

What sort of filesystems?

AI & BI also have their own filesystems.

Ok.

Type I & Type II storage areas in use.

Why aren't they all type II?

How many? And what scheme have you used to assign objects to areas and area types?

nmon reports system 75% idle, promon reports 99% buffer hits. No major paging to report.

Does nmon report any disk bottlenecks?

I have arrived in work this morning to find that one of my build scripts is STILL running (I created one script per storage area, so one script serially loads multiple tables)

I understand the attraction but it may not be necessary -- especially if this is a type II area.

My conclusion is that creating too many srt files has slowed my load/build down, my main question is why would this be?

I doubt that the number of sort files has anything to do with it.

The location of the sort files may though. Are they on the same disks as the data being loaded?

Are you aware that there have been significant multi-threading capabilities added to the OE10 binary dump & load and index rebuild?

What parameters are you using for the various parts of this process (the dump, the load and the index rebuild...)

Is there a db server up and running when you load? Is it running with -i?

I share the same srt file for all loads, should I look at having a sort file per storage area? I can't see that making a difference but I am clutching at straws now.

The sort file is used by progress to seed the names of the srt files that it creates. You don't need to do anything different there.

You might want to peruse this:

Highly Parallel Dump & Load

 

[MikeHorn]

Thanks for the reply Tom.

It looks like we've found the culprit now though. We've increased the MINPERM/MAXPERM kernel parameters to use up to 80% of real memory for file handling. We are mainly an Oracle shop and this apparently crucifies Oracle DBs so the default server build is 30%.

In reply to your questions:- Many of our static tables have a small record size and low record count, these tables have been allocated to Type I. The largest tables have separate Type II SAs of their own for data and indexes, the majority of tables are spread over Type II SAs.

14 storage areas in total (including schema, AI & BI areas). Multiple dump/load scripts in use because we only have 24 hours in which to achieve the D&L.

We're using FC disk on a SAN.

All dump scripts use -TM 24 -TB 32 and -i.

My question on sort files arose because I was running out of ideas and beginning to clutch at straws. I have a copy of Highly Parallel Dump & Load on my desk!

thanks again
Mike