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 <[email protected]> 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 -- 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
