Informix dbimport blob data slow

[cbo...@yahoo.com.br]

Hi people !!!

I have the following situation :

Informix 9.40.UC6 with Big Chunks Enable
Linux Red Hat 8 - kernel 2.4

1 - I done the dbexport with -ss and the time was 1 hour and 40
minutes. This database that I exported has four blobspaces with 2k, 6k,
12k and 20k page size respectively.I have only filds with datatype
"byte" in the blobspaces.

2 - When I done the dbimport the time was 14 hours !!!

I done the configuration in onconfig parameters before I started the
import, like, LRU_MAX_DIRTY=90 / LRU_MIN_DIRTY=80 / CKPTINTVL=3000 /
PDQ parameters / BUFFERS / and others !!

I don´t know, why the import was so slow !!! Can anybody help me ?

Tks,

Edurado

[Ben Thompson]

cbo...@yahoo.com.br wrote:
> Hi people !!!
>
> I have the following situation :
>
> Informix 9.40.UC6 with Big Chunks Enable
> Linux Red Hat 8 - kernel 2.4
>
> 1 - I done the dbexport with -ss and the time was 1 hour and 40
> minutes. This database that I exported has four blobspaces with 2k, 6k,
> 12k and 20k page size respectively.I have only filds with datatype
> "byte" in the blobspaces.
>
> 2 - When I done the dbimport the time was 14 hours !!!
>
> I done the configuration in onconfig parameters before I started the
> import, like, LRU_MAX_DIRTY=90 / LRU_MIN_DIRTY=80 / CKPTINTVL=3000 /
> PDQ parameters / BUFFERS / and others !!

What command and command switches did you use for the dbimport?
Specifically did you use logging? It's fastest to turn logging off.

It looks like you know what to do with the onconfig but maybe you could
post it here. You may want a much shorter checkpoint interval so that
chunk writes happen more frequently than LRU writes.

I don't think dbimport uses PDQ so you might want to consider using the
myimport utility available from the IIUG repository which loads in
parallel. I don't think this is exactly the same as PDQ but it may help
speed things up.

I am not sure about dbimport speed with specific reference to blobs -
the above is general advice - so maybe someone else can help with that.

I hope this helps, Ben.

[Neil Truby]

> I don't think dbimport uses PDQ so you might want to consider using the
> myimport utility available from the IIUG repository which loads in
> parallel. I don't think this is exactly the same as PDQ but it may help
> speed things up.

I think it does for import builds. However, setting PDQ set for the UPDTE
STATISTICS phase of the dbimport can screw things subsequently, so one
should use with care.

Generally, on a brief read of the original post, he seemed to have a feeling
that the dbimport was slow relative to the dbexport, which is of course
entirely expected.

[Denis.Z...@gmail.com]

I have a perfomance problems with dbimport blobs (empty).

TEST:
create database bt in dev1;
create table test_w_blob(a int, b byte );
create PROCEDURE sptest_w_blob2(cnt int);
define i int;
FOR i in (1 to cnt)
insert into test_w_blob(a, b) values (1, null);
END FOR
END PROCEDURE;

execute procedure sptest_w_blob2(5000000)
67 sec

unload to '1.unl' select * from test_w_blob;
drop table test_w_blob;
create table test_w_blob(a int, b byte );
load from '1.unl' insert into test_w_blob;
65 sec

dbexport bt
drop database bt;
dbimport bt -d dev1
370 sec.

If edit .sql script in export - change byte to integer:
dbimport bt -d dev1
63 sec.

IBM Informix Dynamic Server Version 10.00.TC3

But in Linux platform problem not exists:

time echo "execute procedure sptest_w_blob2(5000000);" |dbaccess bt
real 2m42.688s

time echo "load from '1.unl' insert into test_w_blob;" |dbaccess bt
real 3m31.410s

time dbimport bt -d dev1
real 2m48.178s

IBM Informix Dynamic Server Version 10.00.UC3 Linux