Informix performance Issues

We are facing serious informix performance issues, to the extent that usres are not willing to use the system. The system is a data warehouse implementation. The data size including indexes etc. is about 8 G. It is running on Sun E10K with 8 CPU's and 8G of memory, which is a big machine for the given data size. Informix is configured to use 6 cpuvps. The queries run typically 3-4 times slower when compared to equivalent Oracle based system. Preliminary analysis showed us that informix is not pushing IO's beyond 3.5 MB/sec, while the disk subsytems are capable of doing atleast 3 times the IO. When we switched to PDQ queries the performance improved, but only when not more than 2-3 queries are runing on the system. When 5-6 queries run concurrently the performance goes bad, even worse than nonPDQ run times. We use EMC for disk storage. Adding extra SCSI channel to EMC box didn't improve the performance, but reduced it by 15-20%! One thing I am noticing that database is taking up all the available CPU time!, even when relatively small number of queries are running. Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 Dinkar Rane 3Com Corporation dinkar_rane@3com.com Here is the database statistics $ onstat -g sch VP Scheduler Statistics: vp pid class semops busy waits spins/wait 1 17932 cpu 1 1 10001 2 17939 adm 0 0 0 3 17942 cpu 2593546 2736941 9656 4 17943 cpu 1509848 1616736 9565 5 17944 cpu 1138103 1232007 9480 6 17945 cpu 804594 882667 9387 7 17946 cpu 679758 754912 9296 8 17947 lio 0 0 0 9 17948 pio 0 0 0 10 17949 aio 28 0 0 11 17950 msc 4152 0 0 12 17951 aio 26 0 0 13 17952 aio 25 0 0 14 17953 aio 5 0 0 15 17954 aio 6 0 0 16 17955 aio 6 0 0 17 17956 aio 5 0 0 18 17957 aio 4 0 0 19 17958 aio 4 0 0 20 17959 aio 2 0 0 21 17960 tli 16 119 321 22 17961 tli 603 669 923 wis-edw:~$ onstat -g ioq Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:24:17 -- 2933312 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 69 1388019 1369068 18951 0 kio 2 0 69 2462959 2441236 21723 0 kio 3 0 0 0 0 0 0 kio 4 0 0 0 0 0 0 kio 5 0 0 0 0 0 0 kio 6 0 0 0 0 0 0 kio 7 0 69 1261021 1233802 27219 0 kio 8 0 0 0 0 0 0 kio 9 0 32 805394 789369 16025 0 kio 10 0 69 1038493 1024245 14248 0 kio 11 0 32 695336 679656 15680 0 adt 0 0 0 0 0 0 0 msc 0 0 1 4153 0 0 0 aio 0 0 1 115 20 54 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 0 0 0 gfd 15 0 0 0 0 0 0 gfd 16 0 0 0 0 0 0 gfd 17 0 0 0 0 0 0 gfd 18 0 0 0 0 0 0 gfd 19 0 0 0 0 0 0 gfd 20 0 0 0 0 0 0 gfd 21 0 0 0 0 0 0 gfd 22 0 0 0 0 0 0 gfd 23 0 0 0 0 0 0 gfd 24 0 0 0 0 0 0 gfd 25 0 0 0 0 0 0 gfd 26 0 0 0 0 0 0 gfd 27 0 0 0 0 0 0 gfd 28 0 0 0 0 0 0 gfd 29 0 0 0 0 0 0 gfd 30 0 0 0 0 0 0 gfd 31 0 0 0 0 0 0 gfd 32 0 0 0 0 0 0 gfd 33 0 0 0 0 0 0 gfd 34 0 0 0 0 0 0 gfd 35 0 0 0 0 0 0 gfd 36 0 0 0 0 0 0 gfd 37 0 0 0 0 0 0 gfd 38 0 0 0 0 0 0 gfd 39 0 0 0 0 0 0 gfd 40 0 0 0 0 0 0 gfd 41 0 0 0 0 0 0 gfd 42 0 0 0 0 0 0 gfd 43 0 0 0 0 0 0 gfd 44 0 0 0 0 0 0 gfd 45 0 0 0 0 0 0 gfd 46 0 0 0 0 0 0 gfd 47 0 0 0 0 0 0 gfd 48 0 0 0 0 0 0 gfd 49 0 0 0 0 0 0 gfd 50 0 0 0 0 0 0 gfd 51 0 0 0 0 0 0 gfd 52 0 0 0 0 0 0 gfd 53 0 0 0 0 0 0 gfd 54 0 0 0 0 0 0 gfd 55 0 0 0 0 0 0 gfd 56 0 0 0 0 0 0 gfd 57 0 0 0 0 0 0 gfd 58 0 0 0 0 0 0 gfd 59 0 0 0 0 0 0 gfd 60 0 0 0 0 0 0 gfd

