Database Schema

This chapter contains information about the database schema of the N1 Grid Engine ARCo module

Tables

sge_job

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. With versions newer than N1GE6.0u7 the handling of the array task id for non array jobs has been changed. Before each non array job had an entry with j_task_number -1 (job state pending) and j_task_number 0 (for all other states). Now j_task_number -1 is used for all states instead.
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.

sge_job_usage

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	This column is currently not used.
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.

sge_job_request

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

sge_job_log

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

sge_share_log

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

sge_host

The sge_host table lists all hosts in the Cluster.

Column	Type	Description
h_id	Integer	Unique host identifier
h_hostname	text	The hostname.

sge_host_values

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).

sge_queue

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

sge_queue_values

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).

sge_department

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

sge_department_values

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).

sge_project

Lists all projects referenced in the database.

Column	Type	Description
p_id	Integer	Unique project identifier
p_project	text	Name of the project.

sge_project_values

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).

sge_user

Lists all users referenced in the database.

Column	Type	Description
u_id	Integer	Unique user id
u_user	text	Name of the user.

sge_user_values

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).

sge_group

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

sge_group_values

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).

List of Predefined Views

view_accounting

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

view_job_times

This is the same as view_accounting, but no tasks of tightly integrated parallel jobs are listed.

view_jobs_completed

Finished jobs per hour, one record per hour.

Column	Type	Description
completed	integer	Completed jobs
time	timestamp	Time when the jobs finished

view_job_log

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

view_department_values

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)

view_group_values

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)

view_host_values

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)

view_project_values

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)

view_queue_values

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)

view_user_values

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)

List of Derived Values

Derived values stored in the database can highly reduce query processing time. The reporting database 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.