Re: Informix performance Issues
For a DSS system it's better to decrease BUFFERS and increase SHMVIRTSIZE
and MAXIMIZE DS_TOTAL_MEMORY for PDQ. This will induce the engine to use
more light scans and avoid the LRU algorithm in the resident memory.
What about the table's framentation scheme?
Have you used clustering index?
Is your database using buffered logging?
By the way, when the user issue a PDQ query, what is the value of
PDQPRIQRITY? Cause your MAX_PDQPRIORITY is 80% that means if one user is
setting their PDQPRIORITY to 100 then all the other PDQ queries will be shut
out of the door.
Dong Xiao
>From: Dinkar Rane <dinkar_rane@3com.com>
>Reply-To: Dinkar Rane <dinkar_rane@3com.com>
>To: informix-list@iiug.org
>Subject: Informix performance Issues
>Date: Thu, 10 Jun 1999 09:09:42 -0700
>
>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@@