Oninit cdi / Archive
Talk to an expert →

DS_NONPDQ_QUERY_MEM not changing

Posted in 2018

Summary

User on 12.10.FC7 ran 'onmode -wm DS_NONPDQ_QUERY_MEM=51240' but 'onstat -g mgm' kept showing 256 KB. Suggestions: check online.log for recalculation messages (the parameter is capped at 25% of DS_TOTAL_MEMORY), and query sysmaster:syscfgtab for the effective value. That query showed cf_original/cf_effective = 51240, and online.log confirmed the change, so the setting was actually applied; others reported onstat -g mgm reflecting changes correctly on FC8/FC9 and 11.70, so the stale display was judged a defect in FC7 (reporting only, not a functional problem). No PMR/fix cited beyond upgrading.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Performance & Tuning, Server Administration, Security, Permissions & Auditing, Versions, Editions & End-of-Life

R MICHAEL — — source: IIUG Forums & Mailing Lists

Hi, and Happy New Year everybody I've tried to increase DS_NONPDQ_QUERY_MEM value because of CPU io wait issue . The performance improved since the value increased from 128 to 256 but I've set a much higher value. Still: [informix@server1 ~]$ onmode -wm DS_NONPDQ_QUERY_MEM=51240 DS_NONPDQ_QUERY_MEM is already set to 51240. [informix@informix01ha-dal09-prod ~]$ onstat -g mgm IBM Informix Dynamic Server Version 12.10.FC7 -- On-Line (Prim) -- Up 24 days 05:35:11 -- 5343628 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 20 DS_MAX_SCANS: 1048576 DS_NONPDQ_QUERY_MEM: 256 KB DS_TOTAL_MEMORY: 250000 KB Queries: Active Ready Maximum 0 0 20 Memory: Total Free Quantum (KB) 250000 250000 12496 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 31250 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 IBM says the following: Use the DS_NONPDQ_QUERY_MEM configuration parameter to increase the amount of memory that is available for a query that is not a Parallel Database Query (PDQ). (You can only use this parameter if PDQ priority is set to zero.) If you specify a value for the DS_NONPDQ_QUERY_MEM parameter, determine and adjust the value based on the number and size of table rows. I don't know how to underline "You can only use this parameter if PDQ priority is set to zero" Even so, I don't see a reason which my value is not set. Any help would be appreciated Thank you!

↪ replying to R MICHAEL
david@smooth1.co.uk — — source: IIUG Forums & Mailing Lists

Hi, Change the value and then check the online.log file. As per https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.adref.doc/i ds_adr_0064.htm "values From the default value to 25 percent of the value of DS_TOTAL_MEMORY" "If during the processing of the DS_NONPDQ_QUERY_MEM, the database server changes the value that you set, the server sends a message in this format: DS_NONPDQ_QUERY_MEM recalculated and changed from old_value Kb to new_value Kb." 51240 sounds like a very high value, the largest I have seen was 2048 in a bug report! - https://www.cursor-distribution.de/en/component/buglist/?lang_id=de&get_bug=IC55 409 Try with a smaller value say 500, keep increasing by 500 and check the online.log. Regards, David. > On 04 January 2018 at 16:31 R MICHAEL <mboncalo@gmail.com> wrote: > > > Hi, and Happy New Year everybody > > I've tried to increase DS_NONPDQ_QUERY_MEM value because of CPU io wait issue > .. The performance improved since the value increased from 128 to 256 but I've > set a much higher value. > Still: > > [informix@server1 ~]$ onmode -wm DS_NONPDQ_QUERY_MEM=51240 > DS_NONPDQ_QUERY_MEM is already set to 51240. > [informix@informix01ha-dal09-prod ~]$ onstat -g mgm> > IBM Informix Dynamic Server Version 12.10.FC7 -- On-Line (Prim) -- Up 24 days > 05:35:11 -- 5343628 Kbytes> > Memory Grant Manager (MGM) > -------------------------- > > MAX_PDQPRIORITY: 100 > DS_MAX_QUERIES: 20 > DS_MAX_SCANS: 1048576 > DS_NONPDQ_QUERY_MEM: 256 KB > DS_TOTAL_MEMORY: 250000 KB > > Queries: Active Ready Maximum > > 0 0 20 > > Memory: Total Free Quantum > (KB) 250000 250000 12496 > > 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 31250 > 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 > > IBM says the following: > > Use the DS_NONPDQ_QUERY_MEM configuration parameter to increase the amount of > memory that is available for a query that is not a Parallel Database Query > (PDQ). (You can only use this parameter if PDQ priority is set to zero.) If > you specify a value for the DS_NONPDQ_QUERY_MEM parameter, determine and > adjust the value based on the number and size of table rows. > > I don't know how to underline "You can only use this parameter if PDQ priority > is set to zero" > > Even so, I don't see a reason which my value is not set. > > Any help would be appreciated > > Thank you! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to R MICHAEL
Fernando Nunes — — source: IIUG Forums & Mailing Lists

