Login
[x]
Log in using an account from:
Fedora Account System
Red Hat Associate
Red Hat Customer
Or login using a Red Hat Bugzilla account
Forgot Password
Login:
Hide Forgot
Create an Account
Red Hat Bugzilla – Attachment 946306 Details for
Bug 1141543
[scale] - getdisksvmguid hit the performance due to all_disks_including_snapshots view
[?]
New
Simple Search
Advanced Search
My Links
Browse
Requests
Reports
Current State
Search
Tabular reports
Graphical reports
Duplicates
Other Reports
User Changes
Plotly Reports
Bug Status
Bug Severity
Non-Defaults
|
Product Dashboard
Help
Page Help!
Bug Writing Guidelines
What's new
Browser Support Policy
5.0.4.rh83 Release notes
FAQ
Guides index
User guide
Web Services
Contact
Legal
This site requires JavaScript to be enabled to function correctly, please enable it.
Function replacement for 3.4.2 v3
query-test3.txt (text/plain), 14.39 KB, created by
Liran Zelkha
on 2014-10-13 10:35:27 UTC
(
hide
)
Description:
Function replacement for 3.4.2 v3
Filename:
MIME Type:
Creator:
Liran Zelkha
Created:
2014-10-13 10:35:27 UTC
Size:
14.39 KB
patch
obsolete
>Create or replace FUNCTION GetDisksVmGuid(v_vm_guid UUID, v_only_plugged BOOLEAN, v_user_id UUID, v_is_filtered BOOLEAN) >RETURNS SETOF all_disks_including_snapshots STABLE > AS $procedure$ >BEGIN > if v_is_filtered then > RETURN QUERY SELECT 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, > 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, > array_to_string(array_agg(storage_domain_static_view.id), ',') 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, >cast(max( CAST(images.it_guid as varchar(50))) as uuid) as it_guid, > snapshots.description as description, >cast(max( CAST(images.ParentId as varchar(50))) as uuid) as ParentId, >max( images.lastModified) as lastModified, > snapshots.app_list as app_list, >cast(max(CAST(images.vm_snapshot_id as varchar(50))) as uuid) 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, > 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, >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 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, > 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) > INNER JOIN user_vm_permissions_view ON all_disks_including_snapshots.disk_id = user_vm_permissions_view.entity_id > WHERE vm_device.vm_id = v_vm_guid > AND ((vm_device.snapshot_id IS NULL AND all_disks_including_snapshots.active IS NOT FALSE) > OR vm_device.snapshot_id = all_disks_including_snapshots.vm_snapshot_id) > AND user_id = v_user_id; >ELSE > > RETURN QUERY SELECT 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, > 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, > array_to_string(array_agg(storage_domain_static_view.id), ',') 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, >cast(max( CAST(images.it_guid as varchar(50))) as uuid) as it_guid, > snapshots.description as description, >cast(max( CAST(images.ParentId as varchar(50))) as uuid) as ParentId, >max( images.lastModified) as lastModified, > snapshots.app_list as app_list, >cast(max(CAST(images.vm_snapshot_id as varchar(50))) as uuid) 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, > 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, >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 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, > 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) > OR vm_device.snapshot_id = all_disks_including_snapshots.vm_snapshot_id); >END IF; > > >END; $procedure$ >LANGUAGE plpgsql;
<b>You cannot view the attachment while viewing its details because your browser does not support IFRAMEs. <a href="attachment.cgi?id=946306">View the attachment on a separate page</a>.</b>
View Attachment As Raw
Actions:
View
Attachments on
bug 1141543
:
940416
|
942610
|
943920
| 946306