idxbuild failed to create index keys for large index

[FocusIT]

Hi Everyone, hope you all had a good Christmas and New Year.

I dumped and loaded a very large (1.71 billion rows) 9.1D08 database over the holidays and all seemed to go well. No issues encountered at any point in the .lg file or otherwise. Tabanalys checked out between the old and new databases. However, once promoted to the live it has become apparent that some of the larger indexes that were built against the largest table (950 million rows) have not completely built. It is like the idxbuild utility just skipped records past a certain rowid. Luckily the primary unique key index has built and the only indexes affected are the large ones with three or more fields in a compound key. Also, new records added to the table after the dump and load are fine, all index keys present and correct.

Is there a limit idxbuild may have reached which is both un-reported in the .lg file and undocumented in the Progress KB?

The command I used was: -

proutil <dbname> -C idxbuild -TB 24 -TM 32 -threadnum 4

I also created a .srt file with 120GB worth of 2GB sort files + a variable file. OS is Windows 2003 R2 Enterprise Edition.

I am busy filling in the missing index keys with idxfix which seems to be working although its going to be running for days, but I have serious concerns about the upgrade to 10.2B we have for this coming Easter. The main question being, can Progress utilities like idxbuild handle such a large table? Has anyone any ideas about what went wrong, could it be related to 32-bit limitations?

Thanks in advance.

 

[FocusIT]

Missing index keys after IDXBUILD

Hope you had a good Christmas and New Year.


I have a major issue with a database after running IDXBUILD and was wondering if you had seen it before? The database is still on 9.1D08/Windows 2003 R2 server as the upgrade to 10.2B/Windows 2008 is planned for this Easter.

I dumped and loaded the very large database we manage over the Christmas period. No errors were reported in the .lg and tabanalys matched up perfectly between the old and new databases. The newly built database is now live and it has come to light that a few hundred million index keys are missing on the largest table. All the index keys are from large compound indexes (3 or more fields) and only missing against the last 300 or so million records loaded from 956 million. All the records affected were added since the last dump and load which went off without a hitch. Also, some of the smaller indexes on the table including the primary key appears to have built without issue.


The issue very much looks like an undocumented limit of the IDXBUILD facility, something like a maximum addressable index size during sorting. I have trawled the Progress KB and can find no reference to this beyond a number of references stating the cause of the error (1422) is either unknown, a bug in Progress or bad 4GL code. Its just seems like the index build got to a certain point on some indexes and simply said 'no more keys' and went onto the build the next index without reporting an error.


I am now stuck with a live database with some 1.5 billion missing index keys across 5 indexes which has new data in it since the dump and load so cannot be rolled to backup even if I want to. Incidentally, newly added records since the dump and load have inserted with all index keys intact and IDXFIX is successfully finding and back filling the missing keys, albeit very very slowly.


Is there a known limitation of the IDXBUILD facility in Progress 9.1D? If we somehow manage to overcome this latest challenge in 9.1D, can we trust 10.2B to handle a table of this size or will IDXBUILD just lose index keys again.

The command run was: -

proutil <dbname> -C idxbuild -TB 24 -TM 32 -threadnum 4
All
Y
Y

A .srt file was also created with 80 2GB extents and one variable. Disk space was ample for all files and no OS, .lg or command line errors were encountered during the run.

 

[Cringer]

Re: Missing index keys after IDXBUILD

Seems you fell foul of the automatic thread moderation. That's why your posts didn't appear. I've merged both of them as they are slightly different from each other so that nothing is lost.

This isn't something I know about so will leave that to the more intelligent members of the community

 

[TomBascom]

There is no such limit. I have dumped and loaded larger tables and larger indexes without issue.

There might, of course, be a bug. 9.1D is really, really old -- ancient, obsolete and unsupported. But I'm sure that you know that. Why 9.1D08? 9.1E04, while also ancient and obsolete, is at least sort of "supported" and has every bug fix that v9 will ever get. If you're going to be running v9 you should be running 9.1E04.

I'm not sure why you would have concerns about 10.2B because of this. If anything this would be a very good reason to accelerate that upgrade.

BTW -- you should be planning on upgrading to 64 bit 10.2B (the OS should also be a 64 bit OS). It seems obvious but since you are still on 9.1D it may not be.

 

[FocusIT]

Yes I know its old and obsolete, but like many on this forum the end users were tied in by the original ISV for a considerable length of time. This tie has only just been resolved and the plan is to upgrade to X64 10.2B on X64 Windows 2008 R2 at Easter this year.

My concern is that the issues were unreported in the .lg and undetectable in the database until it was subjected to operational load by users, at which point its essentially too late. There seems to be some undocumented tipping point in data volume that meant IDXBUILD simply decided not to create index keys past a certain point. The point it stopped appears different for each index based on its size (number and type of fields) with smaller indexes building for all records. Unless this is documented somewhere as a known issue then I am afraid there will be serious loss of trust in the Progress RDBMS from the end users regardless of version. Which would be a real shame as it has served them extremely well until this point including many successful dump and load cycles. It is just my humble opinion, but surely any RDBMS technology should inform the administrator that records or in this case index keys are missing, if a genuine bug prevents such notification then there should be a great big warning sign in the KB telling admins to avoid a particular version. The end users are quite rightly going to ask me if this will happen again in later versions of Progress or if there are any other gotchas, and at the moment I really don't know. Is there a KB article referencing such a bug?