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 -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : [email protected] Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
