Pivotal Knowledge Base

Follow

Queries are run with lower priority on segments when compared to master (gp_segworker_relative_priority)

Problem

Queries that has been run from master, when dispatched/executed on the segments are run with lower priority than the master.

For eg

Assuming there was an insert query run from the master, it corresponding process id and the Nice value is "0".

On Master.

gpadmin:Fullrack@mdw $ top -n1 -cb -p 3782
top - 03:27:47 up 79 days, 14:27,  2 users,  load average: 15.33, 15.71, 15.85
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s): 10.1%us, 20.8%sy,  0.0%ni, 68.9%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  49316552k total, 48825284k used,   491268k free,   354668k buffers
Swap: 50331604k total,     1312k used, 50330292k free, 44323108k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3782 gpadmin   15   0  443m  15m  10m S  0.0  0.0   0:00.03 postgres: port  4300, gpadmin gpadmin [local] con14 [local] cmd4 INSERT

But on segments, the same connection (con14) is running with lower priority or nice value (19) as indicated in the below message.

On segments.

[gpadmin@sdw10 ~]$ top -n1 -cb -p 28883
top - 06:29:14 up 205 days,  6:35,  3 users,  load average: 2.74, 1.54, 0.62
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  49316552k total, 49180648k used,   135904k free,   149476k buffers
Swap: 50339636k total,     1420k used, 50338216k free, 35266212k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
28883 gpadmin   34  19  419m 135m 132m S 39.9  0.3   0:52.93 postgres: port 43006, gpadmin gpadmin 172.28.8.250(39437) con14 seg5 cmd5 MPPEXEC INSERT

Solution

This is controlled by a parameter "gp_segworker_relative_priority".

Please note: Before changing or altering this parameter, by default GPDB runs service processes at the same priority as the query processes. When a segment is overloaded with CPU-intensive queries, it may become unresponsive. This can result in connection timeouts e.g. the fault prober.

To overcome this, the parameter "gp_segworker_relative_priority" was introduced, this control the priority at which query processes are started, this allows DB admins to downgrade the priority of query processes relative to the postmaster process by specifying higher values for the GUC i.e the higher the value, the lower the priority of the query processes. For example a value of 5 would mean that the query processes will be started at a renice level which is 5 levels below that of the postmaster process.

So altering this parameter is never recommended and should only be done with throughout testing of your system so that it doesn't effect your day to day operation.

Comments

Powered by Zendesk