Dear all:
I intend to copy large data from ids7.3 to ids10 .
The data size about 40 GB includes several hundred tables.
I try my best to copy it fast as I can such that
- use INSERT .. SELECT command to copy data
- create index after copy data
- separate into several process running concurrently.
- set database unlogged.
Is any other way that I can make it faster ?
Or any parameters I can modify to make it ?

Set PDQPRIORITY for the index builds.

--
Neil Truby t:01932 724027
Director m:07798 811708
Ardenta Limited e:neil.truby@ardenta.com

<roger@star2000.com.tw> wrote in message
news:1168753830.198456.261650@s34g2000cwa.googlegr oups.com...
> Dear all:
> I intend to copy large data from ids7.3 to ids10 .
> The data size about 40 GB includes several hundred tables.
> I try my best to copy it fast as I can such that
> - use INSERT .. SELECT command to copy data
> - create index after copy data
> - separate into several process running concurrently.
> - set database unlogged.
> Is any other way that I can make it faster ?
> Or any parameters I can modify to make it ?
>

roger@star2000.com.tw wrote:
> Dear all:
> I intend to copy large data from ids7.3 to ids10 .
> The data size about 40 GB includes several hundred tables.
> I try my best to copy it fast as I can such that
> - use INSERT .. SELECT command to copy data
> - create index after copy data
> - separate into several process running concurrently.
> - set database unlogged.
> Is any other way that I can make it faster ?
> Or any parameters I can modify to make it ?

HPL? (High performance loader)

On 15/01/07, Ian Michael Gumby <im_gumby@hotmail.com> wrote:
>
>
>
> >From: "John" <jgleipold@gmail.com>
>
> >roger@star2000.com.tw wrote:
> > > Dear all:
> > > I intend to copy large data from ids7.3 to ids10 .
> > > The data size about 40 GB includes several hundred tables.
> > > I try my best to copy it fast as I can such that
> > > - use INSERT .. SELECT command to copy data
> > > - create index after copy data
> > > - separate into several process running concurrently.
> > > - set database unlogged.
> > > Is any other way that I can make it faster ?
> > > Or any parameters I can modify to make it ?
> >
> >HPL? (High performance loader)
> >
>
> He has the data with a table.
>
> So he can do a select from the local database and insert in to the remote
> database, assuming that he can establish a connection to that machine...
>
>
> This would be faster that the HPL since he's skipping the unload process.
>
> He could do each table in parallel as well.
>
> That would be the *fastest* way.
>
> Well, actually there is a "faster" way, but it would require a tad more
> work, however it isn't worth the effort.
>
>
> -G
>
> __________________________________________________ _______________
> Dave vs. Carl: The Insignificant Championship Series. Who will win?
> http://clk.atdmt.com/MSN/go/msnnkwsp...001MSN38C07001
>
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Need to watch logging, both databases need to be the same so there may
be a requirement for lots of log space on the target to avoid long tx,
also the number of locks used may come into play if the tables have a
large number of records. However that aside I agree this is the
fastest method.

Keith

Ian Michael Gumby wrote:
> >HPL? (High performance loader)
> >
>
> He has the data with a table.
>
> So he can do a select from the local database and insert in to the remote
> database, assuming that he can establish a connection to that machine...
>
>
> This would be faster that the HPL since he's skipping the unload process.
>

I believe that you can unload to a named pipe with HPL and then load
from that same named pipe. This is a very fast way of doing this. I
think there might even be a program in the iiug repository that will
set up the hpl job to do this so you don't have to go through the
horrible HPL GUI. I think you can even create the jobs and run many in
parallel.

>
> Ian Michael Gumby wrote:
>> >HPL? (High performance loader)
>> >
>>
>> He has the data with a table.
>>
>> So he can do a select from the local database and insert in to the remote
>> database, assuming that he can establish a connection to that machine...
>>
>>
>> This would be faster that the HPL since he's skipping the unload process.
>>
>
> I believe that you can unload to a named pipe with HPL and then load
> from that same named pipe. This is a very fast way of doing this. I
> think there might even be a program in the iiug repository that will
> set up the hpl job to do this so you don't have to go through the
> horrible HPL GUI. I think you can even create the jobs and run many in
> parallel.

HPL with named pipes is the one of the coolest things ever and if you have a
later version you can use the onpladm command line tool and bypass the GUI.

With a little work you can perform an unload of the source table, ship the
data over a network (i.e. skip the unload to temporary flat file bottleneck)
and concurrently load the data into your target table.

1. Create some named pipes on the target server with the mkfifo command:
mkfifo /home/informix/pipe.0
2. Create a device array in HPL on the source server consisting of PIPE
devices with a command that looks like:

ssh informix@target_server "cat - > /home/informix/pipe.0"

3. Create a device array in HPL on the target server consisting of PIPE
devices with a command that looks like:

cat /home/informix/pipe.0

4. Create an unload job on the source server and start it with onpload
5. Create a load job on the target server and start it with onpload

There you go, you're simultaneously unloading from the source and loading
into the target and eliminating those pesky temporary unload files.

informix on the source server must be trusted on the target server for the
ssh (or remsh or rsh) to work without requesting a password.

To speed up the index builds you can change some config parameters and
environment variables:

set pdqpriority 100
set MAX_PDQPRIORITY to 100
increase SHMVIRTSIZE as much a possible without causing swapping
increase DS_TOTAL_MEMORY to about 80% of SHMVIRTSIZE
set LRU_MIN_DIRTY to 60
set LRU_MAX_DIRTY to 70
set CKPTINTVL to something silly like 1 hour and let the physical log
trigger checkpoints
export PSORT_NPROCS to 2 * number of CPUVPS
export PSORT_DBTEMP to as many large filesystems as you can


Andrew

Andrew Ford wrote:

> HPL with named pipes is the one of the coolest things ever and if you have a
> later version you can use the onpladm command line tool and bypass the GUI.
>

Do you have examples of using the onpladm command? I haven't used it
this way. In fact I haven't had a need to use it for 6 years. I avoided
the gui in the past by reverse engineering the onpload database (this
was before I knew that other people had already done it.) I have lost
that shell script now but I remember using the named pipes to unload
and load.