Michael Kublin has uploaded a new change for review. Change subject: engine: Improving almost all retrieves of storage_domains object in the system ......................................................................
engine: Improving almost all retrieves of storage_domains object in the system The following patch should improve all queries that retrieving a storage_domains object at the system except iso domain. The problem was in fn_get_storage_domain_shared_status_by_domain_id, all calculation should be done only for iso storage domain. The following query was also reported as problematic in bug #879930, so I added a bug number to commit message Change-Id: I617a631c63cd38f058e6c0711dea3a43062874fd Bug-Url: https://bugzilla.redhat.com/879930 Signed-off-by: Michael Kublin <mkub...@redhat.com> --- M backend/manager/dbscripts/create_functions.sql M backend/manager/dbscripts/create_views.sql 2 files changed, 60 insertions(+), 48 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/11/9511/1 diff --git a/backend/manager/dbscripts/create_functions.sql b/backend/manager/dbscripts/create_functions.sql index 7748c22..bbe2679 100644 --- a/backend/manager/dbscripts/create_functions.sql +++ b/backend/manager/dbscripts/create_functions.sql @@ -354,60 +354,69 @@ CREATE OR REPLACE FUNCTION fn_get_storage_domain_shared_status_by_domain_id(v_storage_domain_id UUID, - v_storage VARCHAR(250), - v_storage_type INTEGER) + v_storage_status INTEGER, + v_storage_domain_type INTEGER) RETURNS INTEGER AS $function$ DECLARE v_result INTEGER; v_rowsCount INTEGER; v_status INTEGER; -BEGIN - BEGIN - CREATE GLOBAL TEMPORARY TABLE tt_TEMP22 - ( - status INTEGER, - count INTEGER - ) WITH OIDS; - exception when others then - truncate table tt_TEMP22; - END; - delete from tt_TEMP22; - Insert INTO tt_TEMP22 - select status, count(storage_id) from storage_pool_iso_map - where storage_id = v_storage_domain_id - group by status; - - select count(*) INTO v_rowsCount from tt_TEMP22; - - -- if return 0 rows then the domain is unattached (0) or locked (4) if storage is null or empty for @storage_type iscsi (2) and fcp (3) - if (v_rowsCount = 0) then - if (v_storage_type in(2,3) and (v_storage is null or v_storage = '')) then - v_result := 4; - else +BEGIN + if (v_storage_domain_type != 2) then + if (v_storage_status is null) then v_result := 0; - end if; - else - if (v_rowsCount = 1) then - select status INTO v_status from tt_TEMP22 LIMIT 1; - -- if 1 row and status active (3) then domain is active (1) - if v_status = 3 then - v_result := 1; - -- if 1 row and status not active then domain is inactive (2) - else - v_result := 2; - end if; - -- else (if return more then 1 row) else - select count(*) INTO v_rowsCount from tt_TEMP22 where status = 3; - if (v_rowsCount > 0) then - v_result := 3; - -- non of the statuses is active + -- if 1 row and status active (3) then domain is active (1) + if v_storage_status = 3 then + v_result := 1; + -- if 1 row and status not active then domain is inactive (2) else v_result := 2; end if; end if; - end if; + else + BEGIN + CREATE GLOBAL TEMPORARY TABLE tt_TEMP22 + ( + status INTEGER, + count INTEGER + ) WITH OIDS; + exception when others then + truncate table tt_TEMP22; + END; + delete from tt_TEMP22; + Insert INTO tt_TEMP22 + select status, count(storage_id) from storage_pool_iso_map + where storage_id = v_storage_domain_id + group by status; + + select count(*) INTO v_rowsCount from tt_TEMP22; + + -- if return 0 rows then the domain is unattached + if (v_rowsCount = 0) then + v_result := 0; + else + if (v_rowsCount = 1) then + -- if 1 row and status active (3) then domain is active (1) + if v_storage_status = 3 then + v_result := 1; + -- if 1 row and status not active then domain is inactive (2) + else + v_result := 2; + end if; + -- else (if return more then 1 row) + else + select count(*) INTO v_rowsCount from tt_TEMP22 where status = 3; + if (v_rowsCount > 0) then + v_result := 3; + -- non of the statuses is active + else + v_result := 2; + end if; + end if; + end if; + end if; return v_result; END; $function$ LANGUAGE plpgsql; diff --git a/backend/manager/dbscripts/create_views.sql b/backend/manager/dbscripts/create_views.sql index d6dd745..0b623b1 100644 --- a/backend/manager/dbscripts/create_views.sql +++ b/backend/manager/dbscripts/create_views.sql @@ -256,7 +256,7 @@ storage_domain_static.storage_domain_format_type as storage_domain_format_type, storage_domain_static.last_time_used_as_master as last_time_used_as_master, storage_pool_iso_map.owner as owner, - fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_domain_static.storage,storage_domain_static.storage_type) as storage_domain_shared_status, + fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_pool_iso_map.status,storage_domain_static.storage_domain_type) as storage_domain_shared_status, storage_domain_static.recoverable as recoverable FROM storage_domain_static INNER JOIN storage_domain_dynamic ON storage_domain_static.id = storage_domain_dynamic.id @@ -273,15 +273,18 @@ storage_domain_static.storage_type as storage_type, storage_domain_static.storage_domain_type as storage_domain_type, storage_domain_static.storage_domain_format_type as storage_domain_format_type, storage_domain_static.last_time_used_as_master as last_time_used_as_master, - null as status, null as owner, null as storage_pool_id, null as storage_pool_name, + null as owner, null as storage_pool_id, null as storage_pool_name, storage_domain_dynamic.available_disk_size as available_disk_size, storage_domain_dynamic.used_disk_size as used_disk_size, fn_get_disk_commited_value_by_storage(storage_domain_static.id) as commited_disk_size, - fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_domain_static.storage,storage_domain_static.storage_type) as storage_domain_shared_status, + storage_pool_iso_map.status as status, + fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_pool_iso_map.status,storage_domain_static.storage_domain_type) as storage_domain_shared_status, storage_domain_static.recoverable as recoverable FROM storage_domain_static -INNER JOIN storage_domain_dynamic ON storage_domain_static.id = storage_domain_dynamic.id; +INNER JOIN storage_domain_dynamic ON storage_domain_static.id = storage_domain_dynamic.id +LEFT OUTER JOIN storage_pool_iso_map ON storage_domain_static.id = storage_pool_iso_map.storage_id +LIMIT 1; CREATE OR REPLACE VIEW storage_domains_for_search @@ -299,7 +302,7 @@ storage_domain_dynamic.available_disk_size as available_disk_size, storage_domain_dynamic.used_disk_size as used_disk_size, fn_get_disk_commited_value_by_storage(storage_domain_static.id) as commited_disk_size, - fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_domain_static.storage,storage_domain_static.storage_type) as storage_domain_shared_status, + fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,status_table.status,storage_domain_static.storage_domain_type) as storage_domain_shared_status, storage_domain_static.recoverable as recoverable FROM storage_domain_static @@ -901,7 +904,7 @@ storage_domain_static.storage_domain_type, storage_domain_static.storage_domain_format_type, storage_domain_static.last_time_used_as_master as last_time_used_as_master, - fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_domain_static.storage,storage_domain_static.storage_type) AS + fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id,storage_pool_iso_map.status,storage_domain_static.storage_domain_type) AS storage_domain_shared_status, vds_groups.vds_group_id, vds_static.vds_id, -- To view, visit http://gerrit.ovirt.org/9511 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I617a631c63cd38f058e6c0711dea3a43062874fd Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Michael Kublin <mkub...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
