Database Schemas

Searching Sun Grid Engine 6.2

Sun Grid Engine Information Center
Accounting and Reporting Console (ARCo)
Index

Database Schemas

Schema Tables

sge_job

The sge_job table contains one record for each new job, each new array task, and each parallel task started in a tightly integrated parallel job. A record is created as soon as a job, an array task, or a parallel task is scheduled.

For non-array jobs, the j_task_number is set to -1. For array jobs, one record is created for the parent job with the j_task_number set to -1. Each child array task has one record with the j_task_number set to > 0.

For previous versions of Grid Engine before 6.0 update 7 (6.0u7), each non-array job will have j_task_number set to -1 for PENDING job state and j_task_number set to 0 for all other states.

A short description of SGE jobs, array jobs, parallel jobs and their differences can be found in Submitting Jobs. The Grid Engine Glossary might also be useful as an introduction.

Column Type Description
j_id integer Unique record identifier that serves as the primary key
j_job_number integer JOB_ID issued by qmaster
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 in which the job was executed
j_owner text UNIX user account in which the job was run
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 name
j_department text Department name

sge_job_usage

The sge_job_usage table holds the job's resource usage over time.

Normally, the job usage is recorded after a job has finished. For long-running jobs, an intermediate reporting entry is created after midnight. This results in multiple usage records for a particular job and allows for a fine-grained resource usage monitoring over time.

The intermediate records report the change in job resource usage between the start time and the end time (columns ju_start_time, ju_end_time).

The ju_exit_status is set to -1 if the job is still running, which identifies an intermediate job. For queries that count jobs, add the condition "WHERE ju_exits_status != -1" to the query so that the intermediate records are not included in the final query result.

Column Type Description
ju_id integer Unique record identifier that serves as the primary key
ju_parent integer Identifies the job by reference to j_id in sge_job table as a foreign key
ju_curr_time integer Current time for usage
ju_qname text Name of the queue in which the job was running. In Grid Engine 6.0 systems, this is the cluster queue name
ju_hostname text Name of the host on which the job was running
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; -1 indicates a still running job for the intermediate record
ju_granted_pe text The parallel environment that was selected for that job.
ju_slots integer The number of slots that were dispatched to the job.
ju_ru_wallclock integer Elapsed time between when the job started and ended
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 I/O wait time in seconds
ju_maxvmem double The maximum virtual memory size in bytes
ju_ar_number integer Advance reservation ID (AR_ID) issued by qmaster; 0 indicates a non-advance reservation job

sge_job_request

The sge_job_request table stores resources that a job has requested.

Two types of requests (qsub options) are currently handled:

  • -l resource requests, such as -l arch=solaris,mem_total=100M; For each request one record is created.
  • -q queue requests, such as -q balrog.q; One record is created that contains "queue" as variable and the request contents as variable.
Column Type Description
jr_id integer Unique record identifier that serves as the primary key
jr_parent integer Identifies the job by reference to j_id in sge_job table as a foreign key
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 that serves as the primary key
jl_parent integer Identifies the job by reference to j_id in sge_job table as a foreign key
jl_time unix timestamp Time when the job login entry was generated.
jl_event text Name of the log event
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 Indicates how long the job was in a certain state
jl_message text Message that explains what happened

sge_share_log

The sge_share_log table contains information about the Grid Engine share tree configuration and usage.

For more information, see the sharetree(5) man page at http://gridengine.sunsource.net/unbranded-source/browse/~checkout~/gridengine/doc/htmlman/manuals.html?content-type=text/html.

Column Type Description
sl_id Integer Unique record identifier that serves as the primary key
sl_curr_time timestamp Current time
sl_usage_time timestamp Usage time
sl_node text Node name in the share tree
sl_user text UNIX user account in which the job was running
sl_project text Project name
sl_shares integer Number of shares configured in the share tree
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, memory, and I/O 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 memory
sl_ltio double Long term I/O

sge_host

The sge_host table lists all hosts in the cluster.

Column Type Description
h_id integer Unique host identifier that serves as the primary key
h_hostname text The name of the host

sge_host_values

The sge_host_values table stores the values of host variables that are subject to change, such as the load average. This table also stores derived host values, such as hourly averages and sums.

Column Type Description
hv_id integer Unique record identifier that serves as the primary key
hv_parent integer Identifies the host by reference to h_id in sge_host table as a foreign key
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, for example load_avg
hv_svalue text Variable value, for example 228.000000M
hv_dvalue double precision Variable value as number
hv_dconfig double precision Consumable maximum available value (configured value)

sge_queue

The sge_queue table lists all queues that are configured in the cluster.

