yahoo-eng-team team mailing list archive

[Ihtisham ul Haq <2016704@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.2 seconds

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

** Affects: neutron
     Importance: Undecided
         Status: New

** Description changed:

  Listing ports in a project with large number of ports results in
  querying the database which isn't efficient
  
- 
  Steps to reproduce:
- - Create 2000 ports in 20 different networks(the more the better) in the same project.
- - Enable query log for mysql using `slow_query_log` and setting `long_query_time` to 0.1 seconds
- - Now list the ports using `openstack port list` as non-admin user
- - The following query should be logged:
- 
+ 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: https://paste.opendev.org/show/819649/
+ 5. Here is part of the query that is relevant for the bug:
  ```
- SELECT anon_1.ports_project_id AS anon_1_ports_project_id, anon_1.ports_id AS anon_1_ports_id, anon_1.ports_name AS anon_1_ports_name, anon_1.ports_network_id AS anon_1_ports_network_id, anon_1.ports_mac_address AS anon_1_ports_mac_address, anon_1.ports_admin_state_up AS anon_1_ports_admin_state_up, anon_1.ports_status AS anon_1_ports_status, anon_1.ports_device_id AS anon_1_ports_device_id, anon_1.ports_device_owner AS anon_1_ports_device_owner, anon_1.ports_ip_allocation AS anon_1_ports_ip_allocation, anon_1.ports_standard_attr_id AS anon_1_ports_standard_attr_id, standardattributes_1.id AS standardattributes_1_id, standardattributes_1.resource_type AS standardattributes_1_resource_type, standardattributes_1.description AS standardattributes_1_description, standardattributes_1.revision_number AS standardattributes_1_revision_number, standardattributes_1.created_at AS standardattributes_1_created_at, standardattributes_1.updated_at AS standardattributes_1_updated_at, securitygroupportbindings_1.port_id AS securitygroupportbindings_1_port_id, securitygroupportbindings_1.security_group_id AS securitygroupportbindings_1_security_group_id, portdnses_1.port_id AS portdnses_1_port_id, portdnses_1.current_dns_name AS portdnses_1_current_dns_name, portdnses_1.current_dns_domain AS portdnses_1_current_dns_domain, portdnses_1.previous_dns_name AS portdnses_1_previous_dns_name, portdnses_1.previous_dns_domain AS portdnses_1_previous_dns_domain, portdnses_1.dns_name AS portdnses_1_dns_name, portdnses_1.dns_domain AS portdnses_1_dns_domain, qos_network_policy_bindings_1.policy_id AS qos_network_policy_bindings_1_policy_id, qos_network_policy_bindings_1.network_id AS qos_network_policy_bindings_1_network_id, qos_port_policy_bindings_1.policy_id AS qos_port_policy_bindings_1_policy_id, qos_port_policy_bindings_1.port_id AS qos_port_policy_bindings_1_port_id, ml2_port_bindings_1.port_id AS ml2_port_bindings_1_port_id, ml2_port_bindings_1.host AS ml2_port_bindings_1_host, ml2_port_bindings_1.vnic_type AS ml2_port_bindings_1_vnic_type, ml2_port_bindings_1.profile AS ml2_port_bindings_1_profile, ml2_port_bindings_1.vif_type AS ml2_port_bindings_1_vif_type, ml2_port_bindings_1.vif_details AS ml2_port_bindings_1_vif_details, ml2_port_bindings_1.status AS ml2_port_bindings_1_status, portsecuritybindings_1.port_id AS portsecuritybindings_1_port_id, portsecuritybindings_1.port_security_enabled AS portsecuritybindings_1_port_security_enabled, standardattributes_2.id AS standardattributes_2_id, standardattributes_2.resource_type AS standardattributes_2_resource_type, standardattributes_2.description AS standardattributes_2_description, standardattributes_2.revision_number AS standardattributes_2_revision_number, standardattributes_2.created_at AS standardattributes_2_created_at, standardattributes_2.updated_at AS standardattributes_2_updated_at, trunks_1.project_id AS trunks_1_project_id, trunks_1.id AS trunks_1_id, trunks_1.admin_state_up AS trunks_1_admin_state_up, trunks_1.name AS trunks_1_name, trunks_1.port_id AS trunks_1_port_id, trunks_1.status AS trunks_1_status, trunks_1.standard_attr_id AS trunks_1_standard_attr_id, subports_1.port_id AS subports_1_port_id, subports_1.trunk_id AS subports_1_trunk_id, subports_1.segmentation_type AS subports_1_segmentation_type, subports_1.segmentation_id AS subports_1_segmentation_id, portdataplanestatuses_1.port_id AS portdataplanestatuses_1_port_id, portdataplanestatuses_1.data_plane_status AS portdataplanestatuses_1_data_plane_status, portuplinkstatuspropagation_1.port_id AS portuplinkstatuspropagation_1_port_id, portuplinkstatuspropagation_1.propagate_uplink_status AS portuplinkstatuspropagation_1_propagate_uplink_status, portnumaaffinitypolicies_1.port_id AS portnumaaffinitypolicies_1_port_id, portnumaaffinitypolicies_1.numa_affinity_policy AS portnumaaffinitypolicies_1_numa_affinity_policy, portdeviceprofiles_1.port_id AS portdeviceprofiles_1_port_id, portdeviceprofiles_1.device_profile AS portdeviceprofiles_1_device_profile 
- FROM (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) AS anon_1 LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = anon_1.ports_standard_attr_id LEFT OUTER JOIN securitygroupportbindings AS securitygroupportbindings_1 ON anon_1.ports_id = securitygroupportbindings_1.port_id LEFT OUTER JOIN portdnses AS portdnses_1 ON anon_1.ports_id = portdnses_1.port_id LEFT OUTER JOIN qos_network_policy_bindings AS qos_network_policy_bindings_1 ON qos_network_policy_bindings_1.network_id = anon_1.ports_network_id LEFT OUTER JOIN qos_port_policy_bindings AS qos_port_policy_bindings_1 ON anon_1.ports_id = qos_port_policy_bindings_1.port_id LEFT OUTER JOIN ml2_port_bindings AS ml2_port_bindings_1 ON anon_1.ports_id = ml2_port_bindings_1.port_id LEFT OUTER JOIN portsecuritybindings AS portsecuritybindings_1 ON anon_1.ports_id = portsecuritybindings_1.port_id LEFT OUTER JOIN trunks AS trunks_1 ON anon_1.ports_id = trunks_1.port_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = trunks_1.standard_attr_id LEFT OUTER JOIN subports AS subports_1 ON anon_1.ports_id = subports_1.port_id LEFT OUTER JOIN portdataplanestatuses AS portdataplanestatuses_1 ON anon_1.ports_id = portdataplanestatuses_1.port_id LEFT OUTER JOIN portuplinkstatuspropagation AS portuplinkstatuspropagation_1 ON anon_1.ports_id = portuplinkstatuspropagation_1.port_id LEFT OUTER JOIN portnumaaffinitypolicies AS portnumaaffinitypolicies_1 ON anon_1.ports_id = portnumaaffinitypolicies_1.port_id LEFT OUTER JOIN portdeviceprofiles AS portdeviceprofiles_1 ON anon_1.ports_id = portdeviceprofiles_1.port_id ORDER BY anon_1.ports_id ASC;
- ```
- 
- - Part of the query relevant for the bug is:
- ```
- 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 
+ 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
  ```
  
- - Executing this query responds in about 0.25 seconds <-- Which is the
- problem.
+ 6. Executing the above query responds in about 0.25 seconds <-- Which is
+ the problem.
  
- - Now update this query to use *explicit join* instead of implicit on:
+ 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  
+ 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;
  ```
  
- - The response time of this is around 0.2 seconds
+ 8. The response time of this is around 0.2 seconds
  
  
  Version:
  - Openstack version, neutron from trunk. CommitID 28961c8b76a4b09412825231a3f69374b183aefd
  - Single node devstack on Ubuntu 22.04.02 LTS

** Description changed:

  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: https://paste.opendev.org/show/819649/
+ 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.2 seconds
  
- 
  Version:
  - Openstack version, neutron from trunk. CommitID 28961c8b76a4b09412825231a3f69374b183aefd
  - Single node devstack on Ubuntu 22.04.02 LTS

-- 
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:
  New

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.2 seconds

  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