Duplicate indexes in nova-db

[Andrew Garner]

Several nova db tables have duplicate indexes defined. This behavior was deprecated in MySQL 5.6 and presumably will fail on some future MySQL/MariaDB version. There are further some redundant indexes that overlap the left-most prefix over other indexes. The extra indexes have maintenance overhead that does become bothersome at larger scales and seems to be low hanging fruit.

The duplicate indexes are almost always caused by both flagging a field as unique and also specifying a UniqueConstraint in nova.db.sqlalchemy.models. As an example, there is nova.instances:

class Instance(BASE, NovaBase, models.SoftDeleteMixin):
...
        Index('uuid', 'uuid', unique=True),
...
        schema.UniqueConstraint('uuid', name='uniq_instances0uuid'),

Examples below are from a 14.0.2 MariaDB 10.0.27 database. I believe this also affects a Postgres backend.

Here are some obvious candidates that are essentially exact duplicates. Pulled using Percona's pt-duplicate-key-checker to make this a bit more obvious:

# ########################################################################
# nova.console_auth_tokens
# ########################################################################

# console_auth_tokens_token_hash_idx is a duplicate of uniq_console_auth_tokens0token_hash
# Key definitions:
# KEY `console_auth_tokens_token_hash_idx` (`token_hash`)
# UNIQUE KEY `uniq_console_auth_tokens0token_hash` (`token_hash`),
# Column types:
# `token_hash` varchar(255) not null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`console_auth_tokens` DROP INDEX `console_auth_tokens_token_hash_idx`;

# ########################################################################
# nova.instances
# ########################################################################

# Uniqueness of uniq_instances0uuid ignored because uuid is a duplicate constraint
# uniq_instances0uuid is a duplicate of uuid
# Key definitions:
# UNIQUE KEY `uniq_instances0uuid` (`uuid`),
# UNIQUE KEY `uuid` (`uuid`),
# Column types:
# `uuid` varchar(36) not null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`instances` DROP INDEX `uniq_instances0uuid`;

# ########################################################################
# nova.inventories
# ########################################################################

# inventories_resource_provider_resource_class_idx is a duplicate of uniq_inventories0resource_provider_resource_class
# Key definitions:
# KEY `inventories_resource_provider_resource_class_idx` (`resource_provider_id`,`resource_class_id`)
# UNIQUE KEY `uniq_inventories0resource_provider_resource_class` (`resource_provider_id`,`resource_class_id`),
# Column types:
# `resource_provider_id` int(11) not null
# `resource_class_id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_resource_class_idx`;

# ########################################################################
# nova.networks
# ########################################################################

# networks_vlan_deleted_idx is a duplicate of uniq_networks0vlan0deleted
# Key definitions:
# KEY `networks_vlan_deleted_idx` (`vlan`,`deleted`)
# UNIQUE KEY `uniq_networks0vlan0deleted` (`vlan`,`deleted`),
# Column types:
# `vlan` int(11) default null
# `deleted` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`networks` DROP INDEX `networks_vlan_deleted_idx`;

# ########################################################################
# nova.resource_providers
# ########################################################################

# resource_providers_name_idx is a duplicate of uniq_resource_providers0name
# Key definitions:
# KEY `resource_providers_name_idx` (`name`)
# UNIQUE KEY `uniq_resource_providers0name` (`name`),
# Column types:
# `name` varchar(200) character set utf8 default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_name_idx`;

# resource_providers_uuid_idx is a duplicate of uniq_resource_providers0uuid
# Key definitions:
# KEY `resource_providers_uuid_idx` (`uuid`),
# UNIQUE KEY `uniq_resource_providers0uuid` (`uuid`),
# Column types:
# `uuid` varchar(36) not null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`resource_providers` DROP INDEX `resource_providers_uuid_idx`;

And simply redundant indexes:

# ########################################################################
# nova.agent_builds
# ########################################################################

# agent_builds_hypervisor_os_arch_idx is a left-prefix of uniq_agent_builds0hypervisor0os0architecture0deleted
# Key definitions:
# KEY `agent_builds_hypervisor_os_arch_idx` (`hypervisor`,`os`,`architecture`)
# UNIQUE KEY `uniq_agent_builds0hypervisor0os0architecture0deleted` (`hypervisor`,`os`,`architecture`,`deleted`),
# Column types:
# `hypervisor` varchar(255) default null
# `os` varchar(255) default null
# `architecture` varchar(255) default null
# `deleted` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`agent_builds` DROP INDEX `agent_builds_hypervisor_os_arch_idx`;

