Hello everyone.
We noticed a strange behavior on Neutron when we added network rbacs. Basically, we create network rbacs for subnet pools, address-scope and network. So, after we include the network rbac rules, the SQL queries without filters on the subnet and network tables took minutes, as well as attaching gw router ports.
The network topologhy is basically like this:
- rbacs
[project SERVICE-1] address-scope rbac for [address-scope type-1]
[project SERVICE-1] subnetpool rbac for [subnet pool type-1]
- subnet
[project SERVICE-1] subnet using [subnet pool type-1] via project rbac
- inter projects network rbacs...
[project tenant-1] network rbac to (network X of [project SERVICE-1])
It's not so different from common usage, with the difference that we have thousands of projects associated with some types of address-scopes and subnet-pools.
openstack address scope list | grep -v "+" | wc -l
8
openstack subnet pool list | grep pool | wc -l
9
openstack subnet list | wc -l
5343
openstack network list | wc -l
6813
openstack router list | wc -l
3808
openstack network rbac list | wc -l
6770
openstack network rbac list | grep address_scope | wc -l
2254
openstack network rbac list | grep subnetpool | wc -l
2254
openstack network rbac list | grep network | wc -l
2258
openstack project list | wc -l
3804
We enabled the slow query log in mysql and started seeing huge queries like this:
# Time: 240627 15:11:37
# User@Host: neutron[neutron] @ srv-0001 [10.1.2.3]
# Thread_id: 353060 Schema: neutron QC_hit: No
# Query_time: 58.846345 Lock_time: 0.000528 Rows_sent: 583345 Rows_examined: 1750117
# Rows_affected: 0 Bytes_sent: 412285556
SET timestamp=1719501097;
SELECT subnets.project_id AS subnets_project_id, subnets.id AS subnets_id, subnets.in_use AS subnets_in_use, subnets.name AS subnets_name, subnets.network_id AS subnets_network_id, subnets.segment_id AS subnets_segment_id, subnets.subnetpool_id AS subnets_subnetpool_id, subnets.ip_version AS subnets_ip_version, subnets.cidr AS subnets_cidr, subnets.gateway_ip AS subnets_gateway_ip, subnets.enable_dhcp AS subnets_enable_dhcp, subnets.ipv6_ra_mode AS subnets_ipv6_ra_mode, subnets.ipv6_address_mode AS subnets_ipv6_address_mode, subnets.standard_attr_id AS subnets_standard_attr_id, subnetpools_1.shared AS subnetpools_1_shared, subnetpoolrbacs_1.project_id AS subnetpoolrbacs_1_project_id, subnetpoolrbacs_1.id AS subnetpoolrbacs_1_id, subnetpoolrbacs_1.target_project AS subnetpoolrbacs_1_target_project, subnetpoolrbacs_1.action AS subnetpoolrbacs_1_action, subnetpoolrbacs_1.object_id AS subnetpoolrbacs_1_object_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, tags_1.standard_attr_id AS tags_1_standard_attr_id, tags_1.tag AS tags_1_tag, subnetpools_1.project_id AS subnetpools_1_project_id, subnetpools_1.id AS subnetpools_1_id, subnetpools_1.name AS subnetpools_1_name, subnetpools_1.ip_version AS subnetpools_1_ip_version, subnetpools_1.default_prefixlen AS subnetpools_1_default_prefixlen, subnetpools_1.min_prefixlen AS subnetpools_1_min_prefixlen, subnetpools_1.max_prefixlen AS subnetpools_1_max_prefixlen, subnetpools_1.is_default AS subnetpools_1_is_default, subnetpools_1.default_quota AS subnetpools_1_default_quota, subnetpools_1.hash AS subnetpools_1_hash, subnetpools_1.address_scope_id AS subnetpools_1_address_scope_id, subnetpools_1.standard_attr_id AS subnetpools_1_standard_attr_id, networkrbacs_1.project_id AS networkrbacs_1_project_id, networkrbacs_1.id AS networkrbacs_1_id, networkrbacs_1.target_project AS networkrbacs_1_target_project, networkrbacs_1.action AS networkrbacs_1_action, networkrbacs_1.object_id AS networkrbacs_1_object_id, 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, tags_2.standard_attr_id AS tags_2_standard_attr_id, tags_2.tag AS tags_2_tag, subnet_dns_publish_fixed_ips_1.subnet_id AS subnet_dns_publish_fixed_ips_1_subnet_id, subnet_dns_publish_fixed_ips_1.dns_publish_fixed_ip AS subnet_dns_publish_fixed_ips_1_dns_publish_fixed_ip
FROM subnets LEFT OUTER JOIN subnetpools AS subnetpools_1 ON subnets.subnetpool_id = subnetpools_1.id LEFT OUTER JOIN subnetpoolrbacs AS subnetpoolrbacs_1 ON subnetpools_1.id = subnetpoolrbacs_1.object_id LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = subnetpools_1.standard_attr_id LEFT OUTER JOIN tags AS tags_1 ON standardattributes_1.id = tags_1.standard_attr_id LEFT OUTER JOIN networkrbacs AS networkrbacs_1 ON subnets.network_id = networkrbacs_1.object_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = subnets.standard_attr_id LEFT OUTER JOIN tags AS tags_2 ON standardattributes_2.id = tags_2.standard_attr_id LEFT OUTER JOIN subnet_dns_publish_fixed_ips AS subnet_dns_publish_fixed_ips_1 ON subnets.id = subnet_dns_publish_fixed_ips_1.subnet_id ORDER BY subnets.id ASC, subnets.standard_attr_id ASC;
Executing this query in mysql cli the result takes approximately 10 seconds:
$ time mysql --database neutron -e "SELECT subnets.project_id AS subnets_project_id, subnets.id AS subnets_id, subnets.in_use AS subnets_in_use, subnets.name AS subnets_name, subnets.network_id AS subnets_network_id, subnets.segment_id AS subnets_segment_id, subnets.subnetpool_id AS subnets_subnetpool_id, subnets.ip_version AS subnets_ip_version, subnets.cidr AS subnets_cidr, subnets.gateway_ip AS subnets_gateway_ip, subnets.enable_dhcp AS subnets_enable_dhcp, subnets.ipv6_ra_mode AS subnets_ipv6_ra_mode, subnets.ipv6_address_mode AS subnets_ipv6_address_mode, subnets.standard_attr_id AS subnets_standard_attr_id, subnetpools_1.shared AS subnetpools_1_shared, subnetpoolrbacs_1.project_id AS subnetpoolrbacs_1_project_id, subnetpoolrbacs_1.id AS subnetpoolrbacs_1_id, subnetpoolrbacs_1.target_project AS subnetpoolrbacs_1_target_project, subnetpoolrbacs_1.action AS subnetpoolrbacs_1_action, subnetpoolrbacs_1.object_id AS subnetpoolrbacs_1_object_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, subnetpools_1.project_id AS subnetpools_1_project_id, subnetpools_1.id AS subnetpools_1_id, subnetpools_1.name AS subnetpools_1_name, subnetpools_1.ip_version AS subnetpools_1_ip_version, subnetpools_1.default_prefixlen AS subnetpools_1_default_prefixlen, subnetpools_1.min_prefixlen AS subnetpools_1_min_prefixlen, subnetpools_1.max_prefixlen AS subnetpools_1_max_prefixlen, subnetpools_1.is_default AS subnetpools_1_is_default, subnetpools_1.default_quota AS subnetpools_1_default_quota, subnetpools_1.hash AS subnetpools_1_hash, subnetpools_1.address_scope_id AS subnetpools_1_address_scope_id, subnetpools_1.standard_attr_id AS subnetpools_1_standard_attr_id, networkrbacs_1.project_id AS networkrbacs_1_project_id, networkrbacs_1.id AS networkrbacs_1_id, networkrbacs_1.target_project AS networkrbacs_1_target_project, networkrbacs_1.action AS networkrbacs_1_action, networkrbacs_1.object_id AS networkrbacs_1_object_id, 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, subnet_dns_publish_fixed_ips_1.subnet_id AS subnet_dns_publish_fixed_ips_1_subnet_id, subnet_dns_publish_fixed_ips_1.dns_publish_fixed_ip AS subnet_dns_publish_fixed_ips_1_dns_publish_fixed_ip FROM subnets LEFT OUTER JOIN subnetpools AS subnetpools_1 ON subnets.subnetpool_id = subnetpools_1.id LEFT OUTER JOIN subnetpoolrbacs AS subnetpoolrbacs_1 ON subnetpools_1.id = subnetpoolrbacs_1.object_id LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = subnetpools_1.standard_attr_id LEFT OUTER JOIN networkrbacs AS networkrbacs_1 ON subnets.network_id = networkrbacs_1.object_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = subnets.standard_attr_id LEFT OUTER JOIN subnet_dns_publish_fixed_ips AS subnet_dns_publish_fixed_ips_1 ON subnets.id = subnet_dns_publish_fixed_ips_1.subnet_id ORDER BY subnets.id ASC, subnets.standard_attr_id ASC;" > /tmp/output
real 0m8.150s
user 0m4.325s
sys 0m1.381s
But, the end result is a series of "repeated subnet name values" generated by the super query that orm created... with almost 600k lines.
cat /tmp/output | wc -l
583346
This seems like a classic case of “cartesian” issue since we are using joined inside joined for (subnets -> subnetpool -> rbac_rules).
From the user's perspective this is very bad, as it takes minutes to return a list of networks or subnets.
$ time openstack network list | wc -l
6813
real 2m17.439s
user 0m6.258s
sys 0m0.140s
time openstack subnet list | wc -l
5343
real 1m51.134s
user 0m4.932s
sys 0m0.116s
My proposal is to replace the joined relationship for rbac_entries inside SubnetPool DB model, because we don't need a many-to-many relationship here in my point of view. So, we can use the selectin eager loading to make this relationship one-to-many and create the model with only the necessary steps, without exploding into a cartesian product caused by the "left outer join" which is unnecessary in this model.
The "total" queries of this process would be splited into a series of smaller queries (slectin design) with much better performance, and the huge resulting query would be reduced to something like this:
$ time mysql --database neutron -e "SELECT subnets.project_id AS subnets_project_id, subnets.id AS subnets_id, subnets.in_use AS subnets_in_use, subnets.name AS subnets_name, subnets.network_id AS subnets_network_id, subnets.segment_id AS subnets_segment_id, subnets.