Re: Informix and PDQ

In article <5jue9j$p30$1@news7.gte.net>, Paul Drake <pdrake1@gte.net> writes > >> I assumed that since for most of our queries we see almost no speed >> increase from a setting of 5 to 40, we could run 20 queries at 5. Nope, >> no show. Informix recomends sending one query at a time throught the >> engine at PDQ 100. It seems that multiple queries really freak the >> engine out (8.1x). > >The reason that there is no difference in speed at times when increasing >PDQ appears to be because of some calculation the optimizer does when >deciding what to do with the memory allocated to it. > Correct. >When doing just a sequential scan, memory is not needed for that query, >just scan threads. But when doing a hash join, it obviously does, and the >query is given an amount of memory based on what PDQ is set at. And while Correct - it depends upon PDQ parameters (including things like DS_MAX_SCANS etc.) The exact formula for memory usage vs PDQPRIORITY setting is NOT defined.. >it's scanning the tables, most of that memory is used. However, when doing >the join, despite wishing it would use all of the memory given to it, it >doesn't. > >Example: We had a large join query with PDQ set to 25, and I noticed that >after scanning the 1st table (making the hash table), it did not scan the >2nd table in parallel with doing the join as it usually does. When it >finished scanning the 2nd table, it used none of the memory (onstat -g mgm >-- tells how much memory is given and how much is actually used). It used >the memory when scanning the tables and making the hash table, but the time >it took for that was only 7% of the total query time. We re-did the query >with PDQ of 40. More memory was given, but still not used. Finally at PDQ >of 70 it finally used the memory, and the time went from 9 hrs all the way >down to 1hr 45mins (of course, by this time we realized that >DS_TOTAL_MEMORY was a lot less that what we thought it was). >Another query was at PDQ 20, and was using just over half of the allocated >memory. By setting it to 25, it then used twice as much as before, since >more was now allocated to it. I haven't checked to see if it always uses >memory in increments of a certain size; I'm assuming this isn't the case, >but is based on many variables, such as the size of the hash table. > >Conclusion: Raising PDQ will give the query more resources, but the query Correct. >may not use the resources fully. So we can't make the rule that doubling >PDQ will make the query run twice as fast; there might be hardly any >change (a slight increase in speed when scanning the tables/making the hash >table), or there could be way more than 2 times an increase, depending on >however it determines how much memory to actually use. > There are many factors that decided the final memory usage...unfortunately informix does not document how the formulae used. -- David Williams