OK here goes. Dinkar Rane wrote: > > We are facing serious informix performance issues, to the extent that > usres are not willing to use the system. > The system is a data warehouse implementation. The data size including > indexes etc. is about 8 G. It is running on Sun E10K with 8 CPU's and 8G > of memory, which is a big machine for the given data size. Informix is > configured to use 6 cpuvps. > The queries run typically 3-4 times slower when compared to equivalent > Oracle based system. Preliminary analysis showed us that informix is > not pushing IO's beyond 3.5 MB/sec, while the disk subsytems are capable > of doing atleast 3 times the IO. When we switched to PDQ queries the > performance improved, but only when not more than 2-3 queries are runing [SNIP] Questions: 1) Are you updating statistics according to the recommended scheme layed out in th 7.2 release notes (or using my dostats.ec to accomplish the same)? If not that is a major problem! 2) Do your users use correlated sub-queries? The 7.3x engine will flatten correlated sub-queries into simple joins which will execute much faster if the indexes to support the join exist for all tables involved. Otherwise the flattened query will be slower than the sub-query. > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 [SNIP] > wis-edw:~$ onstat -p > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:26:31 -- > 2933312 > Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 7624055 8728422 1226943650 99.38 125997 603256 1470852 91.43 > > isamtot open start read write rewrite delete commit > rollbk > 801472269 162712 71466870 646637568 603348 170369 143444 > 8653 4 > > 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 44010.55 975.22 11 22 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 851297 20 555331248 0 0 1 78904 24308 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 1780116 4212 1019432 2511565 8074869 You show 24308 sequential scans in an engine that has only been online for 9 1/2 hours that's more than one scan every minute and a half! You are either missing indexes or you have not updated stats! This looks like the biggest culprit. Your RA utilization ratio is only 90%. Reduce RA_THRESHOLD from 16 to 8 so you are not prefetching so many pages that are never used. Those EMC arrays are doing vast amounts of intelligent read ahead for you anyway so the Informix parameters are almost redundant. [SNIP] > wis-edw:~$ onstat -F > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:27:41 -- > 2933312 > Kbytes > > Fg Writes LRU Writes Chunk Writes > 0 0 27085 You show 100% chunk writes. No matter what the manual states chunk writes are death! You MUST detune the LRU_MIN/MAX_DIRTY from the 90/80 to a more reasonable 50/10. You do not need the 2/0 that a heavy transaction system needs but I'd bet your checkpoints are taking over a minute out of every 50 minutes when they could be taking 10-20 seconds instead. [SNIP] > wis-edw:/informix/PRD/etc$ cat onconfig.wisprd [SNIP] > DBSERVERNAME wisprdshm # Name of default database server > DBSERVERALIASES wisprdtcp # List of alternate dbservernames > NETTYPE ipcshm,1,20,CPU # Configure poll thread(s) for nettype > NETTYPE tlitcp,2,20,NET # Configure poll thread(s) for nettype You have 6 CPU VPs for maximum responsiveness to user requests you should be running a listener in every CPU VP so make the first NETTYPE: NETTYPE ipcshm,6,20,CPU # Unless you increase CPUVPS, as below. > DEADLOCK_TIMEOUT 90 # Max time to wait of lock in> distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 6 # Number of user (cpu) vps The CPUs on the E10000 are very fast. There is good evidence that the rule to use not all CPUS and the rule to use only one CPU VP per CPU are not valid on CPUs faster than about 400MHZ. Some users have reported that they continue to gain from >2 CPU VPs per CPU on SUN systems. You may gain much by changing NUMCPUVPS to 16 and cranking up other related parameters like NETTYPE to correspond. > SINGLE_CPU_VP 0 # If non- > > NOAGE 0 # Process > AFF_SPROC 0 # Affinit > AFF_NPROCS 0 # Affinity number of processors OOOOHHH! NOAGE and Affinity are VERY important on a Sun system. Turn these on! Should be: NOAGE 1 AFF_SPROC 0AFF_NPROC 8 [SNIP] > LRUS 24 # Number of LRU queues > LRU_MAX_DIRTY 90 # LRU percent dirty begin cleaning > LRU_MIN_DIRTY 80 # LRU percent dirty end cleaning As noted above performance will improve for queries crossing past checkpoints if you make the DIRTY parameters 50 & 10. [SNIP] > # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS > OPT_GOAL 0 NOOOOOOOOOOOOOOOOOOOOOO! This will force the engine to try to find a way to return the first N rows as quickly as possible. This is an optimization for WEB and other interactive systems that must present the first screenful of data as fast as possible but that have the leisure to fetch the rest of the query in the background or when the user gets around to asking for it (PGDWN). For a DW system where total query time is to be optimized this can be deadly! Change to -1, ALL_ROWS, set PSORT_NPROCS=40, PSORT_DBTEMP to a list of 3 or more filesystems, and your queries will fly. [BALANCE SNIPPED] Looks like there is alot that you have yet to do before you give up on IDS. I suspect that once you have tuned both the instance and the database design IDS will blow Oracle away. Art S. Kagel

Dinkar Rane wrote: > > We are facing serious informix performance issues, to the extent that > usres are not willing to use the system. Ouch . . . it must be bad . . . > The system is a data warehouse implementation. The data size including > indexes etc. is about 8 G. It is running on Sun E10K with 8 CPU's and 8G > of memory, which is a big machine for the given data size. Informix is > configured to use 6 cpuvps. > The queries run typically 3-4 times slower when compared to equivalent > Oracle based system. Ouch again . . . > Preliminary analysis showed us that informix is > not pushing IO's beyond 3.5 MB/sec, while the disk subsytems are capable > of doing atleast 3 times the IO. When we switched to PDQ queries the > performance improved, but only when not more than 2-3 queries are runing > on the system. When 5-6 queries run concurrently the performance goes > bad, even worse than nonPDQ run times. > We use EMC for disk storage. Adding extra SCSI channel to EMC box didn't > improve the performance, but reduced it by 15-20%! > > One thing I am noticing that database is taking up all the available CPU > time!, even when relatively small number of queries are running. > > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 > > Dinkar Rane > 3Com Corporation > dinkar_rane@3com.com > > Here is the database statistics > > $ onstat -g sch> . . . clipped sch info > wis-edw:~$ onstat -g ioq > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:24:17 -- > 2933312 > 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 69 1388019 1369068 18951 0 > kio 2 0 69 2462959 2441236 21723 0 > kio 3 0 0 0 0 0 0 > kio 4 0 0 0 0 0 0 > kio 5 0 0 0 0 0 0 > kio 6 0 0 0 0 0 0 > kio 7 0 69 1261021 1233802 27219 0 > kio 8 0 0 0 0 0 0 > kio 9 0 32 805394 789369 16025 0 > kio 10 0 69 1038493 1024245 14248 0 > kio 11 0 32 695336 679656 15680 0 > adt 0 0 0 0 0 0 0 > msc 0 0 1 4153 0 0 0 > aio 0 0 1 115 20 54 0 KIO queues look quite busy . . . . > > wis-edw:~$ onstat -p > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:26:31 -- > 2933312 > Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 7624055 8728422 1226943650 99.38 125997 603256 1470852 91.43 > > isamtot open start read write rewrite delete commit > rollbk > 801472269 162712 71466870 646637568 603348 170369 143444 > 8653 4 > > 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 44010.55 975.22 11 22 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 851297 20 555331248 0 0 1 78904 24308 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 1780116 4212 1019432 2511565 8074869 > 89% hit ratio on read-aheads . . . . not too bad. Bufwaits look a bit high . . . wonder how many LRUs are configured. > wis-edw:~$ onstat -g seg > . . . seg info clipped . . . > > wis-edw:~$ onstat -F > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:27:41 -- > 2933312 > Kbytes > > Fg Writes LRU Writes Chunk Writes > 0 0 27085 > All writes are checkpoint writes . . . I'd be interested in how many seconds the checkpoint duration would be . . . > > wis-edw:~$ onstat -R > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:28:17 -- > 2933312 > Kbytes > > 24 buffer LRU queue pairs priority levels > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > 0 f 32291 92.5% 29867 0 3564 26289 14 > 1 m 7.5% 2424 0 2424 0 0 > 2 f 33368 92.7% 30943 0 4917 26006 20 > 3 m 7.3% 2425 0 2424 1 0 > 4 f 33370 92.7% 30946 0 5137 25791 18 > 5 m 7.3% 2424 0 2424 0 0 > 6 f 33180 92.7% 30757 0 5055 25681 21 > 7 m 7.3% 2423 0 2423 0 0 > 8 f 33275 92.7% 30852 0 5406 25426 20 > 9 m 7.3% 2423 0 2423 0 0 > 10 f 33108 92.7% 30686 0 4954 25716 16 > 11 m 7.3% 2422 0 2422 0 0 > 12 f 33073 92.7% 30653 0 4487 26148 18 > 13 m 7.3% 2420 0 2420 0 0 > 14 f 33087 92.7% 30666 0 5058 25585 23 > 15 m 7.3% 2421 0 2421 0 0 > 16 f 33304 92.7% 30881 0 5309 25555 17 > 17 m 7.3% 2423 0 2423 0 0 > 18 f 33159 92.7% 30737 0 4994 25727 16 > 19 m 7.3% 2422 0 2421 1 0 > 20 f 33392 92.7% 30970 0 5010 25950 10 > 21 m 7.3% 2422 0 2422 0 0 > 22 f 32305 92.5% 29881 0 4219 25655 7 > 23 m 7.5% 2424 0 2424 0 0 > 24 F 28738 91.5% 26303 0 398 25897 8 > 25 m 8.5% 2435 0 2435 0 0 > 26 f 41859 94.2% 39434 0 13707 25717 10 > 27 m 5.8% 2425 0 2425 0 0 > 28 f 34293 92.9% 31868 0 6334 25518 16 > 29 m 7.1% 2425 0 2425 0 0 > 30 f 33224 92.7% 30800 0 4815 25968 17 > Especially with these dirty pages . . . must be nice to have EMC <G>. > wis-edw:/informix/PRD/etc$ cat onconfig.wisprd > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /informix/PRD/dwdata/sd172r0a2 > # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 20000 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > > MIR

