You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
200 lines
8.5 KiB
SQL
200 lines
8.5 KiB
SQL
-- Fresh-install Agent metric and traffic schema. Agents expose current signed
|
|
-- counters; PHP owns sampling, billing totals, chart retention and all writes.
|
|
CREATE TABLE cloud_vm_metric_samples (
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
sampled_at BIGINT UNSIGNED NOT NULL,
|
|
cpu_percent DOUBLE NULL,
|
|
memory_used_mib DOUBLE NULL,
|
|
memory_total_mib DOUBLE NULL,
|
|
disk_read_bps DOUBLE NULL,
|
|
disk_write_bps DOUBLE NULL,
|
|
network_rx_bps DOUBLE NULL,
|
|
network_tx_bps DOUBLE NULL,
|
|
network_rx_bytes BIGINT UNSIGNED NULL,
|
|
network_tx_bytes BIGINT UNSIGNED NULL,
|
|
PRIMARY KEY (instance_id, sampled_at),
|
|
KEY vm_metric_retention (sampled_at),
|
|
KEY vm_metric_owner (user_id, sampled_at, instance_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Hierarchical chart history. Every accepted raw sample contributes once to
|
|
-- one five-minute and one hourly row; sums and non-null counts preserve exact
|
|
-- averages when a larger display bucket merges several stored rows.
|
|
CREATE TABLE cloud_vm_metric_rollups (
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
interval_seconds SMALLINT UNSIGNED NOT NULL,
|
|
bucket_start BIGINT UNSIGNED NOT NULL,
|
|
sample_count SMALLINT UNSIGNED NOT NULL,
|
|
last_sampled_at BIGINT UNSIGNED NOT NULL,
|
|
cpu_percent_sum DOUBLE NULL,
|
|
cpu_percent_count SMALLINT UNSIGNED NOT NULL,
|
|
memory_used_mib_sum DOUBLE NULL,
|
|
memory_used_mib_count SMALLINT UNSIGNED NOT NULL,
|
|
memory_total_mib_sum DOUBLE NULL,
|
|
memory_total_mib_count SMALLINT UNSIGNED NOT NULL,
|
|
disk_read_bps_sum DOUBLE NULL,
|
|
disk_read_bps_count SMALLINT UNSIGNED NOT NULL,
|
|
disk_write_bps_sum DOUBLE NULL,
|
|
disk_write_bps_count SMALLINT UNSIGNED NOT NULL,
|
|
network_rx_bps_sum DOUBLE NULL,
|
|
network_rx_bps_count SMALLINT UNSIGNED NOT NULL,
|
|
network_tx_bps_sum DOUBLE NULL,
|
|
network_tx_bps_count SMALLINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (instance_id, interval_seconds, bucket_start),
|
|
KEY vm_metric_rollup_retention (
|
|
interval_seconds, bucket_start, instance_id
|
|
),
|
|
KEY vm_metric_rollup_owner (user_id, instance_id, bucket_start),
|
|
CONSTRAINT chk_vm_metric_rollup_bucket CHECK (
|
|
interval_seconds IN (300, 3600)
|
|
AND bucket_start % interval_seconds = 0
|
|
AND last_sampled_at >= bucket_start
|
|
AND last_sampled_at < bucket_start + interval_seconds
|
|
),
|
|
CONSTRAINT chk_vm_metric_rollup_counts CHECK (
|
|
sample_count > 0
|
|
AND cpu_percent_count <= sample_count
|
|
AND memory_used_mib_count <= sample_count
|
|
AND memory_total_mib_count <= sample_count
|
|
AND disk_read_bps_count <= sample_count
|
|
AND disk_write_bps_count <= sample_count
|
|
AND network_rx_bps_count <= sample_count
|
|
AND network_tx_bps_count <= sample_count
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE cloud_vm_traffic_totals (
|
|
host_id BIGINT UNSIGNED NOT NULL,
|
|
vm_uuid BINARY(16) NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
last_observed_at BIGINT UNSIGNED NULL,
|
|
network_signature BINARY(32) NOT NULL,
|
|
network_baseline_json JSON NOT NULL,
|
|
download_rx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
upload_tx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
updated_at BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (host_id, vm_uuid),
|
|
KEY vm_traffic_uuid (vm_uuid, host_id),
|
|
KEY vm_traffic_owner (user_id, host_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Hot billing-boundary checkpoints. These rows preserve precise boundaries
|
|
-- while a service period is being activated; verified boundary anchors and
|
|
-- daily checkpoints allow old hourly rows to be pruned later.
|
|
CREATE TABLE cloud_vm_traffic_checkpoints (
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
host_id BIGINT UNSIGNED NOT NULL,
|
|
vm_uuid BINARY(16) NOT NULL,
|
|
checkpoint_hour BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
first_observed_at BIGINT UNSIGNED NOT NULL,
|
|
download_rx_bytes BIGINT UNSIGNED NOT NULL,
|
|
upload_tx_bytes BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (host_id, vm_uuid, checkpoint_hour),
|
|
KEY vm_traffic_checkpoint_owner (user_id, host_id, first_observed_at),
|
|
KEY vm_traffic_checkpoint_instance (
|
|
instance_id, first_observed_at, host_id
|
|
),
|
|
KEY vm_traffic_checkpoint_retention (
|
|
first_observed_at, instance_id, host_id
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- One cumulative first/last observation per logical instance and UTC day.
|
|
-- Logical identity keeps the checkpoint continuous across maintenance moves.
|
|
CREATE TABLE cloud_vm_traffic_daily_checkpoints (
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
checkpoint_day BIGINT UNSIGNED NOT NULL,
|
|
first_observed_at BIGINT UNSIGNED NOT NULL,
|
|
first_download_rx_bytes BIGINT UNSIGNED NOT NULL,
|
|
first_upload_tx_bytes BIGINT UNSIGNED NOT NULL,
|
|
last_observed_at BIGINT UNSIGNED NOT NULL,
|
|
last_download_rx_bytes BIGINT UNSIGNED NOT NULL,
|
|
last_upload_tx_bytes BIGINT UNSIGNED NOT NULL,
|
|
sample_count INT UNSIGNED NOT NULL,
|
|
created_at BIGINT UNSIGNED NOT NULL,
|
|
updated_at BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (instance_id, checkpoint_day),
|
|
KEY vm_traffic_daily_checkpoint_owner (
|
|
user_id, checkpoint_day, instance_id
|
|
),
|
|
KEY vm_traffic_daily_checkpoint_retention (
|
|
checkpoint_day, instance_id
|
|
),
|
|
CONSTRAINT chk_vm_traffic_daily_checkpoint_window CHECK (
|
|
checkpoint_day % 86400 = 0
|
|
AND first_observed_at >= checkpoint_day
|
|
AND first_observed_at < checkpoint_day + 86400
|
|
AND last_observed_at >= first_observed_at
|
|
AND last_observed_at < checkpoint_day + 86400
|
|
),
|
|
CONSTRAINT chk_vm_traffic_daily_checkpoint_values CHECK (
|
|
sample_count > 0
|
|
AND last_download_rx_bytes >= first_download_rx_bytes
|
|
AND last_upload_tx_bytes >= first_upload_tx_bytes
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Sparse immutable evidence for the exact cumulative total selected when a
|
|
-- prepaid service period starts. It remains valid after hourly pruning and
|
|
-- across host/UUID placement changes.
|
|
CREATE TABLE cloud_vm_traffic_boundary_anchors (
|
|
service_period_id BIGINT UNSIGNED NOT NULL,
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
host_id BIGINT UNSIGNED NOT NULL,
|
|
vm_uuid BINARY(16) NOT NULL,
|
|
boundary_at BIGINT UNSIGNED NOT NULL,
|
|
observed_at BIGINT UNSIGNED NOT NULL,
|
|
source_kind VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
download_rx_bytes BIGINT UNSIGNED NOT NULL,
|
|
upload_tx_bytes BIGINT UNSIGNED NOT NULL,
|
|
created_at BIGINT UNSIGNED NOT NULL,
|
|
updated_at BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (service_period_id),
|
|
KEY vm_traffic_boundary_instance (
|
|
instance_id, boundary_at, service_period_id
|
|
),
|
|
KEY vm_traffic_boundary_owner (user_id, created_at, service_period_id),
|
|
CONSTRAINT chk_vm_traffic_boundary_source CHECK (
|
|
source_kind IN ('hourly_checkpoint', 'metric_sample')
|
|
),
|
|
CONSTRAINT chk_vm_traffic_boundary_time CHECK (
|
|
observed_at > boundary_at
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Logical-instance daily traffic survives maintenance migration because its
|
|
-- identity does not depend on the current host or libvirt UUID. The cursor is
|
|
-- retained beyond the 30-day chart window so an old cumulative total can
|
|
-- never be charged into a new day after pruning.
|
|
CREATE TABLE cloud_vm_daily_traffic_cursors (
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
last_observed_at BIGINT UNSIGNED NOT NULL,
|
|
download_total_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
upload_total_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
updated_at BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (instance_id),
|
|
KEY cloud_daily_traffic_cursor_owner (user_id, updated_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE cloud_vm_daily_traffic (
|
|
instance_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
day_start BIGINT UNSIGNED NOT NULL,
|
|
download_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
upload_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
sample_count INT UNSIGNED NOT NULL DEFAULT 0,
|
|
first_observed_at BIGINT UNSIGNED NOT NULL,
|
|
last_observed_at BIGINT UNSIGNED NOT NULL,
|
|
created_at BIGINT UNSIGNED NOT NULL,
|
|
updated_at BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (instance_id, day_start),
|
|
KEY cloud_daily_traffic_retention (day_start),
|
|
KEY cloud_daily_traffic_owner (user_id, day_start)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|