 | |
Re: Slow database creation and loading
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Search this Thread | Display Modes |
#1
 
| |||
| |||
|
Re: Slow database creation and loading -
06-07-2007
, 11:02 AM
|
You are mostly running into defect/feature idsdb00100415 which is available 10.00.xC6 onwards. IDS 10 has many such performance improvements so may be time to upgrade ... Thanks and Regards, Gaurav |
#2
| ||||
| ||||
|
|
Free=13177086K Used=10205524K Full=43.65% Is this 10 plus hour time the best we can do or can we speed this up somehow? If we can speed this up please guide me as I am not a DBA and we do not have a DBA on staff. |
You should be able to speed this up.
Quote:
|
I have been told that we have 4 GB of memory on this box. BUFFERS 75000 # Maximum number of shared buffers |
this bigger
and therefor increase
Quote:
|
PHYSFILE 40000 # Physical log file size (Kbytes) NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06 Are you using kernel io??? |
if so decrease NUMAIOVPS if using cooked files then consider raw
please
Quote:
|
PHYSBUFF 64 # Physical log buffer size (Kbytes) bigger. LOGBUFF 64 # Logical log buffer size (Kbytes) bigger. |
During the load it may be interesting to see what the db is doing, so
an onstat -p
may help...
also you state dbimport/???? if load/unload, make sure that indexes
are created after
data is loaded.
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......)
Superboer.
#3
| |||||||
| |||||||
|
|
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 |
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?
Quote:
|
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 - |
the reads and writes.
Quote:
|
if so decrease NUMAIOVPS if using cooked files then consider raw please |
just a trail and error type of tuning?
Quote:
|
PHYSBUFF 64 # Physical log buffer size (Kbytes) bigger. LOGBUFF 64 # Logical log buffer size (Kbytes) bigger. |
buffers to?
Quote:
|
During the load it may be interesting to see what the db is doing, so an onstat -p may help... |
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
Quote:
|
also you state dbimport/???? if load/unload, make sure that indexes are created after data is loaded. |
contain a "TEXT" column and thus would not work with HPL
(or at least we couldn't get it to work).
Quote:
|
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......) |
unattended so it looks like this option is out unless I'm missing
the boat completely with your comment here.
John
#4
| |||||||
| |||||||
|
|
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 |
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?
Quote:
|
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 - |
the reads and writes.
Quote:
|
if so decrease NUMAIOVPS if using cooked files then consider raw please |
just a trail and error type of tuning?
Quote:
|
PHYSBUFF 64 # Physical log buffer size (Kbytes) bigger. LOGBUFF 64 # Logical log buffer size (Kbytes) bigger. |
buffers to?
Quote:
|
During the load it may be interesting to see what the db is doing, so an onstat -p may help... |
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
Quote:
|
also you state dbimport/???? if load/unload, make sure that indexes are created after data is loaded. |
contain a "TEXT" column and thus would not work with HPL
(or at least we couldn't get it to work).
Quote:
|
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......) |
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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list
#5
| |||
| |||
|
|
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. |
something to do with fragmented tables prevented
express mode from working.
I we are using deluxe mode is it ok to increase buffers?
#6
| ||||
| ||||
|
|
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 |
as informix:
set lrumin and maxdirty to 99 in $ONCONFIG
bounce the engine.
dbaccess sysmaster <<!
-- WARNING CHECK THE CODE may contain a bug..
create procedure generatechkpt()
define dirty decimal (4,3);
while (1=1)
select ( sum(lru_nmod) / sum ( lru_nfree + lru_nmod ))
into dirty
from syslrus ;
if (dirty < 0.75 ) then
system "sleep 1";
else
system "onmode -c";
end if
end while ;
end procedure;
execute procedure generatechkpt();
!
run your load
when done do onmode -c set lrumin and maxdirty back to what it was and
bounce your engine.
regarding onconfig:
grab 1 GB for bufferecache at least;
BUFFERS 250000 # Maximum number of shared buffers
Quote:
|
PHYSFILE 40000 # Physical log file size (Kbytes) i do not want a checkpoint when this becomes 75 % full so |
since you are on 7.31.UD7.
if all is raw and KAIO set NUMAIOVPS to 4 max or to 2.
PHYSBUFF 512 # Physical log buffer size (Kbytes)
LOGBUFF 512 # Logical log buffer size (Kbytes)
Quote:
|
contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). it does work in deluxe as someone else stated, and yes you can |
it will help!!!!! also the above hack spl will help.
i also see > Informix Dynamic Server Version 7.31.UD7 -- On-Line
(CKPT REQ) --
-->> checkpoint request.. how long are your checkpoints??? can your
disks cope???
i sure hope no raid 5; ask Art why.
Superboer.
On 19 jun, 04:19, johneevo <johne... (AT) gmail (DOT) com> wrote:
Quote:
|
On Jun 11, 3:07 am, Superboer <superbo... (AT) t-online (DOT) 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 |
#7
| |||
| |||
|
|
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. |
something to do with fragmented tables prevented
express mode from working.
I we are using deluxe mode is it ok to increase buffers?
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list
#8
| |||
| |||
|
|
On Jun 11, 3:07 am, Superboer <superbo... (AT) t-online (DOT) 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. |
Looking at your onstat -p output, I've calculated your critical
metrics and:
BR = 6.61
BTR = 81.73
RAU = 99.990
A Buffwaits Ratio (BR) over 7 means a slow server over 10 is server
performance death, at 6.61 yours is certainly not optimal. I would
increase LRUS and CLEANERS (always keep CLEANER >= LRUS) to lower your
BR. A Buffer Turnover Rate of 81.73 means that you are turning over
the entire buffer cache almost 82 times an hour or about every 44
seconds. Ideally BTR should be single digits to get down that low
you'll have to increase the number of buffers by several times, I'd
guess that ~400000-500000 would get you below 9. The Readahead
Utilization (RAU) is fine and should be as close as possible to
100.00%, 99.990 is about as good as it gets in practice.
Art S. Kagel
#9
| |||
| |||
|
|
Looking at your onstat -p output, I've calculated your critical metrics and: BR = 6.61 BTR = 81.73 RAU = 99.990 A Buffwaits Ratio (BR) over 7 means a slow server over 10 is server performance death, at 6.61 yours is certainly not optimal. I would increase LRUS and CLEANERS (always keep CLEANER >= LRUS) to lower your BR. A Buffer Turnover Rate of 81.73 means that you are turning over the entire buffer cache almost 82 times an hour or about every 44 seconds. Ideally BTR should be single digits to get down that low you'll have to increase the number of buffers by several times, I'd guess that ~400000-500000 would get you below 9. The Readahead Utilization (RAU) is fine and should be as close as possible to 100.00%, 99.990 is about as good as it gets in practice. Art S. Kagel |
#10
| |||
| |||
|
|
On Jun 19, 8:58 am, "Art S. Kagel" <art.ka... (AT) gmail (DOT) com> wrote: Hi Art, Thanks for your help with this. I changed my BUFFERS to 400000 and increased LRUS and CLEANERS to 99 each. Unfortunately this did not help speed things up. Tonight I am going to tweak some of the settings that Superboer suggested. Namely, lowering NUMAIOVPS from 36 to 4 and increasing the PHYSBUFF and LOGBUFF from 64 to 512 for each. Looking at your onstat -p output, I've calculated your critical metrics and: BR = 6.61 BTR = 81.73 RAU = 99.990 A Buffwaits Ratio (BR) over 7 means a slow server over 10 is server performance death, at 6.61 yours is certainly not optimal. I would increase LRUS and CLEANERS (always keep CLEANER >= LRUS) to lower your BR. A Buffer Turnover Rate of 81.73 means that you are turning over the entire buffer cache almost 82 times an hour or about every 44 seconds. Ideally BTR should be single digits to get down that low you'll have to increase the number of buffers by several times, I'd guess that ~400000-500000 would get you below 9. The Readahead Utilization (RAU) is fine and should be as close as possible to 100.00%, 99.990 is about as good as it gets in practice. Art S. Kagel |
good:
onstat -p (including the time since stats were zero'd).
onstat -D
onstat -P
onstat -g iov
onstat -F
onstat -R
onstat -g iof
And the update ONCONFIG (or onstat -c output).
We'll see if we can help more.
Art S. Kagel
|
« Previous Thread
|
Next Thread »
| Thread Tools | Search this Thread |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.