Printable Accounting and Reporting Console Guide

Contents

Searching Sun Grid Engine 6.2

Sun Grid Engine Information Center
Index

Accounting and Reporting Console (ARCo)

The optional Accounting and Reporting Console (ARCo) enables you to gather live reporting data from the Grid Engine system and to store the data for historical analysis in the reporting database, which is a standard SQL database.

Raw reporting data is generated by sge_qmaster. This raw data is stored in the $SGE_ROOT/$SGE_CELL/common/reporting file. The dbwriter program reads the raw data in the reporting file and writes it to the SQL reporting database, where it can be accessed by ARCo.

ARCo supports the following SQL database systems:

  • PostgreSQL
  • Oracle
  • MySQL

The dbwriter provides functionality that helps you to manage your database size, by specifying Derived Values and Deletion Rules.

ARCo also provides a web-based tool that contains a set of predefined SQL queries. The predefined queries aupplement the most frequent statistical inquiries. You can modify these queries or create your own. To create your queries, you can use either the Simple Query builder (suitable for SQL novices) or the Advanced Query generator. You can display the data in a tabular, graphical, or pivotal form. You can export the data in CVS or PDF form, or store the result for later viewing. You can also use the arcorun utility to run ARCo queries in a batch mode. For information about arcorun, see arcorun. For more information about how to use ARCo, see Starting the Accounting and Reporting Console. For information about how to install ARCo, see Installing ARCo.

If you have multiple clusters, one dbwriter installation per cluster is needed, but only one Reporting installation is needed for all clusters.

For more information, see these topics:

Printer Icon To print this section, see the Printable Accounting and Reporting Console Guide.

Searching Sun Grid Engine 6.2

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

ARCo Frequently-Asked Questions (FAQ)

Do I need to re-install database server and create new database every time I update/upgrade ARCo?

No. Generally, you want to keep inserting the data in the same database. You just need to re-install dbwriter and Reporting software and during the installation supply your existing database parameters. If a newer version of database model is available, your existing ARCo database model will be updated during the installation of dbwriter. See Upgrading ARCo.

Can I restore database backup into a database already containing data?

No. A database backup must only be restored into an empty database. Because ARCo database is a relational database, there are primary key constrains defined on tables. You would run into and SQL error if a primary key (unique identifier), you are trying to restore, already exists in the database.

How do I change the debug level of the dbwriter?

You specify the debug level during the installation of dbwriter.

To change the debug level:

  1. Stop the dbwriter. 
    $SGE_ROOT/$SGE_CELL/common/sgedbwriter stop


  2. Edit the dbwriter configuration file ($SGE_ROOT/$SGE_CELL/common/dbwriter.conf). 
    #
# Debug level
# Valid values: WARNING, INFO, CONFIG, FINE, FINER, FINEST, ALL
#
DBWRITER_DEBUG=INFO


  3. Start the dbwriter. 
    $SGE_ROOT/$SGE_CELL/common/sgedbwriter stop

In general, you should use the default debug level, which is info. If you use a more verbose debug level, you substantially increase the amount of data output by dbwriter.

You can specify the following debug levels:

  • WARNING – Displays only severe errors and warnings.
  • INFO – Adds a number of informational messages. INFO is the default debug level.
  • CONFIG – Gives additional information that is related to dbwriter configuration, for example, about the processing of rules.
  • FINE – Produces more information. If you choose this debug level, all SQL statements run by dbwriter are outputted.
  • FINER – For debugging.
  • FINEST – For debugging.
  • ALL – For debugging, displays information for all levels.

How do I verify the version of the installed ARCo database model?

With Sun Grid Engine 6.1 the table sge_version was introduced. This table contains the installed versions of the ARCo database model.

Column Type Description
v_id integer version id (with SGE6u1 the version id has been set to 1)
v_version text Version text. Contains the SGE version when the database model has been changed to this version
v_time timestamp Timestamp of the upgrade to this version

Connect to your database, and as a superuser or the owner of the database objects issue an SQL command:

SELECT * FROM sge_version;

Searching Sun Grid Engine 6.2

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

ARCo Configuration Files and Scripts

About dbwriter


The dbwriter component writes and deletes the reporting data in the reporting database. It performs the following tasks:

  • Reads raw data from the reporting file and writes this raw data to the reporting database.
  • Calculates derived values. You can configure which values to calculate, as well as the rules that govern the calculations.
  • Deletes outdated data. You can configure how long to keep data.

The sge_qmaster component generates the reporting file. You can configure the generation of the reporting file. See the attribute reporting_params in the man page sge_conf(5), and the attribute report_variables in the man page host_conf(5).

When dbwriter starts up, it calculates derived values. dbwriter also deletes outdated records at startup. If dbwriter runs in continuous mode, dbwriter continues to calculate derived values and to delete outdated records at hourly intervals, or at whatever interval you specify. See Derived Values and Deletion Rules.

You can specify in a XML file the values that you want to calculate and the records that you want to delete. The path to this file is specified during installation. To change the path to the file, edit the DBWRITER_CALCULATION_FILE parameter in the dbwriter.conf file

inst_dbwriter Command Options

The inst_dbwriter script, used for installing dbwriter, is located at $SGE_ROOT/dbwriter and supports the following options:

  • -nosmf – Disables SMF for Solaris 10+ machines. Instead the regular RC scripts are used.
  • -upd – Removes old RC scripts not containing the SGE_CLUSTER_NAME and starts the installation. This option must be used if you are upgrading from version prior to 6.2.
  • -rmrc – Removes 6.2 RC scripts or SMF service.
  • -h – Prints usage text to stdout.

If no option is specified, installation is started.

dbwriter Configuration Parameters

During dbwriter module installation, the following configuration parameters are collected. These parameters are stored in the $SGE_ROOT/$SGE_CELL/common/dbwriter.conf file. Changes to the dbwriter.conf file require restarting the dbwriter.

Table – dbwriter Configuration Parameters
Parameter Description Sample Value
DBWRITER_USER_PW Password of the database user with the write privileges password
DBWRITER_USER Name of the database user with the write privileges; this user will become the owner of the database objects that will be created. arco_write
READ_USER Name of the ARCo read user; This user will be granted SELECT privileges on the objects owned by the user specified above, and on Oracle it is also used to create synonyms. arco_read
DBWRITER_URL JDBC URL to database jdbc:postgresql://host.domain:5432/arco
DB_SCHEMA Name of the database schema for the objects public
TABLESPACE Tablespace used for storing tables pg_default
TABLESPACE_INDEX Tablespace used for storing indexes pg_default
DBWRITER_CONTINOUS Continuous running mode; Default value is true true
DBWRITER_INTERVAL Interval in s for continuous; Default value is 60 seconds 60
DBWRITER_DRIVER JDBC driver name org.postgresql.Driver
DBWRITER_REPORTING_FILE File name of reporting file /myroot/opt/sge62/default/common/reporting
DBWRITER_CALCULATION_FILE File containing calculation rules /myroot/opt/sge62/dbwriter/database/mysql/dbwriter.xml
DBWRITER_SQL_THRESHOLD The dbwriter writes a warning into the log file if the execution of a single statement takes longer then the DBWRITER_SQL_THRESHOLD. The threshold is specified in seconds. If the threshold is 0, no warning will be written. 0
SPOOL_DIR Spool directory of the dbwriter log files and pid file is stored in this directory /myroot/opt/sge62/default/spool/dbwriter
DBWRITER_DEBUG Debug level. Valid values are: WARNING, INFO, CONFIG, FINE, FINER, FINEST, ALL INFO

sgedbwriter Command Options

The sgedbwriter script, used for starting and stopping dbwriter, is located at $SGE_ROOT/$SGE_CELL/common and supports the following sub-commands:

  • start – Starts the dbwriter as a background process. If no options are supplied, this is the default behavior. You can supply three options with the start command:
    • -debug – Start the dbwriter in in debug mode. This allows you to attach a debugger.
    • -debug_port port-number – Specify a port to use for debugging. The default is 8000.
    • -nosmf – Force no SMF
  • stop – Stops the dbwriter process.
  • print setting – Prints the specified dbwriter setting to stdout. The following settings are available:
    • pid_file – Prints the default pid file
    • log_file – Print the default log file
    • spool_dir – Print the default spool directory
  • -h – Prints usage text to stdout.

The content environment variable JVMARGS is treated as options for the Java virtual machine. If $JAVA_HOME is, set the Java virtual machine at $JAVA_HOME/bin/java is started.

About Reporting


The reporting file contains the following types of data:

  • Host load values and consumable resources
  • Queue consumable resources
  • Job logging
  • Job accounting
  • Share-tree usage
  • Advance reservation logging
  • Advance reservation accounting

Enabling Generation of the Reporting File

When the Grid Engine system is first installed, the reporting file is disabled. To use ARCo, you must enable the reporting file for the cluster. Once enabled, the reporting file will be generated by sge_qmaster. By default, the reporting file is located in $SGE_ROOT/$SGE_CELL/common. The path to the file is stored in the DBWRITER_REPORTING_FILE parameter of the dbwriter.conf file.

Once the reporting file is enabled, the dbwriter can read raw data from the reporting file and write it to the reporting database.

For information about configuring the generation of the reporting file, see the reporting_params parameter of the sge_conf(5) man page, and the report_variables parameter of the host_conf(5) man page. For more information about configuring the reporting file, see the reporting(5) man page. For complete details about installing and configuring ARCo, see Installing the Accounting and Reporting Console.

How to Enable Generation of the Reporting File From the Command Line

To enable reporting from the command line, use the qconf -mconf command to set the reporting_params attributes, as described in the last step of How to Enable Generation of the Reporting File With QMON.

How to Enable Generation of the Reporting File With QMON

  1. To enable reporting with QMON, on the QMON Main Control window click the Cluster Configuration button.

  2. On the Cluster Configuration dialog box, select the global host, and click Modify.

  3. On the Cluster Settings dialog box, click the Advanced Settings tab.

  4. In the Reporting Parameters field, set the following parameters:
    • Set accounting to true. true is the default value.
    • Set reporting to true.
    • Set flush_time to 00:00:15. 00:00:15 is the default value.
    • Set joblog to true.
    • Set sharelog to 00:00:00. 00:00:00 is the default value.

Reporting Module Configuration Parameters

During reporting module installation, the following configuration parameters are collected. These parameters are stored in the $SGE_ROOT/$SGE_CELL/arco/reporting/config.xml file. Changes to the config.xml file require restarting the smcwebserver.

  • The <database> element includes several attributes that configure the database connection for the application to use. This element includes two sub-elements and several attributes. Attributes include the following:
    • name
    • host
    • port
    • schema
    • clusterName
      Sub-elements include the following:
    • <driver>
    • <user>, which has three attributes:
      • name
      • passwd
      • maxConnections
  • The <appUser> element identifies each user that is permitted to use the reporting feature. One appUser element is provided for each user that is permitted to use the reporting feature.
    Note
    You can edit the config.xml file to add additional users. Provide another appUser element for each user to add.
  • The <storage> element defines the storage of ARCo queries and results. This element includes three sub-elements:
    • <root> defines the path of the spool directory
    • <queries> defines the directory where to store queries
    • <results> defines the directory where to store results
Example - Reporting Module Configuration File

The following config.xml example illustrates a single cluster configuration. For a multiple cluster configuration, there would be multiple <database> tags. 

<configuration>
 <!--
   Configure the database connection to be used by the application
 -->
 <database name="arco" host="host.domain" port="5432" schema="public" clusterName="testsuite">
    <driver type="postgres">
       <javaClass>org.postgresql.Driver</javaClass>
    </driver>
    <user name="arco_read" passwd="ed5sq937d20ecf5c" maxConnections="10"/>
 </database>

 <applUser>
    admin
 </applUser>
 <applUser>
    sgetest1
 </applUser>
 <applUser>
    sgetest2
 </applUser>

 <storage>
   <root>/var/spool/arco</root>
   <queries>queries</queries>
   <results>results</results>
 </storage>
</configuration>

Other ARCo Utilities

arcorun

The arcorun utility enables you to view and run ARCo queries from the command line. You can view query output in XML (default), CSV, PDF or HTML format. You can also set values for late-binding parameters.For more information, see the arcorun(1) man page.

Note
You must run the arcorun utility on a host from which the ARCo spooling directory (default: /var/spool/arco) is accessible.

Example – Running a Query

A query is run by simply invoking the arcorun command with
the name of the query as the argument:

     % $SGE_ROOT/$SGE_CELL/arco/reporting/arcorun Statistics

