Pivotal Knowledge Base

Follow

Work Load Manager (WLM) Query running non-stop against the Segment Database

Environment

 Product  Version
 Pivotal Greenplum  4.3.x
 Work Load Manager (WLM)  1.6.0

Symptom

Greenplum WLM keeps polling queries on the database even when there are no rules created.

Master Log Message: 

2017-02-07 15:28:06.563949 EST,"gpadmin","postgres",p573218,th441334720,"10.xx.xx.xx","46198",2017-02-07 15:27:52 EST,0,con81743,cmd420,seg3,slice1,,,,
"LOG","00000","duration: 5.419 ms statement: SELECT SUM(gpwf.size) AS size, SUM(gpwf.numfiles)
AS numfiles FROM gp_toolkit.gp_workfile_usage_per_query gpwf WHERE gpwf.sess_id = '81741'",,,,,,"SELECT SUM(gpwf.size) AS size, SUM(gpwf.numfiles) AS numfiles FROM gp_toolkit.gp_workfile_usage_per_query gpwf
WHERE gpwf.sess_id = '81741'",0,,"postgres.c",1418,
2017-02-07 15:28:07.578236 EST,"gpadmin","postgres",p573218,th441334720,"10.xx.xx.xx","46198",2017-02-07 15:27:52 EST,0,con81743,cmd422,seg3,slice1,,,,
"LOG","00000","duration: 6.841 ms statement: SELECT SUM(gpwf.size) AS size, SUM(gpwf.numfiles)
AS numfiles FROM gp_toolkit.gp_workfile_usage_per_query gpwf WHERE gpwf.sess_id = '81642'",,,,,,"SELECT SUM(gpwf.size) AS size, SUM(gpwf.numfiles) AS numfiles FROM gp_toolkit.gp_workfile_usage_per_query gpwf
WHERE gpwf.sess_id = '81642'",0,,"postgres.c",1418,
2017-02-07 15:28:08.107675 EST,"gpadmin","postgres",p573218,th441334720,"10.xx.xx.xx","46198",2017-02-07 15:27:52 EST,0,con81743,cmd432,seg3,slice1,,,,
"LOG","00000","duration: 8.351 ms statement: SELECT SUM(gpwf.size) AS size, SUM(gpwf.numfiles)
AS numfiles FROM gp_toolkit.gp_workfile_usage_per_query gpwf WHERE gpwf.sess_id = '81740'",,,,,,"SELECT SUM(gpwf.size) AS size, SUM(gpwf.numfiles) AS numfiles FROM gp_toolkit.gp_workfile_usage_per_query gpwf
WHERE gpwf.sess_
id = '81740'",0,,"postgres.c",1418, 2017-02-07 15:28:07.119545

Cause 

The frequent polling happens because the gpdb_stats collect_frequency is set to 1 second, which results in 1 workfile query per session per second.

config show gpdb_stats collect_frequency 
collect_frequency = 1

Resolution

The workaround for this is to the modify the gpdb_stats collect_frequency to a higher number, 60 is the maximum at the WLM prompt. This would give the user more time before the stats are collected but also still collects the stats. Also, note that collect_frequency is directly proportional to the rule execution time. So increasing it a lot would delay the rule execution. 

Additional Information

Refer to this documentation for more details: Configurable Workload Manager Settings.

 

Comments

Powered by Zendesk