Pivotal Greenplum Database (GPDB) all versions
Queries that have been run from the master when dispatched/executed on the segments are run with lower priority than the master.
Assuming there was an insert query run from the master, it corresponding process id and the Nice value is "0".
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.
[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
This is controlled by a parameter "gp_segworker_relative_priority".
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 affect your day to day operation.