Ahhhh, data warehousing . . . please ignore the OLTP specific issues I discussed . . . . need to pay better attention . . . . Carlson@WHSmith wrote: > > Dinkar Rane wrote: > > > > We are facing serious informix performance issues, to the extent that > > usres are not willing to use the system. > > Ouch . . . it must be bad . . . > > > The system is a data warehouse implementation. The data size including > > indexes etc. is about 8 G. It is running on Sun E10K with 8 CPU's and 8G > > of memory, which is a big machine for the given data size. Informix is > > configured to use 6 cpuvps. > > The queries run typically 3-4 times slower when compared to equivalent > > Oracle based system. > > Ouch again . . . > > > Preliminary analysis showed us that informix is > > not pushing IO's beyond 3.5 MB/sec, while the disk subsytems are capable > > of doing atleast 3 times the IO. When we switched to PDQ queries the > > performance improved, but only when not more than 2-3 queries are runing > > on the system. When 5-6 queries run concurrently the performance goes > > bad, even worse than nonPDQ run times. > > We use EMC for disk storage. Adding extra SCSI channel to EMC box didn't > > improve the performance, but reduced it by 15-20%! > > > > One thing I am noticing that database is taking up all the available CPU > > time!, even when relatively small number of queries are running. > > > > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 > > > > Dinkar Rane > > 3Com Corporation > > dinkar_rane@3com.com > > > > Here is the database statistics > > > > $ onstat -g sch> > > > . . . clipped sch info > > > > wis-edw:~$ onstat -g ioq > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:24:17 -- > > 2933312 > > 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 69 1388019 1369068 18951 0 > > kio 2 0 69 2462959 2441236 21723 0 > > kio 3 0 0 0 0 0 0 > > kio 4 0 0 0 0 0 0 > > kio 5 0 0 0 0 0 0 > > kio 6 0 0 0 0 0 0 > > kio 7 0 69 1261021 1233802 27219 0 > > kio 8 0 0 0 0 0 0 > > kio 9 0 32 805394 789369 16025 0 > > kio 10 0 69 1038493 1024245 14248 0 > > kio 11 0 32 695336 679656 15680 0 > > adt 0 0 0 0 0 0 0 > > msc 0 0 1 4153 0 0 0 > > aio 0 0 1 115 20 54 0 > > KIO queues look quite busy . . . . > > > > > wis-edw:~$ onstat -p > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:26:31 -- > > 2933312 > > Kbytes > > > > Profile > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > > 7624055 8728422 1226943650 99.38 125997 603256 1470852 91.43 > > > > isamtot open start read write rewrite delete commit > > rollbk > > 801472269 162712 71466870 646637568 603348 170369 143444 > > 8653 4 > > > > 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 44010.55 975.22 11 22 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 851297 20 555331248 0 0 1 78904 24308 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 1780116 4212 1019432 2511565 8074869 > > > > 89% hit ratio on read-aheads . . . . not too bad. > > Bufwaits look a bit high . . . wonder how many LRUs are configured. > > > wis-edw:~$ onstat -g seg > > > > . . . seg info clipped . . . > > > > > wis-edw:~$ onstat -F > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:27:41 -- > > 2933312 > > Kbytes > > > > Fg Writes LRU Writes Chunk Writes > > 0 0 27085 > > > > All writes are checkpoint writes . . . I'd be interested in how many > seconds the checkpoint duration would be . . . > > > > > wis-edw:~$ onstat -R > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:28:17 -- > > 2933312 > > Kbytes > > > > 24 buffer LRU queue pairs priority levels > > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > > 0 f 32291 92.5% 29867 0 3564 26289 14 > > 1 m 7.5% 2424 0 2424 0 0 > > 2 f 33368 92.7% 30943 0 4917 26006 20 > > 3 m 7.3% 2425 0 2424 1 0 > > 4 f 33370 92.7% 30946 0 5137 25791 18 > > 5 m 7.3% 2424 0 2424 0 0 > > 6 f 33180 92.7% 30757 0 5055 25681 21 > > 7 m 7.3% 2423 0 2423 0 0 > > 8 f 33275 92.7% 30852 0 5406 25426 20 > > 9 m 7.3% 2423 0 2423 0 0 > > 10 f 33108 92.7% 30686 0 4954 25716 16 > > 11 m 7.3% 2422 0 2422 0 0 > > 12 f 33073 92.7% 30653 0 4487 26148 18 > > 13 m 7.3% 2420 0 2420 0 0 > > 14 f 33087 92.7% 30666 0 5058 25585 23 > > 15 m 7.3% 2421 0 2421 0 0 > > 16 f 33304 92.7% 30881 0 5309 25555 17 > > 17 m 7.3% 2423 0 2423 0 0 > > 18 f 33159 92.7% 30737 0 4994 25727 16 > > 19 m 7.3% 2422 0 2421 1 0 > > 20 f 33392 92.7% 30970 0 5010 25950 10 > > 21 m 7.3% 2422 0 2422 0 0 > > 22 f 32305 92.5% 29881 0 4219 25655 7 > > 23 m 7.5% 2424 0 2424 0 0 > > 24 F 28738 91.5% 26303 0 398 25897 8 > > 25 m 8.5% 2435 0 2435 0 0 > > 26 f 41859 94.2% 39434 0 13707 25717 10 > > 27 m 5.8% 2425 0 2425 0 0 > > 28 f 34293 92.9% 31868 0 6334 25518 16 > > 29 m 7.1% 2425 0 2425 0 0 > > 30 f 33224 92.7% 30800 0 4815 25968 17 > > > > Especially with these dirty pages . . . must be nice to have EMC <G>. > > > wis-edw:/informix/PRD/etc$ cat onconfig.wispr

