Very Computer

by **rbosci** » Sun, 31 Dec 1899 09:00:00

Using HPL deluxe mode --
At what point do you recreate your indexes?
I am trying to unload, make table changes needed , & load the
data back in. I am not sure when the indexes need to be
created. Do I create them in my query language when I am
creating the new table (after the old one is dropped)?
I've used Express mode and always have problems with my indexes
not creating properly or the HPL* up. I don't want to do
this incorrectly....
Or is deluxe the way to go???
Thanks

Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.veryComputer.com/

by **Sanka** » Sun, 31 Dec 1899 09:00:00

Hello,

HPL-Deluxe and HPL-Express has it's own advantages and
disadvantages.

In both type, you can create index before the data
load or after the data load. Load data first and
create index after would be the faster way to build
the table. But if you want to keep the constraints on
(for validations) while the data load, HPL-DELUXE
method is the way. It can create a rejected records
log file. No doubt Express is much faster, because it
does not use transaction logging and keeps an
Exclusive lock on a table. You need a Fake backup or
Zero level backup after the Express load. All depends
on your data. If it's a clean data or you don't have
to do validations and if you got to load huge data
then use Express mode and build indexes later by
setting PDQPRIORITY and PSORT_NPROCS at the best your
environment supports.

Ganesh.

Quote:> Using HPL deluxe mode --
> At what point do you recreate your indexes?
> I am trying to unload, make table changes needed , &
> load the
> data back in. I am not sure when the indexes need
> to be
> created. Do I create them in my query language when
> I am
> creating the new table (after the old one is
> dropped)?
> I've used Express mode and always have problems with
> my indexes
> not creating properly or the HPL* up. I
> don't want to do
> this incorrectly....
> Or is deluxe the way to go???
> Thanks

> Got questions? Get answers over the phone at
> Keen.com.
> Up to 100 minutes free!
> http://www.veryComputer.com/

__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://www.veryComputer.com/

by **Art S. Kage** » Sun, 31 Dec 1899 09:00:00

What table changes do you need to make that require you to unload and
reload all that data? I'd have to think REALLY hard to find some
modification to a table that I could not get Informix to perform online
and usually in-place at that!

Art S. Kagel

> Using HPL deluxe mode --
> At what point do you recreate your indexes?
> I am trying to unload, make table changes needed , & load the
> data back in. I am not sure when the indexes need to be
> created. Do I create them in my query language when I am
> creating the new table (after the old one is dropped)?
> I've used Express mode and always have problems with my indexes
> not creating properly or the HPL* up. I don't want to do
> this incorrectly....
> Or is deluxe the way to go???
> Thanks

> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.veryComputer.com/

by **rbosci** » Sun, 31 Dec 1899 09:00:00

The extents were set incorrectly and we ran out (error code
136). When we moved the data out and recreated the table (last
week) Informix suggested we set extents at 24 (which was WAY
incorrect & in my non-wisdom I did not know better at the time)
(I sure do now)
so we ran it up again. Moved the data out and in again using
HPL one more time.

Our table is 100,000,000 records.

Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com

by **rbosci** » Sun, 31 Dec 1899 09:00:00

I have never used HPL before last week. Last week we used
Express Mode and our data locked up during the indexes.
Informix suggests building indexes after the load which we
tried this week. The data unload/load took 1.5 hours. The
indexes took 13+ hours and only 4 of the 6 are done. Obviously
with users not able to access the data for that long of a time,
I don't want to go that route again....

So I think I will try Deluxe Mode next time at least for this
table...

Since I still have 2 more indexes to go and my table has been
locked forever, could you tell me more about PDQPRIORITY and
PSORT_NPROCS?
We are running 7.31 UC5 on Sun Solaris 7 E10K machine

Thanks
Robin

Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com

by **Obnoxio The Clow** » Sun, 31 Dec 1899 09:00:00

Quote:
>I have never used HPL before last week. Last week we used
>Express Mode and our data locked up during the indexes.
>Informix suggests building indexes after the load which we
>tried this week. The data unload/load took 1.5 hours. The
>indexes took 13+ hours and only 4 of the 6 are done. Obviously
>with users not able to access the data for that long of a time,
>I don't want to go that route again....

Tsk, tsk. :-)

Quote:>So I think I will try Deluxe Mode next time at least for this
>table...

Puck, puck, puck-uuuuck!! Wussy. :-)

Quote:>Since I still have 2 more indexes to go and my table has been
>locked forever, could you tell me more about PDQPRIORITY and
>PSORT_NPROCS?

For your remaining indexes:

PDQPRIORITY=100
PSORT_NPROCS=40
PSORT_DBTEMP=/directory1:/directory2:/directory3...

(At least 3 file system directories, on separate spindles)

Then get out of the way. :-)

Quote:>We are running 7.31 UC5 on Sun Solaris 7 E10K machine

Nice....
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

by **Martin, Wayne E** » Sun, 31 Dec 1899 09:00:00

Set PSORT_NPROC = number of cpuvp's
SET PSORT_DBTEMP = fast disk and empty directory can use multiple separator
is ":"
Set PDQPRIORITY = 1;

Try that on Index builds.

This will start PDQ on the reading and sorting on index builds.

Wayne E. Martin
Informix Database Administrator
Kmart Corp.

-----Original Message-----

Sent: Wednesday, June 28, 2000 12:31 PM

Subject: Re: HPL Deluxe Mode

I have never used HPL before last week. Last week we used
Express Mode and our data locked up during the indexes.
Informix suggests building indexes after the load which we
tried this week. The data unload/load took 1.5 hours. The
indexes took 13+ hours and only 4 of the 6 are done. Obviously
with users not able to access the data for that long of a time,
I don't want to go that route again....

