Using PDQPRIORITY to throttle certain queries

Posted in 2012

Sean Baker — Tue, 4 Sep 2012 15:48:17 -0700 — source: Usenet: comp.databases.informix

Hoping to get some opinions on this. We have a public website that is currently using our main production database (IDS 11.50.FC6). The main query used by the website uses a single table that typically has 50K to 100K rows. The query typically completes in 0.4 to 0.5 seconds. We ran some load tests, and if the site gets very busy, say 20,000 requests per hour, we have some noticeable performance degradation in the production system (mainly an OLTP system, order entry, reporting, menu response, etc.). I'm not very familiar with PDQ, but a colleague suggested we could use PDQPRIORITY to "throttle" the requests coming from the website. These are certainly not DSS queries, so it seems to me that PDQ is not really intended for that. Is "throttling" certain db queries a common use of PDQ? Thanks, Sean.

↪ replying to Sean Baker

Jason Harris — Tue, 4 Sep 2012 16:41:29 -0700 (PDT) — source: Usenet: comp.databases.informix

On Wednesday, 5 September 2012 06:48:25 UTC+8, Sean Baker wrote: > Hoping to get some opinions on this. > > We have a public website that is currently using our main production database (IDS 11.50.FC6). The main query used by the website uses a single table that typically has 50K to 100K rows. The query typically completes in 0.4 to 0.5 seconds. We ran some load tests, and if the site gets very busy, say 20,000 requests per hour, we have some noticeable performance degradation in the production system (mainly an OLTP system, order entry, reporting, menu response, etc.). > > I’m not very familiar with PDQ, but a colleague suggested we could use PDQPRIORITY to “throttle” the requests coming from the website. These are certainly not DSS queries, so it seems to me that PDQ is not really intended for that. > > Is “throttling” certain db queries a common use of PDQ? > > Thanks, > > Sean. Hi Seam, PDQ can be used for that. For example, setting a PDQ of 25% for a query means that only 4 can run at one time. Any new queries "gate" or wait before proceeding. Unless you designed your app and data model for PDQ is not likely to help. Your problem is probably related to listener threads, with the OLTP users not able to get their requests in during busy times. How have you set them up? You may be better off running a single dedicated NETVP listener for the web server, and running multiple CPUVP listeners for the OLTP queries. Processor affinity can also be used to limit the web server listener to a single CPU. HTH, Jason