If a query name contains whitespaces you have to put
double-quotes around the query name:

     % $SGE_ROOT/$SGE_CELL/arco/reporting/arcorun "Host Load"

updatedb.sh

The updatedb.sh utility enables you to preview changes that will be performed on your database. You supply your existing database parameters and choose y in the following prompt:

Shall we only print all sql statements which will be executed during the upgrade? (y/n) [y] >>

After that, the SQL commands that will be executed during update/upgrade are printed to the stdout.

It is not recommended to use this as a substitute for a regular dbwriter update/upgrade. If you would choose option n, the SQL commands would be executed and only your database definition would be updated, but you would still need to perform regular dbwriter re-installation to also update other parts of dbwriter that might have changed.

Searching Sun Grid Engine 6.2

Index

Creating Cross-Cluster Queries

Note
Prerequisite for performing cross-cluster queries is that you have configured your database with multiple schemas (not necessary for MySQL), and granted one user SELECT privileges an all the objects in all of the schemas. You must use this user when connecting to the database to perform cross-cluster queries.

Although you could JOIN table from one schema with a one from other schema, it might not be useful to do that, as the data comes from separate SGE cluster. However, queries that combine together the results of two or more separate queries could be beneficial.

The syntax is:

select_statement1 UNION [ALL] select_statement2
select_statement1 INTERSECT [ALL] select_statement2
select_statement1 EXCEPT [ALL] select_statement2

The select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clause. These clauses can be appended at the end of all the chained UNION, INTERSECT and EXCEPT queries, which will then be applied to the combined returned result.

UNION effectively appends the result of select_statement1 to the result of select_statement2 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

INTERSECT returns all rows that are both in the result of select_statement1 and in the result of select_statement2. Duplicate rows are eliminated, unless INTERSECT ALL is used

EXCEPT returns all rows that are in the result of select_statement1 but not in the result of select_statement2. Duplicate rows are eliminated, unless EXCEPT ALL is used.

In order to calculate the union, intersection, or difference of two queries, the two queries must be "union compatible", which means that they return the same number of columns and the corresponding columns have compatible data types. The query statements must use the fully qualified object names, i.e. schemaname.tablename, schemaname.viewname, respectively.

Note
In MySQL the syntax would be database.tablename, database.viewname, respectively.

Examples

The following two tables are in schema arco_write_london and arco_write_denver:

Example – arco_write_london.sge_user 
+------+--------+
| u_id | u_user |
+------+--------+
|    1 | jade   |
|    2 | julie  |
|    3 | john   |
+------+--------+
Example – arco_write_denver.sge_user
+------+--------+
| u_id | u_user |
+------+--------+
|    1 | john   |
|    2 | david  |
|    3 | rose   |
+------+--------+

Connected as the multi_read user who has privileges on all the schemas we execute the following queries:

  1. SELECT u_user FROM arco_write_london.sge_user UNION SELECT u_user FROM arco_write_denver.sge_user; 
    +--------+
| u_user |
+--------+
| david  |
| jade   |
| john   |
| julie  |
| rose   |
+--------+


  2. SELECT u_user from arco_write_london.sge_user UNION ALL SELECT u_user FROM arco_write_denver.sge_user; 
    +--------+
| u_user |
+--------+
| jade   |
| julie  |
| john   |
| john   |
| david  |
| rose   |
+--------+


  3. SELECT u_user FROM arco_write_london.sge_user INTERSECT SELECT u_user FROM arco_write_denver.sge_user; 
    +--------+
| u_user |
+--------+
| john   |
+--------+


  4. SELECT u_user FROM arco_write_london.sge_user EXCEPT SELECT u_user FROM arco_write_denver.sge_user; 
    +--------+
| u_user |
+--------+
| jade   |
| julie  |
+--------+

Searching Sun Grid Engine 6.2

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

Derived Values and Deletion Rules

Derived Values

At dbwriter startup, and in continuous mode once an hour, derived values are calculated. You can configure which values to calculate in an XML file, which is by default in $SGE_ROOT/dbwriter/database/<database_type>/dbwriter.xml. <database_type> defines the type of database being used; currently, Oracle, PostgreSQL and MySQL are supported. The path to the configuration file is passed to dbwriter during installation and is stored in the dbwriter.conf file as the value of the parameter DBWRITER_CALCULATION_FILE.

The configuration file uses an XML format, and contains rules for both derived values and deleted values (described in the next section).

Derived Values Format

The rules for derived values have the following format.

  1. The top-level start tag is <derive>. The <derive> tag has three required attributes:
    • object – Based on this attribute, the derived value is ultimately stored in one of: sge_host_values, sge_queue_values, sge_user_values, sge_group_values, sge_department_values, sge_project_values. The object is one of the following:
      • host
      • queue
      • project
      • department
      • user
      • group
    • interval – The time range specifying how often to calculate the derived values. The time range is one of the following:
      • day
      • hour
      • month
      • year
    • variable – This is the name of the variable to hold the calculated data.
  2. A second-level start tag describes the way that the value should be derived. This tag must be either <sql> or <auto>:
    • <sql> – This tag contains an SQL statement used for calculating the derived values. The exact syntax of the entries depends upon the type of database being used. The statement must produce the following columns:
      • time_start – Together with time_end, specifies the time period for the calculated value.
      • time_end
      • value – The calculated derived value.

        The SQL statement can contain the following placeholders. dbwriter replaces the placeholders for each query, based on a rule:
        • time_start – Start time for the query. dbwriter searches for the last previously calculated derived value from this rule, and uses this timestamp as the start time for the next query.
        • time_end – End time for the query. This timestamp specifies the end of the last passed time range.
          For example, if the time range is day, and if derived values are calculated at 00:30, 00:00 is taken as time_end.
        • _key_0, key_1, . . . , key_n_ – Components of the primary key for the specified object type.
          For example, the sge_hosts table has the primary h_hostname. If a rule is processed for the host object type, one query is executed per entry in the sge_hosts table, the _key_0_ placeholder in the SQL statement is replaced by the hostname.

          The sge_queue table has a composed primary key that is made up of q_qname and q_hostname.
    • <auto> – For certain simple derived values, this tag can be used instead of a full SQL query.This tag has two attributes:
      • function – which gives the aggregate function to apply to the variable. This can be any function valid for the type of database being used. Some typical functions are AVG, SUM, VALUE, COUNT, MIN or MAX.
      • variable – which can be any variable tracked in the following tables: sge_host_values, sge_queue_values, sge_user_values, sge_group_values, sge_department_values, sge_project_values the variable specified must be from the table indicated by the object attribute of the enclosing <derive> tag, for example, if the object is host, the variable must be found in sge_host_values.
  3. Two end tags that match the two start tags.

Derived Values Examples

Here is an example of a derivation rule using the <sql> tag. The sge_queue table has a composed primary key comprised of q_qname and q_hostname. For a rule specified for the queue object_type, a query will be made for each entry in the sge_queue table, the placeholders _key_0 will be replaced by the queue name and key_1_ will be replaced by the hostname. 

<!--average queue utilization per hour-->
 <derive object="queue" interval="hour" variable="h_utilized">
    <sql>
         SELECT DATE_TRUNC( 'hour', qv_time_start) AS time_start, 
                DATE_TRUNC( 'hour', qv_time_start) + INTERVAL '1 hour' AS time_end, 
                AVG(qv_dvalue * 100 / qv_dconfig) AS value  
         FROM sge_queue_values 
         WHERE qv_variable = 'slots' AND 
               qv_parent = (SELECT q_id FROM sge_queue WHERE q_qname = __key_0__ AND q_hostname = __key_1__) AND 
               qv_time_start &lt;= '__time_end__' AND 
               qv_time_end &gt; '__time_start__' 
         GROUP BY time_start    
    </sql>
  </derive>

Here is an example when the rule above is processed by the dbwriter. A query will be made for each entry in the sge_queue table, the placeholders _key_0 will be replaced by the queue name and key_1_ will be replaced by the hostname. In this example, the results of these queries will be inserted in the sge_queue_values table, because object="queue".

SELECT DATE_TRUNC('hour', qv_time_start) AS time_start, 
       DATE_TRUNC( 'hour', qv_time_start) + INTERVAL '1 hour' AS time_end, 
       AVG(qv_dvalue * 100 / qv_dconfig) AS value  
FROM sge_queue_values 
WHERE qv_variable = 'slots' AND 
      qv_parent = (SELECT q_id FROM sge_queue WHERE q_qname = 'all.q' AND q_hostname = 'my.hostname') AND 
      qv_time_start <= '2008-05-21 00:00:00.0' AND 
      qv_time_end > '1970-01-01 01:00:00.0' 
GROUP BY time_start;

Here is an example of a derivation rule using the <auto> tag.

<derive object="host" interval="day" variable="d_load">
   <auto function="AVG" variable="h_load" />
</derive>

Deleting Outdated Records

At dbwriter startup, and in continuous mode once an hour, outdated records will be deleted. You can configure how these records are calculated in an XML file, by default in $SGE_ROOT/dbwriter/database/<database_type>/dbwriter.xml. <database_type> is the type of database being used; currently, Oracle, PostgreSQL and MySQL are supported. The path to the configuration file is passed to dbwriter during installation and is stored in the dbwriter.conf file as the value of the parameter DBWRITER_CALCULATION_FILE.

Deletion Rules Format

The configuration file contains rules for both derived values and deleted values. Deletion rules are of the following format.

  • A top-level start tag <delete> with three attributes:
    • scope — which specifies the type of data to be deleted. Valid entries are:
      • job
      • job_log
      • share_log
      • host_values
      • queue_values
      • project_values
      • department_values
      • user_values
      • group_values
      • ar
        Based on this attribute, the values are deleted from the table with the same name with sge_ prepended.
    • time_range — which gives the unit of time_amount.
    • time_amount — which is the number of units (time_range) during which a record is kept.
  • An optional second-level start tag <sub_scope>, which specifies an additional condition for deletion. A subscope can be configured for all *_values scopes and the share_log scope.
  • One or two end tags matching the two start tags

For certain scopes, a sub-scope can be configured. The sub-scope specifies an additional condition for deletion. A sub-scope can be configured for all *_values scopes and for the share_log scope. The following rules apply:

  • If a sub-scope is configured for a *_values rule, it contains a space-separated list of variables to delete.
  • If a sub-scope is specified for the share_log, it contains a space-separated list of share-tree nodes to delete.
  • If sub-scope are used, you should always have a fall-back rule without sub-scope, which will delete all objects that are not explicitly named by the sub-scope.

Here is an example of a delete tag:

<?xml version="1.0" encoding="UTF-8"?>
<DbWriterConfig>
  <!-- keep host values for 2 years -->
  <delete scope="host_values" time_range="year" time_amount="2"/>

  <!-- keep queue values one month -->
  <delete scope="queue_values" time_range="month" time_amount="1">
    <sub_scope>slots</sub_scope>
    <sub_scope>state</sub_scope>
  </delete>
</DbWriterConfig>

Deletion Rules Examples

The following rule indicates that the four variables given in the subscope should be deleted from the table sge_host_values after 7 days.

<delete scope="host_values" time_range="day" time_amount="7">
        <sub_scope>np_load_avg</sub_scope>
        <sub_scope>cpu<sub_scope>
        <sub_scope>mem_free</sub_scope>
        <sub_scope>virtual_free</sub_scope>
</delete>

The following rule says to delete all variables from the table sge_host_values after two years:

<delete scope="host_values" time_range="year" time_amount="2"/>

The following rule says to delete all records for user fred after one month:

<delete scope="share_log" time_range="month" time_amount="1">
        <sub_scope>fred</sub_scope>
</delete>

Searching Sun Grid Engine 6.2

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

Installing ARCo

Installing the Accounting and Reporting console software (ARCo) consists of the following topics:

Planning the ARCo Installation

Before you install the ARCo software, you must plan how to achieve the results that fit your environment. This section helps you make the decisions that affect the rest of the procedure. Write down your installation plan in a table similar to the following example.

Parameter      Value     
sge-root directory  
Database software vendor  
Database user (read access)  
Database user (write access)  
Multi-cluster support?  

Supported Operating Platforms

  • Solaris 10, 9, and 8 Operating Systems (SPARC Platform Edition)
  • Solaris 10 and 9 Operating Systems (x86 Platform Edition)
  • Solaris 10 Operating System (x64 Platform Edition)
  • Linux x64, kernel 2.4, 2.6, glibc >= 2.3.2
  • Linux x86, kernel 2.4, 2.6, glibc >= 2.3.2

