Build Slurm Reporting Tables using sacct output.
September 25, 2024•336 words
I was looking for a way to build reporting from the output of the Slurm sacct command.
Here's my first take on building it. My goal is to build a month to date table throughout the month when push it to a history table.
I used a mysql database.
Build Database/Tables:
create database slurmrpt;
use slurmrpt;
CREATE TABLE monthly_activity (
jobid int,
user varchar(255),
state varchar(255),
jobname varchar(255),
spartition varchar(255),
ncpus int,
nnodes int,
seconds int(10),
start_day date,
end_day date,
start_time datetime,
end_time datetime
);
CREATE TABLE monthly_activity_temp (
jobid int,
user varchar(255),
state varchar(255),
jobname varchar(255),
spartition varchar(255),
ncpus int,
nnodes int,
seconds int(10),
start_time varchar(255),
end_time varchar(255)
);
Generate report
It took a while to figure out how to craft a sacct command to derive the things I wanted
Couple of notes:
-p Adds a delimiter to the output which helps with the loading of string data like job name that for me had spaces.
-X Summarizes the job info.
One issue is which multiple day runs the total is reported for the day things finished.
Need to add nodes * seconds to account for multiple nodes processing.
export SLURM_TIME_FORMAT="%m-%d-%y:%H:%Mm"
sacct -S2024-09-01-00:00 -E2024-09-22-23:59 -sCOMPLETED,FAILED -p -X -o jobid,user%16,state%12,"jobname%60",partition%20,alloccpus,nnodes,elapsedraw,start,end > slurm.txt
delete from monthly_activity;
delete from monthly_activity_temp;
load data local infile 'slurm.txt' into table monthly_activity_temp
fields terminated by '|'
(jobid,user,state,jobname,spartition,ncpus,nnodes,seconds,start_time,end_time) ;
INSERT INTO monthly_activity(jobid,user,state,jobname,spartition,ncpus,nnodes,seconds,start_day,end_day,start_time,end_time)
SELECT jobid,
user,
state,
jobname,
spartition,
ncpus,
nnodes,
seconds,
STR_TO_DATE(start_time, '%c-%d-%y'),
STR_TO_DATE(end_time, '%c-%d-%y'),
STR_TO_DATE(start_time, '%c-%d-%y:%H:%s'),
STR_TO_DATE(end_time, '%c-%d-%y:%H:%s')
FROM monthly_activity_temp;
SET datetime_column=str_to_date(@start_time, '%d %b %Y-%H:%i:%s',@end_time, '%d %b %Y-%H:%s');
Reports
Total
select round(sum(seconds)/60/24,0) as Total_Hours from monthly_activity;
By User
select user as User , round(sum(seconds)/60/24,0) as Total_Hours from monthly_activity group by user order by 2 desc;
By Slurm Queue
select spartition as Slurm_Queue, round(sum(seconds)/60/24,0) as Total_Hours from monthly_activity group by spartition order by 2 desc;
By Job Name
select jobname as Job, round(sum(seconds)/60/24,0) as Total_Hours from monthly_activity group by jobname order by 2 desc;