If you are using HPL, that grabs it's own memory. Data does not go through
the normal buffer pool (unless you are using deluxe mode). You might not
want to increase buffers.

j.

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of johneevo
Sent: Monday, June 18, 2007 10:19 PM
To: informix-list@iiug.org
Subject: Re: Slow database creation and loading


On Jun 11, 3:07 am, Superboer <superbo...@t-online.de> wrote:
Hi Superboer,

Thanks for the reply.

> You should be able to speed this up.
>
> > I have been told that we have 4 GB of memory on this box.
> > BUFFERS 75000 # Maximum number of shared buffers
>
> 4 GB available and only 75000*4k=300MB of buffer cache.. i would make
> this bigger
> and therefor increase
>
> > PHYSFILE 40000 # Physical log file size (Kbytes)
> > NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06

Any suggestions on what the increase the the BUFFERS to. And I guess
there is some ratio that the BUFFERS to PHYSFILE
should be set to. If this is correct would you mind tell me what that
ratio is?

> Are you using kernel io???
> onstat -g ioa will tell.
Yes it appears that we are using kernel io if I am reading the onstat -
g ioa otput correctly. The kio lines have the majority
the reads and writes.

> if so decrease NUMAIOVPS if using cooked files then consider raw
> please

Any suggestions on that the decrease the NUMAIOVPS to? Or is this
just a trail and error type of tuning?

>
> > PHYSBUFF 64 # Physical log buffer size (Kbytes)
> bigger.
> > LOGBUFF 64 # Logical log buffer size (Kbytes)
>
> bigger.

I'm sorry, but once again, any suggestions on what to increase these
buffers to?


> During the load it may be interesting to see what the db is doing, so
> an onstat -p
> may help...

Here is the onstat -p output while the load is running. This was
taken while the HPL portion was running.

Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) --
Up 01:47:2
6 -- 873952 Kbytes
Blocked:CKPT

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44

isamtot open start read write rewrite delete
commit rollbk
53729641 130660 148793 12402936 35365788 3679 13512
5474 139

gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 3789.28 409.72 356 725

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
709578 0 15464370 0 0 580 1856 11850

ixda-RA idx-RA da-RA RA-pgsused lchwaits
79 1 255074 255136 57787

> also you state dbimport/???? if load/unload, make sure that indexes
> are created after
> data is loaded.

I'll check on this, but I believe these are rather small tables. The
contain a "TEXT" column and thus would not work with HPL
(or at least we couldn't get it to work).


> Also you could consider generating your own checkpoints
> setting lrumax and min dirty to 99,
> check buffer cache if 75 % dirty then do a onmode -c
> (i have cut load times have in half using this......)

I would love to cut my load times in half, but the load needs to run
unattended so it looks like this option is out unless I'm missing
the boat completely with your comment here.


John

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list