Re: Building Indexs on Primary Server
On Feb 5, 10:19 pm, Mo <mohitanch...@gmail.com> wrote:
> 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 Kbytes> Index page logging status: Disabled
The problem is that you are creating an indexed based on an UDR:
'my_upper()'.
Unfortunately IDS doesn't seem to parallelize such an index build,
even if your
table would be fragmented. BTW, this might be a good idea for a 100M
row table.
So I believe that the long time it takes to build the index has
nothing to do
with HDR. I guess that it takes approx. the same time if HDR would be
disabled
I assume that you wrote your UDR in normal IDS stored procedure
language.
You might think of implementing that UDR in C as a shared object. This
might be faster and their are options to parallelize such a C UDR.
Take a look
at the documentation:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.udr.doc/sii-13perf-66466.htm
HTH.
.
On Feb 6, 1:55 am, e...@herber-consulting.de wrote:
> On Feb 5, 10:19 pm, Mo <mohitanch...@gmail.com> wrote:
>
>
>
>
>
> > 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 settingNOSORTINDEXto 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
> > exportNOSORTINDEX=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 Kbytes> > Index page logging status: Disabled
>
> The problem is that you are creating an indexed based on an UDR:
> 'my_upper()'.
> Unfortunately IDS doesn't seem to parallelize such an index build,
> even if your
> table would be fragmented. BTW, this might be a good idea for a 100M
> row table.
>
> So I believe that the long time it takes to build the index has
> nothing to do
> with HDR. I guess that it takes approx. the same time if HDR would be
> disabled
>
> I assume that you wrote your UDR in normal IDS stored procedure
> language.
> You might think of implementing that UDR in C as a shared object. This
> might be faster and their are options to parallelize such a C UDR.
> Take a look
> at the documentation:
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic...
>
> HTH.
>
> .- Hide quoted text -
>
> - Show quoted text -
This is the function
create function my_upper( s varchar(64) ) returning varchar(64) with
(not variant) ; return upper(s) ;
end function;
On Feb 6, 4:44 pm, Mo <mohitanch...@gmail.com> wrote:
> On Feb 6, 1:55 am, e...@herber-consulting.de wrote:
>
>
>
> > On Feb 5, 10:19 pm, Mo <mohitanch...@gmail.com> wrote:
>
> > > 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 settingNOSORTINDEXto 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
> > > exportNOSORTINDEX=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 Kbytes> > > Index page logging status: Disabled
>
> > The problem is that you are creating an indexed based on an UDR:
> > 'my_upper()'.
> > Unfortunately IDS doesn't seem to parallelize such an index build,
> > even if your
> > table would be fragmented. BTW, this might be a good idea for a 100M
> > row table.
>
> > So I believe that the long time it takes to build the index has
> > nothing to do
> > with HDR. I guess that it takes approx. the same time if HDR would be
> > disabled
>
> > I assume that you wrote your UDR in normal IDS stored procedure
> > language.
> > You might think of implementing that UDR in C as a shared object. This
> > might be faster and their are options to parallelize such a C UDR.
> > Take a look
> > at the documentation:
>
> >http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic...
>
> > HTH.
>
> > .- Hide quoted text -
>
> > - Show quoted text -
>
> This is the function
> create function my_upper( s varchar(64) ) returning varchar(64) with
> (not variant) ;> return upper(s) ;
> end function;
It is an SPL function. I believe that it will be significantly faster
if you program it as a C UDR instead of SPL.
You need to something like this. Warning, this is only an example and
probably
not perfect:
#include <mi.h>
#include <ctype.h>
mi_lvarchar *eh_upper(str)
mi_lvarchar *str;
{
char *v_ptr, one_char;
mi_lvarchar *lvarch_out;
mi_integer i, v_length;
lvarch_out = mi_var_copy(str);
v_ptr = mi_get_vardata(lvarch_out);
v_length = mi_get_varlen(lvarch_out);
for ( i=0; i < v_length; i++ )
{
one_char = v_ptr[i];
if ( islower(one_char) )
v_ptr[i] = toupper(one_char);
}
return (lvarch_out);
}
Then you need to compile that C function into a shared library:
gcc -fPIC -DMI_SERVBUILD -I$INFORMIXDIR/incl/public -I$INFORMIXDIR/
incl -L$INFORMIXDIR/esql/lib -c eh_upper.c
gcc -shared -Wl,-soname,eh_upper.so.1 -o eh_upper.so.1.0 eh_upper.o
Depending on your platform, you might need to use other compiler
flags.
Finally you register that as a new UDR:
CREATE FUNCTION eh_upper(arg varchar(255))
RETURNING varchar(26)
WITH (NOT VARIANT, PARALLELIZABLE)EXTERNAL NAME "/home/inf
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g