Re: PDQ with lots of threads waiting poor peformance vs RAID5

Jay wrote: > I'm running 7.24, 4-way f50 with 1gig memory on AIX 4.3.3 with PDQ = 100, > tables are fragmented by round robin on three disks. I'm finding very > little performance improvements compared to our production enviroment on > RAID5 with no fragmentation nor PDQ. One thing I find very odd, is the > number of scan and group threads that are just waiting. Can someone > please help me understand and correct the problem. A lot depends on your queries. If you are running OLTP type queries, then PDQ could actually hurt performance by adding extra threads on every operation without getting any value from them. > LOCKS 4000 # Maximum number of locks That's a very low number of locks...? > BUFFERS 150000 # Maximum number of shared buffers > NUMAIOVPS 6 # Number of IO vps Do you have KAIO enabled? Is all your data fragmented? If your source table below isn't fragmented, then PDQ won't do much for you. > LRUS 32 # Number of LRU queues I seem to recall that 32 was a particularly bad number to choose for the number of LRUS. > Current SQL statement : > insert into tmp_e06_dept select * from tmp2_e06_dept where sin = > "490-282-530 " and corp = "SHARP" and plan = "500263" and seq = 1 and > to_acct = "P7200" and flag_gb = "g" and dur_start <= ? and ((ind_aux > != > "na" AND dur_end > ? ) OR ((ind_aux IN ("ac","ap") OR > (ind_type > = "sc" AND init_ind_aux = "ac")) AND dur_end <= ? )) and effect_dt >= > ? and effect_dt <= ? and (ind_cntp = "01" and source = "r")