Column Type Description
q_id integer Unique queue identifier that serves as the primary key
q_qname text Queue name
q_hostname text Host name

sge_queue_values

The sge_queue_values table stores the values of queue variables that are subject to change, such as the number of free slots. This table also stores derived queue values, such as hourly averages and sums.

Column Type Description
qv_id integer Unique record identifier that serves as the primary key
qv_parent integer Identifies the queue by reference to q_id in sge_queue table as a foreign key
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, for example slots
qv_value text Variable value, for example 5
qv_dvalue double precision Variable value as number
qv_dconfig double precision Consumable maximum available value (configured value)

sge_department

The sge_department table lists all departments referenced in the database.

Column Type Description
d_id Integer Unique department identifier that serves as the primary key
d_department text Department name

sge_department_values

The sge_department_values table stores the values of department-related variables that are subject to change. These values are derived values, such as hourly averages and sums.

Column Type Description
dv_id Integer Unique record identifier that serves as the primary key
dv_parent integer Identifies the department by reference to d_id in the sge_department table as a foreign key
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, for example h_sum_jobs
dv_value text Variable value, for example 5
dv_dvalue double precision Variable value as number
dv_dconfig double precision Not applicable

sge_project

The sge_project table lists all projects that are referenced in the database.

Column Type Description
p_id Integer Unique project identifier that serves as the primary key
p_project text Project name

sge_project_values

The sge_project_values table stores the values of project related variables that are subject to change. These values are derived values, such as hourly averages and sums.

Column Type Description
pv_id integer Unique record identifier that serves as the primary key
pv_parent integer Identifies the project by reference to p_id in the sge_project table as a foreign key
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, for example h_avg_cpu
pv_value text Variable value, for example 345.5
pv_dvalue double precision Variable value as number
pv_dconfig double precision Not applicable

sge_user

The sge_user table lists all users that are referenced in the database.

Column Type Description
u_id integer Unique user id that serves as the primary key
u_user text User name

sge_user_values

The sge_user_values table stores the values of user-related variables that are subject to change. These values are derived values, such as hourly averages and sums.

Column Type Description
uv_id integer Unique record identifier that serves as the primary key
uv_parent integer Identifies the user by reference to u_id in the sge_user table as a foreign key
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, for example h_sum_cpu
uv_value text Variable value, for example 23.2
uv_dvalue double precision Variable value as number
uv_dconfig double precision Not applicable

sge_group

The sge_group table lists all user groups that are referenced in the database.

Column Type Description
g_id Integer Unique group identifier that serves as the primary key
g_group text Group name

sge_group_values

The sge_group_values table stores the values of group-related variables that are subject to change. These values are derived values, such as hourly averages and sums.

Column Type Description
gv_id integer Unique record identifier that serves as the primary key
gv_parent integer Identifies the group by reference to g_id in the sge_group table as a foreign key
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, for example h_sum_jobs
gv_value text Variable value, for example 53
gv_dvalue double precision Variable value as number
gv_dconfig double precision Not applicable

sge_statistic

The sge_statistic table stores the categories of all statistics which are calculated by the dbwriter. Currently only the categories dbwriter and database are available.

Column Type Description
s_id integer Unique statistics identifier that serves as the primary key
s_name text nName of the statistic category

sge_statistic_values

The sge_statistic_values contains the calculated statistic values.

Column Type Description
sv_id integer Unique record identifier that serves as the primary key
sv_parent integer Identifies the job's parent by reference to s_id in the sge_statistic table as a foreign key
sv_time_start timestamp Start time of the validity of the statistic value
sv_time_end timestamp End time of the validity of the statistic value
sv_variable text Name of the calculated statistic value
sv_dvalue double precision Numerical statistic value

sge_ar

The sge_ar table stores records that identify each newly created advance reservation.

Column Type Description
ar_id integer Unique advance reservation identifier that serves as the primary key
ar_number integer AR_ID issued by qmaster
ar_owner text UNIX user account that created the AR
ar_submission_time timestamp Time when the AR was submitted

sge_ar_attribute

The sge_ar_attribute table stores attributes of the advance reservation.

Column Type Description
ara_id integer Unique record identifier that serves as the primary key
ara_parent integer Identifies the advance reservation by reference to ar_id in the sge_ar table as a foreign key
ara_curr_time timestamp Time when this record was created
ara_name text Name of the advance reservation
ara_acccount text Account string specified for the advance reservation (from -A option)
ara_start_time timestamp Start time of the advance reservation
ara_end_time timestamp End time of the advance reservation
ara_granted_pe text Name of the parallel environment selected for the advance reservation

sge_ar_usage

The sge_ar_usage table stores the accounting usage records. These records are written when an advance reservation terminates.

