Re: Online Performance Tuning Question ??

In article <01bbe25d$8e85f440$405694cc@irwin.interaccess.com>, Irwin Goldstein <irwin@objectsoft.com> writes >Mark D. Stock <marks@west.co.za> wrote in article ><32A55398.659F@west.co.za>... >> John DeSilva wrote: >> > > We are running 40 people on an HP9000 ( HPUX 9.04 ) and Online >7.11UC1 = >> > with 256MB. 30 of these are running Order Entry applications (RDS) = 40 users + 256Mb should be enough. I've installed a system using ODBC with 80 users on HP-UX 9.04 Online 7.10.UC1 with 256 Mb. >> > using approx 2MB per user. The system is fine and performace is >normally = >> > good. As I would expect. >> > Unless...... >> > We launch a query / reporting tool ( GQL ). We have two users that >need >> > to use this on a regular basis. When the first starts up GQL things I have used GQL - I have it on my PC at work. >slow >> > down. When the second starts it up the whole thing grinds to a halt. What is the SQL which is generated. >> > Symptoms are as follows. >> > With 40 normal users, the engine is is quite happy. Buffwaits >> > from an onstat -p show minimal if any increase. Unix swapping is >running >> > at around 20MB. Engine is using about 100MB of memory. Sounds correct. >> > When we start up GQL ( runs local on two macs and accesses data via a >> > DBACCESS session ) swapping jumps to 88 MB and the buffwaits go >through >> > the roof, increasing by several 000's per minute. PDQ allows for a max This indicates it is not using indicies. I would need to see the SQL generated (once you build you query from the model, before you submit the query you can choose view generated sql from one of the menu). >= >> > of 5MB and is only enable for the GQL user login. >> > Sounds like someone is trying to use PDQ rather then create the appropriate indices/traing the users to submit sensible querys (see note below)/ create a missing index. >> > Q. Is it possible that the initial virtual segment of shared memory >> > (64MB) is being swapped out ? If so would it swap out the entire >> > segment or only a small part ? If this the reason for the large >> > jump in unix swapping that we are seeing ??? >> > Probably, it sounds like the GQL session is using a PDQ query. This can use a large amount of memory. >> > Q. My though here is to increase the # of buffers even though I will >be >> > increasing op sys swapping. The HP9000 seems to handle swapping >quite >> > well. The extra 20 MB of buffers might more than offset the >decrease >> > in performance caused by the extra swapping. ???? >> > No it will just mean more swapping and hence even worse performance. The system will spend more time swapping and less time doing useful work. Accessing memory from swap is generally >100/>1000 times slower than no accessing memory from swap. >> > NB. We see similar problems with performance ( not as pronounced ) >when >> > start a large / lengthy report such as sales analysis reports >> > or price lists. >> > My though is that these reports / GQL are flooding the buffers >with = >> > data >> > not related to OE, and hence the dramatic performance dip and >large >> > increase in the buffwaits statistics. Sounds possible - especially if the GQL user is doing a sequential scan of a large table rather than using an index. >> <snip> >> >> Your reports are probably using PDQ as well. In your current situation, >you >> may improve things by switching PDQ off all together. This will probably >> help balance resource allocation between your order entry, GQL, and >reports. >> Agreed, also check if any indicies are missing and if the GQL query is sensible. In GQL it is very easy to select 1/5 of the database in one query and try to generate a result with million of rows of data. > >Actually, turning PDQ off for a big DSS query will probably have a bigger >impact on OLTP. With PDQ off (PDQPRIORITY=0) the "DSS" query will be >treated as an OLTP query which means it will be given the highest priority >possible within the engine -- on par with "true" OLTP queries. The True but it sounds like the problem is excessive memory utilisation by PDQ queries leading to swapping. The queries may well be scanning large tables when this is not planned. >symptoms described actually makes it sound like you're running with PDQ off >for the big queries. If you are on a single CPU, try setting PDQPRIORITY >to 1 for the "DSS" queries -- this will utilize parallel scans if possible I would first try ot fix the query/database scheme, if you want to use PSQ then agreed I would start with PDQPRIORITY =1. > More importantly this will place the DSS query at a lower priority than >the OLTP users -- which should have a PDQPRIORITY of 0. If you are on a >multi-cpu machine, PDQPRIORITY settings above 2 will begin to enable >further parallel features. >Setting the PDQPRIORITY for a client/server application can be tricky -- in >some case it may not be possible to use anything but the default >PDQPRIORITY set on your onconfig file. Are you sure the GQL sessions are >truly running with a PDQPRIORITY which makes them DSS queries? (try >"onstat -g mgm" while a GQL query is running.) (If you are using OpenLink >ODBC drivers I can tell you how to accomplish this.) Also setting the >PDQPRIORITY in a 4GL application can only be done by preparing and >executing a "set pdqpriority" statement. > >HTH -- David Williams