Required Software

For ARCo software to function correctly, you must already have installed the following on your ARCo system:

  • Sun Grid Engine 6.2 software
  • Java Runtime Environment (JRE) version 1.5
  • One of the following database software versions
    • PostgresSQL 8.0 through 8.3
    • MySQL 5.0.36 and higher
    • Oracle 9i or 10g
  • Sun Java Web Console version 3.0 and one of the following web browsers:
    • Netscape 6.2 and above
    • Mozilla 1.4 and above
    • Internet Explorer 5.5 and above
    • Firefox 1.0 and above
Note
Sun Java Web Console 3.0 is installed automatically with Solaris 10 Update 3 or later. If you need to install Sun Java Web Console, see Installing Sun Java Web Console.

Disk Space Recommendations

Component Space Needed
ARCo software 100 MB
Sun Java Web Console ??
Database server memory 250 to 750 MB *
Database server disk space 10 GB *

* Your specific database server configuration settings depend on the following:

  • Cluster size and number of jobs running on cluster
  • Setting of joblog in reporting_params (qconf -mconf)
  • Configured report_variables (qconf -me global)
  • Configuration of dbwriter deletion rules (<sge_root>/dbwriter/database/<database_type>/dbwriter.xml)

For guidelines about determining specific database needs, see Space Requirements for the ARCo Database on the Open Grid Engine site.

Multi-Cluster Support Overview

If you have multiple Sun Grid Engine clusters, you can log in to one instance of ARCo from which you can run reports on all ARCo instances that use the same database vendor and structure. With the ARCo multi-cluster support, one dbwriter instance per qmaster is still required, but a single reporting installation is sufficient for all qmasters. During the reporting installation, you can supply separate database parameters, such as database name, database user, database host, database password for each cluster, the only condition being that that databases are of the same vendor. Database connections are configured from these parameters, which enables you to run the same queries on separate clusters, while logged in to the single instance of the ARCo reporting.

For the multi-cluster database configuration, you can use any of these database setups:

  • A single database with multiple schemas (one per each cluster) on a single DBMS
  • Separate databases (one per each cluster) on a single DBMS
  • Separate databases on separate DBMS (one per each cluster)

If you are not interested in cross-cluster queries, you can choose any of these setups. However, to run cross-cluster queries, you must configure a single database with multiple schemas (one per each cluster) on a single DBMS.

Database Configuration Illustrations

The following diagrams illustrate the supported database configurations. Additional steps, described in How to Configure the ARCo Database with Multiple Schemas on PostgreSQL, are necessary to configure a PostgresSQL database with separate schemas.

If you want to run cross-cluster queries, use the configuration depicted in One Database With Multiple Schemas on a Single DBMS. Otherwise, you can choose either of the other configurations, although Separate Databases on a Single DBMS is slightly preferred.

Figure - Separate Databases on Separate DBMS

Each database is created on a separate Database Management Server (DBMS).

"Diagram shows two databases

Figure - Separate Databases on a Single DBMS

Databases of different names are created on the same DBMS. Only two users are required to access all ARCo databases on the same server, arco_read and arco_write.

Diagram shows two databases on one DBMS."

Figure - One Database With Multiple Schemas on a Single DBMS

Only one database is created with multiple schemas (one per each cluster). There are two users for each schema, arco_write_cluster and arco_read_cluster. The name of the schema should be the same as the name of the owner (arco_write_cluster).

Diagram shows one database on one DBMS with multiple schemas.

Schema Overview

A database cluster contains one or more named databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request. A database can contain one or more named schemas, which in turn contain tables. Schemas also contain other objects, such as views, aliases, indexes and functions. The same object name can be used in different schemas without conflict; for example, both schemas arco_write_denver and arco_write_london may contain the sge_job table.

Unlike databases, schemas are not rigidly separated: a user may access objects in any of the schemas in the database to which the user is connected, if the user has privileges to do so. For user to access objects from a different schema, he needs to be granted SELECT privilege on the objects and access them using the fully-qualified name, for example schema_name.table_name. A user does not need to use the fully-qualified names if accessing objects in its own schema.

Each database handles the schema notion differently.

  • Oracle - In Oracle, one schema is created automatically for each user. Because there is a 1-to-1 relationship between a user and a schema, these two terms are often used interchangeably. To perform cross-cluster queries, one designated database user (e.g multi_read) needs to be granted SELECT privileges on all the objects (tables, views) from all the other schemas. See Using Oracle Database
  • PostgreSQL - In PostgreSQL when a table is created without explicitly specifying schema name, it is automatically put into the default schema called 'public'. Each PostgreSQL database contains such a schema and all users have ALL privilege on that schema. The dbdefinition.xml for Postgres allows for explicit specification of schema for table definition. Detailed instructions, described in How to Configure the ARCo Database with Multiple Schemas on PostgreSQL. For more information on schemas, see http://www.postgresql.org/docs/8.2/static/ddl-schemas.html.
  • MySQL – MySQL does not support schemas; the term schema is analogous to database. Command CREATE SCHEMA is the same as CREATE DATABASE. The implication is that a user can access any object from any database on the same DBMS using a client connection to any single database. If you configure MySQL multiple databases using just one pair of users (arco_write, arco_read) and grant the privileges as described in step 7 of How to Configure the ARCo Database on MySQL, you can perform cross_cluster queries. You must use the fully-qualified names when accessing objects, for example database_name.table_name.

Configuring the Database Server

You must properly install and configure the database server before you can install and use ARCo. Specific database installation instructions and configuration settings differ by database vendor. For more information, see the following:

How to Configure the PostgresSQL Server

Before you configure the database server, you must download, compile and install the PostgreSQL database software and create a user account to own the database processes. Usually, this user is postgres. Add the PostgreSQL bin directory and necessary LD_LIBRARY_PATH settings to your environment. You can find detailed information on the PostgreSQL database in the Postgres documentation.

  1. Create a home directory for the postgres user.
    In this example, the home directory is /space/postgres/data. 
    % mkdir -p /space/postgres/data
% useradd -d /space/postgres  postgres 
% chown postgres /space/postgres/data
% su - postgres
    • Continue as described in the PostgreSQL documentation to set up a database.* 
      > initdb -D /space/postgres/data

creating directory /space/postgres/data... ok
creating directory /space/postgres/data/base... ok
creating directory /space/postgres/data/global... ok
creating directory /space/postgres/data/pg_xlog... ok
creating directory /space/postgres/data/pg_clog... ok
creating template1 database in /space/postgres/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:
   postmaster -D /space/postgres/data
   or
   pg_ctl -D /space/postgres/data -l logfile start


  2. Make the following changes to the pg_hba.conf file.
    This change permits unrestricted and password free access to the database superuser postgres but requires md5 encrypted passwords for all other database users. Replace nnn.nnn.nnn with your subnet address without the trailing 0. You also can add access rules on a per-host basis by adding similar lines with host IP addresses. 
    # TYPE  DATABASE  USER  IP-ADDRESS     IP-MASK         METHOD
local   all       postgres                             trust 
local   all       all                                  md5 
# IPv4-style local connections: 
#host    all       all   nnn.nnn.nnn.0  255.255.255.0  md5


  3. Make the following changes to the postgresql.conf file, to enable TCP/IP access from other hosts. 
    tcpip_socket = true
max_connections = 40   (increase if necessary)
    Note
    Ensure that the value of shared_buffers is at least twice the value of max_connections. On PostgreSQL > 8.0 also modify the value of listen_addresses


  4. Start the database.
    In this example, -i enables TCP/IP communication, while -S is for silent mode. 
    > postmaster -S -i


  5. Verify the installation.
    As the postgres user, try the following commands: 
    % su - postgres
> createuser -P test_user
Enter password for new user: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
> createdb -O test_user -E UNICODE test
CREATE DATABASE


  6. Execute commands as the database super user. 
     > psql test
Welcome to psql 8.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
test=# create table test (x int, y text);
CREATE TABLE
test=# insert into test values (1, 'one');
INSERT 16982 1
test=# insert into test values (2, 'two');
INSERT 16983 1
test=# select * from test;
x |  y
---+------
1 | one
2 | two
(2 rows)
test=# \q
 > psql -U test_user test
Password: 
Welcome to psql 8.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
test=>


  7. After you have successfully tested your database software, set up the PostgresSQL database.
  8. After you have set up the database, install the accounting and reporting software.
    See Installing dbwriter and Reporting Software.

How to Configure the ARCo Database on PostgresSQL

  1. Configure the PostgresSQL database server, as described in How to Configure the PostgresSQL Server.

  2. Log in as the database superuser, e.g. postgres. 
    # su - postgres


  3. Create the database owner name and password.
    You will need this information when you install the dbwriter and ARCo console as described in Installing dbwriter and Reporting Software. 
    > createuser -P arco_write
Enter password for new user: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER


  4. Create the accounting and reporting database. 
    > createdb -O arco_write arco
CREATE DATABASE


  5. Create a database user for reading the database. 
    > createuser -P arco_read
Enter password for new user: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER


  6. Grant arco_write permissions on default tablespace.
    The dbdefinition.xml explicitly specifies tablespace name in table definition.
    The arco_write must have permissions to create objects in the specified tablespace. 
    > psql

postgres=# GRANT CREATE ON TABLESPACE pg_default TO arco_write;
    Note
    By using tablespaces, an administrator can control the disk layout of a database installation and optimize performance.
    You can find detailed information on the PostgreSQL tablespaces in the Postgres documentation.


  7. After you have set up the database, install the accounting and reporting software.
    See Installing dbwriter and Reporting Software.

How to Configure the ARCo Database with Multiple Schemas on PostgreSQL

  1. Configure the PostgresSQL database server, as described in How to Configure the PostgresSQL Server.

  2. Log in as the database superuser, e.g. postgres. 
    # su - postgres


  3. Create database user for writing. 
    > createuser -P arco_write_london

 Enter password for new user:  
 Enter it again:  
 Shall the new role be a superuser? (y/n) n
 Shall the new user be allowed to create databases? (y/n) y 
 Shall the new user be allowed to create more new users? (y/n) n
 CREATE USER


  4. Repeat step 2 for each cluster, changing the user name.
    For example, if you have a second cluster called denver, you might use arco_write_denver.
    Note
    The user, database, schema names are arbitrary. You are free to use your own, these were chosen for the demonstrative purposes.


  5. Create a database user for reading. 
    > createuser -P arco_read_london
 Enter password for new user:  
 Enter it again:  
 Shall the new role be a superuser? (y/n) n
 Shall the new user be allowed to create databases? (y/n) n 
 Shall the new user be allowed to create more new users? (y/n) n
 CREATE USER


  6. Repeat step 4 for each cluster, changing the user name.
    For example, if you have a second cluster called denver, you might use arco_write_denver.

  7. Create the accounting and reporting database. 
    > createdb arco

CREATE DATABASE


  8. Log in to the accounting and reporting database console. 
    > psql arco

 arco=#


  9. Grant arco_write_london permissions on default tablespace.
    The dbdefinition.xml explicitly specifies tablespace name in table definition.
    The arco_write must have permissions to create objects in the specified tablespace. 
    arco=# GRANT CREATE ON TABLESPACE pg_default TO arco_write_london;
    Note
    By using tablespaces, an administrator can control the disk layout of a database installation and optimize performance.
    You can find detailed information on the PostgreSQL tablespaces in the Postgres documentation.


  10. Repeat step 8 for each cluster, changing the user name.

  11. Create schemas.
    The schema name should equal the owner name of the schema. The owner of the schema is the arco_write_cluster user. 
    arco=# CREATE SCHEMA arco_write_london AUTHORIZATION arco_write_london; 
CREATE SCHEMA

    Schema arco_write_london owned by user arco_write_london was created.

  12. Repeat step 10 for each cluster, changing the schema name and owner name.

  13. Grant appropriate privileges for users to schemas that they do not own.
    By default, users cannot access any objects in schemas they do not own. To allow other user access to the schema, the user needs to be granted USAGE privilege on that schema. Grant arco_read_cluster the USAGE privilege on the arco_write_cluster schema. 
    arco=# GRANT USAGE ON SCHEMA arco_write_london TO arco_read_london;
