Is PDQ only for DSS?

Posted in 2017

FLAVIO CARDOSO — Fri, 13 Oct 2017 16:09:13 — source: IIUG Forums & Mailing Lists

Hello all!! IBM's docs says "Parallel database query (PDQ) is a database server feature that can improve performance dramatically when the server processes queries that decision-support applications initiate." (source: https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.perf.doc/ids_ prf_578.htm". So, is PDQ only for DSS or can I use it for an OLTP instance as well? Cheers!

↪ replying to FLAVIO CARDOSO

Fernando Nunes — Fri, 13 Oct 2017 23:34:08 — source: IIUG Forums & Mailing Lists

Hi, An OLTP workload hardly takes advantage of PDQ. But many OLTP instances also have DSS like processes, so let's assume we don't live in a black and white world... There are many shades of grey (the color, not the person...). If you need to do some maintenance work PDQ can also help. But if you're consider activating PDQ for an OLTP application, don't! The way PDQ works, can have serious impact on the system. The most notorious is that once the system decides it will use PDQ, if there are no PDQ resources available, the query will hang!. It will NOT fallback to non-PDQ. So... if you're having performance issues on an OLTP instance, and you just noticed the sentence "improve performance dramatically", that's the wrong path. Regards. On Fri, Oct 13, 2017 at 5:09 PM, FLAVIO CARDOSO <flavio@null.net.br> wrote: > Hello all!! > > IBM's docs says "Parallel database query (PDQ) is a database server feature > that can improve performance dramatically when the server processes queries > that decision-support applications initiate." (source: > https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70. > 0/com.ibm.perf.doc/ids_prf_578.htm". > > So, is PDQ only for DSS or can I use it for an OLTP instance as well? > > Cheers! > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

↪ replying to Fernando Nunes

jmiller@jfmiii.com — Sun, 15 Oct 2017 17:32:27 — source: IIUG Forums & Mailing Lists

I will agree with Fernando but add that there is one small pa= rt of the PDQ which has been adapted to OLTP. This is the amoun= t of sort memory an OLTP application can use, by default the amount of memo= ry an OLTP application can use before doing a disk sort is very small (I th= ink it is 256KB). You can use DS_NONPDQ_QUERY_MEM to increase the upp= er bound on sort memory of SQL operations which have a PDQ of 0. If y= ou have plenty of memory I would start at a couple of MB. There= are sysmaster tables which you can monitor to see how this is tuning is he= lping you. John Miller =0A =0A =0A------= -- Original Message -------- =0ASubject: Re: Is PDQ only for DSS? [4007= 5] =0AFrom: "Fernando Nunes" <[1]domusonline@gmail.com> =0ADate: Fri, October 13, 2017 4:34 pm=0ATo: [2]ids@iiug.org =0A =0AHi, = =0A =0AAn OLTP workload hardly takes advantage of PDQ. But many OLTP= instances =0Aalso have DSS like processes, so let's assume we don't li= ve in a black and =0Awhite world... There are many shades of grey (the = color, not the person...). =0A =0AIf you need to do some maintenance= work PDQ can also help. But if you're =0Aconsider activating PDQ for a= n OLTP application, don't! =0A =0AThe way PDQ works, can have seriou= s impact on the system. The most =0Anotorious is that once the system d= ecides it will use PDQ, if there are no =0APDQ resources available, the= query will hang!. It will NOT fallback to =0Anon-PDQ. =0A =0ASo= ... if you're having performance issues on an OLTP instance, and you just <= br>=0Anoticed the sentence "improve performance dramatically", that's the w= rong =0Apath. =0A =0ARegards. =0A =0AOn Fri, Oct 13, 2017= at 5:09 PM, FLAVIO CARDOSO <[3]flavi= o@null.net.br> wrote: =0A =0A> Hello all!! =0A> =0A> IBM's docs says "Parallel database query (PDQ) is a database serve= r feature =0A> that can improve performance dramatically when the se= rver processes queries =0A> that decision-support applications initi= ate." (source: =0A> [4]https://www.ibm.com/support/knowledgecenter/SSGU8G_11= .70. =0A> 0/com.ibm.perf.doc/ids_prf_578.htm". =0A> = =0A> So, is PDQ only for DSS or can I use it for an OLTP instance as wel= l? =0A> =0A> Cheers! =0A> =0A> =0A> ****= ******************************************************** =0A> ******= ************* =0A> Forum Note: Use "Reply" to post a response in the= discussion forum. =0A> =0A> =0A =0A-- =0AFernando= Nunes =0APortugal =0A =0A[5]http://informix-technology.blogspot.com =0AMy email w= orks... but I don't check it frequently... =0A =0A =0A***********= ******************************************************************** = =0A Forum Note: Use "Reply" to post a response in the discussion forum. =0A =0A=0A =0A References 1. 3D"mailto:domusonline@gmail.com= 2. 3D"mailto:ids@iiug.org" 3. 3D"mailto:flavio@null.net.br" 4. 3D"https://www.ibm.com/support/knowledge= 5. 3D"http://informix-technology.=/

↪ replying to Fernando Nunes

FLAVIO CARDOSO — Mon, 16 Oct 2017 10:05:43 — source: IIUG Forums & Mailing Lists

Thank you Fernando. It helped me a lot. Best regards!

↪ replying to jmiller@jfmiii.com

FLAVIO CARDOSO — Mon, 16 Oct 2017 10:09:19 — source: IIUG Forums & Mailing Lists

Thank you John. I'll consider that too. You guys helped me a lot. Best regards!