yahoo-eng-team team mailing list archive

[Ilya Chukhnakov <ichukhnakov@xxxxxxxxxxxx>]

Public bug reported:

Current Neutron database model does not define an index for Port.device_id column. However observing the MySQL query log one could notice queries that would benefit from such an index:
# sed -n "/WHERE.*device_id/s/'[^']*'/<UUID>/gp" < /var/lib/mysql/$DB_HOSTNAME.log|sort|uniq -c
     34 WHERE ports.device_id IN (<UUID>)
     78 WHERE ports.tenant_id IN (<UUID>) AND ports.device_id IN (<UUID>)

Without that index the database is currently forced to use the full scan
table access path (or potentially less selective 'tenant_id' index for
the second query) which has suboptimal performance.

Pre-conditions: Devstack (master) configured with Neutron networking
(from Devstack guide
http://docs.openstack.org/developer/devstack/guides/neutron.html
#devstack-configuration).
Neutron@master:91d95197d892356bd1ab8a96966c11e97d78441b

Steps to reproduce:
0. enable MySQL query logging unless already enabled (set global general_log = 'ON')
1. launch new instance
2. observe MySQL log file for queries having ports.device_id in WHERE clause
3. run EXPLAIN query plan for such queries and observe the full scan table access path for 'ports' table

** Affects: neutron
     Importance: Medium
     Assignee: Ilya Chukhnakov (ichukhnakov)
         Status: In Progress

** Changed in: neutron
     Assignee: (unassigned) => Ilya Chukhnakov (ichukhnakov)

** Changed in: neutron
       Status: New => In Progress

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to neutron.
https://bugs.launchpad.net/bugs/1574750

Title:
  Full table scan on "ports" table lookup by "device_id"

Status in neutron:
  In Progress

Bug description:
  Current Neutron database model does not define an index for Port.device_id column. However observing the MySQL query log one could notice queries that would benefit from such an index:
  # sed -n "/WHERE.*device_id/s/'[^']*'/<UUID>/gp" < /var/lib/mysql/$DB_HOSTNAME.log|sort|uniq -c
       34 WHERE ports.device_id IN (<UUID>)
       78 WHERE ports.tenant_id IN (<UUID>) AND ports.device_id IN (<UUID>)

  Without that index the database is currently forced to use the full
  scan table access path (or potentially less selective 'tenant_id'
  index for the second query) which has suboptimal performance.

  Pre-conditions: Devstack (master) configured with Neutron networking
  (from Devstack guide
  http://docs.openstack.org/developer/devstack/guides/neutron.html
  #devstack-configuration).
  Neutron@master:91d95197d892356bd1ab8a96966c11e97d78441b

  Steps to reproduce:
  0. enable MySQL query logging unless already enabled (set global general_log = 'ON')
  1. launch new instance
  2. observe MySQL log file for queries having ports.device_id in WHERE clause
  3. run EXPLAIN query plan for such queries and observe the full scan table access path for 'ports' table

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