From cbcd65669e0040a2a172a1e36d074dc98f118537 Mon Sep 17 00:00:00 2001 From: Michael Vrable Date: Thu, 14 Jun 2007 21:29:41 -0700 Subject: [PATCH] Update database schema with views for choosing segments to clean. Create a couple of database views, one for gathering summary statistics about segments in use, and one for ranking segments to be cleaned and rewritten, based on utilization and the age of the data contained in it. --- schema.sql | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) diff --git a/schema.sql b/schema.sql index d225ec1..5017d9b 100644 --- a/schema.sql +++ b/schema.sql @@ -36,3 +36,21 @@ create table snapshot_contents ( ); create unique index snapshot_contents_unique on snapshot_contents(blockid, snapshotid); + +-- Summary statistics for each segment. +create view segment_info as select * from + (select segmentid, max(timestamp) as mtime, + sum(size) as size, count(*) as objects + from block_index natural join segments group by segmentid) +natural join + (select segmentid, sum(size) as used, count(*) as objects_used + from block_index where blockid in + (select blockid from snapshot_contents) group by segmentid); + +-- Ranking of segments to be cleaned, using a benefit function of +-- (fraction free space)*(age of youngest object). +create view cleaning_order as select *, (1-u)*age as benefit from + (select segmentid, + cast(used as real) / size as u, julianday('now') - mtime as age + from segment_info) +where benefit > 0; -- 2.20.1