Column Type Description
aru_id integer Unique record identifier that serves as the primary key
aru_parent integer Identifies the advance reservation by reference to ar_id in the sge_ar table as a foreign key
aru_termination_time timestamp Time when the advance reservation terminated
aru_qname text Name of the cluster queue that the advance reservation reserved
aru_hostname text Name of the execution host
aru_slots integer Number of slots that the advance reservation reserved

sge_ar_log

The sge_ar_log table stores the advance reservation logging information. These records are written when the advance reservation changes state. A state change can be from pending to active, but can also be triggered by system events like host outage.

Column Type Description
arl_id integer Unique record identifier that serves as the primary key
arl_parent integer Identifies the advance reservation by reference to ar_id in the sge_ar table as a foreign key
arl_time timestamp Time of the advance reservation state change
arl_event text Event that has triggered the state change
arl_state text State of the advance reservation
arl_message text Message that describes the event that triggered the state change

sge_ar_resource_usage

The sge_ar_resource_usage table stores the resources that were granted to the advance reservation.

Column Type Description
arru_id integer Unique record identifier that serves as the primary key
arru_parent integer Identifies the advance reservation by reference to ar_id in the sge_ar table as a foreign key
arru_variable text Name of the granted resource
arru_value text Value of the granted resource

List of Predefined Views

view_accounting

Views accounting records for jobs, array tasks, and tightly integrated tasks. Jobs that have not yet started (sge_job.start_time >= sge_job_usage.ju_submission_time) are excluded. This view also includes intermediate usage records for long running jobs (see sge_job_usage description).

Column Type Description
job_number integer JOB_ID issued by the qmaster
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 in which the job was executed
username text UNIX user account in which the job was running
account text Account string set with the submit option -A
project text Project name
department text Department name
submission_time timestamp Time of the job submission
ar_number integer AR_ID (advance reservation identifier) issued by qmaster
start_time timestamp Time when the job was started
end_time timestemp Time when the job finished
wallclock_time integer Elapsed time for the job (end_time - start_time)
cpu double The CPU time usage in seconds
mem double Integral memory usage in Gbytes * seconds
io double The amount of data transferred in input/output operations
iow double The I/O wait time in seconds
maxvmem double The maximum virtual memory size in bytes

view_job_times

Includes calculated wait, turnaround, and job duration times for all jobs, except for tasks of tightly integrated parallel jobs.

Column Type Description
job_number integer JOB_ID issued by the qmaster
task_number integer Array task id
name text Job name (script name or value set with the submit option -N)
groupname text UNIX group name of the primary group in which the job was executed
username text UNIX user account in which the job was running
account text Account string set with the submit option -A
project text Project name
department text Department name
submission_time timestamp Time of the job submission
ar_number integer AR_ID (advance reservation identifier) issued by qmaster
start_time timestamp Time when the job started
end_time timestemp Time when the job finished
wait_time interval start_time - submission_time
turnaround_time interval end_time - submission_time. The turnaround time is computed even if the job has not yet finished, i.e. the end_time is the time when the intermediate reporting entry is created after a midnight (only the highest end_time for a particular job is taken in account). Queries using this view that only want to include data for finished jobs need to also add condition WHERE exit_status != -1.
job_duration interval end_time - start_time. The job_duration is computed even if the job has not yet finished, i.e. the end_time is the time when the intermediate reporting entry is created after a midnight (only the highest end_time for a particular job is taken in account). Queries using this view that only want to include data for finished jobs need to also add condition WHERE exit_status != -1.
wallclock_time integer end_time - start_time
exit_status integer Exit status of the job. -1 indicates a still running job, i.e. the intermediate record

view_jobs_completed

Includes all finished jobs regardless of their exit status value, i.e. even jobs that exited with an error code are included. The ju_exit_status is only checked to exclude the intermediate lines. Tasks of tightly integrated parallel jobs are also not included. This view is used in queries that count completed jobs.

Column Type Description
completed integer Completed jobs
time timestamp Time when the jobs finished
ar_number integer AR_ID (advance reservation identifier) issued by qmaster

view_job_log

Job logging (e.g. Submission, state changes, job finish).

Column Type Description
job_number integer JOB_ID issued by the qmaster
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 in which the job was executed
account text UNIX user account in which the job was running
project text Project name
department text Department name
time timestamp Time when the job logging entry was generated
event text Name of the event being recorded
state text Job state after the reported event
initiator text User who initiated action for the event
host text Host on which the event action was initiated
message text A message that explains what happened

view_department_values

Department specific variables.

Column Type Description
department text Department name
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, for example h_sum_jobs
str_value text Variable value, for example 5
num_value double precision Variable value as number
num_config double precision Not applicable