"Art S. Kagel" wrote: > > Fg Writes LRU Writes Chunk Writes > > 0 0 27085 > > You show 100% chunk writes. No matter what the manual states chunk > writes are death! You MUST detune the LRU_MIN/MAX_DIRTY from the 90/80 > to a more reasonable 50/10. You do not need the 2/0 that a heavy > transaction system needs but I'd bet your checkpoints are taking over > a minute out of every 50 minutes when they could be taking 10-20 > seconds instead. > <...> > > Art S. Kagel Art, Not only do the manuals state chunk writes are best, but the training states this as well. Could you elaborate on why chunk writes are bad? This is counter to not only the docs but the training. Thanks, Tim -- - -- --- Tim Schaefer ---- tschaefe@mindspring.com --- http://www.inxutil.com -- -

Tim Schaefer wrote: > > "Art S. Kagel" wrote: > > > > Fg Writes LRU Writes Chunk Writes > > > 0 0 27085 > > > > You show 100% chunk writes. No matter what the manual states chunk > > writes are death! You MUST detune the LRU_MIN/MAX_DIRTY from the 90/80 > > to a more reasonable 50/10. You do not need the 2/0 that a heavy > > transaction system needs but I'd bet your checkpoints are taking over > > a minute out of every 50 minutes when they could be taking 10-20 > > seconds instead. > > > <...> > > > > Art S. Kagel > > Art, > > Not only do the manuals state chunk writes are best, but the training > states this as well. Could you elaborate on why chunk writes are bad? > This is counter to not only the docs but the training. The manual is right in what it says, it is just that the point it makes is irrelevant in the real world. Your instructor is just towing the party line. I had a former benchmark group tech as instructor for the admin course and it was he who first told me that CHUNK writes are bad. My own testing has proved him correct through ever version from 5.01 to 7.31 so I stand by my statement. Follow the reasoning: OK, what the manual says is that CHUNK writes minimize the impact that the engine has on the system. Because these writes are ganged at checkpoint time the page cleaners can divvy writes up by chunk, sort the pages, and use Big Buffers so that the I/Os can be accomplished as quickly as possible and with the fewest possible I/O system calls and intelligent controllers can take best advantage of the I/O stream. This minimizes the impact that the engine has on the OS and other applications. HOWEVER, remember that in 7.[12]x all threads running in CPU VP #1, and that is ALWAYS a majority of the active threads BTW, will be suspended until all buffers have been flushed so if you have 600,000 buffers and LRU_MAX/MIN_DIRTY set to 90/80 then as many as 540,000 buffers, but no fewer than 480,000 if you have update activity (and if you do not this whole discussion is moot anyway, must be flushed at checkpoint time! With 2K pages that is 900MB which will take a while. Now Menlo claims that the checkpoint algorithms have been rewritten for 7.3x but I have not tested this yet to determine if the query stalling still occurs and Menlo will not say anything about it beyond that it was rewritten (and my case about the problem was NOT QA'd against 7.3x by support)! So the query stall problem may still exist. But that is besides the point anyway since for most of us it is enough that this results in long checkpoints and all update activity is suspended on all VPs until the checkpoint completes. Now on the other hand if you set the DIRTY params to 50/10 then at most there will be 300,000 dirty buffers and as few as 60,000 buffers to be flushed with the average about 180,000 which will take about 1/3->1/4 as long to flush. This shifts the majority of writes to LRU Writes. So my contention, born out by the Informix benchmark folk BTW, is that for a transaction heavy system nearly ALL writes should be LRU writes to minimize the impact of I/O on the SERVER and its client applications and for DW/DSS systems there can be a balance of LRU and CHUNK writes but still leaning toward mostly LRU writes. Since any clients may be running on a different machine (and for a DSS/DW server they most likely are) they are therefore are not directly affected by the increased and spread out I/O load on the server so favoring LRU writes is win-win. Heck I'm the DBA not the damn SysAdmin, I only care how my server performs and how it APPEARS to perform to my users. If tuning my engine to peak performance causes other apps on the server machine to crawl, S**T get them the hell off of my server! Remember the assumption I make in my Tech Notes article that we are all running dedicated Informix server machines with few, if any, other applications running on the server. I hope this clears up the issue as I see it. Art S. Kagel

Thanks Art, This is always something I'm trying to get a better understanding of. Tim -- - -- --- Tim Schaefer ---- tschaefe@mindspring.com --- http://www.inxutil.com -- -

