yahoo-eng-team team mailing list archive

[OpenStack Infra <2016704@xxxxxxxxxxxxxxxxxx>]

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

commit f23d7af8d7e36b9d52a8c8ccf64355e22f3adb9c
Author: Ihtisham ul Haq <ihtisham.ul_haq@mail.schwarz>
Date:   Mon Apr 17 12:23:30 2023 +0200

    Use explicit inner join for networks in port query
    
    This improves the performance of the database when fetching a list of ports
    for a project user. This change creates an inner join with the networks
    belonging to the ports.
    
    Previous SQL query:
    SELECT ports ...
    FROM network, ports ...
    WHERE ports.project_id = <project>
    OR ports.network_id = networks.id
    AND networks.project_id = <project>
    
    Current SQL query:
    SELECT ports ...
    FROM ports
    INNER JOIN networks ON networks.id = ports.network_id
    WHERE ports.project_id = <project>
    OR networks.project_id = <project>
    
    Closes-Bug: #2016704
    Change-Id: I9c49a307956ecfbf8bd2e866cefb21a212c38bd6


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

Title:
  Listing ports creates inefficient database query

Status in neutron:
  Fix Released

Bug description:
  Listing ports in a project with large number of ports results in
  querying the database which isn't efficient

  Steps to reproduce:
  1. Create 2000 ports in 20 different networks(the more the better) in the same project.
  2. Enable query log for mysql using `slow_query_log` and setting `long_query_time` to 0.1 seconds
  3. Now list the ports using `openstack port list` as non-admin user
  4. The following query should be logged in the `slow_query_log_file`: https://paste.opendev.org/show/819649/
  5. Here is part of the query that is relevant for the bug:
  ```
  SELECT DISTINCT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id
  FROM ports, networks
  WHERE ports.project_id = '<project-id>' OR ports.network_id = networks.id AND networks.project_id = '<project-id>' ORDER BY ports.id ASC
  ```

  6. Executing the above query responds in about 0.25 seconds <-- Which
  is the problem.

  7. Now update this query to use *explicit join* instead of implicit on:
  ```
  SELECT DISTINCT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id
  AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id
  FROM ports JOIN networks ON ports.network_id = networks.id
  WHERE ports.project_id = '<project-id>' AND networks.project_id = '<project-id>' ORDER BY ports.id ASC;
  ```

  8. The response time of this is around 0.02 seconds

  On our production environment(Yoga version) we see an extreme scenario
  of this, we have one project which has about 350 ports and 2 networks.
  And for that project, the query responds in 15 seconds, where as the
  optimized one responds in 0.01 seconds.

  In total we have about 1000 projects and most of the projects have
  about 10 ports.

  Version:
  - Openstack version, neutron from trunk. CommitID 28961c8b76a4b09412825231a3f69374b183aefd
  - Single node devstack on Ubuntu 22.04.02 LTS

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