poor (?) perf on NT

I am running a batch process on INFORMIX 7.31TC2 running on NT4.0 Enterprise SP5 and IBM NetFinity with 4 CPU and 4G RAM. This same process (called batch1) used to run on a 2-CPU no-name-brand system. The run time on both systems is 2 hours 5 minutes. The IBM system was supposed to be faster, but not so. We had diagnostic gurus look at the IBM to find why it is no faster than a vanilla system, but they found no performance or bottleneck issues in memory, CPU, or disk i/o. I suspected disk i/o so I created a 2nd instance on the SAME IBM using the SAME disk raid and ran a different batch process on the new instance concurrently with batch1 running on the original instance. The batch1 process completed in 2 hours 7 minutes with the 2nd instance running batch processing concurrently, which seems to negate my theory that disk i/o is an issue. The same data is used on EVERY batch1 run. We set up a dataset, ran an ontape -s -L 0 to disk, and do a restore prior to each successive test run. The space utilization for the instance is about 8G. The batch1 process is running on a NON-LOGGING database. NT performance monitor shows about 25% overall CPU usage with batch1 running alone and about 60% overall CPU usage with batch1 running concurrently with the second instance doing batch processing. Below are outputs from some onstat commands representing the activity of batch1 running concurrently with the second instance running a batch process on the same system IBM. Following the onstats is the ONCONFIG file for the batch1 instance and the online log for time batch1 ran. My questions are: 1) Based on the chunk writes from onstat -F (201053) and the total page writes from onstat -p (427893), the average chunk write is only 2 pages. I have seen over 6 pages per write on my unix systems. Is 2 pages per chunk write too low? If so, what can be done? 2) The onstat -g ioq shows max queue length for the 3 kaio threads to be 52, 33, and 33. I have heard the max queue length for kaio should be no more than 32. Are mine too high? If so, what can be done? 3) For the onstat -g iov output: a) are the io/s values acceptable for the kaio threads? b) are the # of wakeups too high (over 300,000,000 total for the 3 kaio threads) ? 4) For the onstat -g glo output: a) what are "lngspins" and is the value 21 acceptable? b) what are the "yield 0", "yield n", and "yield forever" and are the values listed acceptable? 5) Another comments or feedback would be appreciated Thanks, Steve Murray, DBA Associated Grocers, Inc onstat -p: Informix Dynamic Server Version 7.31.TC2 -- On-Line -- Up 1 days 01:41:24 -- 1 293440 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 190736 324988 261011768 99.93 217620 427893 8208077 97.35 isamtot open start read write rewrite delete commit rollbk 76875816 44649 4306148 49351896 1168569 1658390 809793 682 0 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 13971.48 1145.87 41 620 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 46829 0 39190218 0 0 36 61643 525227 ixda-RA idx-RA da-RA RA-pgsused lchwaits 29045 768 81107 110916 4485238 onstat -F: Informix Dynamic Server Version 7.31.TC2 -- On-Line -- Up 21:47:39 -- 1293440 Kbytes Fg Writes LRU Writes Chunk Writes 0 0 201053 address flusher state data 4af6a500 0 I 0 = 0X0 4af6a9e8 1 I 0 = 0X0 4af6aed0 2 I 0 = 0X0 4af6b3b8 3 I 0 = 0X0 onstat -D: dbs/chk Pg Rd Pg Wr dbspace path 1 1 295 700 rootdbs E:\\IFMXDATA\\ol_dayton\\rootdbs_dat.000 1 12 1 0 rootdbs e:\\ifmxdata\\ol_dayton\\rootdbs_dat.001 2 7 12018 13630 tempdbs3 e:\\ifmxdata\\ol_dayton\\tempdbs3_dat.000 3 8 8638 9394 tempdbs4 f:\\ifmxdata\\ol_dayton\\tempdbs4_dat.000 4 2 17084 387 p_rtl_pricing h:/ifmxdata/ol_dayton/prtl_pricing_dat.001 4 3 36494 41960 p_rtl_pricing f:/ifmxdata/ol_dayton/prtl_pricing_dat.002 4 4 62238 8278 p_rtl_pricing e:/ifmxdata/ol_dayton/prtl_pricing_dat.000 4 5 136938 111400 p_rtl_pricing g:/ifmxdata/ol_dayton/prtl_pricing_dat.003 4 6 30949 29516 p_rtl_pricing h:\\ifmxdata\\ol_dayton\\prtl_pricing_dat.004 5 9 9757 10579 tempdbs1 H:\\IFMXDATA\\ol_dayton\\tempdbs1_dat.001 6 11 10516 11269 tempdbs2 g:\\ifmxdata\\ol_dayton\\tempdbs2_dat.000 7 13 52 0 p_storebatch f:\\ifmxdata\\ol_dayton\\p_storebatch_dat.000 7 14 1 0 p_storebatch e:\\ifmxdata\\ol_dayton\\p_storebatch_dat.001 7 15 1 0 p_storebatch h:\\ifmxdata\\ol_dayton\\p_storebatch_dat.002 10 10 6 190780 plogs f:/ifmxdata/ol_dayton/plogs Dbspaces: 8 active, 2047 maximum Chunks: 15 active, 2047 maximum onstat -g ioq: Informix Dynamic Server Version 7.31.TC2 -- On-Line -- Up 21:47:39 -- 1293440 Kbytes AIO I/O queues: q name/id len maxlen totalops dskread dskwrite dskcopy kio 0 0 0 0 0 0 0 kio 1 0 52 124391 73064 51327 0 kio 2 0 33 142694 52831 89863 0 kio 3 0 33 141277 64845 76432 0 adt 0 0 0 0 0 0 0 msc 0 0 1 473 0 0 0 aio 0 0 1 58 14 0 0 pio 0 0 0 0 0 0 0 lio 0 0 0 0 0 0 0 gfd 3 0 0 0 0 0 0 gfd 4 0 0 0 0 0 0 gfd 5 0 0 0 0 0 0 gfd 6 0 0 0 0 0 0 gfd 7 0 0 0 0 0 0 gfd 8 0 0 0 0 0 0 gfd 9 0 0 0 0 0 0 gfd 10 0 0 0 0 0 0 gfd 11 0 0 0 0 0 0 gfd 12 0 0 0 0 0 0 gfd 13 0 0 0 0 0 0 gfd 14 0 0 0

