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.
