Re: Pls help to tune my informix engine .. thanks

Hi gurus, Here are the answers for the questions: 1) What type of system - DSS or OLTP? - DSS 2) Result of "top" - i think during peak hours, server is still around 30% IDLE. 3) What are your checkpoints? - CKPTINTVL 300 4) What's your virtsize? - is it this? :) => SHMVIRTSIZE 500000 5) What is the physical size of mem on the box? - physical mem Physical: 4194280 Kbytes, lockable: 3768188 Kbytes, available: 3633152 Kbytes - sawpinfo Kb Kb Kb PCT START/ Kb TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME dev 2097152 0 2097152 0% 0 - 1 /dev/vg00/lvol2 dev 2674688 0 2674688 0% 0 - 1 /dev/vgclar/swap6 reserve - 1898540 -1898540 memory 3159584 707668 2451916 22% 6) What type of storage? - currently: EMC Clariion, will move to NetApp soon 7) Do you have a hot disk? - not sure... may i know how to check? 8) Raw or cooked? - raw 9) onstat -m 15:37:44 Fuzzy Checkpoint Completed: duration was 0 seconds, 3buffers not flushed. 15:37:44 Checkpoint loguniq 7127, logpos 0x472d4, timestamp:0x2a86ca65 15:37:44 Maximum server connections 133 15:42:48 Fuzzy Checkpoint Completed: duration was 0 seconds, 3buffers not flushed. 15:42:48 Checkpoint loguniq 7127, logpos 0x4816c, timestamp:0x2a873943 15:42:48 Maximum server connections 133 15:47:51 Fuzzy Checkpoint Completed: duration was 0 seconds, 3buffers not flushed. 15:47:51 Checkpoint loguniq 7127, logpos 0x69724, timestamp:0x2a8c4328 15:47:51 Maximum server connections 133 15:52:53 Fuzzy Checkpoint Completed: duration was 0 seconds, 3buffers not flushed. 15:52:53 Checkpoint loguniq 7127, logpos 0x6b744, timestamp:0x2a8cc3e5 15:52:53 Maximum server connections 133 15:57:56 Fuzzy Checkpoint Completed: duration was 0 seconds, 3buffers not flushed. 15:57:56 Checkpoint loguniq 7127, logpos 0x6c358, timestamp:0x2a8d1b94 10) onstat -g seg Segment Summary: id key addr size ovhd class blkused blkfree 7 1381451777 c000000000659000 1128763392 458304 R 275552 25 8 1381451778 c000000043ad2000 512000000 16296 V 35986 89014 Total: - - 1640763392 - - 311538 89039 11) we are performing update statistics on those widely used tables everyday 12) KAIO?? not sure ... how to check whether we are using it or not? Thanks a lot for your help. :)

Henry wrote: > Hi gurus, > > Here are the answers for the questions: > > 1) What type of system - DSS or OLTP? > - DSS > > 2) Result of "top" > - i think during peak hours, server is still around 30% IDLE. > > 3) What are your checkpoints? > - CKPTINTVL 300 > > 4) What's your virtsize? > - is it this? :) => SHMVIRTSIZE 500000 > > 5) What is the physical size of mem on the box? > - physical mem > > Physical: 4194280 Kbytes, lockable: 3768188 Kbytes, available: 3633152 > Kbytes > > - sawpinfo > > Kb Kb Kb PCT START/ Kb > TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME > dev 2097152 0 2097152 0% 0 - 1 > /dev/vg00/lvol2 > dev 2674688 0 2674688 0% 0 - 1 > /dev/vgclar/swap6 > reserve - 1898540 -1898540 > memory 3159584 707668 2451916 22% > > 6) What type of storage? > - currently: EMC Clariion, will move to NetApp soon > > 7) Do you have a hot disk? > - not sure... may i know how to check? > > 8) Raw or cooked? > - raw > > 9) onstat -m > 15:37:44 Fuzzy Checkpoint Completed: duration was 0 seconds, 3> buffers not flushed. > 15:37:44 Checkpoint loguniq 7127, logpos 0x472d4, timestamp:> 0x2a86ca65 > > 15:37:44 Maximum server connections 133 > 15:42:48 Fuzzy Checkpoint Completed: duration was 0 seconds, 3> buffers not flushed. > 15:42:48 Checkpoint loguniq 7127, logpos 0x4816c, timestamp:> 0x2a873943 > > 15:42:48 Maximum server connections 133 > 15:47:51 Fuzzy Checkpoint Completed: duration was 0 seconds, 3> buffers not flushed. > 15:47:51 Checkpoint loguniq 7127, logpos 0x69724, timestamp:> 0x2a8c4328 > > 15:47:51 Maximum server connections 133 > 15:52:53 Fuzzy Checkpoint Completed: duration was 0 seconds, 3> buffers not flushed. > 15:52:53 Checkpoint loguniq 7127, logpos 0x6b744, timestamp:> 0x2a8cc3e5 > > 15:52:53 Maximum server connections 133 > 15:57:56 Fuzzy Checkpoint Completed: duration was 0 seconds, 3> buffers not flushed. > 15:57:56 Checkpoint loguniq 7127, logpos 0x6c358, timestamp:> 0x2a8d1b94 > > 10) onstat -g seg > Segment Summary: > id key addr size ovhd class > blkused blkfree > 7 1381451777 c000000000659000 1128763392 458304 R > 275552 25 > 8 1381451778 c000000043ad2000 512000000 16296 V > 35986 89014 > Total: - - 1640763392 - - > 311538 89039 > > 11) we are performing update statistics on those widely used tables > everyday > > 12) KAIO?? not sure ... how to check whether we are using it or not? > > Thanks a lot for your help. :) > Well, "being on the box" (nice phrase) would help :P Still ... NOAGE 1 RESIDENT -1NUMAIOVPS 31 (assuming no kaio 'cos no RESIDENT) CLEANERS 31Change your RA_PAGES / RA_THRESHOLD to 64 / 8 What about temp dbspaces ... do you have any? Make sure you have at least 3. Are you using PDQ? If so the 6 for DS_MAX_QUERIES seems possibly low. and yes more BUFFERS. Check for kaio with onstat -g ath and see if there are kaio threads. You may get more out using KAIO. Difficult to make any definite statements as no info on how long the engine has been up.

> and yes more BUFFERS. you can add buffers until the cows come in if you have set LRUMAX/MINDIRTY to 70/60 % then you will have a read cache of 30/40 % of 500.000 pages. The engine will not flush a dirty buffer for a page which needs to be in memory for reading.... (sometimes it does and that is called a foreground write which we all want to avoid.....) So if readcache should be improved, then reduce LRUMAX/MINDIRTY to say 10 / 5 % Superboer.