** Changed in: nova
Status: Fix Committed => Fix Released
** Changed in: nova
Milestone: None => kilo-2
--
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1378395
Title:
Slow MySQL queries with lots of deleted instances
Status in OpenStack Compute (Nova):
Fix Released
Bug description:
While analyzing the slow query log in our public cloud, we ran across
this slow query:
# Query_time: 21.113669 Lock_time: 0.000485 Rows_sent: 46 Rows_examined:
848516
SET timestamp=1412484367;
SELECT anon_1.instances_created_at AS anon_1_instances_created_at,
anon_1.instances_updated_at AS anon_1_instances_updated_at,
anon_1.instances_deleted_at AS anon_1_instances_deleted_at,
anon_1.instances_deleted AS anon_1_instances_deleted, anon_1.instances_id AS
anon_1_instances_id, anon_1.instances_user_id AS anon_1_instances_user_id,
anon_1.instances_project_id AS anon_1_instances_project_id,
anon_1.instances_image_ref AS anon_1_instances_image_ref,
anon_1.instances_kernel_id AS anon_1_instances_kernel_id,
anon_1.instances_ramdisk_id AS anon_1_instances_ramdisk_id,
anon_1.instances_hostname AS anon_1_instances_hostname,
anon_1.instances_launch_index AS anon_1_instances_launch_index,
anon_1.instances_key_name AS anon_1_instances_key_name,
anon_1.instances_key_data AS anon_1_instances_key_data,
anon_1.instances_power_state AS anon_1_instances_power_state,
anon_1.instances_vm_state AS anon_1_instances_vm_state,
anon_1.instances_task_state AS anon_1_instances_task_state, anon_1.inst
ances_memory_mb AS anon_1_instances_memory_mb, anon_1.instances_vcpus AS
anon_1_instances_vcpus, anon_1.instances_root_gb AS anon_1_instances_root_gb,
anon_1.instances_ephemeral_gb AS anon_1_instances_ephemeral_gb,
anon_1.instances_ephemeral_key_uuid AS anon_1_instances_ephemeral_key_uuid,
anon_1.instances_host AS anon_1_instances_host, anon_1.instances_node AS
anon_1_instances_node, anon_1.instances_instance_type_id AS
anon_1_instances_instance_type_id, anon_1.instances_user_data AS
anon_1_instances_user_data, anon_1.instances_reservation_id AS
anon_1_instances_reservation_id, anon_1.instances_scheduled_at AS
anon_1_instances_scheduled_at, anon_1.instances_launched_at AS
anon_1_instances_launched_at, anon_1.instances_terminated_at AS
anon_1_instances_terminated_at, anon_1.instances_availability_zone AS
anon_1_instances_availability_zone, anon_1.instances_display_name AS
anon_1_instances_display_name, anon_1.instances_display_description AS
anon_1_instances_display_description, anon
_1.instances_launched_on AS anon_1_instances_launched_on,
anon_1.instances_locked AS anon_1_instances_locked, anon_1.instances_locked_by
AS anon_1_instances_locked_by, anon_1.instances_os_type AS
anon_1_instances_os_type, anon_1.instances_architecture AS
anon_1_instances_architecture, anon_1.instances_vm_mode AS
anon_1_instances_vm_mode, anon_1.instances_uuid AS anon_1_instances_uuid,
anon_1.instances_root_device_name AS anon_1_instances_root_device_name,
anon_1.instances_default_ephemeral_device AS
anon_1_instances_default_ephemeral_device, anon_1.instances_default_swap_device
AS anon_1_instances_default_swap_device, anon_1.instances_config_drive AS
anon_1_instances_config_drive, anon_1.instances_access_ip_v4 AS
anon_1_instances_access_ip_v4, anon_1.instances_access_ip_v6 AS
anon_1_instances_access_ip_v6, anon_1.instances_auto_disk_config AS
anon_1_instances_auto_disk_config, anon_1.instances_progress AS
anon_1_instances_progress, anon_1.instances_shutdown_terminate AS anon_1_insta
nces_shutdown_terminate, anon_1.instances_disable_terminate AS
anon_1_instances_disable_terminate, anon_1.instances_cell_name AS
anon_1_instances_cell_name, anon_1.instances_internal_id AS
anon_1_instances_internal_id, anon_1.instances_cleaned AS
anon_1_instances_cleaned, security_groups_1.created_at AS
security_groups_1_created_at, security_groups_1.updated_at AS
security_groups_1_updated_at, security_groups_1.deleted_at AS
security_groups_1_deleted_at, security_groups_1.deleted AS
security_groups_1_deleted, security_groups_1.id AS security_groups_1_id,
security_groups_1.name AS security_groups_1_name, security_groups_1.description
AS security_groups_1_description, security_groups_1.user_id AS
security_groups_1_user_id, security_groups_1.project_id AS
security_groups_1_project_id, instance_info_caches_1.created_at AS
instance_info_caches_1_created_at, instance_info_caches_1.updated_at AS
instance_info_caches_1_updated_at, instance_info_caches_1.deleted_at AS
instance_info_caches_1_
deleted_at, instance_info_caches_1.deleted AS instance_info_caches_1_deleted,
instance_info_caches_1.id AS instance_info_caches_1_id,
instance_info_caches_1.network_info AS instance_info_caches_1_network_info,
instance_info_caches_1.instance_uuid AS instance_info_caches_1_instance_uuid
FROM (SELECT instances.created_at AS instances_created_at,
instances.updated_at AS instances_updated_at, instances.deleted_at AS
instances_deleted_at, instances.deleted AS instances_deleted, instances.id AS
instances_id, instances.user_id AS instances_user_id, instances.project_id AS
instances_project_id, instances.image_ref AS instances_image_ref,
instances.kernel_id AS instances_kernel_id, instances.ramdisk_id AS
instances_ramdisk_id, instances.hostname AS instances_hostname,
instances.launch_index AS instances_launch_index, instances.key_name AS
instances_key_name, instances.key_data AS instances_key_data,
instances.power_state AS instances_power_state, instances.vm_state AS
instances_vm_state, instances.task_state AS instances_task_state,
instances.memory_mb AS instances_memory_mb, instances.vcpus AS instances_vcpus,
instances.root_gb AS instances_root_gb, instances.ephemeral_gb AS
instances_ephemeral_gb, instances.ephemeral_key_uuid AS
instances_ephemeral_key_uuid, instances.h
ost AS instances_host, instances.node AS instances_node,
instances.instance_type_id AS instances_instance_type_id, instances.user_data
AS instances_user_data, instances.reservation_id AS instances_reservation_id,
instances.scheduled_at AS instances_scheduled_at, instances.launched_at AS
instances_launched_at, instances.terminated_at AS instances_terminated_at,
instances.availability_zone AS instances_availability_zone,
instances.display_name AS instances_display_name, instances.display_description
AS instances_display_description, instances.launched_on AS
instances_launched_on, instances.locked AS instances_locked,
instances.locked_by AS instances_locked_by, instances.os_type AS
instances_os_type, instances.architecture AS instances_architecture,
instances.vm_mode AS instances_vm_mode, instances.uuid AS instances_uuid,
instances.root_device_name AS instances_root_device_name,
instances.default_ephemeral_device AS instances_default_ephemeral_device,
instances.default_swap_device AS i
nstances_default_swap_device, instances.config_drive AS
instances_config_drive, instances.access_ip_v4 AS instances_access_ip_v4,
instances.access_ip_v6 AS instances_access_ip_v6, instances.auto_disk_config AS
instances_auto_disk_config, instances.progress AS instances_progress,
instances.shutdown_terminate AS instances_shutdown_terminate,
instances.disable_terminate AS instances_disable_terminate, instances.cell_name
AS instances_cell_name, instances.internal_id AS instances_internal_id,
instances.cleaned AS instances_cleaned
FROM instances
WHERE instances.deleted = 0 AND (instances.vm_state != 'soft-delete' OR
instances.vm_state IS NULL) AND instances.project_id = 'xxxxxx' ORDER BY
instances.created_at DESC, instances.created_at DESC, instances.created_at
DESC, instances.id DESC
LIMIT 1000) AS anon_1 LEFT OUTER JOIN security_group_instance_association AS
security_group_instance_association_1 ON
security_group_instance_association_1.instance_uuid = anon_1.instances_uuid AND
anon_1.instances_deleted = 0 LEFT OUTER JOIN security_groups AS
security_groups_1 ON security_groups_1.id =
security_group_instance_association_1.security_group_id AND
security_group_instance_association_1.deleted = 0 AND security_groups_1.deleted
= 0 LEFT OUTER JOIN instance_info_caches AS instance_info_caches_1 ON
instance_info_caches_1.instance_uuid = anon_1.instances_uuid ORDER BY
anon_1.instances_created_at DESC, anon_1.instances_created_at DESC,
anon_1.instances_created_at DESC, anon_1.instances_id DESC;
This is generated by instance_get_all_by_filters()
Looking at the explain output, MySQL uses only the project_id index.
This causes it to scan through potentially hundreds of thousands of
deleted instances.
Changing the index that covers (project_id) to cover (project_id,
deleted) allows this query (and some others) to run much faster (about
95% faster in our tests).
To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1378395/+subscriptions
--
Mailing list: https://launchpad.net/~yahoo-eng-team
Post to : [email protected]
Unsubscribe : https://launchpad.net/~yahoo-eng-team
More help : https://help.launchpad.net/ListHelp