GRANT


  14. Repeat step 12 for each cluster, changing the schema name and arco_read_cluster name.
    For example, for a Denver cluster the schema name should be arco_write_denver and the user should be arco_read_denver.

  15. Set search path for ARCo users.
    In the reporting queries, tables are referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching names exist in other schemas in the database.

    In a default setup the search path is: $user, public
    Command SHOW search_path; can be run to show search path for the currently connected user.

    If the schemas in step 10. were created using the pattern schema_name = user_name, then no additional steps are required for arco_write_cluster users. The arco_read_cluster needs to be altered. 
    arco=# ALTER USER arco_read_london SET search_path=arco_write_london;
ALTER ROLE


  16. Repeat step 14 for each cluster, changing the arco_read_cluster and the search_path.

  17. Verify that search_paths are set correctly. 
    arco=# SELECT * FROM pg_user;

    Each arco_read_cluster user should have search_path in useconfig column set to the appropriate arco_write_cluster. Each arco_write_cluster user should have the useconfig field empty, signifying the default search_path.

  18. Create the cross-cluster user
    Note
    In order to perform cross-cluster queries, one user has to be granted SELECT privileges an all the objects in all of the schemas and access these objects using the fully-qualified name, for example <schema_name>.<table_name>. For clarity, we will create a new user. However, you can choose any of your existing users. You will need to supply information for this user during the installation of the reporting module.

    Perform steps 13 - 18 of Migrating PostgreSQL Database to a Different Schema.
    See the example of a cross-cluster query.

  19. After you have set up the database, install the dbwriter and reporting software.
    See Installing dbwriter and Reporting Software.

How to Configure the MySQL Database Server

The Accounting and Reporting Console uses views. As a result, the console supports MySQL database version 5.0.36 and higher. For more information on the MySQL database software, see the MySQL documentation.

Case Sensitivity in MySQL Database

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Because of this the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. Therefore, database and table names are case sensitive in most varieties of UNIX, and not case sensitive in Windows.

  1. Download the appropriate MySQL software for your system from http://www.mysql.com.
    The standard installation directory for UNIX systems is /usr/local/mysql. If you install the software into a different directory, you have to change the settings for the scripts provided in the package.
    Note
    ARCo is a Java web-based application and needs the Java DataBase Connectivity (JDBC) driver for converting JDBC calls into the network protocol used by the MySQL database. You can download the JDBC driver from http://www.mysql.com/products/connector.


  2. Create a symbolic link from the installation directory to MySQL. 
    # ln -s $installation_directory/mysql-standard-5.0.26-solaris10-i386 mysql

    The mysql directory contains several files and subdirectories.

  3. Add a login user and group for mysqld. 
    # groupadd mysql
# useradd -g mysql mysql


  4. Create the MySQL grant tables. 
    # scripts/mysql_install_db --user=mysql


  5. Change the ownership of program binaries to root and ownership of the data directory to the user that you use to run mysqld. 
    # chown -R root .
# chown -R mysql data
# chgrp -R mysql .


  6. Configure MySQL server to use InnoDB as the default storage engine.
    MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both, those that handle transaction-safe tables, and those that handle non-transaction-safe tables. ARCo installation requires the use of transaction-safe tables.

    Edit the my.cnf file and set the option 
    default_storage_engine=innodb

    Configure other innodb properties such as innodb_data_home_dir, innodb_data_file_path.

    For details on InnoDB storage configuration see: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

  7. Start the MySQL server. 
    # bin/mysqld_safe --user=mysql &amp;


  8. Assign the root password. 
    # ./bin/mysqladmin -u root password 'new-password'
# ./bin/mysqladmin -u root -h ${hostname} password 'new-password'


  9. Verify installation.
    Log in to the MySQL console as a superuser. 
    # mysql -u root -p<password>

    As a superuser perform these commands:

    mysql> GRANT ALL on *.* to 'test'@'<database_host>' identified by '<password>' with GRANT OPTION;
mysql> GRANT ALL on *.* to 'test'@'%' identified by '<password>' with GRANT OPTION;

    Log out and log in as the user test.

    mysql> \q
# mysql -u test -p<password>

    As the user test perform these commands:

    mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE sge_test (x integer, y varchar(50));
mysql> SHOW TABLE STATUS FROM test LIKE 'sge_test';


    Important: The field 'Engine' should have a value InnoDB.

MySQL Installation Tips
  • To start the MySQL server at boot time, copy support-files/mysql.server to /etc/init.d and link it to both /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
    If MySQL is not installed in /usr/local/mysql, edit the file to change the basedir and datadir variables.
  • Add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.

How to Configure the ARCo Database on MySQL

  1. Log in to the MySQL console as a superuser. 
    # mysql -u root -p<password>


  2. Create user arco_write and grant him privileges. 
    mysql> GRANT ALL on *.* to 'arco_write'@'<database_host>' identified by '<password>' with GRANT OPTION;
mysql> GRANT ALL on *.* to 'arco_write'@'%' identified by '<password>' with GRANT OPTION;


  3. Exit the MySQL console. 
    mysql> \q


  4. Log in to the MySQL console as arco_write user. 
    # mysql -u arco_write -p<password>


  5. Create the accounting and reporting database. 
    mysql> CREATE DATABASE <db_name>;


  6. Create user arco_read and grant him privileges. 
    mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'<database_host>' identified by '<password>';
mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'%' identified by '<password>';
    Note
    The user 'arco_read'@'%' must be created. If the MySQL database host is the same as the host Sun Java Web Console host where ARCo is running, you also need to create the user 'arco_read'@'database_host'.


  7. Multi-cluster configuration.
    If you are configuring databases for multiple clusters, repeat step 5. through 6., changing the db_name, or grant the user privileges on all databases. 
    mysql> GRANT SELECT,SHOW VIEW on *.* to 'arco_read'@'<database_host>' identified by '<password>';
mysql> GRANT SELECT,SHOW VIEW on *.* to 'arco_read'@'%' identified by '<password>';



    Since the same set of read and write users is used for all databases, no additional steps are required to perform cross-cluster queries.
    See the example of a cross-cluster query.

  8. Install the dbwriter and reporting software.
    See Installing dbwriter and Reporting Software.

Using Oracle Database

  1. Ask your database administrator for an instance of an Oracle database.
    You need two database users for this instance, arco_write and arco_read. The arco_write user must be able to create or alter tables, views, and indexes. During the installation of dbwriter, the arco_read user is granted SELECT privileges on the objects owned by the arco_write user and SYNONYMS for these objects are created in the schema of arco_read user. The SYNONYMS are created by arco_read user, so this user needs to have privilege to create synonyms.

    Here is an example how these users should be created on Oracle:
    Note

    The actual TABLESPACE and QUOTA values might differ.

    CREATE USER "ARCO_WRITE" PROFILE "DEFAULT" IDENTIFIED BY "<password>" 
       DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA 100 M ON "USERS" ACCOUNT UNLOCK;

CREATE USER "ARCO_READ" PROFILE "DEFAULT" IDENTIFIED BY "<password>" 
       DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA 100 M ON "USERS" ACCOUNT UNLOCK;

GRANT CREATE TABLE, CREATE VIEW, CREATE SESSION TO "ARCO_WRITE"
GRANT CREATE SYNONYM, CREATE SESSION TO "ARCO_READ"


  2. Multi-cluster configuration.
    If you have multiple Sun Grid Engine clusters, you will need one pair of users (arco_write_cluster, arco_read_cluster) for each cluster. You will need to install one dbwriter module per cluster, providing one pair of users each time, but only one reporting installation for all the clusters is necessary. During the installation of reporting module, you will provide information for all your clusters (database schemas).

  3. Cross-cluster queries.
    If you intend to perform cross-cluster queries, ask your database administrator to create another user (multi_read) and grant him SELECT privileges on all the objects from the other database schemas. You will provide the information for this user during the installation of the reporting module, and use it to connect to database when performing cross-cluster queries.
    See the example of a cross-cluster query.

  4. Ask your database administrator for the connection parameters to the database.

  5. Install the dbwriter and reporting software.
    See Installing dbwriter and Reporting Software.

Searching Sun Grid Engine 6.2

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

Installing dbwriter and Reporting Software

How to Install dbwriter

Before You Begin

Prior to installing dbwriter, you must install and configure the following on your ARCo system:

Steps
  1. Extract the accounting and reporting software using either the tar method or the pkgadd method.
    • If you use the tar method, type the following command: 
      # cd $SGE_ROOT

      Next, type the following command as one string, with a space between the -dc and the path to the tar file:

      # gunzip -dc <path-to-location-of-file>/sge-6_2-arco.tar.gz | tar xvpf -
    • If you use the pkgadd method, type the following command and respond to the script questions: 
      # cd <cdrom_mount_point>/Sun_Grid_Engine_6_2/ARCo/Packages
# pkgadd -d . SUNWsgeea


  2. As the administrative user, set your environment variables.
    • If you are using a Bourne shell or Korn shell, type the following command: 
      $ . $SGE_ROOT/default/common/settings.sh
    • If you are using a C shell, type the following command: 
      % source $SGE_ROOT/default/common/settings.csh


  3. Change the global configuration to enable reporting.
    For details on how to enable reporting, see About Reporting. 
    % qconf -mconf
<......>
reporting_params    accounting=true \
 reporting=true flush_time=00:00:15 joblog=true \
 sharelog=00:00:00<......>

    By default, report variables are not activated. You can use the qconf command to enable statistics gathering on specific variables, as shown in the following example:

    % qconf -me global
hostname              global
<......>
report_variables      cpu,np_load_avg,mem_free,virtual_free
<......>


  4. Install the dbwriter software.
    For a complete installation example, see Example dbwriter Installation.
    Note
    • You must install the dbwriter software as a root user.
    • If you are upgrading from a Grid Engine version that was released before version 6.2, you must run the installations script with option -upd. This will remove existing RC scripts.
    • If you run the installation script with option -nosmf, SMF will not be used and you will have the option to create an RC script.


  5. Specify the location of your Grid Engine root directory ($SGE_ROOT).
    See lines 011 to 026 of the Example dbwriter Installation.

  6. Specify the names of your Grid Engine cells.
    See lines 027 through 048 of the Example dbwriter Installation.
    If you are not planning to support multiple grid clusters with this ARCo installation, you can use the default cell.

  7. Specify the location of your Java Software Development Kit.
    See lines 049 through 054 of the Example dbwriter Installation.
    • Java Software Development Kit version 1.5 or higher is required.
    • If your JAVA_HOME environment variable is set, the script will use that as a default value.
  8. Specify whether you want to use an existing dbwriter configuration file.
    See lines 055 through 060 of the Example dbwriter Installation.
    This step only appears if an existing dbwriter.conf is detected in $SGE_ROOT/$SGE_CELL/common.
    • Because the configuration file differs between versions, you will be prompted for any missing reporting database connection parameters.
    • If you choose to use the existing dbwriter configuration file, the installation script will skip to step 13.

  9. Specify the basic connection parameters for the reporting database.
    See lines 061 through 070 of the Example dbwriter Installation.

  10. Specify the database user name and password (owner of the database objects).
    See lines 071 through 074 of the Example dbwriter Installation.
    The database user must have permissions to create objects in the database. The default user is arco_write.

  11. Specify the tablespace for tables and indexes.
    See lines 075 through 079 of the Example dbwriter Installation.
    If you are using PostgreSQL or Oracle, you must always specify the following tablespaces:
    • For PostgreSQL, the default tablespace is pg_default.
    • For Oracle, the default is typically USERS.
      The arco_write user must be granted the CREATE privilege on this tablespace. If the arco_write user does not have sufficient privileges, the following error message appears: 
      SEVERE: SQL error: ERROR: permission denied for tablespace pg_default

      To grant privileges, login as a superuser and issue the following command in the database console:

      GRANT CREATE ON TABLESPACE pg_default to arco_write;


  12. Specify the name of the database schema.
    See lines 080 through 081 of the Example dbwriter Installation.
    If you are using PostgreSQL or Oracle, you must supply the schema name. The following values apply:
  13. Specify the database user name and password (the ARCo web application user).
    See lines 082 through 090 of the Example dbwriter Installation.
    The ARCo web application connects to the database using this user, the default is arco_read.
    The user arco_read is granted SELECT privilege on the database tables and views.
    Note
    You will only be prompted to enter a password if you are using Oracle. The installation connects to the database as this user to create synonyms and thus the password for this user is also needed.


  14. Locate the JDBC driver and test the database connection.
    See lines 091 through 102 of the Example dbwriter Installation.
    • If the corresponding JDBC driver is not found, the following error message appears: 
      Error: jdbc driver org.postgresql.Driver
       not found in any jar file of directory
       /opt/sge62/dbwriter/lib

