"How much did the protein run use" is a question about a piece of work, not about c003-15. A tenant asks for clusters over months; individually each one is an address, some hardware and a lease, and nothing joined them up. A project groups them, and is optional throughout. Somebody who wants one cluster for an afternoon should not have to invent a project to get it, and requiring one would mean every tenant carrying a "default" that means nothing. The form does not even ask when there are none: a field whose only option is "none" is a question with one answer. The cost side needed no new columns. The cluster record already said when it was created and destroyed, and cluster_vm said how many cores and how much memory it held; what was missing was anything that read them. cluster_usage is that, and it is a view rather than a table because a derived number stored is a number that can disagree with what it was derived from. It works retroactively - every cluster this estate has ever built now has a line. Core-hours are reserved, not executed, and the view says so. That is the honest unit for an on-demand cluster: the hardware was yours and nobody else could have it, whether or not a job ran. What was actually executed is a Slurm accounting question and there is no slurmdbd. The project is attached inside the allocating transaction, for the same reason the placement and the applications are, and it is checked there to be the tenant's own and still open - a cluster filed against somebody else's project leaks what they are working on. Closing keeps history and stops it being offered. There is no delete: the reference from cluster is RESTRICT, because a project removed from under its clusters leaves their cost attributed to nothing, and afterwards is when the question gets asked. A release now writes manifest.json beside home.tgz: the nodes, the wall time, the core-hours. The database knows all of it and outlives the cluster, but the tarball can be moved off this host and the description travels with it. Not fatal if it fails - the data is already safe by then, and holding hardware because a note could not be written is the wrong trade. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
113 lines
4.4 KiB
PL/PgSQL
113 lines
4.4 KiB
PL/PgSQL
-- Projects, and what a cluster cost.
|
|
--
|
|
-- Two things that turn out to be one thing.
|
|
--
|
|
-- A tenant asks for clusters over months. Individually each one is an
|
|
-- address, some hardware and a lease; together they are a piece of work
|
|
-- somebody is accountable for, and nothing recorded that. "How much did
|
|
-- the protein run use" is a question about a project, not about c003-15.
|
|
--
|
|
-- A project is optional on purpose. Somebody who wants one cluster for an
|
|
-- afternoon should not have to invent a project to get it, and requiring
|
|
-- one would mean every tenant carrying a "default" that means nothing.
|
|
--
|
|
-- The cost side needs no new columns at all: the cluster record already
|
|
-- says when it was created and when it was destroyed, and cluster_vm says
|
|
-- how many cores and how much memory it held. What was missing was
|
|
-- anything that read them. The view at the bottom is that, and it is a
|
|
-- view rather than a table because a derived number stored is a number
|
|
-- that can disagree with what it was derived from.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS project (
|
|
id bigserial PRIMARY KEY,
|
|
tenant_id bigint NOT NULL REFERENCES tenant(id),
|
|
|
|
-- Unique per tenant, not globally: two tenants may both have a
|
|
-- project called "benchmarks" and neither has to know about the
|
|
-- other.
|
|
name text NOT NULL CHECK (name <> ''),
|
|
description text,
|
|
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
created_by text NOT NULL,
|
|
|
|
-- Closing a project keeps its history and stops it being offered for
|
|
-- new clusters. Deleting one that still has clusters is refused by
|
|
-- the reference below, which is the point: the accounting has to
|
|
-- outlive the interest in it.
|
|
closed_at timestamptz
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS project_name_unique
|
|
ON project (tenant_id, name);
|
|
|
|
COMMENT ON TABLE project IS
|
|
'A piece of work a tenant is accountable for, grouping clusters over time. Optional: a cluster without one is a cluster nobody needed to file.';
|
|
|
|
-- RESTRICT, not SET NULL. A project deleted out from under its clusters
|
|
-- would leave their cost attributed to nothing, and the whole reason the
|
|
-- record outlives the machines is that somebody will ask later.
|
|
ALTER TABLE cluster
|
|
ADD COLUMN IF NOT EXISTS project_id bigint
|
|
REFERENCES project(id) ON DELETE RESTRICT;
|
|
|
|
CREATE INDEX IF NOT EXISTS cluster_project_idx ON cluster (project_id);
|
|
|
|
COMMENT ON COLUMN cluster.project_id IS
|
|
'Which project this cluster belongs to, or null. Never required: a cluster asked for on a Tuesday afternoon is not a project.';
|
|
|
|
-- What a cluster held, and for how long.
|
|
--
|
|
-- Wall time runs to destroyed_at, or to now() while the cluster is still
|
|
-- up, so a live cluster's cost is the cost so far rather than zero.
|
|
--
|
|
-- Core-hours count what was RESERVED, not what was busy. That is the
|
|
-- honest unit for an on-demand cluster: the hardware was yours and nobody
|
|
-- else could have it, whether or not you ran anything on it. What was
|
|
-- actually executed is a Slurm accounting question and there is no
|
|
-- slurmdbd yet.
|
|
CREATE OR REPLACE VIEW cluster_usage AS
|
|
SELECT
|
|
c.serial_id,
|
|
c.accounting_name,
|
|
c.cluster_id,
|
|
c.tenant_id,
|
|
t.name AS tenant_name,
|
|
c.project_id,
|
|
p.name AS project_name,
|
|
c.site,
|
|
c.state,
|
|
c.created_at,
|
|
c.destroyed_at,
|
|
COALESCE(c.destroyed_at, now()) - c.created_at AS wall,
|
|
v.vms,
|
|
v.cores,
|
|
v.memory_gb,
|
|
d.devices,
|
|
ROUND((v.cores * EXTRACT(EPOCH FROM
|
|
(COALESCE(c.destroyed_at, now()) - c.created_at)) / 3600.0)::numeric, 2)
|
|
AS core_hours,
|
|
ROUND((v.memory_gb * EXTRACT(EPOCH FROM
|
|
(COALESCE(c.destroyed_at, now()) - c.created_at)) / 3600.0)::numeric, 2)
|
|
AS gb_hours
|
|
FROM cluster c
|
|
JOIN tenant t ON t.id = c.tenant_id
|
|
LEFT JOIN project p ON p.id = c.project_id
|
|
LEFT JOIN LATERAL (
|
|
SELECT count(*)::int AS vms,
|
|
COALESCE(sum(cores), 0)::int AS cores,
|
|
COALESCE(sum(memory_gb), 0)::int AS memory_gb
|
|
FROM cluster_vm WHERE cluster_serial_id = c.serial_id
|
|
) v ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT count(*)::int AS devices
|
|
FROM cluster_device WHERE cluster_serial_id = c.serial_id
|
|
) d ON true;
|
|
|
|
COMMENT ON VIEW cluster_usage IS
|
|
'What each cluster held and for how long. Core-hours are reserved, not executed: the hardware was held whether or not a job ran on it.';
|
|
|
|
COMMIT;
|