Load Grid Engine accounting file into MySQL

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.