Re: Building Indexs on Primary Server

On Feb 4, 10:36 am, e...@herber-consulting.de wrote: > 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. When I try with ONLINE I get: 212: Cannot add index. 21522: No online index build possible Error in line 2Near character position 39 I also tried setting NOSORTINDEX to both 0 and 1 but it didn't work.

On Feb 5, 7:20 pm, Mo <mohitanch...@gmail.com> wrote: > On Feb 4, 10:36 am, e...@herber-consulting.de wrote: > > > > > 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. > > When I try with ONLINE I get: > >   212: Cannot add index.> > 21522: No online index build possible > Error in line 2> Near character position 39 > > I also tried setting NOSORTINDEX to both 0 and 1 but it didn't work. Please post the following information: 1) Exact version of IDS: $INFORMIXDIR/bin/oninit -version 2) dbschema from the table where you want to create your index on: dbschema -d <db> -t <table> -ss 3) create index statement 4) onstat -g ipl 5) onstat -g mgm Thanks.

On Feb 5, 12:15 pm, e...@herber-consulting.de wrote: > On Feb 5, 7:20 pm, Mo <mohitanch...@gmail.com> wrote: > > > > > > > On Feb 4, 10:36 am, e...@herber-consulting.de wrote: > > > > 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. > > > When I try with ONLINE I get: > > >   212: Cannot add index.> > > 21522: No online index build possible > > Error in line 2> > Near character position 39 > > > I also tried setting NOSORTINDEX to both 0 and 1 but it didn't work. > > Please post the following information: > > 1) Exact version of IDS:  $INFORMIXDIR/bin/oninit -version > > 2) dbschema from the table where you want to create your index on: >     dbschema -d <db> -t <table> -ss> > 3) create index statement > > 4) onstat -g ipl > > 5) onstat -g mgm > > Thanks.- Hide quoted text - > > - Show quoted text - 1)Program Name: oninit Build Version: 11.50.FC2X2 Build Number: N129 Build Host: pothose Build OS: HP-UX B.11.23 Build Date: Thu Sep 25 22:15:58 CDT 2008 GLS Version: glslib-4.50.FC3 2) { TABLE "phoenix".tpi row size = 145 number of columns = 5 index size = 0 } create table "phoenix".tpi ( first_name varchar(20) not null constraint "phoenix".nnc_tpp_fname1, last_name varchar(20) not null constraint "phoenix".nnc_tpp_lname1, street varchar(40), city varchar(20), company_name varchar(40) ) extent size 1999994 next size 1999994 lock mode row; revoke all on "phoenix".tpi from "public" as "phoenix"; 3) #export PSORT_NPROCS=4 export NOSORTINDEX=1 #onmode -wm=0 dbaccess $TAXDB << EOT --SET PDQPRIORITY 100; update statistics for function my_upper; --drop index ix_tpp_name; create index ix_tpp_name on taxpayer_pri(my_upper(last_name)) using btree in $SPACE1 ONLINE; EOT 4) IBM Informix Dynamic Server Version 11.50.FC2X2 -- On-Line (Prim) -- Up 20:01:11 -- 27830624 KbytesIndex page logging status: Disabled