Load Grid Engine accounting file into MySQL | Just my blog

/dev/random

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 :-D


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.



  • Social

  • By continuing to use the site, you agree to the use of cookies. more information

    The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible and enable advertising to provide you free content. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

    Close