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