Re: Online 7.x performance on a single CPU machine

Dear Irwin, I too noticed the same problem and did not find a workaround. Since working on that system I have heard of people setting up 2 CPU VPs even though they have only one CPU. What this may mean is that while one is busy on the big query the other VP can still execute the small OLTP operations, with unix multiprocessing handling who gets the CPU. I dont have a single CPU machine to try it on but it cannot hurt. One extra process on the machine should not be able to degrade performance by that much. Woth a try. HTH Jason On 17 Oct 1997 04:06:31 GMT, "Irwin Goldstein" <irwin@objectsoft.com> wrote: >Over the past couple of years, I have noticed the following effect on both >HP/UX and AIX single CPU platforms when running Informix Online 7.x: > >When running just OLTP type applications, it is possible to tune the engine >to perform quite well. At least equal to, and in some cases better than >5.x on the same platform. (The improvements usually come from better query >plans when the appropriate update statistics statements are used.) > >When a single user executes a "large" query, 7.x again often outperforms >5.x. > >However, when a "large" query is running under 7.x, OLTP users experience >extremely poor performance -- worse than when the same query was running >under 5.x. As near as I can tell, this seems to be due to the >multi-threaded acrchitecture of 7.x. It seems that a CPU virtual processor >"cooperatively" multi-tasks its threads. That is a thread must >periodically yield in order to allow another thread to run. When a big >query is running, the thread running that query does not yield often enough >to allow the CPU VP to respond rapidly to small OLTP type queries. Even >doing a simple "fetch next" on a cursor which is already open results in an >unusual delay while the thread doing the fetch waits for the "big query" >thread to yield. Under 5.x this did not occur because each user session >was a different UNIX process, and UNIX pre-emptively multi-tasks its >processes. Thus under 5.x even a "big" query could be pre-empted at any >time for a context switch to another process. On a 4+ CPU machine, 7.x >does not usually have this problem because each CPU VP is a separate UNIX >process and can thus be pre-empted, even if it is running a "big" query. >Run enough big queries, however, and I'm sure the same thing would start to >happen. > >While the merits of multi-threading can be argued back and forth, it is >clear that for the above scenario it does not work out so well. On a >single CPU machine, even a single "big" query sends OLTP performance down >the tubes. Has anyone found a reasonable work-around to this problem? I >have tried experimenting by setting the PDQPRIORITY of the "big" query to >various numbers above 2 in order to have them treated as DSS queries. >(Based on the implication in the manuals that PDQPRIORITY 0 queries -- non >DSS queries -- are give a higher priority by the engine than DSS queries.) >This seems to sometimes help a little bit, in that the OLTP queries don't >suffer quite as much, but the improvement is still not enough to make the >performance acceptable in most situations. Are there any other parameters >that might help? > >In general, it would be nice if Informix provided some means of setting >thread priorities other than PDQPRIORITY, or at least forced "big" queries >to yield more often depending on PDQPRIORITY or some other setting. >-- >Irwin Goldstein >Objective Software Systems, Inc. >http://www.objectsoft.com Jason Harris Informix DBA Westpac Banking Corporation NOTE: I add all these address at the bottom of people who spam me Now when their extractors extract my e-mail address they will also end up spamming each other. wtcjr@ix.netcom.com Shevsky@hotmail.com speech@speechrecognition.com SmartBiz@NevWest.com slender@juice4u.net sbliss@earthfriends.com ron@moneyaction.com removeme@gwh.net rocco@lostvegas.com market@DRAWBRIDGE.NET Foxyrusski@mail-response.com emaster@email-man.com waynesmail@earthlink.net