Re: [all][db] Lots of redundant DB indices ?

16 Jun 2023

      (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

Re: [all][db] Lots of redundant DB indices ?

Christian Rohmann