[all][db] Lots of redundant DB indices ?
Helolo openstack-discuss! I recently saw lots of warnings the like of: "/usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (1831, 'Duplicate index `uniq_instances0uuid`. This is deprecated and will be disallowed in a future release') result = self._query(query)" This originated from MySQL error 1831 (see [1]). I then raised a few bugs and also did some changes in regards to the duplicate indices I noticed: * Placement https://storyboard.openstack.org/#!/story/2010251 <https://storyboard.openstack.org/#!/story/2010251> / https://review.opendev.org/c/openstack/placement/+/856770 <https://review.opendev.org/c/openstack/placement/+/856770> * Keystone - https://bugs.launchpad.net/keystone/+bug/1988297 <https://bugs.launchpad.net/keystone/+bug/1988297> / https://review.opendev.org/c/openstack/keystone/+/885463 <https://review.opendev.org/c/openstack/keystone/+/885463> * Neutron - https://bugs.launchpad.net/neutron/+bug/1988421 <https://bugs.launchpad.net/neutron/+bug/1988421> / https://review.opendev.org/c/openstack/neutron/+/885456 <https://review.opendev.org/c/openstack/neutron/+/885456> * Nova - https://review.opendev.org/c/openstack/nova/+/856757 <https://review.opendev.org/c/openstack/nova/+/856757> But running Percona's pt-duplicate-key-checker ([2]) there are quite a few more redundant indices reported:
ALTER TABLE `cinder`.`driver_initiator_data` DROP INDEX `ix_driver_initiator_data_initiator`; ALTER TABLE `cinder`.`quota_usages` DROP INDEX `ix_quota_usages_project_id`; ALTER TABLE `cinder`.`quota_usages` DROP INDEX `quota_usage_project_resource_idx`; ALTER TABLE `designate`.`records` DROP FOREIGN KEY `records_ibfk_2`; ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_zoneid`; ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_type`; ALTER TABLE `glance`.`image_members` DROP INDEX `ix_image_members_image_id`; ALTER TABLE `glance`.`image_members` DROP INDEX `ix_image_members_image_id_member`; ALTER TABLE `glance`.`image_properties` DROP INDEX `ix_image_properties_image_id`; ALTER TABLE `glance`.`image_tags` DROP INDEX `ix_image_tags_image_id`; ALTER TABLE `keystone`.`access_rule` DROP INDEX `external_id`; ALTER TABLE `keystone`.`access_rule` DROP INDEX `user_id`; ALTER TABLE `keystone`.`project_tag` DROP INDEX `project_id`; ALTER TABLE `keystone`.`token` DROP INDEX `ix_token_expires`; ALTER TABLE `mysql`.`transaction_registry` DROP INDEX `commit_timestamp`, ADD INDEX `commit_timestamp` (`commit_timestamp`); ALTER TABLE `neutron`.`addressgrouprbacs` DROP INDEX `ix_addressgrouprbacs_target_project`; ALTER TABLE `neutron`.`addressscoperbacs` DROP INDEX `ix_addressscoperbacs_target_project`; ALTER TABLE `neutron`.`floatingipdnses` DROP INDEX `ix_floatingipdnses_floatingip_id`; ALTER TABLE `neutron`.`networkdnsdomains` DROP INDEX `ix_networkdnsdomains_network_id`; ALTER TABLE `neutron`.`networkrbacs` DROP INDEX `ix_networkrbacs_target_project`; ALTER TABLE `neutron`.`ovn_hash_ring` DROP INDEX `ix_ovn_hash_ring_node_uuid`; ALTER TABLE `neutron`.`ovn_revision_numbers` DROP INDEX `ix_ovn_revision_numbers_resource_uuid`; ALTER TABLE `neutron`.`portdataplanestatuses` DROP INDEX `ix_portdataplanestatuses_port_id`; ALTER TABLE `neutron`.`portdnses` DROP INDEX `ix_portdnses_port_id`; ALTER TABLE `neutron`.`ports` DROP INDEX `ix_ports_network_id_mac_address`; ALTER TABLE `neutron`.`portuplinkstatuspropagation` DROP INDEX `ix_portuplinkstatuspropagation_port_id`; ALTER TABLE `neutron`.`qos_minimum_bandwidth_rules` DROP INDEX `ix_qos_minimum_bandwidth_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_minimum_packet_rate_rules` DROP INDEX `ix_qos_minimum_packet_rate_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_packet_rate_limit_rules` DROP INDEX `ix_qos_packet_rate_limit_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_policies_default` DROP INDEX `ix_qos_policies_default_project_id`; ALTER TABLE `neutron`.`qospolicyrbacs` DROP INDEX `ix_qospolicyrbacs_target_project`; ALTER TABLE `neutron`.`quotas` DROP INDEX `ix_quotas_project_id`; ALTER TABLE `neutron`.`quotausages` DROP INDEX `ix_quotausages_project_id`; ALTER TABLE `neutron`.`securitygrouprbacs` DROP INDEX `ix_securitygrouprbacs_target_project`; ALTER TABLE `neutron`.`segmenthostmappings` DROP INDEX `ix_segmenthostmappings_segment_id`; ALTER TABLE `neutron`.`subnet_dns_publish_fixed_ips` DROP INDEX `ix_subnet_dns_publish_fixed_ips_subnet_id`; ALTER TABLE `neutron`.`subnetpoolrbacs` DROP INDEX `ix_subnetpoolrbacs_target_project`; ALTER TABLE `nova`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`; ALTER TABLE `nova`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`; ALTER TABLE `nova`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`; ALTER TABLE `nova`.`fixed_ips` DROP INDEX `address`; ALTER TABLE `nova`.`fixed_ips` DROP INDEX `network_id`; ALTER TABLE `nova`.`instance_actions` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`; ALTER TABLE `nova`.`instance_type_projects` DROP INDEX `instance_type_id`; ALTER TABLE `nova`.`instances` DROP INDEX `uniq_instances0uuid`; ALTER TABLE `nova`.`instances` DROP INDEX `instances_project_id_idx`; ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova`.`networks` DROP INDEX `networks_vlan_deleted_idx`; ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `nova_api`.`build_requests` DROP INDEX `build_requests_instance_uuid_idx`; ALTER TABLE `nova_api`.`cell_mappings` DROP INDEX `uuid_idx`; ALTER TABLE `nova_api`.`flavor_extra_specs` DROP INDEX `flavor_extra_specs_flavor_id_key_idx`; ALTER TABLE `nova_api`.`host_mappings` DROP INDEX `host_idx`; ALTER TABLE `nova_api`.`instance_mappings` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova_api`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova_api`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova_api`.`placement_aggregates` DROP INDEX `ix_placement_aggregates_uuid`; ALTER TABLE `nova_api`.`project_user_quotas` DROP INDEX `project_user_quotas_user_id_idx`; ALTER TABLE `nova_api`.`request_specs` DROP INDEX `request_spec_instance_uuid_idx`; ALTER TABLE `nova_api`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova_api`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `nova_cell0`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`; ALTER TABLE `nova_cell0`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`; ALTER TABLE `nova_cell0`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`; ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `address`; ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `network_id`; ALTER TABLE `nova_cell0`.`instance_actions` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova_cell0`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`; ALTER TABLE `nova_cell0`.`instance_type_projects` DROP INDEX `instance_type_id`; ALTER TABLE `nova_cell0`.`instances` DROP INDEX `uniq_instances0uuid`; ALTER TABLE `nova_cell0`.`instances` DROP INDEX `instances_project_id_idx`; ALTER TABLE `nova_cell0`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova_cell0`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova_cell0`.`networks` DROP INDEX `networks_vlan_deleted_idx`; ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `placement`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `placement`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `placement`.`placement_aggregates` DROP INDEX `ix_placement_aggregates_uuid`; ALTER TABLE `placement`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `placement`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 35810 # Total Duplicate Indexes 84 # Total Indexes 1792 (When adding more services (Telemetry, ...) there are even more indices reported.) Does it make sense to address this more broadly maybe? Especially with all the changes happening for SQLAlchemy 2.x currently? Regards Christian [1] https://mariadb.com/kb/en/mariadb-error-codes/ [2] https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html
(Sending this message again, as apparently the formatting was messed up the first time). Hello OpenStack-Discuss, I recently saw lots of warnings the like of: "/usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (1831, 'Duplicate index `uniq_instances0uuid`. This is deprecated and will be disallowed in a future release') result = self._query(query)" This originated from MySQL error 1831 (see [1]). I then raised a few bugs and also did some changes in regards to the duplicate indices I noticed: * Placement https://storyboard.openstack.org/#!/story/2010251 / https://review.opendev.org/c/openstack/placement/+/856770 * Keystone - https://bugs.launchpad.net/keystone/+bug/1988297 / https://review.opendev.org/c/openstack/keystone/+/885463 * Neutron - https://bugs.launchpad.net/neutron/+bug/1988421 / https://review.opendev.org/c/openstack/neutron/+/885456 * Nova - https://review.opendev.org/c/openstack/nova/+/856757 But running Percona's pt-duplicate-key-checker ([2]) there are quite a few more redundant indices reported:
ALTER TABLE `cinder`.`driver_initiator_data` DROP INDEX `ix_driver_initiator_data_initiator`; ALTER TABLE `cinder`.`quota_usages` DROP INDEX `ix_quota_usages_project_id`; ALTER TABLE `cinder`.`quota_usages` DROP INDEX `quota_usage_project_resource_idx`; ALTER TABLE `designate`.`records` DROP FOREIGN KEY `records_ibfk_2`; ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_zoneid`; ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_type`; ALTER TABLE `glance`.`image_members` DROP INDEX `ix_image_members_image_id`; ALTER TABLE `glance`.`image_members` DROP INDEX `ix_image_members_image_id_member`; ALTER TABLE `glance`.`image_properties` DROP INDEX `ix_image_properties_image_id`; ALTER TABLE `glance`.`image_tags` DROP INDEX `ix_image_tags_image_id`; ALTER TABLE `keystone`.`access_rule` DROP INDEX `external_id`; ALTER TABLE `keystone`.`access_rule` DROP INDEX `user_id`; ALTER TABLE `keystone`.`project_tag` DROP INDEX `project_id`; ALTER TABLE `keystone`.`token` DROP INDEX `ix_token_expires`; ALTER TABLE `mysql`.`transaction_registry` DROP INDEX `commit_timestamp`, ADD INDEX `commit_timestamp` (`commit_timestamp`); ALTER TABLE `neutron`.`addressgrouprbacs` DROP INDEX `ix_addressgrouprbacs_target_project`; ALTER TABLE `neutron`.`addressscoperbacs` DROP INDEX `ix_addressscoperbacs_target_project`; ALTER TABLE `neutron`.`floatingipdnses` DROP INDEX `ix_floatingipdnses_floatingip_id`; ALTER TABLE `neutron`.`networkdnsdomains` DROP INDEX `ix_networkdnsdomains_network_id`; ALTER TABLE `neutron`.`networkrbacs` DROP INDEX `ix_networkrbacs_target_project`; ALTER TABLE `neutron`.`ovn_hash_ring` DROP INDEX `ix_ovn_hash_ring_node_uuid`; ALTER TABLE `neutron`.`ovn_revision_numbers` DROP INDEX `ix_ovn_revision_numbers_resource_uuid`; ALTER TABLE `neutron`.`portdataplanestatuses` DROP INDEX `ix_portdataplanestatuses_port_id`; ALTER TABLE `neutron`.`portdnses` DROP INDEX `ix_portdnses_port_id`; ALTER TABLE `neutron`.`ports` DROP INDEX `ix_ports_network_id_mac_address`; ALTER TABLE `neutron`.`portuplinkstatuspropagation` DROP INDEX `ix_portuplinkstatuspropagation_port_id`; ALTER TABLE `neutron`.`qos_minimum_bandwidth_rules` DROP INDEX `ix_qos_minimum_bandwidth_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_minimum_packet_rate_rules` DROP INDEX `ix_qos_minimum_packet_rate_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_packet_rate_limit_rules` DROP INDEX `ix_qos_packet_rate_limit_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_policies_default` DROP INDEX `ix_qos_policies_default_project_id`; ALTER TABLE `neutron`.`qospolicyrbacs` DROP INDEX `ix_qospolicyrbacs_target_project`; ALTER TABLE `neutron`.`quotas` DROP INDEX `ix_quotas_project_id`; ALTER TABLE `neutron`.`quotausages` DROP INDEX `ix_quotausages_project_id`; ALTER TABLE `neutron`.`securitygrouprbacs` DROP INDEX `ix_securitygrouprbacs_target_project`; ALTER TABLE `neutron`.`segmenthostmappings` DROP INDEX `ix_segmenthostmappings_segment_id`; ALTER TABLE `neutron`.`subnet_dns_publish_fixed_ips` DROP INDEX `ix_subnet_dns_publish_fixed_ips_subnet_id`; ALTER TABLE `neutron`.`subnetpoolrbacs` DROP INDEX `ix_subnetpoolrbacs_target_project`; ALTER TABLE `nova`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`; ALTER TABLE `nova`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`; ALTER TABLE `nova`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`; ALTER TABLE `nova`.`fixed_ips` DROP INDEX `address`; ALTER TABLE `nova`.`fixed_ips` DROP INDEX `network_id`; ALTER TABLE `nova`.`instance_actions` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`; ALTER TABLE `nova`.`instance_type_projects` DROP INDEX `instance_type_id`; ALTER TABLE `nova`.`instances` DROP INDEX `uniq_instances0uuid`; ALTER TABLE `nova`.`instances` DROP INDEX `instances_project_id_idx`; ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova`.`networks` DROP INDEX `networks_vlan_deleted_idx`; ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `nova_api`.`build_requests` DROP INDEX `build_requests_instance_uuid_idx`; ALTER TABLE `nova_api`.`cell_mappings` DROP INDEX `uuid_idx`; ALTER TABLE `nova_api`.`flavor_extra_specs` DROP INDEX `flavor_extra_specs_flavor_id_key_idx`; ALTER TABLE `nova_api`.`host_mappings` DROP INDEX `host_idx`; ALTER TABLE `nova_api`.`instance_mappings` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova_api`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova_api`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova_api`.`placement_aggregates` DROP INDEX `ix_placement_aggregates_uuid`; ALTER TABLE `nova_api`.`project_user_quotas` DROP INDEX `project_user_quotas_user_id_idx`; ALTER TABLE `nova_api`.`request_specs` DROP INDEX `request_spec_instance_uuid_idx`; ALTER TABLE `nova_api`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova_api`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `nova_cell0`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`; ALTER TABLE `nova_cell0`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`; ALTER TABLE `nova_cell0`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`; ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `address`; ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `network_id`; ALTER TABLE `nova_cell0`.`instance_actions` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova_cell0`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`; ALTER TABLE `nova_cell0`.`instance_type_projects` DROP INDEX `instance_type_id`; ALTER TABLE `nova_cell0`.`instances` DROP INDEX `uniq_instances0uuid`; ALTER TABLE `nova_cell0`.`instances` DROP INDEX `instances_project_id_idx`; ALTER TABLE `nova_cell0`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova_cell0`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova_cell0`.`networks` DROP INDEX `networks_vlan_deleted_idx`; ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `placement`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `placement`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `placement`.`placement_aggregates` DROP INDEX `ix_placement_aggregates_uuid`; ALTER TABLE `placement`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `placement`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`;
# ######################################################################## # Summary of indexes # ########################################################################
# Size Duplicate Indexes 35810 # Total Duplicate Indexes 84 # Total Indexes 1792
(When adding more services (Telemetry, ...) there are even more indices reported.) Does it make sense to address this more broadly maybe? Especially with all the changes happening for SQLAlchemy 2.x currently? Regards Christian [1] https://mariadb.com/kb/en/mariadb-error-codes/ [2] https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html
Hi Christian, Rodolfo started to investigate this topic for Neutron, see the bug and patch for it: https://bugs.launchpad.net/neutron/+bug/2024044 https://review.opendev.org/c/openstack/neutron/+/886213 Please review and comment it :-) Lajos (lajoskatona) Christian Rohmann <christian.rohmann@inovex.de> ezt írta (időpont: 2023. jún. 16., P, 8:57):
(Sending this message again, as apparently the formatting was messed up the first time).
Hello OpenStack-Discuss,
I recently saw lots of warnings the like of:
"/usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (1831, 'Duplicate index `uniq_instances0uuid`. This is deprecated and will be disallowed in a future release') result = self._query(query)" This originated from MySQL error 1831 (see [1]).
I then raised a few bugs and also did some changes in regards to the duplicate indices I noticed:
* Placement https://storyboard.openstack.org/#!/story/2010251 / https://review.opendev.org/c/openstack/placement/+/856770 * Keystone - https://bugs.launchpad.net/keystone/+bug/1988297 / https://review.opendev.org/c/openstack/keystone/+/885463 * Neutron - https://bugs.launchpad.net/neutron/+bug/1988421 / https://review.opendev.org/c/openstack/neutron/+/885456 * Nova - https://review.opendev.org/c/openstack/nova/+/856757
But running Percona's pt-duplicate-key-checker ([2]) there are quite a few more redundant indices reported:
ALTER TABLE `cinder`.`driver_initiator_data` DROP INDEX `ix_driver_initiator_data_initiator`; ALTER TABLE `cinder`.`quota_usages` DROP INDEX `ix_quota_usages_project_id`; ALTER TABLE `cinder`.`quota_usages` DROP INDEX `quota_usage_project_resource_idx`; ALTER TABLE `designate`.`records` DROP FOREIGN KEY `records_ibfk_2`; ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_zoneid`; ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_type`; ALTER TABLE `glance`.`image_members` DROP INDEX `ix_image_members_image_id`; ALTER TABLE `glance`.`image_members` DROP INDEX `ix_image_members_image_id_member`; ALTER TABLE `glance`.`image_properties` DROP INDEX `ix_image_properties_image_id`; ALTER TABLE `glance`.`image_tags` DROP INDEX `ix_image_tags_image_id`; ALTER TABLE `keystone`.`access_rule` DROP INDEX `external_id`; ALTER TABLE `keystone`.`access_rule` DROP INDEX `user_id`; ALTER TABLE `keystone`.`project_tag` DROP INDEX `project_id`; ALTER TABLE `keystone`.`token` DROP INDEX `ix_token_expires`; ALTER TABLE `mysql`.`transaction_registry` DROP INDEX `commit_timestamp`, ADD INDEX `commit_timestamp` (`commit_timestamp`); ALTER TABLE `neutron`.`addressgrouprbacs` DROP INDEX `ix_addressgrouprbacs_target_project`; ALTER TABLE `neutron`.`addressscoperbacs` DROP INDEX `ix_addressscoperbacs_target_project`; ALTER TABLE `neutron`.`floatingipdnses` DROP INDEX `ix_floatingipdnses_floatingip_id`; ALTER TABLE `neutron`.`networkdnsdomains` DROP INDEX `ix_networkdnsdomains_network_id`; ALTER TABLE `neutron`.`networkrbacs` DROP INDEX `ix_networkrbacs_target_project`; ALTER TABLE `neutron`.`ovn_hash_ring` DROP INDEX `ix_ovn_hash_ring_node_uuid`; ALTER TABLE `neutron`.`ovn_revision_numbers` DROP INDEX `ix_ovn_revision_numbers_resource_uuid`; ALTER TABLE `neutron`.`portdataplanestatuses` DROP INDEX `ix_portdataplanestatuses_port_id`; ALTER TABLE `neutron`.`portdnses` DROP INDEX `ix_portdnses_port_id`; ALTER TABLE `neutron`.`ports` DROP INDEX `ix_ports_network_id_mac_address`; ALTER TABLE `neutron`.`portuplinkstatuspropagation` DROP INDEX `ix_portuplinkstatuspropagation_port_id`; ALTER TABLE `neutron`.`qos_minimum_bandwidth_rules` DROP INDEX `ix_qos_minimum_bandwidth_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_minimum_packet_rate_rules` DROP INDEX `ix_qos_minimum_packet_rate_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_packet_rate_limit_rules` DROP INDEX `ix_qos_packet_rate_limit_rules_qos_policy_id`; ALTER TABLE `neutron`.`qos_policies_default` DROP INDEX `ix_qos_policies_default_project_id`; ALTER TABLE `neutron`.`qospolicyrbacs` DROP INDEX `ix_qospolicyrbacs_target_project`; ALTER TABLE `neutron`.`quotas` DROP INDEX `ix_quotas_project_id`; ALTER TABLE `neutron`.`quotausages` DROP INDEX `ix_quotausages_project_id`; ALTER TABLE `neutron`.`securitygrouprbacs` DROP INDEX `ix_securitygrouprbacs_target_project`; ALTER TABLE `neutron`.`segmenthostmappings` DROP INDEX `ix_segmenthostmappings_segment_id`; ALTER TABLE `neutron`.`subnet_dns_publish_fixed_ips` DROP INDEX `ix_subnet_dns_publish_fixed_ips_subnet_id`; ALTER TABLE `neutron`.`subnetpoolrbacs` DROP INDEX `ix_subnetpoolrbacs_target_project`; ALTER TABLE `nova`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`; ALTER TABLE `nova`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`; ALTER TABLE `nova`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`; ALTER TABLE `nova`.`fixed_ips` DROP INDEX `address`; ALTER TABLE `nova`.`fixed_ips` DROP INDEX `network_id`; ALTER TABLE `nova`.`instance_actions` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`; ALTER TABLE `nova`.`instance_type_projects` DROP INDEX `instance_type_id`; ALTER TABLE `nova`.`instances` DROP INDEX `uniq_instances0uuid`; ALTER TABLE `nova`.`instances` DROP INDEX `instances_project_id_idx`; ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova`.`networks` DROP INDEX `networks_vlan_deleted_idx`; ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `nova_api`.`build_requests` DROP INDEX `build_requests_instance_uuid_idx`; ALTER TABLE `nova_api`.`cell_mappings` DROP INDEX `uuid_idx`; ALTER TABLE `nova_api`.`flavor_extra_specs` DROP INDEX `flavor_extra_specs_flavor_id_key_idx`; ALTER TABLE `nova_api`.`host_mappings` DROP INDEX `host_idx`; ALTER TABLE `nova_api`.`instance_mappings` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova_api`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova_api`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova_api`.`placement_aggregates` DROP INDEX `ix_placement_aggregates_uuid`; ALTER TABLE `nova_api`.`project_user_quotas` DROP INDEX `project_user_quotas_user_id_idx`; ALTER TABLE `nova_api`.`request_specs` DROP INDEX `request_spec_instance_uuid_idx`; ALTER TABLE `nova_api`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova_api`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `nova_cell0`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`; ALTER TABLE `nova_cell0`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`; ALTER TABLE `nova_cell0`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`; ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `address`; ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `network_id`; ALTER TABLE `nova_cell0`.`instance_actions` DROP INDEX `instance_uuid_idx`; ALTER TABLE `nova_cell0`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`; ALTER TABLE `nova_cell0`.`instance_type_projects` DROP INDEX `instance_type_id`; ALTER TABLE `nova_cell0`.`instances` DROP INDEX `uniq_instances0uuid`; ALTER TABLE `nova_cell0`.`instances` DROP INDEX `instances_project_id_idx`; ALTER TABLE `nova_cell0`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `nova_cell0`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `nova_cell0`.`networks` DROP INDEX `networks_vlan_deleted_idx`; ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`; ALTER TABLE `placement`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`; ALTER TABLE `placement`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`; ALTER TABLE `placement`.`placement_aggregates` DROP INDEX `ix_placement_aggregates_uuid`; ALTER TABLE `placement`.`resource_providers` DROP INDEX `resource_providers_name_idx`; ALTER TABLE `placement`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`;
# ######################################################################## # Summary of indexes # ########################################################################
# Size Duplicate Indexes 35810 # Total Duplicate Indexes 84 # Total Indexes 1792
(When adding more services (Telemetry, ...) there are even more indices reported.)
Does it make sense to address this more broadly maybe? Especially with all the changes happening for SQLAlchemy 2.x currently?
Regards
Christian
[1] https://mariadb.com/kb/en/mariadb-error-codes/ [2] https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html
participants (2)
-
Christian Rohmann
-
Lajos Katona