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
--
Mailing list: https://launchpad.net/~yahoo-eng-team
Post to : yahoo-eng-team@lists.launchpad.net
Unsubscribe : https://launchpad.net/~yahoo-eng-team
More help : https://help.launchpad.net/ListHelp
