yahoo-eng-team team mailing list archive

[OpenStack Infra <1574750@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://review.openstack.org/310049
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=3fb07b662930889b52e9ed0bc5a5616212aca46c
Submitter: Jenkins
Branch:    master

commit 3fb07b662930889b52e9ed0bc5a5616212aca46c
Author: Ilya Chukhnakov <ichukhnakov@xxxxxxxxxxxx>
Date:   Mon Apr 25 22:16:54 2016 +0300

    Add device_id index to Port
    
    Some 'Port' queries use 'device_id' column for lookup.
    Such queries could be observed in database query log (at least) during
    instance launch. In the absence of 'device_id' index that leads to full
    table scan. That causes unnecessary database load and impacts query
    response time.
    
    Change-Id: If42b7d3265e216d393d3ab8c172b97637af908cc
    Closes-Bug: #1574750


** Changed in: neutron
       Status: In Progress => Fix Released

-- 
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:
  Fix Released

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