Copy a jdbc driver for your database into
this directory!

      A JDBC driver is not provided with the distribution. If you need to install the driver, do the following:

      • Copy the appropriate JDBC driver into the $SGE_ROOT/dbwriter/lib directory. Use the following drivers:
        • For PostgreSQL, use postgresql-8.3-603.jdbc3.jar
        • For Oracle, use ojdbc14.jar
        • For MySQL, use mysql-connector-java-5.0.4-bin.jar
      • After you copy the JAR file to the correct location, press RETURN, and the search repeats.
    • If the database connection test fails, you can repeat the setup procedure.
  15. Specify how often the dbwriter program should check the reporting file for new data.
    See lines 103 and 106 of the Example dbwriter Installation.

  16. Specify the spool directory.
    See line 107 the Example dbwriter Installation.
    The dbwriter log and process id (pid) files are stored in this directory.

  17. Specify the location of the file that contains the deletion and derived values rules.
    See line 108 of the Example dbwriter Installation.
    By default the dbwriter.xml file that contains the deletion and derived values rules is stored in $SGE_ROOT/dbwriter/database/<database_type>/dbwriter.xml.
    If you move this file to a different location, specify the path to this location.
    For more information, see Derived Values and Deletion Rules.

  18. Set the logging level for the dbwriter.
    See lines 109 through 111 of the Example dbwriter Installation.
    The following levels are available:
    • WARNING is the least-detailed logging level.
    • FINEST is the most-detailed logging level.

  19. Verify the settings.
    See lines 112 through 129 of the Example dbwriter Installation.
    If the settings are not correct and you answer n, you are given the option to repeat the setup.
    If any configuration changes are necessary, do the following:
    • Stop the dbwriter ($SGE_ROOT/$SGE_CELL/common/sgedbwriter stop)
    • Edit the dbwriter.conf file or repeat the installation script
    • Start the dbwriter again ($SGE_ROOT/$SGE_CELL/common/sgedbwriter start)

  20. Check current database model.
    See lines 130 through 151 of the Example dbwriter Installation.
    If a newer version of your current database model is necessary, an upgrade is suggested. During the upgrade, the database objects and constraints (tables, views, indexes, primary and foreign keys) are created or updated.
    Once any necessary upgrades have been completed, the dbwriter installation creates two files:
    • A start script $SGE_ROOT/$SGE_CELL/common/sgedbwriter
    • A configuration file $SGE_ROOT/$SGE_CELL/common/dbwriter.conf.

  21. Specify whether dbwriter should start at boot time.
    See lines 152 through 161 of the Example dbwriter Installation.
    If you choose not to start dbwriter automatically, the SMF will not be used.
    If you choose not to start dbwriter automatically and you ran the installations script with the option -nosmf, RC scripts will not be created.
    To start the dbwriter manually, use one of the following commands: 
    # /etc/init.d/sgedbwriter start

# $SGE_ROOT/$SGE_CELL/common/sgedbwriter start

How to Install Reporting

Before You Begin

Before you begin, verify that the Sun Java Web Console is installed as explained in Installing Sun Java Web Console.

Note
On some Linux platforms, you must set $JAVA_HOME to point to Java version 1.5 or higher, prior to installing the reporting module.
Steps
  1. Change directory to $SGE_ROOT/reporting. 
    # cd $SGE_ROOT/reporting


  2. Use the inst_reporting script to install the software.
    For a complete installation example, see Example Reporting Installation. 
    # ./inst_reporting

Welcome to the Grid Engine ARCo reporting module installation
----------------------------------------------------
The installation will take approximately 5 minutes

Hit <RETURN> to continue >>


  3. (Optional) Set the path to the Sun Java Web Console.
    If the installation script cannot find the Sun Java Web Console commands smcwebserver, wcadmin, and smwebapp, the script asks you to add the appropriate path to your $PATH environment variable. The installation script looks in the following places to find the commands:
    1. The $PATH environment variable
    2. Information contained in the packages for Linux or Solaris
    3. The default path (/opt/sun/webconsole/bin/ for Linux and /usr/share/webconsole/bin/ for Solaris)

  4. Confirm the location of your Grid Engine root directory ($SGE_ROOT).
    See lines 011 through 025 of the Example Reporting Installation.

  5. Specify the names of your Grid Engine cells.
    See lines 026 through 048 of the Example Reporting Installation.
    If you are not planning to support multiple grid clusters with this ARCo installation, you can use the default cell.

  6. Specify the location of your Java Software Development Kit.
    See lines 049 through 055 of the Example Reporting Installation.
    Java Software Development Kit version 1.5 or higher is required. If your JAVA_HOME environment variable is set, the script will use that as a default value.

  7. Specify the spool directory where all queries and results will be stored.
    See lines 056 through 063 of the Example Reporting Installation.
    If this directory does not exist, it will be created for you.

  8. Specify the location of your Grid Engine root directory ($SGE_ROOT).
    See lines 011 through 026 of the Example dbwriter Installation.

  9. Specify the parameters for the database connection.
    See lines 064 through 074 of the Example Reporting Installation.
    If you plan to support more than one grid cluster with this ARCo instance, the next several steps are repeated for each cluster.

  10. Specify an accounting and reporting database user name and password.
    See lines 075 through 079 of the Example Reporting Installation.
    Note
    For security reasons, the database user for accounting and reporting should have only read permission (SELECT) for the database tables. Do not use the same database user that was granted CREATE permission on ARCo database.


  11. Identify the database schema for ARCo console.
    See lines 080 through 081 of the Example Reporting Installation.
    The name of the database schema depends on your database. For a PostgresSQL database, the database schema name should be public. For Oracle, the name of the database schema should the same as the name of the user account which is used by the dbwriter (arco_write).

  12. Identify the name of your grid cluster.
    See lines 082 through 084 of the Example Reporting Installation.
    You should use the same name as $SGE_CLUSTER_NAME.

  13. Confirm the location of the JDBC driver and test the database connection.
    See lines 085 through 096 of the Example Reporting Installation.
    Note
    The JDBC driver is not provided with the distribution.
    Copy the appropriate JDBC driver into the $SGE_ROOT/reporting/WEB-INF/lib directory. We recommend using postgresql-8.3-603.jdbc3.jar for PostgreSQL, ojdbc14.jar for Oracle, and mysql-connector-java-5.0.4-bin.jar for MySQL.


  14. Confirm cluster database parameters and specify whether to support additional grid clusters.
    See lines 097 through 106 of the Example Reporting Installation.
    If you answer yes, the previous several steps are repeated for each cluster.

  15. Enter the login names of users who are allowed to store the queries and results.
    See lines 107 through 112 of the Example Reporting Installation.
    Note
    After installation, you can add or delete authorized users by editing the config.xml file. See How to Add Authorized ARCo Users.


  16. Verify the information.
    See lines 113 through 119 of the Example Reporting Installation.

  17. If a previous version of ARCo is installed, you will be asked to remove it:
    See lines 120 through 131 of the Example Reporting Installation.

  18. Install pre-defined queries.
    See lines 132 through 157 of the Example Reporting Installation.
    If the query directory does not exist, it will be created. The example queries will be installed in the spool directory you have specified. Default /var/spool/arco/queries. Existing queries will be replaced if you choose Y.

  19. Confirm that reporting module is set up.
    See lines 158 through 176 of the Example Reporting Installation.

  20. Confirm that reporting module is registered to the web console and the console starts.
    See lines 177 through 198 of the Example Reporting Installation.
    You should see a series of messages that tell you that the ARCo has installed successfully.

  21. Check the log file for error or warning messages. 
    # more /var/log/webconsole/console/console_debug_log

    The accounting and reporting logs are written to the /var/log/webconsole/console/console_debug_log file. The default log level is INFO, but you can modify the log level from the command line:

    # wcadmin add -p -a reporting arco_logging_level=FINE

    The new log takes effect the next time the console is started or restarted. The possible log levels are WARNING, INFO, FINE, FINER and FINEST.

  22. Connect to the Sun Java Web Console by accessing the following URL in your browser and replace the hostname with the name of your master host. 
    https://hostname:6789


  23. Login with your UNIX account.

  24. Select the Grid Engine Accounting and Reporting Console.

How to Add Authorized ARCo Users

During the installation of the ARCo reporting module, you are asked to enter a list of users who should have write permissions to the ARCo system. Only those users are allowed save modifications on ARCo.

  1. Add users to the appropriate file.
    The list of authorized users is stored in $SGE_ROOT/$SGE_CELL/arco/reporting/config.xml.

  2. After editing this file, restart the Sun Java Web Console: 
    # smcwebserver restart

Searching Sun Grid Engine 6.2

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

Example – dbwriter Installation

The following example shows a complete dbwriter installation. The steps in this example are referred to from the dbwriter installation and configuration description at How to Install dbwriter.

Step 4

001  % su
002  password:
003  # cd $SGE_ROOT/dbwriter
004  # ./inst_dbwriter
005 
006  Welcome to the Grid Engine ARCo dbwriter module 007  installation
007  ------------------------------------------------------------
008  The installation will take approximately 5 minutes
009
010  Hit <RETURN> to continue >

Step 5

011  Checking $SGE_ROOT directory
012  ----------------------------
013
014  The Grid Engine root directory is:
015
016  $SGE_ROOT = /mydiskhome/myuser/sge62
017
018  If this directory is not correct (e.g. it may 
019  contain an automounter
020  prefix) enter the correct path to this directory 
021  or hit <RETURN>
022  to use default [/mydiskhome/myuser/sge62] >>
023
024  Your $SGE_ROOT directory: /mydiskhome/myuser/sge62
025
026  Hit <RETURN> to continue >>

Step 6

027  Grid Engine cells
028  -----------------
029
030  Grid Engine supports multiple cells.
031
032  If you are not planning to run multiple Grid Engine clusters or if you don't
033  know yet what is a Grid Engine cell it is safe to keep the default cell name
034
035     default
036
037  If you want to install multiple cells you can enter a cell name now.
038
039  The environment variable
040
041     $SGE_CELL=<your_cell_name>
042
043  will be set for all further Grid Engine commands.
044
045  Enter cell name [default] >>
046
047  Using cell >default<.
048  Hit <RETURN> to continue >>

Step 7

049  Java setup
050  ----------
051
052  ARCo needs at least java 1.5
053
054  Enter the path to your java installation [/usr/java] >>

Step 8

055  Dbwriter configuration file
056  ---------------------------
057
058  /mydiskhome/myuser/sge62/default/common/dbwriter.conf found.
059
060  Do you want to use the existing dbwriter configuration file? (y/n) [y] >>

Step 9

061  Setup your database connection parameters
062  -----------------------------------------
063
064  Enter your database type ( o = Oracle, p = PostgreSQL, m = MySQL ) [] >> o
065
066  Enter the name of your oracle database host [] >> ge4
067
068  Enter the port of your oracle database [1521] >>
069
070  Enter the name of your oracle database [arco] >> arco

Step 10

071  Enter the name of the database user [arco_write] >> arco_write
072
073  Enter the password of the database user >>
074  Retype the password >>

Step 11

075  The arco_write must have permissions to create objects in the specified tablespace.
076
077  Enter the name of TABLESPACE for tables [USERS] >>
078
079  Enter the name of TABLESPACE for indexes [USERS] >>

Step 12

080  Enter the name of the database schema [arco_write] >> arco_write
081

Step 13

082  The ARCo web application connects to the database with a user which has restricted
083  access. The name of this database user is needed to grant him access to the sge tables
084  and must be different from arco_write.
085  Enter the name of this database user [arco_read] >> arco_read
086
087  This user will also create the synonyms for the ARCo tables and views.
088
089  Enter the password of the database user >>
090  Retype the password >>

Step 14

091  Database connection test
092  ------------------------
093
094  Searching for the jdbc driver oracle.jdbc.driver.OracleDriver
095  in directory /mydiskhome/myuser/sge62/dbwriter/lib
096
097  OK, jdbc driver found
098
099  Should the connection to the database be tested? (y/n) [y] >>
100
101
102  Test database connection to 'jdbc:oracle:thin:@ge4:1521:orcl' ... OK

Step 15

103  Generic parameters
104  ------------------
105
106  Enter the interval between two dbwriter runs in seconds [60] >>

Step 16

107  Enter the path of the dbwriter spool directory [/mydiskhome/myuser/sge62/default/spool/dbwriter]>>

