Re: Informix thread management

Thanks you Norton for shutting that down. cheers j. -.-- --- ..- / -. . . -.. / - --- / --. . - / .- / .-.. .. ..-. . .-.-.- / ... --- / -.. --- / .. .-.-.- ----- Original Message ----- From: "Jack Parker" <vze2qjg5@verizon.net> To: "Andy Kent" <andykent.bristol@virgin.net>; <informix-list@iiug.org> Sent: Thursday, January 01, 2004 8:44 AM Subject: Re: Informix thread management > > Decision Support has evolved from the data warehouse community. It > generally implies that you intend to read entire tables, join them and > whatever. > > A query which meets the criteria to be processed in parallel means a query > where you intend to pull more than one row from whatever tables you happen > to be reading - and that the multiple rows reside on different dbspaces. I > suppose that's what we call horizontal parallelism, vertical parallelism > would be where elements of the query tree can be run in parallel - maybe > I've got that backwards (who cares). > > Imagine that you have something like: > > select count(*) (or whatnot) > from tab1, tab2 > where tab1.key=tab2.key > and ......> > where tab1 is fragmented across 4 dbspaces and tab2 across another 4. > > If you are running in parallel then the engine can read all 4 of the chunks > in parallel. > > -- h*ll - I've already written this article. See: > > http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/ > part-1.pdf > > cheers > j. > > ----- Original Message ----- > From: "Andy Kent" <andykent.bristol@virgin.net> > To: <informix-list@iiug.org> > Sent: Thursday, December 18, 2003 5:01 AM > Subject: Re: Informix thread management > > > > Does "DSS query" mean the same thing as "Query which meets the > > criteria to be processed in parallel" - or not? Is this what you mean > > by "rating the query" - or is there some set of rules other than those > > for parallelisation? > > > > Andy > > > > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2003.12.17.08.51.35.618216.12806@bloomberg.net>... > > > On Wed, 17 Dec 2003 05:22:35 -0500, Andy Kent wrote: > > > > > > > OK, but on the other hand I *have* seen very small queries get blocked > when > > > > for some inexplicable reason IDS calculated a DS_MAX_QUERIES of 6, > which is a > > > > mystery in itself. (They haven't set the DS values in the onconfig, > but > > > > according to the calculation in the manual it should be coming up with > 768, > > > > i.e. 3 CPU VPS * 2 * 128. Bug perchance?). > > > > > > > > It seems to me the line between what it deems to be DS and OLTP is a > rather > > > > arbitrary one. The only thing I can see in the query it blocks most > often is a > > > > gratuitous DISTINCT, apart from that it is minute in both estimated > cost and > > > > actual execution time. > > > > > > The DISTINCT would force either a sort or a caching of results to filter > for > > > dups which raises the internal complexity rating of the query. I can > > > understand how that might cause an otherwise simple query to be > classified as a > > > DSS query. > > > > > > Art S. Kagel > > > > > > > Andy > > > > > > > > > > > > > > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > > > > news:<pan.2003.12.16.09.08.12.43378.12806@bloomberg.net>... > > > >> 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. > > > >> >> > > > >> >>