Very slow index generation.
Hi all.
This weekend I have reorganized my database.
I have changed slow disks (SCSII-2 of 2Gb) for new disks (FW-
SCSII of 8.5 Gb)
My instance of Informix has a size, of data, of around some 14Gb.
To be able to make these operations I created an identical instance
in another machine, I reorganized the new instance and pass over
the data from it schemes it of backup toward the new one. With the
new configuration I have a dbspace for rootdbs, one for the
logicalslogs, another for the physicallog, those of data (4) and one
for the catalogs of the databases (create database in...). The load
of data has been carried out with the database in NON LOG.
Until here all well. The problem arises in the moment to regenerate
the indexes (this operation is already carrying out it with the data
loaded). The time of regeneration of the indexes is enormous
almost consuming all the resources of the CPUs of the system.
To regenerate the indexes I have followed the advice of Art
,PSORT_NPROCS=6, PSORT_DBTEMP=<3 filesystems> and the
PDQPRIORITY=50, but I have not noticed any yield improvement.
To generate the primary keys and the indexes I have used two
scripts with the following syntax:
database perico << EOF
alter table table_a add contraint primary key (col_1,.. col_n)constraint pk_table_a;
create index i1_table_a (col_1,.. col_n);.
.
EOF
Working environment:
Platform: Sun SS1000E with 4 cpu's and 524288K mem
OS, Solaris 2.4 with last ones patches.
Informix: IDS 7.24.UC7-1
Onconfig:
# System Configuration
SERVERNUM 0
DBSERVERNAME perico
DBSERVERALIASES perico0,perico1
DEADLOCK_TIMEOUT 60
RESIDENT 1
MULTIPROCESSOR 1
NUMCPUVPS 3
SINGLE_CPU_VP 0
NOAGE 1
AFF_SPROC 1
AFF_NPROCS 3
And now the question:
Because the generation of indexes is so slow?
Thanks in advance.
---------------------------------------
Isidre PONS ROCA
BASE - Gesti' d'Ingressos Locals
(Diputacio de Tarragona)
Servei de Sistemes de Informacio
Av President Lluis Companys 12-C
43005 - Tarragona
SPAIN
Tel # +34 977 236731
Fax # +34 977 227302
http://www.altanet.org
ipons@dtgna.altanet.org
---------------------------------------
Questions to get the "dumb" stuff out of the way first:
o How is the disk farm layed out? Singleton drives? RAID 0,1,2,3,4,5,10?
o Are there multiple dbspaces on a logical drive? Are the data chunks on
different drives/arrays from the root and logs?
o Where are the indexes being created? Same dbspace or detached?
o Are the separate filesystems for PSORT_DBTEMP actually on different sets
of spindles or just different devices cut from the same array?
o Are you sure that the PSORT_ variables have been exported?
o Are you using RAW or COOKED chunks? If COOKED you need more AIO VPS.
o How many LRUS & CLEANERS have you configured?
Art S. Kagel
"Pons Roca, Isidre" wrote:
>
> Hi all.
>
> This weekend I have reorganized my database.
>
> I have changed slow disks (SCSII-2 of 2Gb) for new disks (FW-
> SCSII of 8.5 Gb)
> My instance of Informix has a size, of data, of around some 14Gb.
>
> To be able to make these operations I created an identical instance
> in another machine, I reorganized the new instance and pass over
> the data from it schemes it of backup toward the new one. With the
> new configuration I have a dbspace for rootdbs, one for the
> logicalslogs, another for the physicallog, those of data (4) and one
> for the catalogs of the databases (create database in...). The load
> of data has been carried out with the database in NON LOG.
>
> Until here all well. The problem arises in the moment to regenerate
> the indexes (this operation is already carrying out it with the data
> loaded). The time of regeneration of the indexes is enormous
> almost consuming all the resources of the CPUs of the system.
>
> To regenerate the indexes I have followed the advice of Art
> ,PSORT_NPROCS=6, PSORT_DBTEMP=<3 filesystems> and the
> PDQPRIORITY=50, but I have not noticed any yield improvement.
>
> To generate the primary keys and the indexes I have used two
> scripts with the following syntax:
>
> database perico << EOF
> alter table table_a add contraint primary key (col_1,.. col_n)> constraint pk_table_a;
> create index i1_table_a (col_1,.. col_n);> .
> .
> EOF
>
>
> Working environment:
> Platform: Sun SS1000E with 4 cpu's and 524288K mem
> OS, Solaris 2.4 with last ones patches.
> Informix: IDS 7.24.UC7-1
> Onconfig:
> # System Configuration
>
> SERVERNUM 0
> DBSERVERNAME perico
> DBSERVERALIASES perico0,perico1
> DEADLOCK_TIMEOUT 60
> RESIDENT 1
> MULTIPROCESSOR 1
> NUMCPUVPS 3
> SINGLE_CPU_VP 0
> NOAGE 1
> AFF_SPROC 1
> AFF_NPROCS 3>
> And now the question:
> Because the generation of indexes is so slow?
>
> Thanks in advance.
>
> ---------------------------------------
> Isidre PONS ROCA
> BASE - Gestió d'Ingressos Locals
> (Diputacio de Tarragona)
> Servei de Sistemes de Informacio
> Av President Lluis Companys 12-C
> 43005 - Tarragona
> SPAIN
> Tel # +34 977 236731
> Fax # +34 977 227302
> http://www.altanet.org
> ipons@dtgna.altanet.org
> ---------------------------------------
In article <7v1030$qc8$1@news.xmission.com>, Pons Roca, Isidre
<ipons@dtgna.altanet.org> writes
>
>Hi all.
>
>This weekend I have reorganized my database.
>
>I have changed slow disks (SCSII-2 of 2Gb) for new disks (FW-
>SCSII of 8.5 Gb)
>My instance of Informix has a size, of data, of around some 14Gb.
>
>To be able to make these operations I created an identical instance
>in another machine, I reorganized the new instance and pass over
>the data from it schemes it of backup toward the new one. With the
>new configuration I have a dbspace for rootdbs, one for the
>logicalslogs, another for the physicallog, those of data (4) and one
>for the catalogs of the databases (create database in...). The load
>of data has been carried out with the database in NON LOG.
>
>Until here all well. The problem arises in the moment to regenerate
>the indexes (this operation is already carrying out it with the data
>loaded). The time of regeneration of the indexes is enormous
>almost consuming all the resources of the CPUs of the system.
>
>To regenerate the indexes I have followed the advice of Art
>,PSORT_NPROCS=6, PSORT_DBTEMP=<3 filesystems> and the
>PDQPRIORITY=50, but I have not noticed any yield improvement.
>
PDQPRIORITY=100
PSORT_MAXALLOC=10240
>
>To generate the primary keys and the indexes I have used two
>scripts with the following syntax:
>
>database perico << EOF
>alter table table_a add contraint primary key (col_1,.. col_n)>constraint pk_table_a;
>create index i1_table_a (col_1,.. col_n);>.
>.
>EOF
>
>
>Working environment:
> Platform: Sun SS1000E with 4 cpu's and 524288K mem
> OS, Solaris 2.4 with last ones patches.
> Informix: IDS 7.24.UC7-1
>Onconfig:
># System Configuration
>
>SERVERNUM 0
>DBSERVERNAME perico
>DBSERVERALIASES perico0,perico1
>DEADLOCK_TIMEOUT 60
>RESIDENT 1
>MULTIPROCESSOR 1
>NUMCPUVPS 3
>SINGLE_CPU_VP 0
>NOAGE 1
>AFF_SPROC 1
>AFF_NPROCS 3>
>
>And now the question:
>Because the generation of indexes is so slow?
>
>Thanks in advance.
>
>---------------------------------------
> Isidre PONS ROCA
> BASE - Gestió d'Ingressos Locals
> (Diputacio de Tarragona)
> Servei de Sistemes de Informacio
> Av President Lluis Companys 12-C
> 43005 - Tarragona
> SPAIN
> Tel # +34 977 236731
> Fax # +34 977 227302
> http://www.altanet.org
> ipons@dtgna.altanet.org
>---------------------------------------
--
David Williams
David Williams wrote: [SNIP] > PSORT_MAXALLOC=10240 [SNIP] This environment variable is no longer looked at by the IDS engine kernel. It was effective in OL5.xx through early IDS 7.1x releases. The resource that this once controlled is now managed by the engine dynamically. As the person who first mentioned it I thought I should update everyone. Art S. Kagel