In article <376130E6.D0CCDBA6@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes > >The manual is right in what it says, it is just that the point it makes >is irrelevant in the real world. Your instructor is just towing the >party line. I had a former benchmark group tech as instructor for the >admin course and it was he who first told me that CHUNK writes are bad. >My own testing has proved him correct through ever version from 5.01 to >7.31 so I stand by my statement. Follow the reasoning: > >OK, what the manual says is that CHUNK writes minimize the impact that >the engine has on the system. Because these writes are ganged at >checkpoint time the page cleaners can divvy writes up by chunk, sort >the pages, and use Big Buffers so that the I/Os can be accomplished as >quickly as possible and with the fewest possible I/O system calls and >intelligent controllers can take best advantage of the I/O stream. >This minimizes the impact that the engine has on the OS and other >applications. > True. Larger I/Os = less system call overhead and sequential I/O reduces seeking. >HOWEVER, remember that in 7.[12]x all threads running in CPU VP #1, and >that is ALWAYS a majority of the active threads BTW, will be suspended >until all buffers have been flushed so if you have 600,000 buffers and >LRU_MAX/MIN_DIRTY set to 90/80 then as many as 540,000 buffers, but no >fewer than 480,000 if you have update activity (and if you do not this >whole discussion is moot anyway, must be flushed at checkpoint time! >With 2K pages that is 900MB which will take a while. Now Menlo claims >that the checkpoint algorithms have been rewritten for 7.3x but I have >not tested this yet to determine if the query stalling still occurs and >Menlo will not say anything about it beyond that it was rewritten (and >my case about the problem was NOT QA'd against 7.3x by support)! So >the query stall problem may still exist. But that is besides the point I now have a 7.30.UC8 at work running on a Sun E250 with Solaris 2.6 so I'll check this out tomorrow... >anyway since for most of us it is enough that this results in long >checkpoints and all update activity is suspended on all VPs until the >checkpoint completes. > True. >Now on the other hand if you set the DIRTY params to 50/10 then at most >there will be 300,000 dirty buffers and as few as 60,000 buffers to be >flushed with the average about 180,000 which will take about 1/3->1/4 >as long to flush. This shifts the majority of writes to LRU Writes. > >So my contention, born out by the Informix benchmark folk BTW, is that >for a transaction heavy system nearly ALL writes should be LRU writes >to minimize the impact of I/O on the SERVER and its client applications i.e. response time vs overall time, like optimisation first rows vs all rows. i.e. perceived performance vs overall performance. >and for DW/DSS systems there can be a balance of LRU and CHUNK writes >but still leaning toward mostly LRU writes. Since any clients may be >running on a different machine (and for a DSS/DW server they most >likely are) they are therefore are not directly affected by the >increased and spread out I/O load on the server so favoring LRU writes >is win-win. > >Heck I'm the DBA not the damn SysAdmin, I only care how my server >performs and how it APPEARS to perform to my users. If tuning my >engine to peak performance causes other apps on the server machine to >crawl, S**T get them the hell off of my server! Remember the >assumption I make in my Tech Notes article that we are all running >dedicated Informix server machines with few, if any, other applications >running on the server. > >I hope this clears up the issue as I see it. > >Art S. Kagel -- David Williams

Art S. Kagel wrote: > OK here goes. > > Dinkar Rane wrote: > > > > We are facing serious informix performance issues, to the extent that > > usres are not willing to use the system. > > The system is a data warehouse implementation. The data size including > > indexes etc. is about 8 G. It is running on Sun E10K with 8 CPU's and 8G > > of memory, which is a big machine for the given data size. Informix is > > configured to use 6 cpuvps. > > The queries run typically 3-4 times slower when compared to equivalent > > Oracle based system. Preliminary analysis showed us that informix is > > not pushing IO's beyond 3.5 MB/sec, while the disk subsytems are capable > > of doing atleast 3 times the IO. When we switched to PDQ queries the > > performance improved, but only when not more than 2-3 queries are runing > [SNIP] > > Questions: > > 1) Are you updating statistics according to the recommended > scheme layed out in th 7.2 release notes (or using my dostats.ec to > accomplish the same)? If not that is a major problem! We do update statistics medium for large fact tables and high for small dimension tables. > > > 2) Do your users use correlated sub-queries? The 7.3x engine will > flatten correlated sub-queries into simple joins which will execute > much faster if the indexes to support the join exist for all tables > involved. Otherwise the flattened query will be slower than the > sub-query. > There are no subqueries. The queries are generated thru Business Objects. > > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 > [SNIP] > > > wis-edw:~$ onstat -p > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:26:31 -- > > 2933312 > > Kbytes > > > > Profile > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > > 7624055 8728422 1226943650 99.38 125997 603256 1470852 91.43 > > > > isamtot open start read write rewrite delete commit > > rollbk > > 801472269 162712 71466870 646637568 603348 170369 143444 > > 8653 4 > > > > 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 44010.55 975.22 11 22 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 851297 20 555331248 0 0 1 78904 24308 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 1780116 4212 1019432 2511565 8074869 > > You show 24308 sequential scans in an engine that has only been online > for 9 1/2 hours that's more than one scan every minute and a half! You > are either missing indexes or you have not updated stats! This looks > like the biggest culprit. > > Your RA utilization ratio is only 90%. Reduce RA_THRESHOLD from 16 to 8 so you > are not prefetching so many pages that are never used. Those > EMC arrays are doing vast amounts of intelligent read ahead for you > anyway so the Informix parameters are almost redundant. > > [SNIP] > > wis-edw:~$ onstat -F > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:27:41 -- > > 2933312 > > Kbytes > > > > Fg Writes LRU Writes Chunk Writes > > 0 0 27085 > > You show 100% chunk writes. No matter what the manual states chunk > writes are death! You MUST detune the LRU_MIN/MAX_DIRTY from the 90/80 > to a more reasonable 50/10. You do not need the 2/0 that a heavy > transaction system needs but I'd bet your checkpoints are taking over > a minute out of every 50 minutes when they could be taking 10-20 > seconds instead. > > [SNIP] > > > wis-edw:/informix/PRD/etc$ cat onconfig.wisprd > [SNIP] > > DBSERVERNAME wisprdshm # Name of default database server > > DBSERVERALIASES wisprdtcp # List of alternate dbservernames > > NETTYPE ipcshm,1,20,CPU # Configure poll thread(s) for nettype > > NETTYPE tlitcp,2,20,NET # Configure poll thread(s) for nettype> > You have 6 CPU VPs for maximum responsiveness to user requests you > should be running a listener in every CPU VP so make the first NETTYPE: > NETTYPE ipcshm,6,20,CPU #> > Unless you increase CPUVPS, as below. > > > DEADLOCK_TIMEOUT 90 # Max time to wait of lock in> > distributed env. > > RESIDENT 1 # Forced residency flag (Yes = 1, No = > > 0) > > > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> > multi-processor > > NUMCPUVPS 6 # Number of user (cpu) vps> > The CPUs on the E10000 are very fast. There is good evidence that the > rule to use not all CPUS and the rule to use only one CPU VP per CPU > are not valid on CPUs faster than about 400MHZ. Some users have > reported that they continue to gain from >2 CPU VPs per CPU on SUN > systems. You may gain much by changing NUMCPUVPS to 16 and cranking up > other related parameters like NETTYPE to correspond. > > > SINGLE_CPU_VP 0 # If non- > > > > NOAGE 0 # Process > > AFF_SPROC 0 # Affinit > > AFF_NPROCS 0 # Affinity number of processors> > OOOOHHH! NOAGE and Affinity are VERY important on a Sun system. Turn > these on! Should be: > > NOAGE 1 > AFF_SPROC 0> AFF_NPROC 8 > > [SNIP] > > LRUS 24 # Number of LRU queues > > LRU_MAX_DIRTY 90 # LRU percent dirty begin cleaning > > LRU_MIN_DIRTY 80 # LRU percent dirty end cleaning> > As noted above performance will improve for queries crossing past > checkpoints if you make the DIRTY parameters 50 & 10. > > [SNIP] > > > # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS > > OPT_GOAL 0> > NOOOOOOOOOOOOOOOOOOOOOO! This will force the engine to try to find > a way to return the first N rows as quickly as possible. This is an > optimization for WEB and other interactive systems that must present > the first screenful of data as fast as possible but that have the > leisure to fetch the rest of the query in the background or when the > user gets around to asking for it (PGDWN). For a DW system where total > query time is to be optimized this can be deadly! Change to -1, > ALL_ROWS, set PSORT_NPROCS=40, PSORT_DBTEMP to a list of 3 or more > filesystems, and your queries will fly. > > [BALANCE SNIPPED] > > Looks like there is alot that you have yet to do before you give up on > IDS. I suspect that once you have tuned both the instance and the > database design IDS will blow Oracle away. > > Art S. Kagel

