Re: Informix thread management

Thanks for a most informative post Art - as ever. On the site I'm on at the moment users often connect with PDQPRIORITY=HIGH and I'm sure I've seen several run at the same time and not get blocked. They just don't seem to get to do much in the way of parallel processing - though it's difficult to tell whether that's because of the nature of queries they're running. Questions: - Am I mistaken / missing something? - Does HIGH behave the same as 100? - What effect does increasing DS_TOTAL_MEMORY have on how the queries run? Andy "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2003.12.12.12.01.16.130258.12806@bloomberg.net>... > On Fri, 12 Dec 2003 07:22:55 -0500, Vijay wrote: > > > Hi All, > > > > I have fragmented my database & now want to use the parallel > > database feature of Informix.. I read in a document that we need to limit the > > database's parallel processing feature because, the more the parallel threads > > spawned the more the resource used... Thus other process will have to wait > > till the memory used by the previous threads... Please guide me on how the > > paralled query threads are used in informix... > > > > Regards, > > Vijay Kumar.R > > Software Engg. > > HP-ISO Bangalore, > > Ph : 205 2640 > <HTML SNIPPED> > > PLEASE DO NOT POST HTML AND ESPECIALLY NOT MIME! Mime triples the bandwidth > needed to download your postings! This is a text only newsgroup. > > PDQPRIORITY=-1 ==> Use the PDQPRIORITY in the engine's startup environment. > PDQPRIORITY=0 ==> No parallel query at all. No parallel sorting. > PDQPRIORITY=1 ==> Parallel fragment scanning and fragment elimination only. > PDQPRIORITY=2->100 ==> Fill parallel query and sorting. > > Actual effective PDQPRIORITY is PDQPRIORITY / MAX_PDQPRIORITY). > > Now PDQPRIORITY >=2 implies acquisition of that percentage of the engine's > resources including memory and CPU VPs. If you run a query with > PDQPRIORITY=100 and MAX_PDQPRIORITY=100 then that query will a) have to wait > until no other query is actively running, and b) will block all other queries > once it starts until it completes. This implies that only 2 querys with an > effective PDQPRIORITY of 50 can run concurrently, etc. > > > Art S. Kagel

On Tue, 16 Dec 2003 06:34:15 -0500, Andy Kent wrote: > Thanks for a most informative post Art - as ever. > > On the site I'm on at the moment users often connect with PDQPRIORITY=HIGH and > I'm sure I've seen several run at the same time and not get blocked. They just > don't seem to get to do much in the way of parallel processing - though it's > difficult to tell whether that's because of the nature of queries they're > running. > > Questions: > > - Am I mistaken / missing something? Yes. If your user's queries are relatively small you would not notice any blockage, especially under PDQPRIORITY=HIGH (see below). Perform a Cartesian product between several of your largest fragmented tables, especially if you can contrive to include a UNION or two under PDQPRIORITY=100. Then try to run some other complex query with a PDQPRIORITY = 100 and see what happens to the latter. It will pause at least until the original query needs to pause for IO and perhaps until you kill the first one if there is enough cache and high RA_ values. > - Does HIGH behave the same as 100? No. According to the Guide to SQL manual, under HIGH "The database server determines an appropriate PDQPRIORITY value based on several factors, including the number of available processors, the fragmentation of the tables being queried, the complexity of the query, and others." So basically, the engine assigns more resources to more complex queries and ones that can benefit most from those resources but balances those needs with 'playing nice' with other outstanding queries. So under HIGH you are less likely to experience blocking than under PDQPRIORITY=50 or higher. But also under HIGH complex queries will not wait for more resources to become available > - What effect does increasing DS_TOTAL_MEMORY have on how the queries run? Check out the Performance Guide for a discussion of issues like this one. Art S. Kagel > Andy > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2003.12.12.12.01.16.130258.12806@bloomberg.net>... >> On Fri, 12 Dec 2003 07:22:55 -0500, Vijay wrote: >> >> > Hi All, >> > >> > I have fragmented my database & now want to use the parallel >> > database feature of Informix.. I read in a document that we need to limit >> > the database's parallel processing feature because, the more the parallel >> > threads spawned the more the resource used... Thus other process will have >> > to wait till the memory used by the previous threads... Please guide me on >> > how the paralled query threads are used in informix... >> > >> > Regards, >> > Vijay Kumar.R >> > Software Engg. >> > HP-ISO Bangalore, >> > Ph : 205 2640 >> <HTML SNIPPED> >> >> PLEASE DO NOT POST HTML AND ESPECIALLY NOT MIME! Mime triples the bandwidth >> needed to download your postings! This is a text only newsgroup. >> >> PDQPRIORITY=-1 ==> Use the PDQPRIORITY in the engine's startup environment. >> PDQPRIORITY=0 ==> No parallel query at all. No parallel sorting. >> PDQPRIORITY=1 ==> Parallel fragment scanning and fragment elimination only. >> PDQPRIORITY=2->100 ==> Fill parallel query and sorting. >> >> Actual effective PDQPRIORITY is PDQPRIORITY / MAX_PDQPRIORITY). >> >> Now PDQPRIORITY >=2 implies acquisition of that percentage of the engine's >> resources including memory and CPU VPs. If you run a query with >> PDQPRIORITY=100 and MAX_PDQPRIORITY=100 then that query will a) have to wait >> until no other query is actively running, and b) will block all other queries >> once it starts until it completes. This implies that only 2 querys with an >> effective PDQPRIORITY of 50 can run concurrently, etc. >> >> >> Art S. Kagel