Public bug reported:
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:~$
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
** Affects: neutron
Importance: Undecided
Status: New
--
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:
New
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:~$
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
--
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
