Just my blog

Recently I need to create a report about utilization of an HPC Cluster that uses Grid Engine, but we didn’t had ARCO and so not running yet for that cluster

So I digged into my brain on how to load data from a “RAW” format into a database… it’s something I did when I worked for PricewaterhouseCoopers Advisory, but then I used financial data.

Please press the continue reading link below… to read more

First you need to create a database within MySQL:

mysql> create database ge_accounting;

Then we create a table containing the accounting information, so we create a file name (for example) create-tables.sql:

create table ge_jobs
(
  ge_qname              char(30) not null,
  ge_hostname           char(30) not null,
  ge_group              char(10) not null,
  ge_owner              char(10) not null,
  ge_job_name           char(255) not null,
  ge_job_number         int unsigned not null primary key,
  ge_account            char(30) not null,
  ge_account_prio       int unsigned not null,

  tmp_submission_time    int unsigned not null,
  tmp_start_time         int unsigned not null,
  tmp_end_time           int unsigned not null,

  ge_failed             int unsigned not null,
  ge_exit_status        int unsigned not null,
  ge_ru_wallclock       int unsigned not null,
  ge_ru_utime           int unsigned not null,
  ge_ru_stime           int unsigned not null,
  ge_ru_maxrss          int unsigned not null,
  ge_ru_ixrss           int unsigned not null,
  ge_ru_ismrss          int unsigned not null,
  ge_ru_idrss           int unsigned not null,
  ge_ru_isrss           int unsigned not null,
  ge_ru_minflt          int unsigned not null,
  ge_ru_majflt          int unsigned not null,
  ge_ru_nswap           int unsigned not null,
  ge_ru_inblock         int unsigned not null,
  ge_ru_oublock         int unsigned not null,
  ge_ru_msgsnd          int unsigned not null,
  ge_ru_msgrcv          int unsigned not null,
  ge_ru_nsignals        int unsigned not null,
  ge_ru_nvcsw           int unsigned not null,
  ge_ru_nivcsw          int unsigned not null,
  ge_project            char(30) not null,
  ge_department         char(30) not null,
  ge_granted_pe         char(30),
  ge_slots              int unsigned not null,
  ge_task_number        int unsigned not null,
  ge_cpu                int unsigned not null,
  ge_mem                int unsigned not null,
  ge_io                 int unsigned not null,
  ge_category           char(255),
  ge_iow                int unsigned not null,
  ge_pe_taskid          char(30),
  ge_maxvmem            int unsigned not null,
  ge_arid               int unsigned not null,

  tmp_ar_submission_time int unsigned not null,

  ge_submission_time    timestamp not null,
  ge_start_time         timestamp not null,
  ge_end_time           timestamp not null,
  ge_ar_submission_time timestamp not null

);

So use mysql to load the data:

$ mysql -u root -p ge_accounting < create-tables.sql

Now we can load the data into the database. For this example $SGE_ROOT is set to /apps/ge and the $SGE_CELL is set to default.

$ mysql -u root -p ge_accounting

mysql> LOAD DATA INFILE ‘/apps/ge/default/accounting/accounting’
REPLACE
INTO TABLE ge_jobs
FIELDS TERMINATED BY ‘:’
IGNORE 4 LINES;

And now we’ve to convert the EPOCH time stamps into nice timestamps using the following query:

mysql> UPDATE ge_jobs
SET ge_submission_time = (SELECT FROM_UNIXTIME(tmp_submission_time)),
ge_start_time = (SELECT FROM_UNIXTIME(tmp_start_time)),
ge_end_time = (SELECT FROM_UNIXTIME(tmp_end_time)),
ge_ar_submission_time = (SELECT FROM_UNIXTIME(tmp_ar_submission_time));

And now you can make a query that show a utilization per month based on 16 available slots, but with a 10% reserved non availability due to maintanance by admins:

mysql> SELECT MONTH(ge_submission_time) AS show_month,
SUM(ge_ru_wallclock * ge_slots) AS total_wallclock,
(SUM(ge_ru_wallclock * ge_slots) / (DATE_FORMAT(LAST_DAY(ge_submission_time),’%d’) * 86400 * 16 * 0.9) * 100) AS total_util
FROM ge_jobs
WHERE YEAR(ge_submission_time) = ‘2009’
GROUP BY show_month
ORDER BY show_month;

Please note, the query above is not perferct! I use it based on the submission time… but it doesn’t handle jobs that run in multiple months… have to tweak my query a little bit more for this.

A user group we support uses an application and they have the license to use 20 concurrent runs. For them we’ve implement very recently the Sun Grid Engine job scheduler. Although they recently start complaining that jobs didn’t run.Their cluster …