IO Wait and PDQ

I have a session running against Informix 11.7 FC7 database. The SQL is running a join against 2 tables, each with 10 fragments a piece. 1 of these tables is stored under cooked chunks due to the sensitivity of contained data. The user specifies SET PDQPRIORITY 4 prior to starting the query. When it runs; it just sits in limbo with IO Wait status'. There is very minimal disk activity and the CPU load is almost nonexistent. The session memory consumption is low and there is no parallelism. If the SET PDQPRIORITY is removed from the SQL, the session comes to life and spawns sort threads (although they don't appear to be used; PSORT_NPROCS is set to 4 in our ENV), allocates more memory and runs through the tables serially. What factors could be causing PDQ or Informix to behave this way?

It's going to depend on the totals of all PDQPRIORITY settings currently running. There is only 100% to go around. If the other active PDQPRIORITY sessions added up to more than 96% then this 4% er woukd have to queue up and wait. Art On Oct 30, 2014 6:21 PM, "JACOB SHADIX" <jacobshadix@gmail.com> wrote: > I have a session running against Informix 11.7 FC7 database. The SQL is > running a join against 2 tables, each with 10 fragments a piece. 1 of these > tables is stored under cooked chunks due to the sensitivity of contained > data. > The user specifies SET PDQPRIORITY 4 prior to starting the query. When it > runs; it just sits in limbo with IO Wait status'. There is very minimal > disk > activity and the CPU load is almost nonexistent. The session memory > consumption is low and there is no parallelism. If the SET PDQPRIORITY is > removed from the SQL, the session comes to life and spawns sort threads > (although they don't appear to be used; PSORT_NPROCS is set to 4 in our > ENV), > allocates more memory and runs through the tables serially. What factors > could > be causing PDQ or Informix to behave this way? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160b5f40be80a0506ae3516

Agreed, but I can guarantee this is the only session using PDQ during the test cases.

PDQPRIORITY of 4 is low, Informix will allocate a small amount of memory and with PDQPRIORITY that low will not give much if any paralleism. A quantum of memory is DS_TOTAL_MEMORY / DS_MAX_QUERIES and PDQPRIORTY will determne what percentage of the quantums are allocated to the query (rounding down). http://debian.fmi.uni-sofia.bg/~tomecks/Inf/ch19.htm#Heading12 - secondary_threads = (PDQPRIORITY/100)* number_of_virtual_processors What does onstat -g mem give when the query is running? You may want to start with PDQPRIORTY = 1 to force parallel scans. Regards, David. > On 31 October 2014 at 01:49 Art Kagel <art.kagel@gmail.com> wrote: > > > It's going to depend on the totals of all PDQPRIORITY settings currently > running. There is only 100% to go around. If the other active PDQPRIORITY > sessions added up to more than 96% then this 4% er woukd have to queue up > and wait. > > Art > On Oct 30, 2014 6:21 PM, "JACOB SHADIX" <jacobshadix@gmail.com> wrote: > > > I have a session running against Informix 11.7 FC7 database. The SQL is > > running a join against 2 tables, each with 10 fragments a piece. 1 of these > > tables is stored under cooked chunks due to the sensitivity of contained > > data. > > The user specifies SET PDQPRIORITY 4 prior to starting the query. When it > > runs; it just sits in limbo with IO Wait status'. There is very minimal > > disk > > activity and the CPU load is almost nonexistent. The session memory > > consumption is low and there is no parallelism. If the SET PDQPRIORITY is > > removed from the SQL, the session comes to life and spawns sort threads > > (although they don't appear to be used; PSORT_NPROCS is set to 4 in our > > ENV), > > allocates more memory and runs through the tables serially. What factors > > could > > be causing PDQ or Informix to behave this way? > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0160b5f40be80a0506ae3516 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Jacob=2C As you have 20 fragments in 2 tables=2C it starts many readings simultaneou= sly. That is what i realized in my environment. If you have 1 phisical disk= or few disks it might drive to io wait and in this case the CPU load will = be minimal as you Said. CPU minimal because its just waiting for io. Celso Coimbra Em 30/10/2014 23:20=2C JACOB SHADIX <jacobshadix@gmail.com> escreveu: I have a session running against Informix 11.7 FC7 database. The SQL is running a join against 2 tables=2C each with 10 fragments a piece. 1 of the= se tables is stored under cooked chunks due to the sensitivity of contained da= ta. The user specifies SET PDQPRIORITY 4 prior to starting the query. When it runs=3B it just sits in limbo with IO Wait status'. There is very minimal d= isk activity and the CPU load is almost nonexistent. The session memory consumption is low and there is no parallelism. If the SET PDQPRIORITY is removed from the SQL=2C the session comes to life and spawns sort threads (although they don't appear to be used=3B PSORT_NPROCS is set to 4 in our E= NV)=2C allocates more memory and runs through the tables serially. What factors co= uld be causing PDQ or Informix to behave this way? ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum.

If you are using version 12.10 you can look at onstat -g ioh or sysmaster.sysiohistory table to see that the I/O times are increasing. We now store the last 1 hour of detail I/O history by minute. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/31/2014 12:46:36 PM: > From: "Celso Coimbra" <cccoimbra2004@hotmail.com> > To: ids@iiug.org > Date: 10/31/2014 12:47 PM > Subject: Re: IO Wait and PDQ [34074] > Sent by: ids-bounces@iiug.org > > Jacob=2C > > As you have 20 fragments in 2 tables=2C it starts many readings simultaneou= > sly. That is what i realized in my environment. If you have 1 phisical disk= > or few disks it might drive to io wait and in this case the CPU load will = > be minimal as you Said. CPU minimal because its just waiting for io. > > Celso Coimbra > > Em 30/10/2014 23:20=2C JACOB SHADIX <jacobshadix@gmail.com> escreveu: > I have a session running against Informix 11.7 FC7 database. The SQL is > running a join against 2 tables=2C each with 10 fragments a piece. 1 of the= > se > tables is stored under cooked chunks due to the sensitivity of contained da= > ta. > The user specifies SET PDQPRIORITY 4 prior to starting the query. When it > runs=3B it just sits in limbo with IO Wait status'. There is very minimal d= > isk > activity and the CPU load is almost nonexistent. The session memory > consumption is low and there is no parallelism. If the SET PDQPRIORITY is > removed from the SQL=2C the session comes to life and spawns sort threads > (although they don't appear to be used=3B PSORT_NPROCS is set to 4 in our E= > NV)=2C > allocates more memory and runs through the tables serially. What factors co= > uld > be causing PDQ or Informix to behave this way? > > ***************************************************************************= > **** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >