Open Stack

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 at 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
>
>
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.openstack.org/pipermail/openstack-discuss/attachments/20230616/921314aa/attachment.htm>