Building Indexs on Primary Server

Informix 11: We have HDR servers, I am seeing that creating an index on table of 100M rows takes more than 16hrs. Could it be because of HDR secondary server trying to build the index at the same time? We have PDQ priority set to 100 and PSORT_NPROCS set to 4

On Feb 4, 6:30 pm, Mo <mohitanch...@gmail.com> wrote: > Informix 11: > > We have HDR servers, I am seeing that creating an index on table of > 100M rows takes more than 16hrs. Could it be because of HDR secondary > server trying to build the index at the same time? We have PDQ > priority set to 100 and PSORT_NPROCS set to 4 How long does it take without HDR ? How large is your decision support memory (you can increase this thru 'onmode -M <kb>' without bouncing the engine) ? Do you build the Index with the 'create index....ONLINE' keyword ? What is your setting of LOG_INDEX_BUILDS ? If LOG_INDEX_BUILDS is not set, the index pages will be transfered to the secondary after the "create index" transaction on the primary commits. The index will not be rebuild on the secondary, just the index page are transfered from the primary to the secondary. During the shipping of the index, a share lock is placed on the index by the dr_idx_send thread. You can avoid this locking behaviour, if you use the 'ONLINE' keyword. In that case no lock will be placed on the table (only an intent-share) during the creation as well as the transfer to the secondary. If LOG_INDEX_BUILDS is set to 1, the building of the index is logged on the primary which generates a lot of log i/o and those log records are shipped to the secondary and re-applied there. The advantage is that the index is available approx. at the same same on the primary and the secondary. Without LOG_INDEX_BUILDS it will be available on the secondary after the index transfer has completed. Just to give you a clue: on an older 4-cpu box I'm able to build an index in a HDR environment without LOG_INDEX_BUILDS in about 30 minutes on two integer columns on a 200M row table. HTH.