NPS 7.X Managing Workloads

NPS 7.X Managing Workloads

Concurrent Jobs :  netezza 的并发数默认是 48 ,可以更改这个默认值 重启 nzsystem resume 生效
Netezza imposes a limit on the number of concurrent jobs that can run on the system at one time.
The limit is controlled by the system registry setting gkMaxConcurrent, which has a default value of 48.

Specify a maximum concurrent jobs setting of 20: nzsystem set -arg host.gkMaxConcurrent=20

相关概念 :

1. Short query bias(SQB) :  A special reserve of resources (that is, scheduling “slots”, memory, and
       preferential queue placement) for short queries. Short queries are those
       estimated to run in two seconds or less(默认2秒). The time limit is a configurable setting.

关于 SQB 的默认配置 :  /nz/data/postgresql.conf ,查看当前的配置 nzsystem showRegistry | grep -i  
Netezza 是倾向于时间较短的查询任务 . 对于SQB 的sql 会专门保留少许资源(host+spu 的内存,snippet scheduler slots ,GRA scheduler slot )来支持SQB
=Short Query Bias Registry Settings :

host.schedSQBEnabled          bool  true    Enables SQB.
host.schedSQBNominalSecs       int   2      Defines the time threshold for queries that the system defines as “short” in seconds.
host.schedSQBReservedGraSlots  int  10      Defines the number of GRA scheduler slots reserved for short queries.
host.schedSQBReservedSnSlots   int   6      Defines the number of snippet scheduler slots reserved for short queries.
host.schedSQBReservedSnMb      int  50   Specifies how much memory in MB on each SPU to reserve for short query execution.
host.schedSQBReservedHostMb    int  64    Specifies how much memory in MB to reserve on the on the host for short query execution.

2.  Guaranteed resource allocation (GRA)  :  A minimum and/or maximum percentage of the system resources assigned to specific groups of users.
                                             the GRA scheduler 可以确保 RSG 组可以至少拥有minimum的硬件资源,当其他 RSG 空闲的时候,是可以拥有大于 minimum ,
 resource sharing groups(RSGs).    但不超多 maximum 的资源.
    
    *** note1 :
    使用 NzAdmin interface 来配置用户组的时候 ,对于所有的RSG的 maximum 之和是不能大于 100 的,  然后 SQL commands 却可以.但是没有多大意义 .
       假如有三个 RSG 的 Minimum Resource %  分配如下 :
   Group   Minimum Resource %      Maximum Resource %            (( 事实分配的Minimum Resource % ))
   Analysts   70       100                          70 / (70+40+30) = 50%
   RptQuery   40       60                           40 / (70+40+30) = 28.6%
   Public   30       80                           30 / (70+40+30) = 21.4%

    *** note2 :
     对于 Netezza admin user , By default, the admin user is allocated 100 resource sharing points.
     如果分配了资源组,那么Netezza admin user会得到 50% 的资源( 因为其他资源组设置了100%的资源 + Netezza admin user的 100% , 100/ (100+100) ).
  如果其他资源组idle,那么Netezza admin user可能会拥有100% 的资源.
     Note: You can create user accounts with administrative privileges to share the capabilities
     of the admin user, but without the default resource impact of the admin account.
           Do not let your users run as the admin user for their work. Instead, create an administrative users RSG (for example, NzAdmins)
     with an appropriate resource percentage and the correct object and administrative permissions

 2.1  Allocations for Multiple Jobs in the Same Group
         You can also configure a limit on the number of active jobs from an RSG to ensure that a specific number of
   active jobs have reasonable resource allocations; any additional jobs will wait until the active jobs finish.

   You can use the JOB MAXIMUM attribute of the group definition to control the number of actively running jobs submitted by that group
  
  1) : A value of 0 (or OFF) specifies that the group has no maximum for the number of concurrent jobs. The group is restricted by the usual  
      system settings and controls for concurrent jobs.
  2) : A value of 1 to 48 to set the job maximum to the specified integer value.
  3) : A value of -1 (or AUTOMATIC) specifies that the system will calculate a job maximum value based on the group’s resource minimum  
       multiplied by the number of GRA scheduler slots (default 48). For example, if a group has a resource minimum of 20%, the
             job maximum is (.20 * 48) or approximately 9.
  
   ALTER GROUP [JOB MAXIMUM limit]
 
    2.2  Priority and GRA Resource Sharing (权重看起来是在用户层面设置 , RESOURCE MINIMUM/MAXIMUM 是在资源组上分配,每个资源组上还可以设置并发数)
         Netezza uses the host.snPriorityWeights registry setting to specify the relative weights for each priority job;
  
  the setting controls the ratio of resources to priority. By default, the host.snPriorityWeights setting is 1,2,4,8,
  which means that low priority jobs have a weight of 1, normal=2, high=4, and critical=8. (这个权重是可以修改的--host.snPriorityWeight,如 2,3,4,5)
  As with the GRA percentages, the Netezza system sums the weights of all the concurrent jobs and allocates resource percentages
        based on the job weight over the sum of the weights.
  
  assume that there is one critical job and one normal job. The Netezza system uses a default 8:2 weighting ratio (or 4:1) to allocate
        resources between critical and normal priority jobs(Analysts group has 50% resouce of NZ SYSTEM).
            Critical job = 8/(8+2) or 80% of the resources for the Analysts group
   Normal job = 2/10 or 20% of the resources for the Analysts group
  Thus, the critical priority job would receive 80% of the Analysts group’s 50% allocation, for
  a total of 40% of the resources. The normal job would receive 20% of the group’s 50% of resources, which is 10%.
 
