load from external table howto tune...

[s***@t-online.de]

Hello All,


Database 11.70 on linux

Is there a way to tune loading data from an external table??

When i run the select * from insert into in express mode
it writes at a rate of 24 MB / sec while onpload untuned
does 36 MB a sec and onpload overtuned does 150 MB /sec

I verified it did load it in express mode, when i tryed to
drop the table it gave me an error
-197 ISAM error: Partition recently appended to; can't open for write or logging.
, create a lvl 0 first..
when i did that i could drop it.


Loading only from one | delimited file, same as onpload.

Someone told me that loading from external table was faster and would
replace onpload oneday???


Comments are welcome


Superboer

[Jason Harris]

Hi Superboer,

One question:
When you tuned/overtuned onpload, what settings did you make that sped it up?

Jason

[s***@t-online.de]

Hello Jason,


You need multiple cpus otherwise nogo.

assume you know this, if not:

in $INFORMIXDIR/etc/ there is a file called plconfig.std (is the onconfig for
onpload) i copied it to plconfig and changed:



CONVERTTHREADS 8 # Number of conversion threads per device
CONVERTVPS 8 # Max number of vps for converters (total)

# Buffer Configuration

STRMBUFFSIZE 16384 # Buffer size for server stream buffer (kbytes)
STRMBUFFERS 8 # Number of server stream buffers per device
AIOBUFSIZE 16384 # Buffer size for tape/file I/O (kbytes)
AIOBUFFERS 8 # Number of buff


before starting onpload or ipload if you need to use the gui.
export PLCONFIG=plconfig


I have a 4 cpu machine which was 25 % busy when i used the default settings
of the plconfig. So i changed it to above and it was 95 % busy with a
great throughput. The doc says one should alloc one convertvp per cpu but hey
i did not have the time to fine tune so i overtuned it.

Properly tuned the nickname for HPL is the nuke obstacle tool.

See you

Superboer.

[Jason Harris]

Hi Superboer,

Yes, onpload can really push a machine. Even better when you use no-conversion jobs. The external table load is also better when you can use format "informix".

I have not done any loading direct from .unl files for sometime. For one reason or another its always coming from a pipe. For example using gzip or copying database to database without going through disk.

You might be able to speed up the external table load by using dd to simulate a 16k block size, similar to your AIOBUFSIZE setting.

For example:
!mkfifo /tmp/load.fifo
!dd if=unloadfile of=/tmp/load.fifo bs=16k &
create external table on the fifo and then express load to raw table.

Might help, the engine will use a fifo vp to read the pipe. If you have multiple files for the external table, add more fifo vps.

You might tune that dd by trying it on the file at different block sizes to see which works best.

Cheers,

Jason