Re: Fragmented table and PDQ

mars1972@my-deja.com wrote: > > In article <89lig9$1jm$1@news.xmission.com>, > dins dins <dins_d@yahoo.com> wrote: > > > > Hello! > > > > IDS 7.31.UC4-1, on HP-UX.10.20 > > > > I'm trying to use PDQ on fragmented table query. > > With set PDQ SELECT statement works about two > > times longer, than without PDQ. > > > > Environment > > > > 1. set PDQPRIORITY 100 > > > > 2. onstat -g mgm > > > > Memory Grant Manager (MGM) > > -------------------------- > > > > MAX_PDQPRIORITY: 100 > > DS_MAX_QUERIES: 1 > > DS_MAX_SCANS: 100 > > DS_TOTAL_MEMORY: 51200 KB > > > > Queries: Active Ready Maximum > > 1 0 1 > > > > Memory: Total Free Quantum > > (KB) 51200 0 51200 > > > > Scans: Total Free Quantum > > 100 100 1 > > > > Load Control: (Memory) (Scans) (Priority) > > (Max Queries) (Reinit) > > Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 > > (Queue Length) 0 0 0 0 0 > > > > Active Queries: > > ------------ > > Session Query Priority Thread Memory > > Scans Gate > > 924 d609fa60 100 d5dde5b8 6384/6400 > > 0/0 - > > > > Ready Queries: None > > > > Free Resource Average # > > Minimum # > > -------------- --------------- --------- > > Memory 0.0 +- 0.0 0 > > Scans 100.0 +- 0.0 100 > > > > Queries Average # Maximum # Total # > > -------------- ------------ --------- > > ------- > > Active 1.0 +-0.0 1 1 > > Ready 0.0 +- 0.0 0 0 > > > > Resource/Lock Cycle Prevention count: 0 > > > > Table has 7 data fragments > > > > onstat -u shows such number of parallel processes :> > > > Userthreads > > address flags sessid user tty wait tout locks > > nreads nwrites > > > > d4bd92e8 Y--P--- 924 whouse ttyqa d52bf268 0 1 > > 0 0 > > d4bd979c ------- 924 whouse ttyqa 0 0 1 > > 0 0 > > d4bda104 ----R-- 924 whouse ttyqa 0 0 1 > > 374565 0 > > d4bdb3d4 ------- 924 whouse ttyqa 0 0 1 > > 0 0 > > d4bdbd3c ------- 924 whouse ttyqa 0 0 1 > > 0 0 > > d4bdde28 ------- 924 whouse ttyqa 0 0 1 > > 0 0 > > d4bde2dc ------- 924 whouse ttyqa 0 0 1 > > 0 0 > > d4bdec44 ------- 924 whouse ttyqa 0 0 1 > > 0 0 > > > > But all time, while SELECT works, only third from > > above is active. > > What could be the problem? > > > > Thanks in advance! > > Din > > __________________________________________________ > > Do You Yahoo!? > > Talk to your friends online with Yahoo! Messenger. > > http://im.yahoo.com > > > > There are several possible problems. First, by setting MAXPDQPRIORITY > to 100, you are essentially allowing anyone to take over 100% of the > resources, so if two people try the same query at the same time, one > waits. That is not strictly true. Setting MAXPDQPRIORITY to 100 means you can use all the resources allocated to PDQ and to you by your PDQPRIORITY. In this instance the second user will wait because DS_MAX_QUERIES is set to 1. > Second, PDQ doesn't guarantee that you will get multiple > threads, it just allows them. Without knowing how the table is > fragmented and what the select statement is, I can't go into any more > detail. In this instance it appears that multiple threads are fired. > One thing to check is, while the query is running, do an onstat > -g mgm and see if it's waiting in a gate. Second, check onstat -g ses > and check the number of threads that are active. The above onstat shows multiple threads with none waiting at gates. However, the point is valid, you should monitor the query throughout to ensure that it is actually running and not waiting. How many CPU VPs do you have? It suggests to me that you only have one or two. Also, DS_TOTAL_MEMORY doesn't look very big. Is this enough to process this table? When using PDQ you need to throw lots of disk (fragments), memory (virtual) and processes (CPU VPs) at it in order to fully optimise performance. As you have found, you can end up slowing the query down if you are not careful. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+