So I think I will try Deluxe Mode next time at least for this
table...

Since I still have 2 more indexes to go and my table has been
locked forever, could you tell me more about PDQPRIORITY and
PSORT_NPROCS?
We are running 7.31 UC5 on Sun Solaris 7 E10K machine

Thanks
Robin

Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com

by **Mark D. Stoc** » Sun, 31 Dec 1899 09:00:00

> Set PSORT_NPROC = number of cpuvp's

Set this much higher, but make sure you have lots of virtual memory. I
once brought a box to it's knees when we eventually reached a value that
used all memory. We then had to watch while the machine swapped out 1 Gb
of database memory. It was NOT a pretty sight! :-)

Quote:> SET PSORT_DBTEMP = fast disk and empty directory can use multiple separator
> is ":"
> Set PDQPRIORITY = 1;

Set this to 100, unless you are trying to do other things at the same
time. If you are, delay them until after the index build. ;-)

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+

by **David William** » Sun, 31 Dec 1899 09:00:00

Quote:>I have never used HPL before last week. Last week we used
>Express Mode and our data locked up during the indexes.
>Informix suggests building indexes after the load which we
>tried this week. The data unload/load took 1.5 hours. The
>indexes took 13+ hours and only 4 of the 6 are done. Obviously
>with users not able to access the data for that long of a time,
>I don't want to go that route again....

>So I think I will try Deluxe Mode next time at least for this
>table...

>Since I still have 2 more indexes to go and my table has been
>locked forever, could you tell me more about PDQPRIORITY and
>PSORT_NPROCS?
>We are running 7.31 UC5 on Sun Solaris 7 E10K machine

Drool...how much memory, how many/what CPUS?
What is the disk layout (number of disks/size of disks/disk to
controller mapping/RAID levels/mirroring)?

With 100 million rows how is the table fragmented?
How are you using temporary dbspaces/ PSORT_DBTEMP?
Allocate one temp space per drive.

Go with PDQPRIORITY=100 which Art and Mark D. Stock have suggested.

Under Solaris 2.5.1/2.6 with a Ultrasparc 1 170Mhz and Various E450's
I have seen improvements by increasing PSORT_NPROCS > number of
physical cpus. PSORT_NPROCS= 2 x number of physical cpus is as far
as I have tried to go and it scaled almost linearly!!

Quote:>Thanks
>Robin

>Got questions? Get answers over the phone at Keen.com.
>Up to 100 minutes free!
>http://www.keen.com

--
David Williams

by **Martin, Wayne E** » Sun, 31 Dec 1899 09:00:00

Personally I stand by my setting and we have H.P. 7 N class
( 18 when we finish) servers running 7X24 in our
warehouses and have had no problems.

But I do agree - testing in your env. will be needed.

Wayne

-----Original Message-----

Sent: Wednesday, June 28, 2000 4:56 PM
To: Martin, Wayne E.

Subject: Re: HPL Deluxe Mode

> Set PSORT_NPROC = number of cpuvp's

Set this much higher, but make sure you have lots of virtual memory. I
once brought a box to it's knees when we eventually reached a value that
used all memory. We then had to watch while the machine swapped out 1 Gb
of database memory. It was NOT a pretty sight! :-)

> SET PSORT_DBTEMP = fast disk and empty directory can use multiple
separator
> is ":"
> Set PDQPRIORITY = 1;

Set this to 100, unless you are trying to do other things at the same
time. If you are, delay them until after the index build. ;-)

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+

by **Mark D. Stoc** » Sun, 31 Dec 1899 09:00:00

> Personally I stand by my setting and we have H.P. 7 N class
> ( 18 when we finish) servers running 7X24 in our
> warehouses and have had no problems.

Oh I wasn't implying your suggestions would cause any problems. However,
with PDQPRIORITY set to 1, you are only performing parallel scans, which
doesn't help a load. :-)

Of course, on a busy system this may be desirable, because it uses less
resources.

Quote:> But I do agree - testing in your env. will be needed.

Indeed.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+

by **Art S. Kage** » Sun, 31 Dec 1899 09:00:00

> The extents were set incorrectly and we ran out (error code
> 136). When we moved the data out and recreated the table (last
> week) Informix suggested we set extents at 24 (which was WAY
> incorrect & in my non-wisdom I did not know better at the time)
> (I sure do now)
> so we ran it up again. Moved the data out and in again using
> HPL one more time.

> Our table is 100,000,000 records.

So I guess you do not have enough logical log space to use

ALTER FRAGMENT ON TABLE tabname INIT IN dbspace;

Which is the fastest way to reorganize a table. That would do it.

Art S. Kagel

by **Bosci** » Sun, 31 Dec 1899 09:00:00

I tried that once too and it took 12 hours and my table was only 70,000,000
at the time. We are a 7x24 operation and these tables must be able to be
written to and accessed continuously or at least down for a very minimal
amount of time.
Robin

> Organization: Bloomberg LP

> Newsgroups: comp.databases.informix
> Date: Fri, 30 Jun 2000 11:11:37 -0400

> Subject: Re: HPL Deluxe Mode

>> The extents were set incorrectly and we ran out (error code
>> 136). When we moved the data out and recreated the table (last
>> week) Informix suggested we set extents at 24 (which was WAY
>> incorrect & in my non-wisdom I did not know better at the time)
>> (I sure do now)
>> so we ran it up again. Moved the data out and in again using
>> HPL one more time.

>> Our table is 100,000,000 records.

> So I guess you do not have enough logical log space to use

> ALTER FRAGMENT ON TABLE tabname INIT IN dbspace;

> Which is the fastest way to reorganize a table. That would do it.

> Art S. Kagel