yahoo-eng-team team mailing list archive

Thread
Date
[Bug 2071374] [NEW] Performance issue using network rbac rules

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Roberto Bartzen Acosta <2071374@xxxxxxxxxxxxxxxxxx>
Date: Thu, 27 Jun 2024 18:03:56 -0000
Reply-to: Bug 2071374 <2071374@xxxxxxxxxxxxxxxxxx>
Sender: noreply@xxxxxxxxxxxxx
Public bug reported:

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

** 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/2071374

Title:
  Performance issue using network rbac rules

Status in neutron:
  New

Bug description:
  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

To manage notifications about this bug go to:
https://bugs.launchpad.net/neutron/+bug/2071374/+subscriptions
Follow ups

[Bug 2071374] Re: Performance issue using network rbac rules
From: OpenStack Infra, 2024-07-05