Step 17

108  Enter the file with the derived value rules [/mydiskhome/myuser/sge62/dbwriter/database/oracle/dbwriter.xml] >>

Step 18

109  The dbwriter can run with different debug levels
110  Possible values: WARNING INFO CONFIG FINE FINER FINEST
111  Enter the debug level of the dbwriter [INFO] >>

Step 19

112  All parameters are now collected
113  --------------------------------
114
115          SGE_ROOT=/mydiskhome/myuser/sge62
116          SGE_CELL=default
117         JAVA_HOME=/opt/jdk1.5.0 (1.5.0_13)
118            DB_URL=jdbc:oracle:thin:@ge4:1521:orcl
119           DB_USER=arco_write
120         READ_USER=arco_read
121        TABLESPACE=USERS
122  TABLESPACE_INDEX=USERS
123         DB_SCHEMA=arco_write
124          INTERVAL=60
125         SPOOL_DIR=/mydiskhome/myuser/sge62/default/spool/dbwriter
126      DERIVED_FILE=/mydiskhome/myuser/sge62/dbwriter/database/oracle/dbwriter.xml
127       DEBUG_LEVEL=INFO
128
129  Are these settings correct? (y/n) [y] >>

Step 20

130  Database model installation/upgrade
131  -----------------------------------
132  Query database version ... no sge tables found
133  New version of the database model is needed
134
135  Should the database model be upgraded to version 8? (y/n) [y] >>
136
137  Upgrade to database model version 8 ... Install version 6.0 (id=0) -------
138  Create table sge_job
139  Create index sge_job_idx0
140  .
141  .
142  .
143  Update version table
144  committing changes
145  Version 6.2 (id=8) successfully installed
146  OK
147  Create start script sgedbwriter in /mydiskhome/myuser/sge62/default/common
148  
149  Create configuration file for dbwriter in /mydiskhome/myuser/sge62/default/common
150
151  Hit <RETURN> to continue >>

Step 21

152  dbwriter startup script
153  -----------------------
154
155  Do you want to start dbwriter automatically at machine boot?
156  NOTE: If you select "n" SMF will be not used at all! (y/n) [y] >> n
157
158  Creating dbwriter spool directory /mydiskhome/myuser/sge62/default/spool/dbwriter
159  starting dbwriter
160  dbwriter started (pid=4714)
161  Installation of dbwriter completed



Searching Sun Grid Engine 6.2

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

Example – Reporting Installation

The following example shows a complete ARCo reporting installation. The steps in this example are referred to from the ARCo reporting installation and configuration description at How to Install Reporting.

Step 2

001   # cd $SGE_ROOT/reporting
002   
003   # ./inst_reporting
004  
005   Welcome to the Grid Engine ARCo reporting module installation
006   -------------------------------------------------------------
007   The installation will take approximately 5 minutes
008   
009   Hit <RETURN> to continue >>
010

Step 3

011   Checking $SGE_ROOT directory
012   ----------------------------
013   
014   The Grid Engine root directory is:
015   
016      $SGE_ROOT = /mydiskhome/myuser/sge62
017
018   If this directory is not correct (e.g. it may contain an automounter
019   prefix) enter the correct path to this directory or hit <RETURN>
020   to use default [/mydiskhome/myuser/sge62] >>
021   
022   Your $SGE_ROOT directory: /mydiskhome/myuser/sge62
023
024   Hit <RETURN> to continue >>
025

Step 4

026   Grid Engine cells
027   -----------------
028   
029   Grid Engine supports multiple cells.
030   
031   If you are not planning to run multiple Grid Engine clusters or if you don't
032   know yet what is a Grid Engine cell it is safe to keep the default cell name
033   
034      default
035   
036   If you want to install multiple cells you can enter a cell name now.
037   
038    The environment variable
039   
040      $SGE_CELL=<your_cell_name>
041
042   will be set for all further Grid Engine commands.
043
044   Enter cell name [default] >>
045
046   Using cell >default<.
047   Hit <RETURN> to continue >>
048

Step 5

049   Java setup
050   ----------
051
052   We need at least java 1.5
053
054   Enter the path to your java installation [/myhomedisk/SW/jdk1.5.0/sol-amd64] >>
055

Step 6

056   Spool directory
057   ---------------
058
059   In the spool directory the Grid Engine reporting module will
060   store all queries and results
061
062   Enter the path to the spool directory [/var/spool/arco] >>
063

Step 7

064   Cluster Database Setup
065   ----------------------
066
067   Enter your database type ( o = Oracle, p = PostgreSQL, m = MySQL ) [p] >>
068
069   Enter the name of your postgresql database host [] >> ge7
070
071   Enter the port of your postgresql database [5432] >>
072
073   Enter the name of your postgresql database [arco] >>
074

Step 8

075   Enter the name of the database user [arco_read] >>
076   
077   Enter the password of the database user >>
078   Retype the password >>
079

Step 9

080   Enter the name of the database schema [public] >>
081

Step 10

082   Enter the name of your cluster
083  (it is recommended to use the same name as $SGE_CLUSTER_NAME) [ge7:arco:arco_read] >>
084

Step 11

085   Database connection test
086   ------------------------
087   
088   Searching for the jdbc driver org.postgresql.Driver
089   in directory /net/gefs.czech/ws/jo195647/sge62/reporting/WEB-INF/lib
090
091   OK, jdbc driver found
092
093   Should the connection to the database be tested? (y/n) [y] >>
094
095   Test database connection to 'jdbc:postgresql://ge7:5432/arco' ... OK
096

Step 12

097   DB parameters are now collected
098   -------------------------------
099       CLUSTER_NAME=ge7:arco:arco_read
100             DB_URL=jdbc:postgresql://ge7:5432/arco
101             DB_USER=arco_read
102
103   Are these settings correct? (y/n) [y] >>
104
105   Do you want to add another cluster? (y/n) [n] >>
106

== If yes is answered steps starting with Cluster Database Setup
are repeated, so info for next cluster can be entered.

Step 13

107   Configure users with write access
108   ---------------------------------
109
110   Users: myuser1
111   Enter a login name of a user (Press enter to finish) >>
112

Step 14

113   All parameters are now collected
114   --------------------------------
115         SPOOL_DIR=/var/spool/arco
116         APPL_USERS=jo195647
117
118     Are this settings correct? (y/n) [y] >>
119

Step 15

120   Grid Engine reporting module already registered at Sun Java Web Console
121   -------------------------------------------------------------------
122
123   The Grid Engine reporting modules can only be installed
124   if no previous version is registered.
125
126   Should the Grid Engine reporting module com.sun.grid.arco_6.2-Maintrunk be unregistered? (y/n) [y] >>
127
128   The reporting web application has been successfully undeployed.
129   
130   Hit <RETURN> to continue >>
131

Step 16

132   Install predefined queries
133   --------------------------
134
135   query directory /var/spool/arco/queries already exists
136   Copy examples queries into /var/spool/arco/queries
137   Query AR_Attributes.xml already exists. Overwrite? ( y = yes, n = no, Y = yes to all, N = no to all ) [n]  >> Y
138   Copy query AR_Attributes.xml ... OK
139   Copy query AR_Log.xml ... OK
140   Copy query AR_Reserved_Time_Usage.xml ... OK
141   Copy query AR_by_User.xml ... OK
142   Copy query Accounting_per_AR.xml ... OK
143   Copy query Accounting_per_Department.xml ... OK
144   Copy query Accounting_per_Project.xml ... OK
145   Copy query Accounting_per_User.xml ... OK
146   Copy query Average_Job_Turnaround_Time.xml ... OK
147   Copy query Average_Job_Wait_Time.xml ... OK
148   Copy query DBWriter_Performance.xml ... OK
149   Copy query Host_Load.xml ... OK
150   Copy query Job_Log.xml ... OK
151   Copy query Number_of_Jobs_Completed_per_AR.xml ... OK
152   Copy query Number_of_Jobs_completed.xml ... OK
153   Copy query Queue_Consumables.xml ... OK
154   Copy query Statistic_History.xml ... OK
155   Copy query Statistics.xml ... OK
156   Copy query Wallclock_time.xml ... OK
157

== if 'n' or 'N' is selected the queries will not be updated (not recommended)

Step 17

158   ARCo reporting module setup
159   ---------------------------
160   
161   Found a previous installed version of the ARCo reporting
162   modules at /ws/jo195647/sge62/default/arco
163
164   Remove directory /ws/jo195647/sge62/default/arco/reporting? (y/n) [y] >>
165
166   directory /ws/jo195647/sge62/default/arco/reporting removed
167   Copying ARCo reporting file into /ws/jo195647/sge62/default/arco/reporting
168
169   Setting up ARCo reporting configuration file. After registration of
170   the ARCo reporting module at the Sun Java Web Console you can find
171   this file at
172
173         /ws/jo195647/sge62/default/arco/reporting/config.xml
174
175   Hit <RETURN> to continue >>
176

Step 18

177   Importing Sun Java Web Console 3.0 files into the /ws/jo195647/sge62/default/arco/reporting
178   ---------------------------------------------------------------------------
179   Imported files to /ws/jo195647/sge62/default/arco/reporting
180   Created product images in /ws/jo195647/sge62/default/arco/reporting/com_sun_web_ui/images
181
182   Hit <RETURN> to continue >>
183   
184   Registering the Grid Engine reporting module in the Sun Java Web Console
185   --------------------------------------------------------------------
186   The reporting web application has been successfully deployed.
187   Set 1 properties for the com.sun.grid.arco_6.2-Maintrunk application.
188   Set 1 properties for the com.sun.grid.arco_6.2-Maintrunk application.
189   Set 1 properties for the com.sun.grid.arco_6.2-Maintrunk application.
190   Creating the TOC file ... OK
191   
192   Hit <RETURN> to continue >>
193   
194   Restarting Sun Java Web Console
195   -------------------------------
196   Restarting Sun Java(TM) Web Console Version 3.0.2 ...
197   The console is running
198   Grid Engine  ARCo reporting successfully installed

Searching Sun Grid Engine 6.2

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

Installing Sun Java Web Console

Note
Sun Java Web Console 3.0 is installed automatically on Solaris 10 Update 3 or later. To install Sun Java Web Console on an older version of the Solaris operating system, follow these steps.

How to Install Sun Java Web Console

  1. Check whether Sun Java Web Console is already available on your system, as is usually the case for the Solaris 10 software and on newer Solaris 9 versions.
    As root, you can check using the following command: 
    # smcwebserver -V 
Version 3.0.2
    Note
    ARCo for Sun Grid Engine 6.2 software requires Sun Java Web Console 3.0.x.


  2. If you need to install the console, extract the web console package under a temporary directory. 
    # cd /tmp
# umask 022
# mkdir swc
# cd swc
# tar xvf cdrom_mount_point/N1_Grid_Engine_6_2/SunWebConsole/tar/swc_sparc_3.0.2.tar


  3. If the user noaccess and the group noaccess do not exist in your password file or NIS passwd map, add them by typing: 
    # groupadd -g 60002 noaccess
# useradd -u 60002 -g 60002 -d /tmp -s /bin/sh -c "No Access User" noaccess
    Note
    On some operating systems, using /bin/csh as the default shell might cause the installation to fail. If ARCo will not install, and you see the error message "Server not started! No management applications registered", then try setting the default shell as /bin/sh for noaccess.


  4. If you are running SuSE 9.0, create symbolic links for each of the /etc/rc#.d directories. 
    # ln -s /etc/rc.d/rc0.d /etc/rc0.d
# ln -s /etc/rc.d/rc1.d /etc/rc1.d
# ln -s /etc/rc.d/rc2.d /etc/rc2.d


  5. Run the Sun Java Web Console setup script. 
    # ./setup -n
<....>
Installation complete.

Starting Sun(TM) Web Console Version 3.0.2...
See /var/log/webconsole/console/console_debug_log for server logging information

    The web console is installed but not started until after the ARCo console installation. Once the console is installed, you can use the following commands to stop, start, or restart the console at any time:

    # /usr/sbin/smcwebserver start
# /usr/sbin/smcwebserver stop
# /usr/sbin/smcwebserver restart

Searching Sun Grid Engine 6.2

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

Upgrading the Accounting and Reporting Console (ARCo)

About Upgrading ARCo

Note
The ARCo upgrade is in fact re-installation of the dbwriter and reporting modules, during which you supply the parameters of your existing database and database users.