SELECT * FROM sysmaster:syscfgtab WHERE cf_name = 'DS_NONPDQ_QUERY_MEM' Check the effective value. On Jan 4, 2018 5:32 PM, "R MICHAEL" <mboncalo@gmail.com> wrote: > Hi, and Happy New Year everybody > > I've tried to increase DS_NONPDQ_QUERY_MEM value because of CPU io wait > issue > .. The performance improved since the value increased from 128 to 256 but > I've > set a much higher value. > Still: > > [informix@server1 ~]$ onmode -wm DS_NONPDQ_QUERY_MEM=51240 > DS_NONPDQ_QUERY_MEM is already set to 51240. > [informix@informix01ha-dal09-prod ~]$ onstat -g mgm> > IBM Informix Dynamic Server Version 12.10.FC7 -- On-Line (Prim) -- Up 24> days > 05:35:11 -- 5343628 Kbytes > > Memory Grant Manager (MGM) > -------------------------- > > MAX_PDQPRIORITY: 100 > DS_MAX_QUERIES: 20 > DS_MAX_SCANS: 1048576 > DS_NONPDQ_QUERY_MEM: 256 KB > DS_TOTAL_MEMORY: 250000 KB > > Queries: Active Ready Maximum > > 0 0 20 > > Memory: Total Free Quantum > (KB) 250000 250000 12496 > > 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 31250 > 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 > > IBM says the following: > > Use the DS_NONPDQ_QUERY_MEM configuration parameter to increase the amount > of > memory that is available for a query that is not a Parallel Database Query > (PDQ). (You can only use this parameter if PDQ priority is set to zero.) If > you specify a value for the DS_NONPDQ_QUERY_MEM parameter, determine and > adjust the value based on the number and size of table rows. > > I don't know how to underline "You can only use this parameter if PDQ > priority > is set to zero" > > Even so, I don't see a reason which my value is not set. > > Any help would be appreciated > > Thank you! > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to R MICHAEL
JACQUES RENAUT — — source: IIUG Forums & Mailing Lists

Original post: Hi, and Happy New Year everybody I've tried to increase DS_NONPDQ_QUERY_MEM value because of CPU io wait issue . The performance improved since the value increased from 128 to 256 but I've set a much higher value. Still: [informix@server1 ~]$ onmode -wm DS_NONPDQ_QUERY_MEM=51240 DS_NONPDQ_QUERY_MEM is already set to 51240. [informix@informix01ha-dal09-prod ~]$ onstat -g mgm IBM Informix Dynamic Server Version 12.10.FC7 -- On-Line (Prim) -- Up 24 days 05:35:11 -- 5343628 Kbytes <onstat -g mgm output removed> IBM says the following: Use the DS_NONPDQ_QUERY_MEM configuration parameter to increase the amount of memory that is available for a query that is not a Parallel Database Query (PDQ). (You can only use this parameter if PDQ priority is set to zero.) If you specify a value for the DS_NONPDQ_QUERY_MEM parameter, determine and adjust the value based on the number and size of table rows. I don't know how to underline "You can only use this parameter if PDQ priority is set to zero" Even so, I don't see a reason which my value is not set. Any help would be appreciated Thank you! Reply: A quick test would indicate it's possibly a defect in the version you are running. The first 12.10 instance I was able to find was a 12.10.FC8W1 version. I ran onstat -g mgm and noted what DS_NONPDQ_QUERY_MEM was set to. I then ran onmode -wm DS_NONPDQ_QUERY_MEM to modify the value, and then ran onstat -g mgm and I saw my new updated value. I took a quick look to see if Icould find a defect about it, but couldn't see one. Jacques Renaut HCL Informix

↪ replying to R MICHAEL
BENJAMIN THOMPSON — — source: IIUG Forums & Mailing Lists

I would concur with Jacques that it probably is a defect. I recall trying unsuccessfully to change PDQ related values myself via "onmode -wm" in an older version but never raised a PMR about it. However it worked in a 11.70.FC7W1/12.10.FC9 test instances for me just now. It has for a long time being possible to set PDQ values dynamically (before "onmode -wm") using "onmode -D", "onmode -M", "onmode -Q" and "onmode -S" so in my case I successfully used one of these where "onmode -wm" failed. I suspect these are largely redundant/duplicating the more general purpose "onmode -wm/-wf" now. However none of the old onmode switches covers DS_NONPDQ_QUERY_MEM which, as I recall, came along later than PDQ in general, so this won't help you. Ben.

↪ replying to BENJAMIN THOMPSON
ART KAGEL — — source: IIUG Forums & Mailing Lists

It's working for me in 12.10.FC8W2: $ onstat -g mgm IBM Informix Dynamic Server Version 12.10.FC8W2 -- On-Line -- Up 00:01:23 --165016 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 2 DS_MAX_SCANS: 1048576 DS_NONPDQ_QUERY_MEM: 256 KB DS_TOTAL_MEMORY: 10240 KB Queries: Active Ready Maximum 0 0 2 Memory: Total Free Quantum (KB) 10240 10240 5120 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 1280 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 informix@Elbereth:/home/art/Desktop$ onmode -wm DS_NONPDQ_QUERY_MEM=2560 Value of DS_NONPDQ_QUERY_MEM has been changed to 2560 kilobytes. informix@Elbereth:/home/art/Desktop$ onstat -g mgm IBM Informix Dynamic Server Version 12.10.FC8W2 -- On-Line -- Up 00:01:39 --165016 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 2 DS_MAX_SCANS: 1048576 DS_NONPDQ_QUERY_MEM: 2560 KB DS_TOTAL_MEMORY: 10240 KB Queries: Active Ready Maximum 0 0 2 Memory: Total Free Quantum (KB) 10240 10240 5120 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 1280 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

↪ replying to R MICHAEL
R MICHAEL — — source: IIUG Forums & Mailing Lists

Hi, I don't know why my reply wasn't sent.. @David, I see the messages in online.log that "Value of DS_NONPDQ_QUERY_MEM has been changed to .." but the value doesn't change in onstat -g mgm, no matter what value I set @Fernando , the query shows the new value cf_id 220 cf_name DS_NONPDQ_QUERY_MEM cf_flags 294978 cf_original 51240 cf_effective 51240 cf_default 256 @Jacques, Fernando so I guess that the new configured value is used but I won't see it in onstat output until I will upgrade to FC8

Related threads

Glossary