This appendix contains database schema information in a series of tables. The topics include:
The sge_job table contains one record for each array task (one record for non array jobs with the array task number 1) and for each parallel task started in a tightly integrated parallel job.
For N1GE 6.0 systems, a record is created as soon as a job, an array task, or a parallel task is scheduled. It is updated during the job's runtime.
A short description of N1GE jobs, array jobs, parallel jobs and their differences can be found in this Sun Grid Engine User's Guide. The Glossary may be especially useful as an introduction.
Column |
Type |
Description |
---|---|---|
j_id |
Integer |
Unique record identifier |
j_job_number |
integer |
JOB_ID |
j_task_number |
integer |
Array task id. |
j_pe_taskid |
text |
ID of a task of a tightly integrated parallel task. |
j_job_name |
text |
job name (script name or value set with the submit option -N) |
j_group |
text |
UNIX group name of the primary group the job was executed in. References the group table. |
j_owner |
text |
UNIX user account the job was running in. References the user table. |
j_account |
text |
Account string set with the submit option -A. |
j_priority |
integer |
Priority set with the submit option -p or assigned from the queue configuration. |
j_submission_time |
timestamp |
Time of the job submission. |
j_project |
text |
Project (only in Sun Grid Engine, Enterprise Edition) References the project table. |
j_department |
text |
Department (only in Sun Grid Engine, Enterprise Edition) References the department table. |
The sge_job_usage table holds the job's resource usage over time.
For N1GE 5.3 systems, only one record exists per finished job, array task and parallel task. The ju_curr_time column holds the job's end time (j_end_time in sge_job).
For N1GE 6.0 systems, the online usage is stored as well; this results in multiple records for one job, array task, and parallel task stored in sge_job. The resource usage of a job can be monitored over time (ju_curr_time), the last record per job, array task, or parallel task holds the total usage that can be used in accounting, ju_curr_time for this record will equal j_end_time from sge_job.
Column |
Type |
Description |
---|---|---|
ju_id |
Integer |
Unique record identifier |
ju_parent |
Integer |
Reference to sge_job table |
ju_curr_time |
Integer |
current time for usage |
ju_qname |
text |
Name of the queue the job was running in. In N1GE 6.0 systems this will be the cluster queue name. References to queues in the queue table. |
ju_hostname |
text |
Name of the host the job was running on. References to hosts in the host table. |
ju_start_time |
timestamp |
Time when the job was started. |
ju_end_time |
timestamp |
Time when the job finished. |
ju_failed |
integer |
if != 0 indicates a problem |
ju_exit_status |
integer |
exit status of the job |
ju_granted_pe |
text |
The parallel environment which was selected for that job. |
ju_slots |
integer |
The number of slots which were dispatched to the job. |
ju_state |
text |
job state |
ju_ru_wallclock |
integer |
end_time – start_time |
ju_ru_utime |
double |
user time used |
ju_ru_stime |
double |
system time used |
ju_ru_maxrss |
integer |
maximum resident set size |
ju_ru_ixrss |
integer |
currently 0 |
ju_ru_ismrss |
integer | |
ju_ru_idrss |
integer |
integral resident set size |
ju_ru_isrss |
integer |
currently 0 |
ju_ru_minflt |
integer |
page faults not requiring physical I/O |
ju_ru_majflt |
integer |
page faults requiring physical I/O |
ju_ru_nswap |
integer |
swaps |
ju_ru_inblock |
integer |
block input operations |
ju_ru_oublock |
integer |
block output operations |
ju_ru_msgsnd |
integer |
messages sent |
ju_ru_msgrcv |
integer |
messages received |
ju_ru_nsignals |
integer |
signals received |
ju_ru_nvcsw |
integer |
voluntary context switches |
ju_ru_nivcsw |
integer |
involuntary context switches |
ju_cpu |
double |
The cpu time usage in seconds. |
ju_mem |
double |
The integral memory usage in Gbytes seconds. |
ju_io |
double |
The amount of data transferred in input/output operations. |
ju_iow |
double |
The io wait time in seconds. |
ju_maxvmem |
double |
The maximum vmem size in bytes. |
Stores resources a job's requests.
Two types of requests (qsub options) are currently handled:
-l resource requests, e.g. -l arch=solaris,mem_total=100M
For each request one record is created.
-q queue request, e.g. -q balrog.q
One record is created containing “queue” as variable and the request contents as variable.
Column |
Type |
Description |
---|---|---|
jr_id |
Integer |
Unique record identifier |
jr_parent |
Integer |
reference to the sge_job table |
jr_variable |
text |
name of the requested complex variable |
jr_value |
text |
requested value |
The sge_job_log table contains job logging information.
Column |
Type |
Description |
---|---|---|
jl_id |
Integer |
Unique record identifier |
jl_parent |
integer |
Reference to sge_job table |
jl_time |
unix timestamp |
Time when the job login entry was generated. |
jl_event |
text | |
jl_job_number |
integer | |
jl_task_number |
integer | |
jl_pe_task_id |
text | |
jl_state |
text |
job state after the reported event |
jl_user |
text |
user who initiated action for the event |
jl_host |
text |
host on which the event action was initiated |
jl_state_time |
unix timestamp |
describes, how long the job was in a certain state, see description below |
jl_message |
text |
a message explaining what happened |
The sge_share_log table contains information about the N1GE(EE) sharetree configuration and usage.
Further information can be found in the N1GE manual sharetree(5).
Column |
Type |
Description |
---|---|---|
sl_id |
Integer |
Unique identifier for share log record |
sl_curr_time |
timestamp |
Current time |
sl_usage_time |
timestamp |
Usage time |
sl_node |
text |
Node name in the sharetree |
sl_user |
text |
Name of the user (job owner) References the user table. |
sl_project |
text |
Name of the project References the project table. |
sl_shares |
integer |
shares configured in sharetree |
sl_job_count |
integer |
number of jobs that are considered for share tree policy |
sl_level |
double |
share in % within this tree level |
sl_total |
double |
total share in % within whole sharetree |
sl_long_target_share |
double |
targeted long term share in % |
sl_short_target_share |
double |
targeted short term share in % |
sl_actual_share |
double |
actual share in % |
sl_usage |
double |
combined usage, weight of cpu, mem and io can be configured |
sl_cpu |
double |
cpu usage in seconds |
sl_mem |
double |
integral memory usage in Gbyte seconds |
sl_io |
double |
The amount of data transferred in input/output operations. |
sl_ltcpu |
double |
long term cpu |
sl_ltmem |
double |
long term mem |
sl_ltio |
double |
long term io |
The sge_host table lists all hosts in the Cluster.
Column |
Type |
Description |
---|---|---|
h_id |
Integer |
Unique host identifier |
h_hostname |
text |
The hostname. |
The sge_host_values table stores the values of host variables that are subject to change, e.g. the load average.
In addition, derived host values will be stored, e.g. hourly averages, sums etc.
Column |
Type |
Description |
---|---|---|
hv_hostname |
text |
References the host table. |
hv_time_start |
timestamp |
Start time for the validity of a value. |
hv_time_end |
timestamp |
End time for the validity of a value. |
hv_variable |
text |
Variable name, e.g. load_avg. |
hv_value |
text |
Variable value, e.g. 0.34. |
hv_dvalue |
double precision |
Variable value as number. |
hv_dconfig |
double precision |
In case of consumables: Consumable maximum available value (configured value). |
The sge_queue table lists all queues configured in the cluster.
Column |
Type |
Description |
---|---|---|
q_id |
Integer |
Unique queue identifier |
q_qname |
text |
Name of the queue |
q_hostname |
text |
Name of host |
The sge_queue_values table stores the values of queue variables that are subject to change, e.g. the number of free slots.
In addition, derived queue values will be stored, e.g. hourly averages, sums etc.
Column |
Type |
Description |
---|---|---|
qv_parent |
integer |
References q_id in the sge_queue table. |
qv_time_start |
timestamp |
Start time for the validity of a value. |
qv_time_end |
timestamp |
End time for the validity of a value. |
qv_variable |
text |
Variable name, e.g. slots. |
qv_value |
text |
Variable value, e.g. 5. |
qv_dvalue |
double precision |
Variable value as number. |
qv_dconfig |
double precision |
In case of consumables: Consumable maximum available value (configured value). |
Lists all departments referenced in the database.
Column |
Type |
Description |
---|---|---|
d_id |
Integer |
Unique department identifier |
d_department |
text |
Name of the department. |
Table 9: The sge_department Table
The sge_department_values table stores the values of department related variables that are subject to change. Currently these are derived values, e.g. hourly averages, sums etc.
Column |
Type |
Description |
---|---|---|
dv_parent |
integer |
References d_id in the sge_department table. |
dv_time_start |
timestamp |
Start time for the validity of a value. |
dv_time_end |
timestamp |
End time for the validity of a value. |
dv_variable |
text |
Variable name, e.g. h_sum_jobs |
dv_value |
text |
Variable value, e.g. 5. |
dv_dvalue |
double precision |
Variable value as number. |
dv_dconfig |
double precision |
In case of consumables: Consumable maximum available value (configured value). |
Lists all projects referenced in the database.
Column |
Type |
Description |
---|---|---|
p_id |
Integer |
Unique project identifier |
p_project |
text |
Name of the project. |
The sge_project_values table stores the values of project related variables that are subject to change. Currently these values are derived values, e.g. hourly averages, sums etc.
Column |
Type |
Description |
---|---|---|
pv_parent |
integer |
References q_id in the sge_queue table. |
pv_time_start |
timestamp |
Start time for the validity of a value. |
pv_time_end |
timestamp |
End time for the validity of a value. |
pv_variable |
text |
Variable name, e.g. h_avg_cpu |
pv_value |
text |
Variable value, e.g. 345.5 |
pv_dvalue |
double precision |
Variable value as number. |
pv_dconfig |
double precision |
In case of consumables: Consumable maximum available value (configured value). |
Lists all users referenced in the database.
Column |
Type |
Description |
---|---|---|
u_id |
Integer |
Unique user id |
u_user |
text |
Name of the user. |
The sge_user_values table stores the values of user related variables that are subject to change. These values are currently derived queue values, e.g. hourly averages, sums etc.
Column |
Type |
Description |
---|---|---|
uv_parent |
integer |
References q_id in the sge_queue table. |
uv_time_start |
timestamp |
Start time for the validity of a value. |
uv_time_end |
timestamp |
End time for the validity of a value. |
uv_variable |
text |
Variable name, e.g. h_sum_cpu |
uv_value |
text |
Variable value, e.g. 23.2 |
uv_dvalue |
double precision |
Variable value as number. |
uv_dconfig |
double precision |
In case of consumables: Consumable maximum available value (configured value). |
Lists all user groups referenced in the database.
Column |
Type |
Description |
---|---|---|
g_id |
Integer |
Unique group id |
g_group |
text |
Name of the group. |
Table 15: The sge_group Table
The sge_group_values table stores the values of group related variables that are subject to change. These are currently derived values, e.g. hourly averages, sums etc.
Column |
Type |
Description |
---|---|---|
gv_parent |
integer |
References q_id in the sge_queue table. |
gv_time_start |
timestamp |
Start time for the validity of a value. |
gv_time_end |
timestamp |
End time for the validity of a value. |
gv_variable |
text |
Variable name, e.g. h_sum_jobs. |
gv_value |
text |
Variable value, e.g. 53 |
gv_dvalue |
double precision |
Variable value as number. |
gv_dconfig |
double precision |
In case of consumables: Consumable maximum available value (configured value). |
Accounting records for jobs, array tasks, and tightly integrated tasks. Contains only finished jobs.
Column |
Type |
Description |
---|---|---|
job_number |
integer |
Job number |
task_number |
integer |
Array task id |
pe_taskid |
text |
ID of a tightly integrated parallel task |
name |
text |
job name (script name or value set with the submit option -N) |
groupname |
text |
UNIX group name of the primary group the job was executed in. References the group table. |
username |
text |
UNIX user account the job was running in. References the user table. |
account |
text |
Account string set with the submit option -A |
project |
text |
Project, References the project table |
department |
text |
Department, References the department table |
submission_time |
timestamp |
Time of the job submission |
start_time |
timestamp |
Time when the job was started |
end_time |
timestemp |
Time when the job finished |
wallclock_time |
integer |
end_time - start_time |
cpu |
double |
The CPU time usage in seconds |
io |
double |
The amount of data transferred in input/outputoperations |
iow |
double |
The io wait time in seconds |
maxvmem |
double |
The maximum vmem size in bytes |
wait_time |
integer |
start_time - submission_time |
turnaround_time |
integer |
end_time - submission_time |
This is the same as view_accounting, but no tasks of tightly integrated parallel jobs are listed.
Finished jobs per hour, one record per hour.
Column |
Type |
Description |
---|---|---|
completed |
integer |
Completed jobs |
time |
timestamp |
Time when the jobs finished |
Job logging (e.g. Submission, state changes, job finish).
Column |
Type |
Description |
---|---|---|
job_number |
integer |
Job number |
task_number |
integer |
Array task id |
pe_taskid |
text |
ID of a tightly integrated parallel task |
name |
text |
job name (script name or value set with the submit option -N) |
username |
text |
UNIX group name of the primary group the job was executed in. References the group table |
account |
text |
UNIX user account the job was running in.References the user table |
project |
text |
Project. References the project table |
department |
text |
Department. References the department table |
time |
timestamp |
Time when the job logging entry was generated |
event |
text |
Event being recorded |
state |
text |
Job state after the reported event |
initiator |
text |
User who initiated action for the event |
host |
text |
Host on with the event action was initiated |
message |
text |
A message explaining what happened |
Department specific variables.
Column |
Type |
Description |
---|---|---|
department |
text |
Name of the department |
time_start |
timestamp |
Start time for the validity of a value |
time_end |
timestamp |
End time for the validity of a value |
variable |
text |
Variable name, e.g. h_sum_jobs |
str_value |
text |
Variable value, e.g. 5 |
num_value |
double precision |
Variable value as number |
num_config |
double precision |
In case of consumables: Consumable maximum available value (configured value) |
Group specific variables
Column |
Type |
Description |
---|---|---|
groupname |
text |
Name of the group |
time_start |
timestamp |
Start time for the validity of a value |
time_end |
timestamp |
End time for the validity of a value |
variable |
text |
Variable name, e.g. h_sum_jobs |
str_value |
text |
Variable value, e.g. 53 |
num_value |
double precision |
Variable value as number |
num_config |
double precision |
In case of consumables: Consumable maximum available value (configured value) |
Host specific variables
Column |
Type |
Description |
---|---|---|
hostname |
text |
The hostname |
time_start |
timestamp |
Start time for the validity of a value |
time_end |
timestamp |
End time for the validity of a value |
variable |
text |
Variable name, e.g. load_avg |
str_value |
text |
Variable value, e.g. 0.34 |
num_value |
double precision |
Variable value as number |
num_config |
double precision |
In case of consumables: Consumable maximum available value (configured value) |
Project specific variables
Column |
Type |
Description |
---|---|---|
project |
text |
Name of the project |
time_start |
timestamp |
Start time for the validity of a value |
time_end |
timestamp |
End time for the validity of a value |
variable |
text |
Variable name, e.g. h_avg_cpu |
str_value |
text |
Variable value, e.g. 345.5 |
num_value |
double precision |
Variable value as number |
num_config |
double precision |
In case of consumables: Consumable maximum available value (configured value) |
Queue specific variables
Column |
Type |
Description |
---|---|---|
qname |
text |
Name of the queue |
hostname |
text |
Name of host |
time_start |
timestamp |
Start time for the validity of a value |
time_end |
timestamp |
End time for the validity of a value |
variable |
text |
Variable name, e.g. slots |
str_value |
text |
Variable value, e.g. 5 |
num_value |
double precision |
Variable value as number |
num_config |
double precision |
In case of consumables: Consumable maximum available value (configured value) |
User specific variables.
Column |
Type |
Description |
---|---|---|
username |
text |
Name of the user |
time_start |
timestamp |
Start time for the validity of a value |
time_end |
timestamp |
End time for the validity of a value |
variable |
text |
Variable name, e.g. h_sum_cpu |
str_value |
text |
Variable value, e.g. 23.2 |
num_value |
double precision |
Variable value as number |
num_config |
double precision |
In case of consumables: Consumable maximum available value (configured value) |
Derived values stored in the database can highly reduce query processing time. The reporting databasel contains aggregated values (sum, average, min, max) on an hourly basis. After some time period (e.g. one year), these values can even be further compressed to daily, weekly or monthly values.
The following derived values are delivered:
table |
variable |
description |
---|---|---|
sge_host_values |
h_sum_cpu, d_sum_cpu, m_sum_cpu |
cpu usage per host and hour, day, month |
sge_user_values |
h_sum_cpu, d_sum_cpu, m_sum_cpu |
cpu usage per user and hour, day, month |
sge_group_values |
h_sum_cpu, d_sum_cpu, m_sum_cpu |
cpu usage per group and hour, day, month |
sge_department_values |
h_sum_cpu, d_sum_cpu, m_sum_cpu |
cpu usage per department and hour, day, month |
sge_project_values |
h_sum_cpu, d_sum_cpu, m_sum_cpu |
cpu usage per project and hour, day, month |
sge_host_values |
h_avg_load |
average host load |
sge_host_values |
h_max_load |
maximum host load |
Rules for the generation of any derived value can be specified in a configuration file.