CREATE GROUP name ....
[RESOURCE MINIMUM resourcepercent]
[RESOURCE MAXIMUM resourcepercent]...
     
CREATE USER username
[WITH
[PASSWORD {'string' | NULL }]
[SYSID uid]
[ROWSETLIMIT [integer ]
[IN GROUP groupname [, ...] ]
[VALID UNTIL 'date' ]
[SESSIONTIMEOUT [integer ]
[QUERYTIMEOUT [integer ]
[DEFPRIORITY [critical|high|normal|low|none]]
[MAXPRIORITY [critical|high|normal|low|none]]
[IN RESOURCEGROUP resourcegroupname]
]

DEFPRIORITY : Specifies the default priority for the user. The valid priorities are critical,high, normal, low.
MAXPRIORITY : Specifies the maximum priority for the user.
  
   2.3  Guaranteed Resource Allocation Settings 
  host.schedGRAEnabled   bool   True Enables or disables GRA.
  host.snPriorityWeights   1,2,4,8  Specifies the weights assigned to low, normal,high, and critical jobs.
  host.graVtUpdateInterval  600   Specifies the seconds between updates to the _vt_sched_gra table.
  
   2.4  Tracking GRA Compliance  && Monitoring Resource Utilization
    
_v_sched_gra_ext          Displays information about how busy the system is and how GRA resources are being allocated.
_v_sched_sn_ext           Displays information about how busy the system is and how snippet resources are being allocated.
_v_gra_sched_ext_latest   Contains previous 10 minutes of scheduling information in the GRA scheduler.
_v_sched_sn_ext_latest    Contains previous 10 minutes of scheduling information in the snippet scheduler.
_v_plan_resource          Resource usage over time, keeps at least 2000 records of query plans.
_v_system_util            Shows system utilization for host, SPU CPU, disk, memory resources. Updated every 60 seconds.   

3.  Managing PQE ( Prioritized query execution (PQE)  --2.2  Priority and GRA Resource Sharing )

  You assign priority to jobs in several ways:
  1) You can assign a priority to a group of users; each user inherits the priority of the group.
        2) You can assign priority to a user, which can override a priority specified for the user’s group(s).
        3) You can assign a priority as a system default priority for any users who do not have a priority set by their group or account.

    The admin user as well as permitted users can change the priority of a running job. You can raise the job’s priority, or decrease a job’s priority.You can use the nzsession priority command or the ALTER SESSION command to change the priority for all jobs for that session.

  To change the priority of a session using the nzsession priority command, enter :
  nzsession priority -high -u user -pw password -id 21664
  To change the priority of a session using the ALTER SESSION command, enter:
  MYDB.SCHEMA(USER)=> ALTER SESSION 21664 SET PRIORITY TO HIGH; --当有重要的任务想尽快解决时,可以考虑临时给一个高的优先级.
  
4.  Managing the Gate Keeper
     Gate Keeper 默任不启用,这里就先不做深究.