# ########################################################################
# nova.block_device_mapping
# ########################################################################

# block_device_mapping_instance_uuid_idx is a left-prefix of block_device_mapping_instance_uuid_volume_id_idx
# Key definitions:
# KEY `block_device_mapping_instance_uuid_idx` (`instance_uuid`),
# KEY `block_device_mapping_instance_uuid_volume_id_idx` (`instance_uuid`,`volume_id`),
# Column types:
# `instance_uuid` varchar(36) default null
# `volume_id` varchar(36) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`block_device_mapping` DROP INDEX `block_device_mapping_instance_uuid_idx`;

# ########################################################################
# nova.fixed_ips
# ########################################################################

# address is a left-prefix of uniq_fixed_ips0address0deleted
# Key definitions:
# KEY `address` (`address`),
# UNIQUE KEY `uniq_fixed_ips0address0deleted` (`address`,`deleted`),
# Column types:
# `address` varchar(39) default null
# `deleted` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`fixed_ips` DROP INDEX `address`;

# network_id is a left-prefix of fixed_ips_network_id_host_deleted_idx
# Key definitions:
# KEY `network_id` (`network_id`),
# KEY `fixed_ips_network_id_host_deleted_idx` (`network_id`,`host`,`deleted`),
# Column types:
# `network_id` int(11) default null
# `host` varchar(255) default null
# `deleted` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`fixed_ips` DROP INDEX `network_id`;

# ########################################################################
# nova.instance_type_extra_specs
# ########################################################################

# instance_type_extra_specs_instance_type_id_key_idx is a left-prefix of uniq_instance_type_extra_specs0instance_type_id0key0deleted
# Key definitions:
# KEY `instance_type_extra_specs_instance_type_id_key_idx` (`instance_type_id`,`key`),
# UNIQUE KEY `uniq_instance_type_extra_specs0instance_type_id0key0deleted` (`instance_type_id`,`key`,`deleted`),
# Column types:
# `instance_type_id` int(11) not null
# `key` varchar(255) collate utf8_bin default null
# `deleted` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`instance_type_extra_specs` DROP INDEX `instance_type_extra_specs_instance_type_id_key_idx`;

# ########################################################################
# nova.instance_type_projects
# ########################################################################

# instance_type_id is a left-prefix of uniq_instance_type_projects0instance_type_id0project_id0deleted
# Key definitions:
# KEY `instance_type_id` (`instance_type_id`),
# UNIQUE KEY `uniq_instance_type_projects0instance_type_id0project_id0deleted` (`instance_type_id`,`project_id`,`deleted`),
# Column types:
# `instance_type_id` int(11) not null
# `project_id` varchar(255) default null
# `deleted` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`instance_type_projects` DROP INDEX `instance_type_id`;

# ########################################################################
# nova.inventories
# ########################################################################

# inventories_resource_provider_id_idx is a left-prefix of uniq_inventories0resource_provider_resource_class
# Key definitions:
# KEY `inventories_resource_provider_id_idx` (`resource_provider_id`),
# UNIQUE KEY `uniq_inventories0resource_provider_resource_class` (`resource_provider_id`,`resource_class_id`),
# Column types:
# `resource_provider_id` int(11) not null
# `resource_class_id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `nova`.`inventories` DROP INDEX `inventories_resource_provider_id_idx`;

[Christian Rohmann (christian-rohmann)]

The issue is still very present for nova and placement tables:

