← Back to team overview

yahoo-eng-team team mailing list archive

  1. yahoo-eng-team team
  2. Mailing list archive
  3. Message #58608

[Bug 1641185] [NEW] Duplicate indexes in nova-db

  Thread Previous • Date Previous • • Thread Next 
Public bug reported:

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

** Affects: nova
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1641185

Title:
  Duplicate indexes in nova-db

Status in OpenStack Compute (nova):
  New

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

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1641185/+subscriptions

Follow ups

  Thread Previous • Date Previous • • Thread Next