During the installation of dbwriter, the existing database schema version is checked and updated, if a newer version is available.

During the installation of reporting, the following actions occur:

  • Predefined queries in the reporting spool directory (default: /var/spool/arco) are overwritten.
    Note
    Only the predefined queries will be overwritten, none of your custom queries will be modified.
  • The reporting module is unregistered from the Sun Java Web Console and deployed again.

If you specify a different spool directory during re-installation of reporting, you will need to move your custom queries and results from the spool directory of your previous installation to the new directory, so that they appear in the Sun Java Web Console. Before proceeding with the upgrade, read all the steps in How to Upgrade ARCo Software.

How to Upgrade ARCo Software

  1. Ensure that there are no running or pending jobs.

  2. Follow information for shutting down the cluster.
    See Upgrading Sun Grid Engine Software.

  3. Ensure that the reporting file has been completely processed by dbwriter, so all the job information from the previous Sun Grid Engine installation has been inserted into the database.
    There should be no reporting or reporting.processing file in the $SGE_ROOT/$SGE_CELL/common directory.

  4. Once the reporting file has been processed, do the following on the dbwriter host:
    1. Source the cluster settings.sh (or .csh) file.
    2. Stop the dbwriter 
      > $SGE_ROOT/$SGE_CELL/common/sgedbwriter stop


  5. Finish upgrading Sun Grid Engine.
    See Upgrading Sun Grid Engine Software.
    Note
    After finishing the Sun Grid Engine upgrade, qmaster can be started and jobs submitted to minimize the downtime, as long as dbwriter is not started.



  6. Back up the existing ARCo databases.
    Refer to your database manuals on how to backup a database.

  7. (Optional) If you plan to perform cross-cluster queries and have PostgreSQL, go to Migrating PostgreSQL Database to a Different Schema; otherwise, continue with the next step.
    Note
    You might want to migrate your existing PostgreSQL databases under a single one with multiple schemas, even if you do not plan to perform cross-cluster queries, but you simply like to consolidate all under one roof.



  8. Reinstall dbwriter.
    Note
    If upgrading from version < 6.2, you must run the installations script with option -upd. This will remove existing RC scripts.

    See How to Install dbwriter.

  9. Reinstall reporting.
    See How to Install Reporting.

Migrating PostgreSQL Database to a Different Schema

If you do not plan to perform cross-cluster queries, follow the standard ARCo upgrade procedure. If you have an existing ARCo installation and want to use the multi-cluster features, follow these steps to migrate existing PostgreSQL ARCo databases to the schema configuration.

  1. Prepare a database to which to migrate.
    Follow Configuring Single PostgreSQL Database with Multiple Schemas.
    Note
    Throughout this section, in the code snippets and accompanying text, the following values need to be replaced by your appropriate names:
      • postgres - the database superuser
      • arco - the database you are migrating to and that has schemas configured
      • filename - path to a file where all output from the database console will be redirected. The postgres user must have write privileges to the file.
      • arco_write_london - the schema name you are migrating to.
      • arco_read_london - is the user used by reporting application to access the database; search_path of this user is set to arco_write_london.
      • multi_read - is a a user used to perform cross-cluster queries; it must be able to access all schemas and read all object in the schemas.


  2. Restore data from your first database backup file into arco database.
    See http://www.postgresql.org/docs/8.1/interactive/backup.html.
    Note
    After restoring a database backup into a new database with schemas, database object are restored into the default public schema. Hence, you need to restore one backup at a time and only after moving objects to a different schema, you can restore the next backup.


  3. Change to the database superuser. 
    # su - postgres


  4. Log in to the arco database. 
    > psql arco


  5. Change the output display, so that column name headings and row count footer are not shown. 
    arco=# \t
 Showing only tuples.


  6. Redirect the output of a query to a file.
    The postgres user must have write privileges to the file. 
    arco=# \o filename


  7. Execute the following command to generate commands for moving each table to a different schema. 
    arco=# select 'alter table ' || tablename || ' set schema arco_write_london;' 
       from pg_tables where schemaname='public';


  8. Execute the following command to generate commands for moving each view to a different schema. 
    arco=# select 'alter table ' || viewname || ' set schema arco_write_london;' 
       from pg_views where schemaname='public';


  9. Execute the following commands to generate commands for granting arco_read_london select privileges on all the database objects in the specified schema. 
    arco=# select 'grant select on ' ||schemaname||'.'||tablename|| ' to arco_read_london;' 
       from pg_tables where schemaname='arco_write_london';
arco=# select 'grant select on ' ||schemaname||'.'||viewname|| ' to arco_read_london;' 
       from pg_views where schemaname='arco_write_london';


  10. Reset the psql console to the default state. 
    arco=# \o
arco=# \t


  11. Run all the commands from the created file. 
    arco=# \i <filename>


  12. Restore the next backup, and follow the steps 3 - 9, changing the schema and user names appropriately.
    Note
    Remember to use a different output filename or delete the previous file.


  13. Create the multi_read user. 
    arco=# CREATE USER multi_read WITH PASSWORD 'your_password';


  14. Grant multi_read usage on all schemas. 
    arco=# GRANT USAGE ON SCHEMA arco_write_london TO multi_read;


  15. Repeat the previous step for each schema, changing the schema name.

  16. Execute steps 5 - 6.
    Note
    Remember to use a different output filename or delete the previous file.


  17. Execute the following commands to generate commands granting multi_read select privilege on all database object in all the schemas. 
    arco=# select 'grant select on ' ||schemaname||'.'||tablename|| ' to multi_read;' 
       from pg_tables where schemaname in ('arco_write_london', 'next_schema_name', ...);
arco=# select 'grant select on ' ||schemaname||'.'||viewname||' to multi_read;' 
       from pg_views where schemaname in ('arco_write_london', 'next_schema_name', ...);


  18. Execute steps 10 - 11.

  19. Reinstall dbwriter.
    Note
    • If upgrading from version < 6.2, you must run the installations script with option -upd. This will remove existing RC scripts.

      During the installation, point each dbwriter to the newly created database with multiple schemas and specify appropriate arco_write_cluster user and schema.

    See How to Install dbwriter.

  20. Reinstall reporting.
    Note
    During the re-installation of the reporting module, enter the required information for all the configured database schemas.

    See How to Install Reporting.

Searching Sun Grid Engine 6.2

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

Starting the Accounting and Reporting Console

The accounting and reporting console is installed separately from the Sun Grid Engine software. For details on the installation process, see Installing ARCo. In addition, you must enable your Grid Engine system to collect reporting information. For details about how to enable the collection of reporting data, see Enabling Generation of the Reporting File.

How to Start the Accounting and Reporting Console

  1. From your web browser, type the URL to connect to the Sun Java Web Console.
    In the following example, hostname is the host on which the accounting and reporting software has been installed. 
    https://<hostname>:6789


  2. Log in to your UNIX account.

  3. In the Java Web Console main page, select the Accounting and Reporting application.
    Tip
    You can also use a link similar to the following example to go directly to the ARCo application from within your web browser: https://hostname:6789/console/login/Login?redirect_url=%22/reporting/arcomodule/Index%22.

    The Overview page appears. The Query List shows a list of predefined ARCo queries on the selected grid cluster. From the Overview page, you can perform the following tasks:

    • To view details about a defined query, click the query Name in the Query List.
    • To view the results of any queries that you have run on this cluster, click the Results tab.
    • To create a new query, click the New Simple button.
    • To create a query by editing the SQL directly, click the New Advanced button.
    • To run a defined query, click the circle next to the query name that you want to run, then click the Run button.
    • To edit a defined query, click the circle next to the query name that you want to run, then click the Edit button.
    • To view information about or run statistics on a different grid cluster, select the cluster from the Cluster menu.
Note
For detailed information about these tasks, see the online help from within the ARCo console.

Creating and Modifying Simple Queries

The query defines the data set that you want to retrieve. You can create simple queries for which the system formulates the SQL query string. If you know SQL and you want to write the query yourself, you can create advanced queries as described in Creating and Modifying Advanced Queries.

How to Create a Simple Query

  1. In the Query List on the ARCo Overview page, click the New Simple button.
    The Simple Query screen appears showing common information, such as the query category and description. This information is optional.
    "Simple Query screen showing Category and Description fields. Shows Simple Query and View tabs. Shows Run
  2. To define the query, click the Simple Query tab.
    Tip

    To define how to display the results of the query, go to the View tab.


  3. To choose a database table or view to predefine your query, select from the Table/View list.
    "Simple query screen shows table
  4. To define the fields on which the query is to run, click the Add button in the Fields section.
    • The Function eanbles you to apply either an aggregate function or a numeric operator to the specified field. Supported values are:
      VALUE Display the current value of the field
      SUM Accumulate the values of the field
      COUNT Count the number of values of the field
      MIN Get the minimum value of the field
      MAX Get the maximum value of the field
      AVG Get the average value of the field
      Note
      Numeric functions only apply to numeric field values and must be used with a Parameter.
    • The Name is the name of a column in the selected table or view.
    • The Parameter is applied when you choose a numeric operator in the Function.
    • The Username enables you to provide a more meaningful name to display in the query result.
    • Sort enables you to define the sorting order for the field.
  5. (Optional) Define Filters.
    You must specify at least one field before you can define filters.
    • AND/OR is needed for any filter except the first. This setting provides the logical connection to the previous filter condition.
    • The Field Name is the name of the field to be filtered. If a field has a user-defined name, that name is shown in the selection list. Otherwise, a generated name is shown.
    • The Condition field specifies the operators that are used to filter the values from the database.
    • The Parameter field contains a value that is used for filtering the values returned by the query.
    • Late Binding causes the Parameter value to act as a placeholder. When Late Binding is checked, you are given the opportunity to change the Parameter value when you run the query.
    • Active enables or disables the filter.

      The following table lists the supported operators.
      Condition Symbol Description Number of Parameters Parameter Usage
      Equal = Filters the fields that equal the Parameter 1  
      Not Equal <>, != Filters the fields that do not equal the Parameter 1  
      Less Than < Filters the fields that are less than the Parameter 1  
      Less Than or Equal <=, ≤ Filters the fields that are less than or equal to the Parameter 1  
      Greater Than > Filters the fields that are greater than the Parameter 1  
      Greater Than or Equal >=, ≥ Filters the fields that are greater than or equal to the Parameter 1  
      Null   Filters the fields that are null 0  
      Not Null   Filters the fields that are not null 0  
      Between   Filters the fields that are within the specified interval 2 1 AND 100
      In   Filters the fields that ar equal to an element of a specified list 1 or more dep-234, dep-bio, dep-phy
      Like   Filters the fields that match the specified pattern. 1 % allows to match any string on any length (including zero length)
      %bob% will return the only the fields containing the string bob
      _ allows to match on a sigle character
  6. (Optional) Limit the number of rows to be returned.
    Type the number of rows you want to return in the Row Limit textbox. If the result contains more rows, only the specified number are displayed.

  7. Save or run the query.
    • To save the query, click Save or Save As.
    • To run the query, click Run.

How to Modify a Simple Query

  1. Select a query from the list on the Query List screen.

  2. Click Edit.
    The selected Simple Query screen displays.

  3. Make changes to the Simple Query screen by navigating through the tabs and making your changes as you would when creating a simple query.

  4. Save or run the changed query.
    • To save the query, click Save or Save As.
    • To run the query, click Run.

Creating and Modifying Advanced Queries

Note
You must have previous experience writing SQL queries to use this feature of the accounting and reporting console.

How to Create an Advanced Query

  1. In the Query List on the ARCo Overview page, click the New Advanced button.
    The Advanced Query screen appears showing common information, such as the query category and description. This information is optional.

  2. To define the query, click the Simple Query tab.
    Tip

    To define how to display the results of the query, go to the View tab.


  3. Type your SQL query in the field.
    "Advanced Query page showing box in which you type the SQL query
  4. Save or run the query.
    • To save the query, click Save or Save As.
    • To run the query, click Run.

How to Edit an Advanced Query

  1. Select a query from the list on the Query List screen.

  2. Click Edit.
    A completed version of the Advanced Query screen displays.

  3. Make changes to the SQL query.

  4. Save or run the changed query.
    • To save the query, click Save or Save As.
    • To run the query, click Run.

Latebindings for Advanced Queries

The syntax for the latebindings in advanced queries is:

LATEBINDING { <column>;<operator>;<default value> }

   <column>    name of the latebinding
   <operator>  SQL operator (e.g. = < > in .. )
   <value>     default value (e.g. 'localhost' )
