RE: PDQ_PRIORITY parameter!

PDQ is 100 words or less. This may even be reasonably accurate. When you run a query, issue a SET PDQPRIORITY x where x is some valus between 0 and 100; You can also export the environment variable PDQPRIORITY=x I'm not going to try to analyze what you've set it to here without knowing more. When assigning PDQ, keep some things in mind. PDQPRIORITY (after the initial setting of 1) controls how much memory is allocated for your query. (your setting/100)*(MAX_PDQPRIORITY/100)=the percentage of memory allocated. Since you've set MAXPDQ to 100, think of your PDQ setting as a percentage - 10 means 10% of memory. Higher values of PDQ (under version 7) can yield more parallelism, but 1 is enough to turn it on. Everybody who uses PDQ will be competing for a percentage of the same 512KB of memory - (which isn't enough to get out of bed for - if you want to do serious PDQ memory, increase your DS_TOTAL_MEMORY to 60-90% of your SHMEM - depending on your need - of course this will probably mean lowering the number of buffers). PDQ memory is critical for hash joins, and can be very useful for index builds, group by seems to use it as well - although I don't understand why yet. You can calculate the amount of memory needed to run a hash join in memory without overflowing to temp as: 1 - figure out the 'build' table, which table will be scanned first and used to build the hash table 2 - figure out the key for the join Required memory = (32 + keysize + rowsize) * nrows So if you are joining 100,000 rows against 1,000,000 rows. The row size of the smaller table is 54, and the key is an integer (length 4) you will need roughly: (32 + 4 + 54) * 100,000 = 9,000,000 or 9MB of memory. If you had 250MB of DS_TOT_MEMORY, this would be roughly 1/25th or a PDQPRIORITY=4. Memory is actually allocated in quantums, in your case 128. So the memory allocated to your query will be a multiple of that quantum. If your hash table fits into memory, your query will scream. If you hash table is less than 2x the amount of memory you need, the overflow won't be that bad. If your hash table is over 2x, the overflow will seriously degrade your query. That is a rule of thumb pulled out of my experience or nether regions. If you are going to have overflow, make sure you have sufficient TEMP spindles to handle the overflow. There are one or two things about 7 and PDQ that have changed and which surprised me the last time, like an extra PDQ setting in a config file somewhere, but I can't think of it at present. have fun. cheers j. > -----Original Message----- > From: Murat YILDIZ [mailto:murat-y@usa.net] > Sent: Friday, January 12, 2001 7:09 AM > To: informix-list@iiug.org > Subject: PDQ_PRIORITY parameter! > > > I have these settings for PDQ: > > MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority > DS_MAX_QUERIES # Maximum number of decision support> queries > DS_TOTAL_MEMORY # Decision support memory (Kbytes) > DS_MAX_SCANS 1048576 # Maximum number of decision support> scans > DATASKIP off # List of dbspaces to skip> > onstat -g mgm output:> > Informix Dynamic Server Version 7.31.FC7 -- On-Line -- Up 2 days > 00:41:20 -- 1307232 Kbytes > > Memory Grant Manager (MGM) > -------------------------- > > MAX_PDQPRIORITY: 100 > DS_MAX_QUERIES: 4 > DS_MAX_SCANS: 1048576 > DS_TOTAL_MEMORY: 512 KB > > Queries: Active Ready Maximum > 0 0 4 > > Memory: Total Free Quantum > (KB) 512 512 128 > > Scans: Total Free Quantum > 1048576 1048576 1 > > Load Control: (Memory) (Scans) (Priority) (Max Queries) > (Reinit) > Gate 1 Gate 2 Gate 3 Gate 4 > Gate 5 > (Queue Length) 0 0 0 > 0 0 > > Active Queries: None > > Ready Queries: None > > Free Resource Average # Minimum # > -------------- --------------- --------- > Memory 0.0 +- 0.0 64 > Scans 0.0 +- 0.0 1048576 > > Queries Average # Maximum # Total # > -------------- --------------- --------- ------- > Active 0.0 +- 0.0 0 0 > Ready 0.0 +- 0.0 0 0 > > Resource/Lock Cycle Prevention count: 0 > > > I haven't seen any PDQ.What does this mean now.Should I change PDQ > parameters or is there no need?How can I make queries running in > parallel?Thanks... > > > Sent via Deja.com > http://www.deja.com/ >