Carlson, KAIO is enabled. However I did not find any performance degradation when I switched it off. The sequential scans you see are on the small dimension tables. Almost all the fact table scans are using indexes. The reason for long check point interval is that it gets updated only once in a day, rest of the time it accessed for querying only. Dinkar Carlson@WHSmith wrote: > Dinkar Rane wrote: > > > > We are facing serious informix performance issues, to the extent that > > usres are not willing to use the system. > > Ouch . . . it must be bad . . . > > > The system is a data warehouse implementation. The data size including > > indexes etc. is about 8 G. It is running on Sun E10K with 8 CPU's and 8G > > of memory, which is a big machine for the given data size. Informix is > > configured to use 6 cpuvps. > > The queries run typically 3-4 times slower when compared to equivalent > > Oracle based system. > > Ouch again . . . > > > Preliminary analysis showed us that informix is > > not pushing IO's beyond 3.5 MB/sec, while the disk subsytems are capable > > of doing atleast 3 times the IO. When we switched to PDQ queries the > > performance improved, but only when not more than 2-3 queries are runing > > on the system. When 5-6 queries run concurrently the performance goes > > bad, even worse than nonPDQ run times. > > We use EMC for disk storage. Adding extra SCSI channel to EMC box didn't > > improve the performance, but reduced it by 15-20%! > > > > One thing I am noticing that database is taking up all the available CPU > > time!, even when relatively small number of queries are running. > > > > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 > > > > Dinkar Rane > > 3Com Corporation > > dinkar_rane@3com.com > > > > Here is the database statistics > > > > $ onstat -g sch> > > > . . . clipped sch info > > > > wis-edw:~$ onstat -g ioq > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:24:17 -- > > 2933312 > > 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 69 1388019 1369068 18951 0 > > kio 2 0 69 2462959 2441236 21723 0 > > kio 3 0 0 0 0 0 0 > > kio 4 0 0 0 0 0 0 > > kio 5 0 0 0 0 0 0 > > kio 6 0 0 0 0 0 0 > > kio 7 0 69 1261021 1233802 27219 0 > > kio 8 0 0 0 0 0 0 > > kio 9 0 32 805394 789369 16025 0 > > kio 10 0 69 1038493 1024245 14248 0 > > kio 11 0 32 695336 679656 15680 0 > > adt 0 0 0 0 0 0 0 > > msc 0 0 1 4153 0 0 0 > > aio 0 0 1 115 20 54 0 > > KIO queues look quite busy . . . . > > > > > wis-edw:~$ onstat -p > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:26:31 -- > > 2933312 > > Kbytes > > > > Profile > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > > 7624055 8728422 1226943650 99.38 125997 603256 1470852 91.43 > > > > isamtot open start read write rewrite delete commit > > rollbk > > 801472269 162712 71466870 646637568 603348 170369 143444 > > 8653 4 > > > > 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 44010.55 975.22 11 22 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 851297 20 555331248 0 0 1 78904 24308 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 1780116 4212 1019432 2511565 8074869 > > > > 89% hit ratio on read-aheads . . . . not too bad. > > Bufwaits look a bit high . . . wonder how many LRUs are configured. > > > wis-edw:~$ onstat -g seg > > > > . . . seg info clipped . . . > > > > > wis-edw:~$ onstat -F > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:27:41 -- > > 2933312 > > Kbytes > > > > Fg Writes LRU Writes Chunk Writes > > 0 0 27085 > > > > All writes are checkpoint writes . . . I'd be interested in how many > seconds the checkpoint duration would be . . . > > > > > wis-edw:~$ onstat -R > > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 09:28:17 -- > > 2933312 > > Kbytes > > > > 24 buffer LRU queue pairs priority levels > > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > > 0 f 32291 92.5% 29867 0 3564 26289 14 > > 1 m 7.5% 2424 0 2424 0 0 > > 2 f 33368 92.7% 30943 0 4917 26006 20 > > 3 m 7.3% 2425 0 2424 1 0 > > 4 f 33370 92.7% 30946 0 5137 25791 18 > > 5 m 7.3% 2424 0 2424 0 0 > > 6 f 33180 92.7% 30757 0 5055 25681 21 > > 7 m 7.3% 2423 0 2423 0 0 > > 8 f 33275 92.7% 30852 0 5406 25426 20 > > 9 m 7.3% 2423 0 2423 0 0 > > 10 f 33108 92.7% 30686 0 4954 25716 16 > > 11 m 7.3% 2422 0 2422 0 0 > > 12 f 33073 92.7% 30653 0 4487 26148 18 > > 13 m 7.3% 2420 0 2420 0 0 > > 14 f 33087 92.7% 30666 0 5058 25585 23 > > 15 m 7.3% 2421 0 2421 0 0 > > 16 f 33304 92.7% 30881 0 5309 25555 17 > > 17 m 7.3% 2423 0 2423 0 0 > > 18 f 33159 92.7% 30737 0 4994 25727 16 > > 19 m 7.3% 2422 0 2421 1 0 > > 20 f 33392 92.7% 30970 0 5010 25950 10 > > 21 m 7.3% 2422 0 2422 0 0 > > 22 f 32305 92.5% 29881 0 4219 25655 7 > > 23 m 7.5% 2424 0 2424 0 0 > > 24 F 28738 91.5% 26303 0 398 25897 8 > > 25 m 8.5% 2435 0 2435 0 0 > > 26 f 41859 94.2% 39434 0 13707 25717 10 > > 27 m 5.8% 2425 0 2425 0 0 > > 28 f 34293 92.9% 31868 0 6334 25518 16 > > 29 m 7.1%

