Re: Informix thread management

Thanks Jack, I've read your article now and it's certainly quite informative. I'm still not clear on exactly what DS memory stores and what it allows you to do, and how come parallelisation is still able to happen on the system I am currently working on where IDS 7.3 has defaulted it to the ludicrously low value of 768k so it never gets used by any queries? Do I take it that DS and parallelism are in fact two separate concepts? I'd be interested if you could clarify this. I'm interested in your comment "Because insufficient memory was allocated, the hash join is stalled while it swaps hash table pages from the temp disk." How did you tell this was what had happened? Thanks, Andy "Jack Parker" <vze2qjg5@verizon.net> wrote in message news:<bt58cu$5s1$1@terabinaries.xmission.com>... > 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