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, securitygroup portbindings_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, ml 2_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_sta te_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 -- 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
