yahoo-eng-team team mailing list archive

[Max <2121465@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

Description
===========

In our environment we observed similar, recurring and long runnning queries to the migration table
e.g.: [1]

This query gets executed by the _cleanup_incomplete_migrations periodic task
(per default executed every 5 minutes by every compute node).

After exploring the query we noticed that we need 2 additional indexes:
(source_compute AND source_node) OR (dest_compute AND dest_node)

Steps to reproduce
==================
1. create many migration entries
2. execute such query [1]
3. check the response time or do a EXPLAIN on the query -> no index used

Expected result
===============
fast queries

Actual result
=============
slow queries > 0.5sec


Environment
===========
2023.1 - but this should also trigger on master

500k migration table entries
1.8k compute nodes

References:
[1] https://paste.openstack.org/show/bMjLpPGxxl78UQTZVLt1/

** Affects: nova
     Importance: Undecided
         Status: In Progress

-- 
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/2121465

Title:
  db: missing index for per host migrations

Status in OpenStack Compute (nova):
  In Progress

Bug description:
  Description
  ===========

  In our environment we observed similar, recurring and long runnning queries to the migration table
  e.g.: [1]

  This query gets executed by the _cleanup_incomplete_migrations periodic task
  (per default executed every 5 minutes by every compute node).

  After exploring the query we noticed that we need 2 additional indexes:
  (source_compute AND source_node) OR (dest_compute AND dest_node)

  Steps to reproduce
  ==================
  1. create many migration entries
  2. execute such query [1]
  3. check the response time or do a EXPLAIN on the query -> no index used

  Expected result
  ===============
  fast queries

  Actual result
  =============
  slow queries > 0.5sec

  
  Environment
  ===========
  2023.1 - but this should also trigger on master

  500k migration table entries
  1.8k compute nodes

  References:
  [1] https://paste.openstack.org/show/bMjLpPGxxl78UQTZVLt1/

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