view_group_values

Group specific variables

Column Type Description
groupname text Group name
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, for example h_sum_jobs
str_value text Variable value, for example 53
num_value double precision Variable value as number
num_config double precision Not applicable

view_host_values

Host specific variables

Column Type Description
hostname text Host name
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, for example load_avg
str_value text Variable value, for example 0.34
num_value double precision Variable value as number
num_config double precision Consumable maximum available value (configured value)

view_project_values

Project specific variables

Column Type Description
project text Project name
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, for example h_avg_cpu
str_value text Variable value, for example 345.5
num_value double precision Variable value as number
num_config double precision Not applicable

view_queue_values

Queue specific variables

Column Type Description
qname text Queue name
hostname text Host name
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, for example slots
str_value text Variable value, for example 5
num_value double precision Variable value as number
num_config double precision Consumable maximum available value (configured value)

view_user_values

User specific variables.

Column Type Description
username text User name
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, for example h_sum_cpu
str_value text Variable value, for example 23.2
num_value double precision Variable value as number
num_config double precision Not applicable

view_statistic

Statistic variables.

Column Type Description
name text Category of the statistic
time_start timestamp Start time for the validity of the statistic
time_end timestamp End time for the validity of the statistic
variable text Name of the statistic value
num_value double precision Numerical value of the statistic value

view_ar_attribute

Attributes for the AR.

Column Type Description
ar_number integer AR_ID issued by qmaster
owner text UNIX user account that created the AR
submission_time timestamp Time when the AR was submitted
name text Name of the AR
acccount text Account string specified for the AR (from -A option)
start_time timestamp Start time of the AR
end_time timestamp End time of the AR
granted_pe text Name of the parallel environment selected for the AR

view_ar_log

Logging information for the AR.

Column Type Description
ar_number integer AR_ID issued by qmaster
time timestamp Time of the AR state change
event text Event that has triggered the state change
state text State of the AR
message text Message that describes the event that triggered the state change

view_ar_usage

Accounting usage information written whenever an advance reservation terminates.

Column Type Description
ar_number integer AR_ID issued by qmaster
atermination_time timestamp Time when the AR terminated
queue text Name of the cluster queue that the AR reserved
hostname text Name of the execution host
slots integer Number of slots that the AR reserved

view_ar_resource_usage

Resources that were granted to the AR.

Column Type Description
ar_number integer AR_ID issued by qmaster
variable text Name of the granted resource
value text Value of the granted resource

view_ar_time_usage

Information about the AR and job duration.

Column Type Description
ar_number integer AR_ID issued by qmaster
job_duration interval (the exact format differs by database, refer to the dbdefinition.xml) Total duration of jobs that ran in the AR
ar_duration interval (the exact format differs by database, refer to the dbdefinition.xml) Total duration of the AR

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 and sometimes daily basis. After some time period (e.g. one year), these values can even be further compressed to weekly or monthly values.

The following derived values are delivered:

Table Variable Description
sge_host_values h_cpu
h_load, d_load
h_jobs_finished, d_jobs_finished		 Average cpu utilization per hour
Average cpu load per hour, per day
Number of jobs finished per host per hour, per day
sge_user_values h_jobs_finished, d_jobs_finished Number of jobs finished per user per hour, per day
sge_project_values h_jobs_finished, d_jobs_finished Number of jobs finished per project per hour, per day
sge_statistic_values h_lines_per_second, d_lines_per_second
d_derived_value_time
d_deletion_time 		Average number of the lines processed by dbwriter per hour, per day
Average duration of the derived values calculation per day
Average duration of the deletion of the outdated values per day

Rules for the generation of any derived value can be specified in the derived value file: $SGE_ROOT/dbwriter/database/db_type/dbwriter.xml

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (1)

 

  1. Nov 13

    weicfa says:

    The view_accounting table is very useful for adminstrator or user to debug or an...

    The view_accounting table is very useful for adminstrator or user to debug or analyze their job. But it did not show the hostname that each job run on . It will be more convenient for user to debug when job go wrong if hostname can add into view_accounting table.

Sign up or Log in to add a comment or watch this page.


The individuals who post here are part of the extended Sun Microsystems community and they might not be employed or in any way formally affiliated with Sun Microsystems. The opinions expressed here are their own, are not necessarily reviewed in advance by anyone but the individual authors, and neither Sun nor any other party necessarily agrees with them.

Copyright 1994-2009 Sun Microsystems, Inc.
Powered by Atlassian Confluence Sun Guidelines on Public Discourse Privacy Policy Terms of Use Trademarks Site Map Employment Investor Relations Contact