Online Index Build and HDR
Andrew Ford, on 11.50.FC3 with HDR, asked about online index builds lasting over 3 hours on a busy table, and about LOG_INDEX_BUILDS and ONLIDX_MAXMEM. Davorin Kremenjas recalled needing LOG_INDEX_BUILDS enabled, because HDR index send threads held the partition open and a second build failed on non-exclusive access. Andrew had worked around this with SET LOCK MODE TO WAIT. No definitive recommendation was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
11.50.FC3 running HDR I am looking at using the online index build feature for the first time in a production environment running HDR for one of my largest and most active tables. Does anyone have any experience with this feature and index builds that can last more than 3 hours in an HDR environment? Are there any suggestions (other than the normal performance things that can speed up an index build, PDQ, PSORT_NPROCS, PSORT_DBTEMP, etc) to make this as painless as possible or are there and caveats that I should know about? Any suggestions on setting the LOG_INDEX_BUILDS ONCONFIG parameter in an HDR environment and online index builds or the ONLIDX_MAXMEM ONCONFIG parameter? The table will be very active during the index build with lots of selects and lots of updates (but not to the columns I am building indexes on) but there will be very few inserts and no deletes. If I do go ahead with this, I'll document my experience at informix-dba.blogspot.com if you're interested in how it turns out. Thanks, Andrew
> Any suggestions on setting the LOG_INDEX_BUILDS ONCONFIG parameter in an HDR > environment and online index builds or the ONLIDX_MAXMEM ONCONFIG parameter? If I remember correctly from few months ago on our test system (in HDR) we had to set LOG_INDEX_BUILDS to enabled to have everything done smoothly. The reason was HDR index send threads (dr_idx_send) were holding the table partition open until the index shipping was completed. So if we had a second online index build immediately afterwards it would fail with non-exclusive access to the table. With index logging enabled this was not the case. Please test, my memory could be a bit rusty. HTH Davorin
>> Any suggestions on setting the LOG_INDEX_BUILDS ONCONFIG parameter in an >> HDR >> environment and online index builds or the ONLIDX_MAXMEM ONCONFIG >> parameter? > > If I remember correctly from few months ago on our test system (in > HDR) we had to set LOG_INDEX_BUILDS to enabled to have everything done > smoothly. The reason was HDR index send threads (dr_idx_send) were > holding the table partition open until the index shipping was > completed. So if we had a second online index build immediately > afterwards it would fail with non-exclusive access to the table. > With index logging enabled this was not the case. Please test, my > memory could be a bit rusty. > > HTH > > Davorin I think we've run into that even without online index builds, a work around for us without setting LOG_INDEX_BUILDS to 1 was to set lock mode to wait for the session building the indexes. Thanks, Andrew
> I think we've run into that even without online index builds, a work around > for us without setting LOG_INDEX_BUILDS to 1 was to set lock mode to wait > for the session building the indexes. Sure, we just hoped that ONLINE will make it go away which it didn't. I didn't expected "set lock mode to wait" to solve it since it wasn't a lock issue, but rather a non-exclusive access, thanks for the tip. Cheers Davorin