Duplicate indexes in nova-db

Bug #1641185 reported by Andrew Garner on 2016-11-11
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Medium
ABHAY

Bug Description

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`;

Tags: db Edit Tag help
Matt Riedemann (mriedem) on 2016-11-12
tags: added: db
Changed in nova:
status: New → Confirmed
Andrew Garner (abg) on 2016-11-14
Changed in nova:
assignee: nobody → Andrew Garner (abg)
status: Confirmed → In Progress
Changed in nova:
importance: Undecided → Medium
Andrew Garner (abg) on 2017-01-11
Changed in nova:
assignee: Andrew Garner (abg) → nobody
Changed in nova:
status: In Progress → Confirmed
ABHAY (ankatare) on 2018-01-18
Changed in nova:
assignee: nobody → ABHAY (ankatare)
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers