<div dir="ltr"><div dir="ltr"><div>Hi,<br></div><div><br></div><div>While troubleshooting a production issue we identified that the Nova metadata API is fetching a lot more raw data from the database than seems necessary. The problem appears to be caused by the SQL query used to fetch instance data that joins the "instance" table with, among others, two metadata tables: "instance_metadata" and "instance_system_metadata". Below is a simplified version of this query (I've added the full query at the end of this message for reference):</div><div><br></div><div>SELECT ...</div><div>  FROM (SELECT ...</div><div>          FROM `instances`</div><div>         WHERE `instances` . `deleted` = ?</div><div>           AND `instances` . `uuid` = ?</div><div>         LIMIT ?) AS `anon_1`</div><div>  LEFT OUTER JOIN `instance_system_metadata` AS `instance_system_metadata_1`</div><div>    ON `anon_1` . `instances_uuid` = `instance_system_metadata_1` . `instance_uuid`</div><div>  LEFT OUTER JOIN (`security_group_instance_association` AS `security_group_instance_association_1`</div><div>                   INNER JOIN `security_groups` AS `security_groups_1`</div><div>                   ON `security_groups_1` . `id` = `security_group_instance_association_1` . `security_group_id`</div><div>                   AND `security_group_instance_association_1` . `deleted` = ?</div><div>                   AND `security_groups_1` . `deleted` = ? )</div><div>    ON `security_group_instance_association_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>   AND `anon_1` . `instances_deleted` = ?</div><div>  LEFT OUTER JOIN `security_group_rules` AS `security_group_rules_1`</div><div>    ON `security_group_rules_1` . `parent_group_id` = `security_groups_1` . `id`</div><div>   AND `security_group_rules_1` . `deleted` = ?</div><div>  LEFT OUTER JOIN `instance_info_caches` AS `instance_info_caches_1`</div><div>    ON `instance_info_caches_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>  LEFT OUTER JOIN `instance_extra` AS `instance_extra_1`</div><div>    ON `instance_extra_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>  LEFT OUTER JOIN `instance_metadata` AS `instance_metadata_1`</div><div>    ON `instance_metadata_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>   AND `instance_metadata_1` . `deleted` = ?</div><div><br></div><div>The instance table has a 1-to-many relationship to both "instance_metadata" and "instance_system_metadata" tables, so the query is effectively producing a cross join of both metadata tables.</div><div><br></div><div>To illustrate the impact of this query, I have an instance that has 2 records in "instance_metadata" and 5 records in "instance_system_metadata":</div><div><br></div><div>> select instance_uuid,`key`,value from instance_metadata where instance_uuid = 'a6cf4a6a-effe-4438-9b7f-d61b23117b9b';</div><div>+--------------------------------------+-----------+--------+</div><div>| instance_uuid                        | key       | value  |</div><div>+--------------------------------------+-----------+--------+</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property1 | value1 |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property2 | value  |</div><div>+--------------------------------------+-----------+--------+</div><div>2 rows in set (0.61 sec)</div><div><br></div><div>> select instance_uuid,`key`,valusystem_metadata where instance_uuid = 'a6cf4a6a-effe-4438-9b7f-d61b23117b9b';</div><div>+------------------------+--------------------------------------+</div><div>| key                    | value                                |</div><div>+------------------------+--------------------------------------+</div><div>| image_disk_format      | qcow2                                |</div><div>| image_min_ram          | 0                                    |</div><div>| image_min_disk         | 20                                   |</div><div>| image_base_image_ref   | 39cd564f-6a29-43e2-815b-62097968486a |</div><div>| image_container_format | bare                                 |</div><div>+------------------------+--------------------------------------+</div><div>5 rows in set (0.00 sec)</div><div><br></div><div>For this particular instance, the query used by the metadata API will fetch 10 records from the database:</div><div><br></div><div>+--------------------------------------+-------------------------+---------------------------+--------------------------------+--------------------------------------+</div><div>| anon_1_instances_uuid                | instance_metadata_1_key | instance_metadata_1_value | instance_system_metadata_1_key | instance_system_metadata_1_value     |</div><div>+--------------------------------------+-------------------------+---------------------------+--------------------------------+--------------------------------------+</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property1               | value1                    | image_disk_format              | qcow2                                |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property2               | value                     | image_disk_format              | qcow2                                |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property1               | value1                    | image_min_ram                  | 0                                    |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property2               | value                     | image_min_ram                  | 0                                    |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property1               | value1                    | image_min_disk                 | 20                                   |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property2               | value                     | image_min_disk                 | 20                                   |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property1               | value1                    | image_base_image_ref           | 39cd564f-6a29-43e2-815b-62097968486a |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property2               | value                     | image_base_image_ref           | 39cd564f-6a29-43e2-815b-62097968486a |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property1               | value1                    | image_container_format         | bare                                 |</div><div>| a6cf4a6a-effe-4438-9b7f-d61b23117b9b | property2               | value                     | image_container_format         | bare                                 |</div><div>+--------------------------------------+-------------------------+---------------------------+--------------------------------+--------------------------------------+</div><div>10 rows in set (0.00 sec)</div><div><br></div><div>Of course this is only a problem when instances have a lot of metadata records. An instance with 50 records in "instance_metadata" and 50 records in "instance_system_metadata" will fetch 50 x 50 = 2,500 rows from the database. It's not difficult to see how this can escalate quickly. This can be a particularly significant problem in a HA scenario with multiple API nodes pulling data from multiple database nodes.</div><div><br></div><div>This issue is affecting our clusters running OpenStack Mitaka. I verified that this is not an issue on clusters running OpenStack Icehouse because, in Icehouse, instance data is pulled as needed, executing separate queries for each table, but I as far as I could see, this issue could be affecting every release since Mitaka.</div><div><br></div><div>Since both metadata tables are structurally identical, a UNION of both metadata tables could be performed before joining but of course the Instance class would need to be updated to reflect this change.</div><div><br></div><div>I did not find any bug report related to this problem and I'm wondering if this is a known issue or if anybody has any ideas of how this can be mitigated.</div><div><br></div><div>Any help here would be hugely appreciated.</div><div><br></div><div>Regards,</div><div><br></div><div>Sergio</div><div><br></div><div>---</div><div><br></div><div>Full SQL query:</div><div><br></div><div>SELECT `anon_1` . `instances_created_at` AS `anon_1_instances_created_at`,</div><div>       `anon_1` . `instances_updated_at` AS `anon_1_instances_updated_at`,</div><div>       `anon_1` . `instances_deleted_at` AS `anon_1_instances_deleted_at`,</div><div>       `anon_1` . `instances_deleted` AS `anon_1_instances_deleted`,</div><div>       `anon_1` . `instances_id` AS `anon_1_instances_id`,</div><div>       `anon_1` . `instances_user_id` AS `anon_1_instances_user_id`,</div><div>       `anon_1` . `instances_project_id` AS `anon_1_instances_project_id`,</div><div>       `anon_1` . `instances_image_ref` AS `anon_1_instances_image_ref`,</div><div>       `anon_1` . `instances_kernel_id` AS `anon_1_instances_kernel_id`,</div><div>       `anon_1` . `instances_ramdisk_id` AS `anon_1_instances_ramdisk_id`,</div><div>       `anon_1` . `instances_hostname` AS `anon_1_instances_hostname`,</div><div>       `anon_1` . `instances_launch_index` AS `anon_1_instances_launch_index`,</div><div>       `anon_1` . `instances_key_name` AS `anon_1_instances_key_name`,</div><div>       `anon_1` . `instances_key_data` AS `anon_1_instances_key_data`,</div><div>       `anon_1` . `instances_power_state` AS `anon_1_instances_power_state`,</div><div>       `anon_1` . `instances_vm_state` AS `anon_1_instances_vm_state`,</div><div>       `anon_1` . `instances_task_state` AS `anon_1_instances_task_state`,</div><div>       `anon_1` . `instances_memory_mb` AS `anon_1_instances_memory_mb`,</div><div>       `anon_1` . `instances_vcpus` AS `anon_1_instances_vcpus`,</div><div>       `anon_1` . `instances_root_gb` AS `anon_1_instances_root_gb`,</div><div>       `anon_1` . `instances_ephemeral_gb` AS `anon_1_instances_ephemeral_gb`,</div><div>       `anon_1` . `instances_ephemeral_key_uuid` AS `anon_1_instances_ephemeral_key_uuid`,</div><div>       `anon_1` . `instances_host` AS `anon_1_instances_host`,</div><div>       `anon_1` . `instances_node` AS `anon_1_instances_node`,</div><div>       `anon_1` . `instances_instance_type_id` AS `anon_1_instances_instance_type_id`,</div><div>       `anon_1` . `instances_user_data` AS `anon_1_instances_user_data`,</div><div>       `anon_1` . `instances_reservation_id` AS `anon_1_instances_reservation_id`,</div><div>       `anon_1` . `instances_launched_at` AS `anon_1_instances_launched_at`,</div><div>       `anon_1` . `instances_terminated_at` AS `anon_1_instances_terminated_at`,</div><div>       `anon_1` . `instances_availability_zone` AS `anon_1_instances_availability_zone`,</div><div>       `anon_1` . `instances_display_name` AS `anon_1_instances_display_name`,</div><div>       `anon_1` . `instances_display_description` AS `anon_1_instances_display_description`,</div><div>       `anon_1` . `instances_launched_on` AS `anon_1_instances_launched_on`,</div><div>       `anon_1` . `instances_locked` AS `anon_1_instances_locked`,</div><div>       `anon_1` . `instances_locked_by` AS `anon_1_instances_locked_by`,</div><div>       `anon_1` . `instances_os_type` AS `anon_1_instances_os_type`,</div><div>       `anon_1` . `instances_architecture` AS `anon_1_instances_architecture`,</div><div>       `anon_1` . `instances_vm_mode` AS `anon_1_instances_vm_mode`,</div><div>       `anon_1` . `instances_uuid` AS `anon_1_instances_uuid`,</div><div>       `anon_1` . `instances_root_device_name` AS `anon_1_instances_root_device_name`,</div><div>       `anon_1` . `instances_default_ephemeral_device` AS `anon_1_instances_default_ephemeral_device`,</div><div>       `anon_1` . `instances_default_swap_device` AS `anon_1_instances_default_swap_device`,</div><div>       `anon_1` . `instances_config_drive` AS `anon_1_instances_config_drive`,</div><div>       `anon_1` . `instances_access_ip_v4` AS `anon_1_instances_access_ip_v4`,</div><div>       `anon_1` . `instances_access_ip_v6` AS `anon_1_instances_access_ip_v6`,</div><div>       `anon_1` . `instances_auto_disk_config` AS `anon_1_instances_auto_disk_config`,</div><div>       `anon_1` . `instances_progress` AS `anon_1_instances_progress`,</div><div>       `anon_1` . `instances_shutdown_terminate` AS `anon_1_instances_shutdown_terminate`,</div><div>       `anon_1` . `instances_disable_terminate` AS `anon_1_instances_disable_terminate`,</div><div>       `anon_1` . `instances_cell_name` AS `anon_1_instances_cell_name`,</div><div>       `anon_1` . `instances_internal_id` AS `anon_1_instances_internal_id`,</div><div>       `anon_1` . `instances_cleaned` AS `anon_1_instances_cleaned`,</div><div>       `instance_system_metadata_1` . `created_at` AS `instance_system_metadata_1_created_at`,</div><div>       `instance_system_metadata_1` . `updated_at` AS `instance_system_metadata_1_updated_at`,</div><div>       `instance_system_metadata_1` . `deleted_at` AS `instance_system_metadata_1_deleted_at`,</div><div>       `instance_system_metadata_1` . `deleted` AS `instance_system_metadata_1_deleted`,</div><div>       `instance_system_metadata_1` . `id` AS `instance_system_metadata_1_id`,</div><div>       `instance_system_metadata_1` . `key` AS `instance_system_metadata_1_key`,</div><div>       `instance_system_metadata_1` . `value` AS `instance_system_metadata_1_value`,</div><div>       `instance_system_metadata_1` . `instance_uuid` AS `instance_system_metadata_1_instance_uuid`,</div><div>       `security_groups_1` . `created_at` AS `security_groups_1_created_at`,</div><div>       `security_groups_1` . `updated_at` AS `security_groups_1_updated_at`,</div><div>       `security_groups_1` . `deleted_at` AS `security_groups_1_deleted_at`,</div><div>       `security_groups_1` . `deleted` AS `security_groups_1_deleted`,</div><div>       `security_groups_1` . `id` AS `security_groups_1_id`,</div><div>       `security_groups_1` . `name` AS `security_groups_1_name`,</div><div>       `security_groups_1` . `description` AS `security_groups_1_description`,</div><div>       `security_groups_1` . `user_id` AS `security_groups_1_user_id`,</div><div>       `security_groups_1` . `project_id` AS `security_groups_1_project_id`,</div><div>       `security_group_rules_1` . `created_at` AS `security_group_rules_1_created_at`,</div><div>       `security_group_rules_1` . `updated_at` AS `security_group_rules_1_updated_at`,</div><div>       `security_group_rules_1` . `deleted_at` AS `security_group_rules_1_deleted_at`,</div><div>       `security_group_rules_1` . `deleted` AS `security_group_rules_1_deleted`,</div><div>       `security_group_rules_1` . `id` AS `security_group_rules_1_id`,</div><div>       `security_group_rules_1` . `parent_group_id` AS `security_group_rules_1_parent_group_id`,</div><div>       `security_group_rules_1` . `protocol` AS `security_group_rules_1_protocol`,</div><div>       `security_group_rules_1` . `from_port` AS `security_group_rules_1_from_port`,</div><div>       `security_group_rules_1` . `to_port` AS `security_group_rules_1_to_port`,</div><div>       `security_group_rules_1` . `cidr` AS `security_group_rules_1_cidr`,</div><div>       `security_group_rules_1` . `group_id` AS `security_group_rules_1_group_id`,</div><div>       `instance_info_caches_1` . `created_at` AS `instance_info_caches_1_created_at`,</div><div>       `instance_info_caches_1` . `updated_at` AS `instance_info_caches_1_updated_at`,</div><div>       `instance_info_caches_1` . `deleted_at` AS `instance_info_caches_1_deleted_at`,</div><div>       `instance_info_caches_1` . `deleted` AS `instance_info_caches_1_deleted`,</div><div>       `instance_info_caches_1` . `id` AS `instance_info_caches_1_id`,</div><div>       `instance_info_caches_1` . `network_info` AS `instance_info_caches_1_network_info`,</div><div>       `instance_info_caches_1` . `instance_uuid` AS `instance_info_caches_1_instance_uuid`,</div><div>       `instance_extra_1` . `flavor` AS `instance_extra_1_flavor`,</div><div>       `instance_extra_1` . `created_at` AS `instance_extra_1_created_at`,</div><div>       `instance_extra_1` . `updated_at` AS `instance_extra_1_updated_at`,</div><div>       `instance_extra_1` . `deleted_at` AS `instance_extra_1_deleted_at`,</div><div>       `instance_extra_1` . `deleted` AS `instance_extra_1_deleted`,</div><div>       `instance_extra_1` . `id` AS `instance_extra_1_id`,</div><div>       `instance_extra_1` . `instance_uuid` AS `instance_extra_1_instance_uuid`,</div><div>       `instance_metadata_1` . `created_at` AS `instance_metadata_1_created_at`,</div><div>       `instance_metadata_1` . `updated_at` AS `instance_metadata_1_updated_at`,</div><div>       `instance_metadata_1` . `deleted_at` AS `instance_metadata_1_deleted_at`,</div><div>       `instance_metadata_1` . `deleted` AS `instance_metadata_1_deleted`,</div><div>       `instance_metadata_1` . `id` AS `instance_metadata_1_id`,</div><div>       `instance_metadata_1` . `key` AS `instance_metadata_1_key`,</div><div>       `instance_metadata_1` . `value` AS `instance_metadata_1_value`,</div><div>       `instance_metadata_1` . `instance_uuid` AS `instance_metadata_1_instance_uuid`</div><div>  FROM (SELECT `instances` . `created_at` AS `instances_created_at`,</div><div>               `instances` . `updated_at` AS `instances_updated_at`,</div><div>               `instances` . `deleted_at` AS `instances_deleted_at`,</div><div>               `instances` . `deleted` AS `instances_deleted`,</div><div>               `instances` . `id` AS `instances_id`,</div><div>               `instances` . `user_id` AS `instances_user_id`,</div><div>               `instances` . `project_id` AS `instances_project_id`,</div><div>               `instances` . `image_ref` AS `instances_image_ref`,</div><div>               `instances` . `kernel_id` AS `instances_kernel_id`,</div><div>               `instances` . `ramdisk_id` AS `instances_ramdisk_id`,</div><div>               `instances` . `hostname` AS `instances_hostname`,</div><div>               `instances` . `launch_index` AS `instances_launch_index`,</div><div>               `instances` . `key_name` AS `instances_key_name`,</div><div>               `instances` . `key_data` AS `instances_key_data`,</div><div>               `instances` . `power_state` AS `instances_power_state`,</div><div>               `instances` . `vm_state` AS `instances_vm_state`,</div><div>               `instances` . `task_state` AS `instances_task_state`,</div><div>               `instances` . `memory_mb` AS `instances_memory_mb`,</div><div>               `instances` . `vcpus` AS `instances_vcpus`,</div><div>               `instances` . `root_gb` AS `instances_root_gb`,</div><div>               `instances` . `ephemeral_gb` AS `instances_ephemeral_gb`,</div><div>               `instances` . `ephemeral_key_uuid` AS `instances_ephemeral_key_uuid`,</div><div>               `instances` . `host` AS `instances_host`,</div><div>               `instances` . `node` AS `instances_node`,</div><div>               `instances` . `instance_type_id` AS `instances_instance_type_id`,</div><div>               `instances` . `user_data` AS `instances_user_data`,</div><div>               `instances` . `reservation_id` AS `instances_reservation_id`,</div><div>               `instances` . `launched_at` AS `instances_launched_at`,</div><div>               `instances` . `terminated_at` AS `instances_terminated_at`,</div><div>               `instances` . `availability_zone` AS `instances_availability_zone`,</div><div>               `instances` . `display_name` AS `instances_display_name`,</div><div>               `instances` . `display_description` AS `instances_display_description`,</div><div>               `instances` . `launched_on` AS `instances_launched_on`,</div><div>               `instances` . `locked` AS `instances_locked`,</div><div>               `instances` . `locked_by` AS `instances_locked_by`,</div><div>               `instances` . `os_type` AS `instances_os_type`,</div><div>               `instances` . `architecture` AS `instances_architecture`,</div><div>               `instances` . `vm_mode` AS `instances_vm_mode`,</div><div>               `instances` . `uuid` AS `instances_uuid`,</div><div>               `instances` . `root_device_name` AS `instances_root_device_name`,</div><div>               `instances` . `default_ephemeral_device` AS `instances_default_ephemeral_device`,</div><div>               `instances` . `default_swap_device` AS `instances_default_swap_device`,</div><div>               `instances` . `config_drive` AS `instances_config_drive`,</div><div>               `instances` . `access_ip_v4` AS `instances_access_ip_v4`,</div><div>               `instances` . `access_ip_v6` AS `instances_access_ip_v6`,</div><div>               `instances` . `auto_disk_config` AS `instances_auto_disk_config`,</div><div>               `instances` . `progress` AS `instances_progress`,</div><div>               `instances` . `shutdown_terminate` AS `instances_shutdown_terminate`,</div><div>               `instances` . `disable_terminate` AS `instances_disable_terminate`,</div><div>               `instances` . `cell_name` AS `instances_cell_name`,</div><div>               `instances` . `internal_id` AS `instances_internal_id`,</div><div>               `instances` . `cleaned` AS `instances_cleaned`</div><div>          FROM `instances`</div><div>         WHERE `instances` . `deleted` = ?</div><div>           AND `instances` . `uuid` = ?</div><div>         LIMIT ?) AS `anon_1`</div><div>  LEFT OUTER JOIN `instance_system_metadata` AS `instance_system_metadata_1`</div><div>    ON `anon_1` . `instances_uuid` = `instance_system_metadata_1` . `instance_uuid`</div><div>  LEFT OUTER JOIN (`security_group_instance_association` AS `security_group_instance_association_1`</div><div>                   INNER JOIN `security_groups` AS `security_groups_1`</div><div>                   ON `security_groups_1` . `id` = `security_group_instance_association_1` . `security_group_id`</div><div>                   AND `security_group_instance_association_1` . `deleted` = ?</div><div>                   AND `security_groups_1` . `deleted` = ? )</div><div>    ON `security_group_instance_association_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>   AND `anon_1` . `instances_deleted` = ?</div><div>  LEFT OUTER JOIN `security_group_rules` AS `security_group_rules_1`</div><div>    ON `security_group_rules_1` . `parent_group_id` = `security_groups_1` . `id`</div><div>   AND `security_group_rules_1` . `deleted` = ?</div><div>  LEFT OUTER JOIN `instance_info_caches` AS `instance_info_caches_1`</div><div>    ON `instance_info_caches_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>  LEFT OUTER JOIN `instance_extra` AS `instance_extra_1`</div><div>    ON `instance_extra_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>  LEFT OUTER JOIN `instance_metadata` AS `instance_metadata_1`</div><div>    ON `instance_metadata_1` . `instance_uuid` = `anon_1` . `instances_uuid`</div><div>   AND `instance_metadata_1` . `deleted` = ?</div><div><br></div></div></div>