Yaniv Dary has uploaded a new change for review. Change subject: history: added users statistics tables ......................................................................
history: added users statistics tables Change-Id: Ic5f3e2efe1e2043edbb75b02163afb0f7bfa62fb Signed-off-by: Yaniv Dary <[email protected]> --- M data-warehouse/historydbscripts_postgres/create_views_3_2.sql A data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql 2 files changed, 102 insertions(+), 0 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-dwh refs/changes/50/9850/1 diff --git a/data-warehouse/historydbscripts_postgres/create_views_3_2.sql b/data-warehouse/historydbscripts_postgres/create_views_3_2.sql index 0134911..114743e 100644 --- a/data-warehouse/historydbscripts_postgres/create_views_3_2.sql +++ b/data-warehouse/historydbscripts_postgres/create_views_3_2.sql @@ -557,6 +557,52 @@ LEFT OUTER JOIN vm_disks_usage_daily_history as b ON (a.history_datetime = b.history_datetime AND a.vm_id = b.vm_id); +CREATE OR REPLACE VIEW v3_2_statistics_vms_users_usage_hourly + AS +SELECT history_id, + history_datetime, + user_name, + vm_id, + session_time_in_minutes, + cpu_usage_percent, + max_cpu_usage, + memory_usage_percent, + max_memory_usage, + user_cpu_usage_percent, + max_user_cpu_usage_percent, + system_cpu_usage_percent, + max_system_cpu_usage_percent, + vm_last_up_time, + vm_last_boot_time, + vm_ip, + currently_running_on_host, + vm_configuration_version, + current_host_configuration_version +FROM statistics_vms_users_usage_hourly; + +CREATE OR REPLACE VIEW v3_2_statistics_vms_users_usage_daily + AS +SELECT history_id, + history_datetime, + user_name, + vm_id, + session_time_in_minutes, + cpu_usage_percent, + max_cpu_usage, + memory_usage_percent, + max_memory_usage, + user_cpu_usage_percent, + max_user_cpu_usage_percent, + system_cpu_usage_percent, + max_system_cpu_usage_percent, + vm_last_up_time, + vm_last_boot_time, + vm_ip, + currently_running_on_host, + vm_configuration_version, + current_host_configuration_version +FROM statistics_vms_users_usage_daily; + CREATE OR REPLACE VIEW v3_2_configuration_history_vms_interfaces AS SELECT diff --git a/data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql b/data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql new file mode 100644 index 0000000..6a69046 --- /dev/null +++ b/data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql @@ -0,0 +1,56 @@ +CREATE SEQUENCE statistics_vms_users_usage_hourly_seq INCREMENT BY 1 START WITH 1; +CREATE TABLE statistics_vms_users_usage_hourly +( + history_id INTEGER DEFAULT NEXTVAL('statistics_vms_users_usage_hourly_seq') primary key NOT NULL, + history_datetime TIMESTAMP WITH TIME ZONE NOT NULL, + user_name VARCHAR(255), + vm_id UUID NOT NULL, + session_time_in_minutes DECIMAL(7,2) NOT NULL DEFAULT 1, + cpu_usage_percent SMALLINT DEFAULT 0, + max_cpu_usage SMALLINT, + memory_usage_percent SMALLINT DEFAULT 0, + max_memory_usage SMALLINT, + user_cpu_usage_percent SMALLINT DEFAULT 0, + max_user_cpu_usage_percent SMALLINT DEFAULT 0, + system_cpu_usage_percent SMALLINT DEFAULT 0, + max_system_cpu_usage_percent SMALLINT DEFAULT 0, + vm_last_up_time TIMESTAMP WITH TIME ZONE, + vm_last_boot_time TIMESTAMP WITH TIME ZONE, + vm_ip VARCHAR(255), + currently_running_on_host UUID, + vm_configuration_version INTEGER REFERENCES vm_configuration (history_id), + current_host_configuration_version INTEGER REFERENCES host_configuration (history_id) +) WITH OIDS; + +CREATE INDEX IDX_vm_users_usage_history_datetime_hourly ON statistics_vms_users_usage_hourly (history_datetime); +CREATE INDEX IDX_vm_users_usage_configuration_version_hourly ON statistics_vms_users_usage_hourly (vm_configuration_version); +CREATE INDEX IDX_vm_users_usage_current_host_configuration_hourly ON statistics_vms_users_usage_hourly (current_host_configuration_version); + +CREATE SEQUENCE statistics_vms_users_usage_daily_seq INCREMENT BY 1 START WITH 1; +CREATE TABLE statistics_vms_users_usage_daily +( + history_id INTEGER DEFAULT NEXTVAL('statistics_vms_users_usage_daily_seq') primary key NOT NULL, + history_datetime TIMESTAMP WITH TIME ZONE NOT NULL, + user_name VARCHAR(255), + vm_id UUID NOT NULL, + session_time_in_minutes DECIMAL(7,2) NOT NULL DEFAULT 1, + cpu_usage_percent SMALLINT DEFAULT 0, + max_cpu_usage SMALLINT, + memory_usage_percent SMALLINT DEFAULT 0, + max_memory_usage SMALLINT, + user_cpu_usage_percent SMALLINT DEFAULT 0, + max_user_cpu_usage_percent SMALLINT DEFAULT 0, + system_cpu_usage_percent SMALLINT DEFAULT 0, + max_system_cpu_usage_percent SMALLINT DEFAULT 0, + vm_last_up_time TIMESTAMP WITH TIME ZONE, + vm_last_boot_time TIMESTAMP WITH TIME ZONE, + vm_ip VARCHAR(255), + currently_running_on_host UUID, + vm_configuration_version INTEGER REFERENCES vm_configuration (history_id), + current_host_configuration_version INTEGER REFERENCES host_configuration (history_id) +) WITH OIDS; + +CREATE INDEX IDX_vm_users_usage_history_datetime_daily ON statistics_vms_users_usage_daily (history_datetime); +CREATE INDEX IDX_vm_users_usage_configuration_version_daily ON statistics_vms_users_usage_daily (vm_configuration_version); +CREATE INDEX IDX_vm_users_usage_current_host_configuration_daily ON statistics_vms_users_usage_daily (current_host_configuration_version); + -- To view, visit http://gerrit.ovirt.org/9850 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ic5f3e2efe1e2043edbb75b02163afb0f7bfa62fb Gerrit-PatchSet: 1 Gerrit-Project: ovirt-dwh Gerrit-Branch: master Gerrit-Owner: Yaniv Dary <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