Dinkar Rane wrote: > > Art S. Kagel wrote: > > > OK here goes. > > > > Dinkar Rane wrote: > > > > > > We are facing serious informix performance issues, to the extent that > > > usres are not willing to use the system. [SNIP] > > > > Questions: > > > > 1) Are you updating statistics according to the recommended > > scheme layed out in th 7.2 release notes (or using my dostats.ec to > > accomplish the same)? If not that is a major problem! > > We do update statistics medium for large fact tables and high for small dimension > tables. I would recommend the standard suite of UPDATE STATISTICS on the fact tables, though the HIGH on the dimension tables should be fine. Do MEDIUM...DISTRIBUTIONS ONLY on the table, HIGH on the head of each index and on the first column that differs if >1 index starts with the same column DISTRIBUTIONS ONLY, then do LOW on each whole index on the table. Art S. Kagel

Hi Dinkar, My take on your problems is that they are almost certainly application based rather than related to your instance configuration. Here's why 1. Data size 8 Gb - bufreads 1.22 billion in 9hrs. Appears like highly inefficient access to me. For example, our Reporting database (not quite a data warehouse, but similar) is currently about 36Gb but usually records under 1 billion bufreads during working hours - and we have dozens of Financial Analysts hitting it. 2. EMC returning 3.5Mb/sec. I have seen Informix, working with HP10.20 and an EMC 3700, return 8-12Mb/sec consistently (An EMC engineer indicated that to be the disk limit) - but that's when you do a sequential scan. With an indexed access, this is going to dramatically slower but, hopefully, more efficient in that less data is sifted thru to get results. Also, with a read cache of 99.38, I wouldn't be terribly concerned with your disks. 3. Read cache of 99.38. Looks really good on paper, but could also point to a heap of inefficient database accesses. For example, this SQL "select business_unit from ps_jrnl_ln where business_unit matches '*10'" against a 1m row table got me (a) no rows (b) 1 million bufreads (c) a cache hit ratio of 97.5. When I did it the second time (simulating another user doing the same thing), the bufread count doubled and the cache ratio crept up to almost 99%. The third time...you get my point. 4. CPU utilization. > One thing I am noticing that database is taking up all the available CPU > time!, even when relatively small number of queries are running. Your onstat -p stats indicate 21% CPU utilization (45000/(6*9.5*3600)) in the 9.5 hours that the instance was running (presumably, you did not initialize stats in between). However, when oninit processes take up close to 100% of CPU, it usually indicates a well-tuned instance but (given your machine configuration & database size) a 'badly tuning application'. Other points. 1. Oracle. > The queries run typically 3-4 times slower when compared to equivalent > Oracle based system. Did a 'true' benchmark indicate this? 2. PDQ. > When we switched to PDQ queries the > performance improved, but only when not more than 2-3 queries are runing > on the system. When 5-6 queries run concurrently the performance goes > bad, even worse than nonPDQ run times. Watch out for 'gating'. onstat -g mgm will indicate queries which are not running because there aren't PDQ resources available. For example, the instance has MAX_PDQPRIORITY set at 80. If users set PDQPRIORITY at 100, only one query can run at a time. 3. Chunk writes, etc. Don't waste any time on tuning writes in an instance where writes account for less than 0.1% of reads. It is extremely easy to toast an RDBMS - especially, when your users (1) build queries with a point-click-join GUI tool and (2) know little about SQL. I suggest that you take the battle to the users - establish an SQL monitoring system which records a sample of what they are doing. Establish which are your highest-hit tables (sysptprof), factor in their size and home in on the ones likely to give you the most bang for the buck (e.g high bufreads, low row count). Pick up long-running SQL from your SQL log pertaining to these highest hit tables(remember, the SQL could also be against views on the table) and EXPLAIN them before firing back SQL or indexing recommendations on a case by case basis. Also have some 'online' monitoring tools which allow you to diagnose complaints of 'slow system' when they are actually occurring. All the best Rudy > > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 > > Dinkar Rane > 3Com Corporation > dinkar_rane@3com.com > > Here is the database statistics > CLIPPED Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Hi Dinkar, My take on your performance problems is that they are application based rather than related to your instance configuration. Here's why 1. Data size 8 Gb - bufreads 1.22 billion in 9hrs. Appears like highly inefficient access to me. For example, our Reporting database (not quite a data warehouse, but similar) is currently about 36Gb but usually records under 1 billion bufreads during working hours - and we have dozens of Financial Analysts hitting it. 2. EMC returning 3.5Mb/sec. I have seen Informix, working with HP10.20 and an EMC 3700, return 8-12Mb/sec consistently (An EMC engineer indicated that to be the disk limit) - but that's when you do a sequential scan. With an indexed access, this is going to dramatically slower but, hopefully, more efficient in that less data is sifted thru to get results. Also, with a read cache of 99.38, I wouldn't be terribly concerned with your disks. 3. Read cache of 99.38. Looks really good on paper, but could also point to a heap of inefficient database accesses. For example, this SQL "select business_unit from ps_jrnl_ln where business_unit matches '*10'" against a 1m row table got me (a) no rows (b) 1 million bufreads (c) a cache hit ratio of 97.5. When I did it the second time (simulating another user doing the same thing), the bufread count doubled and the cache ratio crept up to almost 99%. The third time...you get my point. 4. CPU utilization. > One thing I am noticing that database is taking up all the available CPU > time!, even when relatively small number of queries are running. Your onstat -p stats indicate 21% CPU utilization (45000/(6*9.5*3600)) in the 9.5 hours that the instance was running (presumably, you did not initialize stats in between). However, when oninit processes take up close to 100% of CPU, it usually indicates a well-tuned instance but (given your machine configuration & database size) a 'badly tuning application'. Other points. 1. Oracle. > The queries run typically 3-4 times slower when compared to equivalent > Oracle based system. Did a 'true' benchmark indicate this? 2. PDQ. > When we switched to PDQ queries the > performance improved, but only when not more than 2-3 queries are runing > on the system. When 5-6 queries run concurrently the performance goes > bad, even worse than nonPDQ run times. Watch out for 'gating'. onstat -g mgm will indicate queries which are not running because there aren't PDQ resources available. For example, the instance has MAX_PDQPRIORITY set at 80. If users set PDQPRIORITY at 100, only one query can run at a time. 3. Chunk writes, etc. Don't waste any time on tuning writes in an instance where writes account for less than 0.1% of reads. It is extremely easy to toast an RDBMS - especially, when your users (1) build queries with a point-click-join GUI tool and (2) know little about SQL. I suggest that you take the battle to the users - establish an SQL monitoring system which records a sample of what they are doing. Establish which are your highest-hit tables (sysptprof), factor in their size and home in on the ones likely to give you the most bang for the buck (e.g high bufreads, low row count). Pick up long-running SQL from your SQL log pertaining to these highest hit tables (remember, the SQL could also be against views on the table) and EXPLAIN them before firing back SQL or indexing recommendations on a case by case basis. Also have some 'online' monitoring tools which allow you to diagnose complaints of 'slow system' when they are actually occurring. All the best Rudy > > Informix version is IDS 7.3 UC7. OS version is SunOS 5.6 > > Dinkar Rane > 3Com Corporation > dinkar_rane@3com.com > > Here is the database statistics > CLIPPED Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