NOVA:
########

    Table: instances (https://opendev.org/openstack/nova/src/commit/c53ec4e48884235566962bc934cbf292ad5b67b8/nova/db/main/models.py#L269)
    Column: uuid
    Indexes:
        Unique Constraint: uniq_instances0uuid
        Index: uuid

    Table: console_auth_tokens (https://opendev.org/openstack/nova/src/commit/c53ec4e48884235566962bc934cbf292ad5b67b8/nova/db/main/models.py#L1043)
    Column: token_hash
    Indexes:
        Unique Constraint: uniq_console_auth_tokens0token_hash
        Index: console_auth_tokens_token_hash_idx

    Table: resource_providers (https://opendev.org/openstack/nova/src/commit/c53ec4e48884235566962bc934cbf292ad5b67b8/nova/db/main/migrations/versions/8f2f1571d55b_initial_version.py#L1299)
    Column: name
    Indexes:
        Unique Constraint: uniq_resource_providers0name
        Index: resource_providers_name_idx

    Table: resource_providers (https://opendev.org/openstack/nova/src/commit/c53ec4e48884235566962bc934cbf292ad5b67b8/nova/db/main/migrations/versions/8f2f1571d55b_initial_version.py#L1298)
    Column: uuid
    Indexes:
        Unique Constraint: uniq_resource_providers0uuid
        Index: resource_providers_uuid_idx

PLACEMENT:
############

    Placement
        Table: inventories (https://opendev.org/openstack/placement/src/commit/d723f225c194659961ce5adaeaaf0515497a48a8/placement/db/sqlalchemy/models.py#L85)
        Column: resource_provider_id, resource_class_id
        Indexes:
            Unique Constraint: uniq_inventories0resource_provider_resource_class
            Index: inventories_resource_provider_resource_class_idx

    Table: placement_aggregates (https://opendev.org/openstack/placement/src/commit/d723f225c194659961ce5adaeaaf0515497a48a8/placement/db/sqlalchemy/models.py#L150)
    Column: uuid
    Indexes:
        Unique Constraint: uniq_placement_aggregates0uuid
        Index: ix_placement_aggregates_uuid

    Table: resource_providers (https://opendev.org/openstack/placement/src/commit/d723f225c194659961ce5adaeaaf0515497a48a8/placement/db/sqlalchemy/models.py#L55)
    Column: name
    Indexes:
        Unique Constraint: uniq_resource_providers0name
        Index: resource_providers_name_idx

    Table: resource_providers (https://opendev.org/openstack/placement/src/commit/d723f225c194659961ce5adaeaaf0515497a48a8/placement/db/sqlalchemy/models.py#L53)
    Column: uuid
    Indexes:
        Unique Constraint: uniq_resource_providers0uuid
        Index: resource_providers_uuid_idx

[Christian Rohmann (christian-rohmann)]

I raised a story for this issue in Placement https://storyboard.openstack.org/#!/story/2010251

[Christian Rohmann (christian-rohmann)]

Apparently the issue with duplicate unique indexes is an old and unfortunately pending "cleanup" from https://opendev.org/openstack/nova/commit/c0ea53ce353684b48303fc59393930c3fa5ade58#diff-f48e61907df1c0a6f3ad32fb57f3b7f1ea8a8b2f to which mriedem commmented:

```
    # NOTE(mriedem): We now have a unique index on instances.uuid from the
    # 216_havana migration and a unique constraint on the same column, which
    # is redundant but should not be a big performance penalty. We should
    # clean this up in a later (separate) migration since it involves dropping
    # any ForeignKeys on the instances.uuid column due to some index rename
    # issues in older versions of MySQL. That is beyond the scope of this
    # migration.

```

I just tried to yolo a drop via

```
ALTER TABLE instances DROP INDEX uniq_instances0uuid;
```

and things just worked, no issues with any foreign key relations.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/nova/+/856757
Committed: https://opendev.org/openstack/nova/commit/531e1e8733b8928d262bf3b0b72540dde3ef1dad
Submitter: "Zuul (22348)"
Branch: master

commit 531e1e8733b8928d262bf3b0b72540dde3ef1dad
Author: Christian Rohmann <email address hidden>
Date: Fri Sep 9 13:56:20 2022 +0200

    db: Drop redundant indexes on instances and console_auth_tokens tables

     * There were two unique constrains on the same column uuid of instances.
       This change drops one of them. The second constraint was introduced with
       https://review.opendev.org/c/openstack/nova/+/97946, but was pending cleanup
       since.
     * In console_auth_tokens there was a unique constraint and another index on
       column token_hash.

    Closes-Bug: #1641185
    Change-Id: I0ffa47d2afbfbfa63651991b3791dfad3e1832e1

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 27.0.0.0rc1 release candidate.