yahoo-eng-team team mailing list archive

[OpenStack Infra <1973349@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://review.opendev.org/c/openstack/neutron/+/841761
Committed: https://opendev.org/openstack/neutron/commit/db2ae854cf65b59eb0f7b0eef1b20e404c2214cb
Submitter: "Zuul (22348)"
Branch:    master

commit db2ae854cf65b59eb0f7b0eef1b20e404c2214cb
Author: Rodolfo Alonso Hernandez <ralonsoh@xxxxxxxxxx>
Date:   Thu May 12 13:08:48 2022 +0000

    Create an index for "ports.network_id"
    
    The method ``_port_filter_hook``, that is added in any "Port" SELECT
    command, filters the database "Port" registers by "network_id", using
    an exact match. This query speed will improve if this column is
    indexed in the database engine.
    
    Closes-Bug: #1973349
    Change-Id: Ia20f96dc78ea04bb0ab4665e6d47a6365789d2c9


** 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/1973349

Title:
  Slow queries after upgrade to Xena

Status in neutron:
  Fix Released

Bug description:
  After upgrading to Xena we started noticing slow queries that were written down in mysql slow log.
  Most of them were including next subquery:
  SELECT DISTINCT ports.id AS ports_id FROM ports, networks WHERE ports.project_id = '<project>' OR ports.network_id = networks.id AND networks.project_id = '<project>'.

  So for example, when issuing `openstack project list` this subquery appears several times:
  ```
  SELECT allowedaddresspairs.port_id AS allowedaddresspairs_port_id, allowedaddresspairs.mac_address AS allowedaddresspairs_mac_address, allowedaddresspairs.ip_address AS allowedaddresspairs_ip_address, anon_1.ports_id AS anon_1_ports_id \nFROM (SELECT DISTINCT ports.id AS ports_id \nFROM ports, networks \nWHERE ports.project_id = '<project>' OR ports.network_id = networks.id AND networks.project_id = '<project>') AS anon_1 INNER JOIN allowedaddresspairs ON anon_1.ports_id = allowedaddresspairs.port_id

  SELECT extradhcpopts.id AS extradhcpopts_id, extradhcpopts.port_id AS
  extradhcpopts_port_id, extradhcpopts.opt_name AS
  extradhcpopts_opt_name, extradhcpopts.opt_value AS
  extradhcpopts_opt_value, extradhcpopts.ip_version AS
  extradhcpopts_ip_version, anon_1.ports_id AS anon_1_ports_id \nFROM
  (SELECT DISTINCT ports.id AS ports_id \nFROM ports, networks \nWHERE
  ports.project_id = '<project>' OR ports.network_id = networks.id AND
  networks.project_id = '<project>') AS anon_1 INNER JOIN extradhcpopts
  ON anon_1.ports_id = extradhcpopts.port_id    0.000

  SELECT ipallocations.port_id AS ipallocations_port_id, ipallocations.ip_address AS ipallocations_ip_address, ipallocations.subnet_id AS ipallocations_subnet_id, ipallocations.network_id AS ipallocations_network_id, anon_1.ports_id AS anon_1_ports_id \nFROM (SELECT DISTINCT ports.id AS ports_id \nFROM ports, networks \nWHERE ports.project_id = '<project>' OR ports.network_id = networks.id AND networks.project_id = '<project>') AS anon_1 INNER JOIN ipallocations ON anon_1.ports_id = ipallocations.port_id ORDER BY ipallocations.ip_address, ipallocations.subnet_id
  ```

  Another interesting thing is difference in execution time between admin/non-admin call:
  (openstack) dmitriy@6BT6XT2:~$ . Documents/openrc/admin.rc
  (openstack) dmitriy@6BT6XT2:~$ time openstack port list --project <project> | wc -l
  2142

  real    0m5,401s
  user    0m1,565s
  sys     0m0,086s
  (openstack) dmitriy@6BT6XT2:~$ . Documents/openrc/<project>.rc
  (openstack) dmitriy@6BT6XT2:~$ time openstack port list | wc -l
  2142

  real    2m38,101s
  user    0m1,626s
  sys     0m0,083s
  (openstack) dmitriy@6BT6XT2:~$
  (openstack) dmitriy@6BT6XT2:~$ time openstack port list --project <project> | wc -l
  2142

  real    1m17,029s
  user    0m1,541s
  sys     0m0,085s
  (openstack) dmitriy@6BT6XT2:~$

  So basically if provide tenant_id to query, it will be execute twice
  as fast.But it won't look through networks owned by tenant (which
  would kind of explain difference in speed).

  Environment:
  Neutron SHA: 97180b01837638bd0476c28bdda2340eccd649af
  Backend: ovs
  OS: Ubuntu 20.04
  Mariadb: 10.6.5
  SQLalchemy: 1.4.23
  Backend: openvswitch
  Plugins: router vpnaas metering neutron_dynamic_routing.services.bgp.bgp_plugin.BgpPlugin

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