The sqlog package contains a set of scripts useful for creating, populating, and issuing queries to a SLURM job log database.

COMPONENTS

sqlog The "SLURM Query Log" utility. Provides a single interface to query jobs from the SLURM job log database and/or current queue of running jobs.

slurm-joblog Logs completed jobs using SLURM's jobcomp/script interface to the SLURM job log database and an optional text file.

sqlog-db-util Administrative utility used to create SLURM job log database and its corresponding users. Also provides an interface to "backfill" the database using existing SLURM joblog files created by the jobcomp/filetxt plugin.

sqlog.conf World-readable config file. Contains local configuration for SQL host, read-only user, and read-only password.

slurm-joblog.conf Private configuration for slurm-joblog script (also used by by sqlog-db-util). Contains SQL read-write user and password, root user passwd (for sqlog-db-util) and a list of hosts that should have RW access to DB.

CONFIGURATION

For fully-automated operation, both the /etc/slurm/sqlog.conf and /etc/slurm/slurm-joblog.conf must exist. These files are read using perl's do() function, so the files can and must be valid perl. This allows a bit of scripting to get the values if necessary. (See the sqlog doc directory for examples).

The available variables in each config file include:

sqlog.conf:

SQLHOST SQL server hostname (default = sqlhost) SQLUSER Read-only user (default = slurm_read) SQLPASS Read-only password (default = none) SQLDB DB name (default = slurm) TRACKNODES Set to 0 to disable per-job node tracking (default = 1) %FORMATS Hash of format aliases (e.g. "f1" => "jid,name,user,state")

slurm-joblog.conf:

SQLUSER Read-write user (default = slurm) SQLPASS Read-write password (not set) SQLROOTPASS DB root password (not set) @SQLRWHOSTS Read-write hosts (array of hosts to give rw access) JOBLOGFILE txt joblog location (set if you want to log to a file too) AUTOCREATE Attempt to create DB if it doesn't yet exist the first time slurm-joblog is run (default = no).

CREATING JOB LOG DATABASE

Once the config files exist, the following command will create the SLURM job log database:

sqlog-db-util --create

If you have existing text joblog files you'd like to seed the new DB with, use

sqlog-db-util --backfill [FILE]...

e.g.

sqlog-db-util --backfill /var/log/slurm/joblog*

If AUTOCREATE is set in slurm-joblog.conf, then sqlog-db-util --create will be automatically run the first time the database is accessed.

CONVERTING JOB LOG DATABASE

The database schema changed from v0.12 to v0.13 of the sqlog package. The highest schema version currently running on a system can be determined from the --info output.

To create tables for the new schema, run:

sqlog-db-util --create

Once created, the slurm-joblog.pl script will detect the new schema and automatically switch to insert records to the new tables. The sqlog command will query both schemas for records.

To copy existing data from the old schema to the new schema, use the --convert option.

Speeding up the conversion: The new schema tracks the nodes that each job uses so that sqlog queries involving nodes names return much faster. The data and indicies associated with this node tracking can significantly slow down the conversion operation when converting a large number of records. There are two options to speed this up:

1) Disable node-tracking for all converted jobs via the --notrack option.
2) Delay indexing of converted data via the --delay-index option.

With the --notrack option, no node-tracking data will be stored for jobs inserted via conversion. As such, if node-tracking is enabled on the system, such jobs will not return in queries involving node names. Newly inserted jobs will still have node-tracking data.

With the --delay-index option, node tracking indicies are removed before data is converted, and they are restored when the conversion completes. Queries involving node names while there are no indicies will take a very long time to return on a large database.

For a database on Atlas, which had 580,000 jobs spanning two years the conversion took:

13 minutes for:  sqlog-db-util --convert --notrack
33 minutes for:  sqlog-db-util --convert --delay-index
85 minutes for:  sqlog-db-util --convert

The recommended method is to use --delay-index.

It's also possible to disable node-tracking in the new schema completely. To do this, add the following line to the sqlog.conf file.

$TRACKNODES=0;

Number of allocated cores: The new schema adds a new field to record the number of cores allocated to a job. This data was not captured in the version 1 schema. However, on many systems, this core count can be computed. On systems that have the same number of cores per node and allocate whole nodes to a job, one may use the --cores-per-node option to specify the number of cores per node. This --cores-per-node value is multiplied with the node count recorded in the version 1 schema to determine the number of cores allocated to the job. For example, to convert from schema version 1 to version 2 on a machine that has 8 cores per node and allocates whole nodes to jobs, run the following command:

sqlog-db-util --convert --cores-per-node=8

For all other systems, do not specify --cores-per-node. In this case, the number of cores allocated will be set to 0. The conversion command on these systems is simply:

sqlog-db-util --convert

If a mistake is made during conversion, you can drop the version 2 tables and start from scratch (be very careful to specify '2' and not '1' here):

sqlog-db-util --drop=2

You may issue the --convert command on a live system, however, be careful to specify the command correctly in this case. The slurm-joblog.pl script will insert records to the new schema as soon as it is created. If a mistake is made during conversion, and the version 2 tables must be dropped and recreated, any records inserted by slurm-joblog.pl will be lost.

After conversion, sqlog may report duplicate records as it finds matches from both the version 1 and version 2 tables. Once converted, it's recommended that the version 1 tables be dropped by running the following command (be very careful to specify '1' and not '2' here):

sqlog-db-util --drop=1

Finally, here is a full example set of commands to create the new schema and convert records to it:

sqlog-db-util -v --create sqlog-db-util -v --backup=all schema1_jobs.log sqlog-db-util -v --convert --delay-index --cores-per-node=8 sqlog-db-util -v --drop=1

BACKING UP AND PRUNING THE DATABASE

It is possible to dump records from the job log database into a text file, which can then be read in via --backfill. This is useful to capture a text file backup of the logs. One must specify the time period as either "all", "DATE", or "DATE..DATE", to dump all jobs, jobs before a given date, and jobs that started between two dates, respectively. DATE should be specified with the 'YYYY-MM-DD HH:MM:SS' format, e.g.,

sqlog-db-util -v --backup='2009-01-01 00:00:00'..'2009-02-01 00:00:00'\ logs.txt

One utility of this backup option is to share job log records with others potentially outside of the organization. Typically, one would like to protect user and job names when sharing such information. For this, an --obfuscate option is available which dumps records and modifies user names to be of the form "user_X", userids to match "X", and job names to be of the form "job_Y", where X and Y are numbers.

Finally, over a long period of time, the database may gather so many records that is slows down significantly. A --prune option is available to remove old records. One specifies a date, and all jobs which started before that date will be removed from the database and written to a file name specified by the user, e.g.,

sqlog-db-util -v --prune='2007-01-01 00:00:00' pre2007.log

ENABLE JOB LOGGING

To enable the SLURM job log database, the following configuration options must be set in slurm.conf:

JobCompType = jobcomp/script JobCompLoc = /usr/libexec/sqlog/slurm-joblog

Adjust the path if the sqlog RPM was installed with a different PREFIX. This has only been tested on SLURM 1.2.10 or greater.

Restart slurmctld and slurm-joblog will begin logging jobs as they complete.

$Id$