> My questions are: > > 1) Based on the chunk writes from onstat -F (201053) and the total page > writes from onstat -p (427893), the average > chunk write is only 2 pages. I have seen over 6 pages per write on > my unix systems. Is 2 pages per chunk write > too low? If so, what can be done? I think this is not bad. This means, that during checkpoints cleaners are not busy and checkpoint runs faster. Also, consider page size difference between Unix and NT systems. For the same amount of data you need less pages on NT then on Unix. > > 2) The onstat -g ioq shows max queue length for the 3 kaio threads to be > 52, 33, and 33. I have heard the max queue > length for kaio should be no more than 32. Are mine too high? If > so, what can be done? Generally speaking, it depends on your system. For kio thread query length = 32 considered normal, but in big systems this is not the issue. If you want to add more kaio threads into to the system, simply add more CPU VPs - number of kio threads are equal to the number of CPU VPs. > onstat -D:> > 4 5 136938 111400 p_rtl_pricing > h:\\ifmxdata\\ol_dayton\\p_storebatch_dat.002 > 10 10 6 190780 plogs > f:/ifmxdata/ol_dayton/plogs I found this chunks have highest i/o activity in the system. Try to fragment data, placed in this chunks, if this possible. > env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) Set to 1. > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 3 # Number of user (cpu) vps Try set to 6, probably more. If you have Informix only server, try to load all CPUs to 50-60 %. > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to> one > > LOCKS 150000 # Maximum number of locks > BUFFERS 250000 # Maximum number of shared buffers As I can see from previous output, you don't need buffer pool like this. I think you can set BUFFERS to at least 150000, then monitor buffer pool with onstat -P, if you still have unused space in your buffer pool - continuedecreasing BUFFERS. > NUMAIOVPS 2 # Number of IO vps On NT you need only 1. > PHYSBUFF 64 # Physical log buffer size (Kbytes)> #LOGBUFF 32 # Logical log buffer size (Kbytes) > LOGBUFF 48 # Logical log buffer size (Kbytes)> LOGSMAX 50 # Maximum number of logical log files > CLEANERS 16 # Number of buffer cleaner processes Heh ... I think at least 32. > # SHMBASE 0x20000000 # Shared memory base address This one is recommended on NT. I understand that you have 4GB RAM, but I still not sure above SHMBASE is correct. > SHMBASE 0xC000000L # Shared memory base address > SHMVIRTSIZE 262144 # initial virtual shared memory segment> size Post onstat -g seg,mem output, please. > #20000222GC 1945PST SHMVIRTSIZE 125000 # initial virt shared memory segment > size > SHMADD 64000 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 2000000 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) Try to set up to 3600 with your BUFFERS. > #CKPTINTVL 90 # Check point interval (in sec) > LRUS 16 # Number of LRU queues Mmm... For 250000 BUFFERS you need _at_least_ 100-127 LRUS. > LRU_MAX_DIRTY 90 # LRU percent dirty begin cleaning limit> # Read Ahead Variables > RA_PAGES 32 # Number of pages to attempt to readahead > RA_THRESHOLD 24 # Number of pages left before next group I think you must try something like 64,60. Monitor effectivness with onstat -p. Also, is there any sequential scans during your batch processing? Have you tried light scans ? PDQ ? Light appends ? > OPTCOMPIND 0 # To hint the optimizer Why not 2 ? -- ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285