Example – Latebindings 
select * from sge_host where LATEBINDING {h_hostname; like; a%}
select * from sge_host where LATEBINDING {h_hostname; in; ('localhost', 'foo.bar')}

Configuring the Query Results View

By default, query results display a database table that contains all the requested information. For Simple and Advanced queries, you can add a pie chart, bar chart, or line diagram to that table. You can also change the view of the database table itself.

How to Configure the Query Results View

  1. To change the view configuration for a query, click the View tab in either the Simple Query or Advanced Query screen.
    To create a view for a saved query:
    • Choose the query from the Query List on the Overview page.
    • Click the Edit button.
    • Click the View tab.

      The current view configuration for the selected query displays.
      View tab showing Add Pivot and Add Graphic buttons
      Note
      For some queries, only a subset of the possible view selections are meaningful. For example, if you have only two columns to select from, pivot makes no sense.


  2. Choose whether to display additional query details.
    In the View Configuration section, you can show or hide the following query details:
    • The query description that you entered in the Common tab
    • The filter conditions or parameters that you defined in the Simple Query
    • The SQL statement that defines the query, either as assembled by the Simple Query or as you typed it in the SQL tab in the Advanced Query.

  3. To configure the table display, click Add Table.
    START HERE ON MONDAY
    Choose the columns that you need to display under Name and adjust their Type and Format. The order in which the columns are added will be the order in which the columns are presented. The selections that you make for this report do not affect the filters applied to the data.
    "Database Table screen shows Name
  4. To add a pivot table, click Add Pivot.
    Add the pivot column, row, and data entries. Then choose the column Name, Type, and Format. To shift an entry to a different pivot type, select it under Pivot Type.
    "Pivot Table screen shows Name
  5. To add a graphical view of your data, click Add Graphic.
    Graphical Presentation page showing type of diagram to display and data to include
  6. Select the diagram type for your graphic.
    You can attach the query data to bar, pie, or line diagram types. The following chart types are available from the Diagram Type menu:
    • Bar Chart
    • Bar Chart (3D)
    • Bar Chart Stacked
    • Bar Chart Stacked (3d)
    • Pie Chart, Pie Chart 3D
    • Line Chart
    • Line Chart Stacked Line

      You can choose to display Bar and Pie types with a 3D effect. You can choose to draw stacked Bar and Line diagrams with values on the y-axis summarized.
  7. Select the value to display on the X-axis.

  8. Decide whether to define the data series based on rows or columns.
    • Series from columns: All column values are added to a series. The name of the series is the column header
    • Series from rows: All column values define the series. The names of the series is defined by the values of the label column. The values of the series are defined by the value column.
  9. Choose specific details as appropriate for your diagram type.
    Because graphic displays are somewhat complex to define, you might find it more useful to look at some examples. See Defining Data Series for Diagrams.

  10. Click Save or Save As to save your View configuration to the query.

  11. Click Run to run your query.

Examples for Defining Graphical Views

The following two examples show the default view first, followed by the View selections, followed by the graphical result.

Example 1 – Accounting per Department Pie Chart

The query “Accounting per Department” results in a table with the columns: time, department, and cpu.

"Database Table showing Time

To display the result in a pie chart, select the following configuration:

"Graphical Presentation page showing Diagram Type of pie chart

The result will be multiple pie charts, similar to those shown in this figure.

Three pie charts

Example 2 – CPU, Input/Output, and Memory Usage Over All Departments Bar Chart

A query summarizes CPU, IO, and Mem usage over all departments:

"Database Table showing Department

To display the results in a bar chart, select the following configuration:

"Graphical Presentation page showing Diagram Type of bar chart

The results will be a bar chart with three bars for each department, similar to the chart shown in this figure.

Bar chart with three bars for each department

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

Searching Sun Grid Engine 6.2

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

ARCo Troubleshooting

Problem: Reporting module installation on Red Hat Enterprise Linux

On certain versions of RHEL, while using SJWC 3.0.x or 3.1.x, you might see following error when during the reporting module installation:

Registering the SGE reporting module in the Sun Java Web Console
----------------------------------------------------------------
Must have administration privileges to execute this command.
Must have administration privileges to execute this command.
Must have administration privileges to execute this command.
Must have administration privileges to execute this command.
Creating the TOC file ... OK

If you run manually the command smcwebserver start, you might see this error:

Starting Sun Java(TM) Web Console Version 3.0.2 ...
Exception while starting container instance console: An exception was thrown while executing 
/var/opt/webconsole/domains/console/conf/wcstart nobody

These issues are related to the Sun Java Web Console.

Solution: Follow these steps:

The unsuccessful reporting installation has to be run prior to performing these steps.
  • Execute these two commands: 
    chmod +x /var/opt/webconsole/domains/console/conf/wcstart
chmod +x /var/opt/webconsole/domains/console/conf/wcstop
  • Manually edit the /etc/opt/webconsole/console/service.properties file and add the following properties (replace the paths with fully qualified names): 
    arco_app_dir=$SGE_ROOT/$SGE_CELL/arco/reporting
arco_logging_level=INFO
arco_config_file=$SGE_ROOT/$SGE_CELL/arco/reporting/config.xml
  • Create file reporting.reg in /etc/opt/webconsole/console/prereg/com.sun.grid.arco_6.2u3
    (this is a regnot file, which is normally created during deploy).
  • Add the following information to the regnot file, which you had created in the previous step (replace paths with fully qualified names): 
    system=false
debug=0
context=reporting
type=webapp
location=$SGE_ROOT/$SGE_CELL/arco/reporting
  • As a root user, restart the smcwebserver 
    smcwebserver restart

After this ARCo should function correctly. However, you will still experience the 'Must have administration privileges to execute this command' while executing the wcadmin command.

Problem – No application is registered with this Sun Java TM Web Console or you have no rights to use any applications that are registered

The above error can happen on some Linux platforms, while using SJWC 3.0.x, if your $JAVA_HOME is not set or is set to a version of Java that is less than 1.5. Another indication of this problem is the absence of the following files: $SGE_ROOT/$SGE_CELL/arco/reporting/WEB-INF/tld and $SGE_ROOT/$SGE_CELL/arco/reporting/WEB-INF/lib/registrationservlet.jar.

Solution: Follow these steps:

  1. Set your $JAVA_HOME variable to at least version 1.5 of the Java software.
  2. Reinstall the reporting module.

Problem – SEVERE: SQL error: ERROR: permission denied for tablespace pg_default

The above SQL error is shown during installation of dbwriter.

Solution – You must always specify the tablespace, unless you are using MySQL. For PostgreSQL, the default tablespace is pg_default. For Oracle, the default is typically USERS.

The arco_write user must be granted the CREATE privilege on this tablespace. If the arco_write user does not have the sufficient privileges the above error message appears.

In database console as a superuser issue a command and then repeat the installation:

GRANT CREATE ON TABLESPACE pg_default to arco_write;

Problem – SEVERE: SQL error: Column 'ju_start_time' in field list is ambiguous

The above error can happen on some minor release versions of MySQL server, namely 5.0.26 or 5.0.27, where MySQL considers, some more complicated queries as syntactically incorrect. Newer versions of MySQL server handle them correctly.

  • If you are doing a fresh ARCo installation and not an upgrade, you can safely edit the $SGE_ROOT/dbwriter/database/mysql/dbdefinition.xml file.
    Remove everything contained between the <version id="6" name="6.1u3"> </version> tags, except the last item. So, the part should look like this:
<version id="6" name="6.1u3">
<item>
        <description>Update version table </description>
        <sql>
           INSERT INTO sge_version (v_id, v_version, v_time)
           VALUES(6, '6.1u3', current_timestamp)
        </sql>
     </item>
</version>
  • If you are upgrading, upgrade your MySQL Server to a higher version, before proceeding with dbwriter installation.

Problem – SEVERE: SQL error: ORA-01031: insufficient privileges

The above error may be caused during the installation of dbwriter, while the synonyms are being created. Because arco_read is the user who uses the synonyms, in ARCo versions > 6.1u4 the synonyms are being created by user arco_read, in the schema of user arco_read. Thus, the user arco_read needs to be granted the privilege to create synonyms. The ARCo users should be granted the following set of privileges:

GRANT CREATE TABLE, CREATE VIEW, CREATE SESSION TO "ARCO_WRITE";
GRANT CREATE SYNONYM, CREATE SESSION TO "ARCO_READ";

Problem – SEVERE: SQL error: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

The above SQL error is shown during installation of dbwriter.

Solution: During the installation, after the connection test and database version check, you are prompted to enter the name of the user which has a restricted access to the database arco_read. The ARCo web application connects to the database using the user arco_read, and because this user is not the owner of the database objects it needs to be granted SELECT privilege on those objects. On Oracle synonyms are also created in the schema of the arco_read user and thus password for this user is also needed.

If you have entered arco_write instead of the arco_read user in the prompt below, you would see the errors above. Repeat installation and provide the correct user name.

The ARCo web application connects to the database
with a user which has restricted access. 
The name of this database user is needed to grant
him access to the sge tables. 
This user will create the synonyms for the ARCo
tables and views, so the user's password is needed.

Enter the name of this database user [] >> ARCO_READ

Enter the password of the database user >>
Retype the password >>
Note
On PostgreSQL or MySQL, you will not see this error during installation of dbwriter but you will not be able to run any queries from the ARCo web application, because the arco_read user has not been granted the SELECT privileges on the database objects.

Problem – SEVERE: SQL error: ORA-00955: name is already used by an existing object

The above SQL error is shown during installation of dbwriter.

Solution: Same as in the error above.

Problem – The table or view dropdown menu of a simple query definition does not contain any entry but the tables are defined in the database.

Solution: The problem normally occurs when using Oracle as the database server. During the installation of the reporting module, wrong database schema name has been specified. For Oracle, the database schema name is equal to the name of the database user, which is used by dbwriter (the default name is arco_write). For Postgres, the database schema is by default public, or if you have configured separate schemas, it is equal to the name of the database user, which is used by dbwriter.

Problem – Connection refused.

Solution: The smcwebserver might be down. Start or restart the smcwebserver.

Problem – The list of queries or the list of results is empty.

Solution: The cause can be any of the following:

  • No queries or results are available in the query /var/spool/arco/queries, results directory /var/spool/arco/results respectively.
  • Queries in the XML files are syntactically incorrect. Check the log file /var/log/webconsole/console/console_debug_log for error messages from the XML parser.
  • User noaccess has no read or write permissions on the query or results directory.

Problem – The list of available database tables is empty.

Solution: The cause can be any of the following:

  • The database is down. Start or restart the database.
  • No more database connections are available. Increase the number of allowable connections to the database.
  • An error exists in the configuration file of the application. Check the configuration for wrong database users, wrong user passwords, or wrong type of database, and then restart the application.

Problem – The list of selectable fields is empty.

Solution: No table is selected. Select a table from the list.

h4. Problem – The list of filters is empty.

Solution: No fields are selected. Define at least one field.

Problem – The sort list is empty.

Solution: No fields are selected. Define at least one field.

Problem – A defined filter is not used.

Solution: The filter may be inactive. Modify the unused filter and make it active.

Problem – The late binding in the advanced query is ignored but the execution runs into an error.

Solution: The late binding macro has a syntactical error. The syntax for the late binding in advanced query is:

   LATEBINDING { <column>;<operator>;<default value> }

   <column>    name if the latebinding
   <operator>  a SQL operator (e.g. = < > in .. )
   <value>     default value (e.g. 'localhost' )

Example:

select * from sge_host where LATEBINDING {h_hostname; like; a%}
select * from sge_host where LATEBINDING {h_hostname; in; ('localhost', 'foo.bar')}

Problem – The breadcrumb is used to move back but the login screen is shown.

Solution: The session has timed out. Log in again, or raise the session timeout value for the Sun Java Web Console (SJWC). To increase the session timeout value to 60 minutes, as a superuser, on the host where the (SJWC) is installed, issue this command:

# wcadmin add -p -a reporting session.timeout.value=60

Problem – The view configuration is defined but the default configuration is shown.

Solution: The defined view configuration is not set to be visible. Open the view configuration and define the view configuration to be used.

Problem – The view configuration is defined but the last configuration is shown.

Solution: The defined view configuration is not set to be visible. Open the view configuration and define the view configuration to be used.

Problem – The execution of a query takes a very long time.

Solution: The results coming from the database are very large. Set a limit for the results, or extend the filter conditions.

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

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