Liran Zelkha has uploaded a new change for review. Change subject: core: Improve the performance of getdisksvmguid ......................................................................
core: Improve the performance of getdisksvmguid This patch improves the performance of getdisksvmguid by optimizing its query Change-Id: Ibecaf557f219c0a1ef8fc688f89aaf5a12011a36 Bug-Url: https://bugzilla.redhat.com/1141543 Signed-off-by: [email protected] <[email protected]> --- M packaging/dbscripts/all_disks_sp.sql 1 file changed, 166 insertions(+), 1 deletion(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/60/33260/1 diff --git a/packaging/dbscripts/all_disks_sp.sql b/packaging/dbscripts/all_disks_sp.sql index 6130131..18efb39 100644 --- a/packaging/dbscripts/all_disks_sp.sql +++ b/packaging/dbscripts/all_disks_sp.sql @@ -42,7 +42,172 @@ AS $procedure$ BEGIN RETURN QUERY SELECT all_disks_including_snapshots.* - FROM all_disks_including_snapshots + FROM (SELECT storage_impl.*, + bd.disk_id, -- Disk fields + bd.disk_interface, + bd.wipe_after_delete, + bd.propagate_errors, + bd.disk_alias, + bd.disk_description, + bd.shareable, + bd.boot, + bd.sgio, + bd.alignment, + bd.last_alignment_scan +FROM +( + SELECT 0 AS disk_storage_type, + storage_id, + storage_path, + storage_name, + storage_type, + storage_pool_id, + image_guid, + creation_date, + actual_size, + read_rate, + write_rate, + read_latency_seconds, + write_latency_seconds, + flush_latency_seconds, + size, + it_guid, + imageStatus, + lastModified, + volume_type, + volume_format, + image_group_id, + description, + ParentId, + app_list, + vm_snapshot_id, + active, + entity_type, + number_of_vms, + vm_names, + quota_id, + quota_name, + quota_enforcement_type, + ovf_store, + disk_profile_id, + disk_profile_name, + null AS lun_id, + null AS physical_volume_id, + null AS volume_group_id, + null AS serial, + null AS lun_mapping, + null AS vendor_id, + null AS product_id, + null AS device_size + FROM ( +SELECT images.image_guid as image_guid, + array_to_string(array_agg(storage_domain_static_view.storage_name), ',') as storage_name, + array_to_string(array_agg(storage_domain_static_view.storage), ',') as storage_path, + cast(array_to_string(array_agg(storage_domain_static_view.id), ',') as uuid) storage_id, + array_to_string(array_agg(storage_domain_static_view.storage_type), ',') storage_type, + max(images.creation_date) as creation_date, +max( images.size) as size, +max( CAST(images.it_guid as varchar(50))) as it_guid, + snapshots.description as description, +max( CAST(images.ParentId as varchar(50))) as ParentId, +max( images.lastModified) as lastModified, + snapshots.app_list as app_list, +max( CAST(images.vm_snapshot_id as varchar(50))) as vm_snapshot_id, +max( images.volume_type) as volume_type, +max( images.volume_format) as volume_format, +max( images.imageStatus) as imageStatus, +cast(max( CAST(images.image_group_id as varchar(50))) as uuid) as image_group_id, +bool_or( images.active) as active, + vms_for_disk_view.entity_type as entity_type, + array_to_string(vms_for_disk_view.array_vm_names, ',') as vm_names, + COALESCE(array_upper(vms_for_disk_view.array_vm_names,1),0) as number_of_vms, + base_disks.disk_id, + max(base_disks.disk_alias) as disk_alias, + max( base_disks.disk_description) as disk_description, + bool_or( base_disks.shareable) as shareable, + max( base_disks.disk_interface) as disk_interface, + bool_or( base_disks.wipe_after_delete) as wipe_after_delete, + max( base_disks.propagate_errors) as ptopagate_errors, + bool_or( base_disks.boot) as boot, + max( base_disks.sgio) as sgio, + array_to_string(array_agg(COALESCE(CAST(quota.id as varchar), '')), ',') as quota_id, + array_to_string(array_agg(COALESCE(quota.quota_name, '')), ',') as quota_name, + max(storage_pool.quota_enforcement_type) as quota_enforcement_type, + array_to_string(array_agg(COALESCE(CAST(disk_profiles.id as varchar), '')), ',') as disk_profile_id, + array_to_string(array_agg(COALESCE(disk_profiles.name, '')), ',') as disk_profile_name, + max(disk_image_dynamic.actual_size) as actual_size, +max( disk_image_dynamic.read_rate) as read_rate, +max( disk_image_dynamic.write_rate) as write_rate, +max( disk_image_dynamic.read_latency_seconds) as read_latency_seconds, +max( disk_image_dynamic.write_latency_seconds) as write_latency_seconds, +max( disk_image_dynamic.flush_latency_seconds) as flush_latency_seconds, +max( base_disks.alignment) as alignment, +max( base_disks.last_alignment_scan) as last_alignment_scan, +bool_or( EXISTS (SELECT 1 FROM storage_domains_ovf_info WHERE images.image_group_id = storage_domains_ovf_info.ovf_disk_id)) as ovf_store, +bool_or( storage_domain_static_view.contains_unregistered_entities) as contains_unregistered_entities, +storage_pool.id as storage_pool_id +FROM images +left outer join disk_image_dynamic on images.image_guid = disk_image_dynamic.image_id +LEFT OUTER JOIN base_disks ON images.image_group_id = base_disks.disk_id +LEFT OUTER JOIN vms_for_disk_view on vms_for_disk_view.device_id = images.image_group_id +LEFT JOIN image_storage_domain_map ON image_storage_domain_map.image_id = images.image_guid +LEFT OUTER JOIN storage_domain_static_view ON image_storage_domain_map.storage_domain_id = storage_domain_static_view.id +LEFT OUTER JOIN snapshots ON images.vm_snapshot_id = snapshots.snapshot_id +LEFT OUTER JOIN quota ON image_storage_domain_map.quota_id = quota.id +LEFT OUTER JOIN disk_profiles ON image_storage_domain_map.disk_profile_id = disk_profiles.id +LEFT OUTER JOIN storage_pool ON storage_pool.id = storage_domain_static_view.storage_pool_id +WHERE images.image_guid != '00000000-0000-0000-0000-000000000000' +GROUP BY images.image_guid, snapshots.description, snapshots.app_list, vms_for_disk_view.entity_type,vm_names, number_of_vms,base_disks.disk_id, storage_pool.id +) as images_storage_domain_view + UNION + SELECT 1 AS disk_storage_type, + null AS storage_id, -- Storage domain fields + null AS storage_path, + null AS storage_name, + null AS storage_type, + null AS storage_pool_id, + null AS image_guid, -- Image fields + null AS creation_date, + null AS actual_size, + null AS read_rate, + null AS write_rate, + null AS read_latency_seconds, + null AS write_latency_seconds, + null AS flush_latency_seconds, + null AS size, + null AS it_guid, + null AS imageStatus, + null AS lastModified, + null AS volume_type, + null AS volume_format, + dlm.disk_id AS image_group_id, + null AS description, -- Snapshot fields + null AS ParentId, + null AS app_list, + null AS vm_snapshot_id, + null AS active, + vms_for_disk_view.entity_type, + COALESCE(array_upper(vms_for_disk_view.array_vm_names,1),0) as number_of_vms, + array_to_string(vms_for_disk_view.array_vm_names, ',') as vm_names, + null AS quota_id, -- Quota fields + null AS quota_name, + null AS quota_enforcement_type, + false as ovf_store, + null AS disk_profile_id, -- disk profile fields + null AS disk_profile_name, + l.lun_id, -- LUN fields + l.physical_volume_id, + l.volume_group_id, + l.serial, + l.lun_mapping, + l.vendor_id, + l.product_id, + l.device_size + FROM disk_lun_map dlm + JOIN luns l ON l.lun_id = dlm.lun_id + LEFT JOIN vms_for_disk_view on vms_for_disk_view.device_id = dlm.disk_id +) AS storage_impl +JOIN base_disks bd ON bd.disk_id = storage_impl.image_group_id) all_disks_including_snapshots LEFT JOIN vm_device ON vm_device.device_id = all_disks_including_snapshots.image_group_id AND (NOT v_only_plugged OR is_plugged) WHERE vm_device.vm_id = v_vm_guid AND ((vm_device.snapshot_id IS NULL AND all_disks_including_snapshots.active IS NOT FALSE) -- To view, visit http://gerrit.ovirt.org/33260 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ibecaf557f219c0a1ef8fc688f89aaf5a12011a36 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Liran Zelkha <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