In article <376491F8.5AB81597@3com.com>, Dinkar Rane <dinkar_rane@3com.com> wrote: > There are no subqueries. The queries are generated thru Business Objects. I'm afraid that that is the main problem. Have you checked out their SQL plans in most common cases? Or may be you could extend this point in order to get better understanding about the nature of your queries. SY, Alexander -- When a thing is done, it's done. Don't look back. Look forward to your next objective. --General Geo Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Large SAP systems (very heavy OLTP) are routinely tuned to LRU_MAX_DIRTY 2, LRU_MIN_DIRTY 1 w/ Checkpoint Interval 1800 (30 Minutes). Smooth as glass. Most writes are LRU that way (90+%), works great. The last thing we want at checkpoint time is hundreds of MBytes of stuff to write out (and freezing the system for tens or hundreds of seconds...). I/O load is much better balanced this way. I guess that's my way of saying I'm with you Art.... Greg Art S. Kagel wrote: > > Tim Schaefer wrote: > > > > "Art S. Kagel" wrote: > > > > > > Fg Writes LRU Writes Chunk Writes > > > > 0 0 27085 > > > > > > You show 100% chunk writes. No matter what the manual states chunk > > > writes are death! You MUST detune the LRU_MIN/MAX_DIRTY from the 90/80 > > > to a more reasonable 50/10. You do not need the 2/0 that a heavy > > > transaction system needs but I'd bet your checkpoints are taking over > > > a minute out of every 50 minutes when they could be taking 10-20 > > > seconds instead. > > > > > <...> > > > > > > Art S. Kagel > > > > Art, > > > > Not only do the manuals state chunk writes are best, but the training > > states this as well. Could you elaborate on why chunk writes are bad? > > This is counter to not only the docs but the training. > > The manual is right in what it says, it is just that the point it makes > is irrelevant in the real world. Your instructor is just towing the > party line. I had a former benchmark group tech as instructor for the > admin course and it was he who first told me that CHUNK writes are bad. > My own testing has proved him correct through ever version from 5.01 to > 7.31 so I stand by my statement. Follow the reasoning: > > OK, what the manual says is that CHUNK writes minimize the impact that > the engine has on the system. Because these writes are ganged at > checkpoint time the page cleaners can divvy writes up by chunk, sort > the pages, and use Big Buffers so that the I/Os can be accomplished as > quickly as possible and with the fewest possible I/O system calls and > intelligent controllers can take best advantage of the I/O stream. > This minimizes the impact that the engine has on the OS and other > applications. > > HOWEVER, remember that in 7.[12]x all threads running in CPU VP #1, and > that is ALWAYS a majority of the active threads BTW, will be suspended > until all buffers have been flushed so if you have 600,000 buffers and > LRU_MAX/MIN_DIRTY set to 90/80 then as many as 540,000 buffers, but no > fewer than 480,000 if you have update activity (and if you do not this > whole discussion is moot anyway, must be flushed at checkpoint time! > With 2K pages that is 900MB which will take a while. Now Menlo claims > that the checkpoint algorithms have been rewritten for 7.3x but I have > not tested this yet to determine if the query stalling still occurs and > Menlo will not say anything about it beyond that it was rewritten (and > my case about the problem was NOT QA'd against 7.3x by support)! So > the query stall problem may still exist. But that is besides the point > anyway since for most of us it is enough that this results in long > checkpoints and all update activity is suspended on all VPs until the > checkpoint completes. > > Now on the other hand if you set the DIRTY params to 50/10 then at most > there will be 300,000 dirty buffers and as few as 60,000 buffers to be > flushed with the average about 180,000 which will take about 1/3->1/4 > as long to flush. This shifts the majority of writes to LRU Writes. > > So my contention, born out by the Informix benchmark folk BTW, is that > for a transaction heavy system nearly ALL writes should be LRU writes > to minimize the impact of I/O on the SERVER and its client applications > and for DW/DSS systems there can be a balance of LRU and CHUNK writes > but still leaning toward mostly LRU writes. Since any clients may be > running on a different machine (and for a DSS/DW server they most > likely are) they are therefore are not directly affected by the > increased and spread out I/O load on the server so favoring LRU writes > is win-win. > > Heck I'm the DBA not the damn SysAdmin, I only care how my server > performs and how it APPEARS to perform to my users. If tuning my > engine to peak performance causes other apps on the server machine to > crawl, S**T get them the hell off of my server! Remember the > assumption I make in my Tech Notes article that we are all running > dedicated Informix server machines with few, if any, other applications > running on the server. > > I hope this clears up the issue as I see it. > > Art S. Kagel