Flux Accounting Database Administration
This document outlines the various methods of administration on the flux-accounting database.
Overview
The tables in the flux-accounting database stores information for associations, banks, job records, queues, and projects.
For associations, shares, limits, queue and project permissions, and bank membership can be managed.
Interacting With the Accounting DB
In order to add, edit, or remove information from the flux-accounting database, you must also have read/write access to the directory that the DB file resides in. The SQLite documentation states:
Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system.
flux-accounting provides an interface to the SQLite database containing all of
the aforementioned data for associations, banks, queues, and projects. Commands
can be run to add/remove all of these, edit certain attributes, and view this
information in different formats. See flux account --help
to list all of
the available flux-accounting commands.
The bank/association database hierarchy assumes a tree structure, meaning that there is one root bank which all other banks and associations descend from:
$ flux account view-bank root -t
Account Username RawShares RawUsage Fairshare
root 1 0.0
bank_A 1 0.0
bank_A user_1 1 0.0 0.5
bank_B 1 0.0
bank_B user_2 1 0.0 0.5
bank_B user_3 1 0.0 0.5
bank_C 1 0.0
bank_C_a 1 0.0
bank_C_a user_4 1 0.0 0.5
bank_C_b 1 0.0
bank_C_b user_5 1 0.0 0.5
bank_C_b user_6 1 0.0 0.5
Multiple levels of banks can be defined under this root bank. Users can belong to more than one bank and will have at most one default bank.
To add a bank to the database, you can use the flux account add-bank
command. Each add-bank
call requires a bank name, their allocated shares,
and a parent bank name (if it is not the root bank):
$ flux account add-bank root 1
$ flux account add-bank --parent-bank=root bank_A 1
From here, users can be added to these banks to create associations, a 2-tuple combination of a username and bank name:
$ flux account add-user --username=user_1 --bank=bank_A
If you wish to delete an association or bank from the database, you can run the
flux account delete-user
or flux account delete-bank
commands. Note
that this will not actually remove the association's or bank's row from the
table where their data is stored, but will instead set their active
column
to 0. To re-enable an association's active
status, simply re-add them with
flux account add-user
.
Information for associations can be viewed in more than one format:
$ flux account view-user user_1
creation_time: 1728922316
mod_time: 1728922316
active: 1
username: user_1
userid: 5001
bank: bank_A
default_bank: bank_A
shares: 1
job_usage: 0.0
fairshare: 0.5
max_running_jobs: 5
max_active_jobs: 7
max_nodes: 2147483647
queues:
projects: *
default_project: *
$ flux account view-user user_1 --json
{
"username": "user_1",
"userid": 5001,
"default_bank": "bank_A",
"banks": [
{
"bank": "bank_A",
"active": 1,
"shares": 1,
"job_usage": 0.0,
"fairshare": 0.5,
"max_running_jobs": 5,
"max_active_jobs": 7,
"max_nodes": 2147483647,
"queues": "",
"projects": "*",
"default_project": "*"
}
]
}
If you are familiar with SQLite syntax, you can also launch into an interactive SQLite shell. From there, you can open the database file and interface with any of the tables using SQLite commands:
$ sqlite3 path/to/FluxAccounting.db
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
To get nicely formatted output from queries (like headers for the tables and proper spacing), you can also set the following options in your shell:
sqlite> .mode columns
sqlite> .headers on
This will output queries like the following:
sqlite> SELECT * FROM association_table;
creation_time mod_time deleted username bank shares max_jobs max_wall_pj
------------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
1605309320 1605309320 0 fluxuser foo 1 1 60
Flux-Accounting Data Import/Export
Multiple rows of data can be loaded to the database at once using .csv
files
and the flux account pop-db
command. Run flux account pop-db --help
for
.csv
formatting instructions.
User and bank information can also be exported from the database using the
flux account export-db
command, which will extract information from both the
user and bank tables and place them into .csv
files.