Displaying a page of a Prefix takes 20 min

[reduzent]

Showing the page of our Prefix with the most child prefixes and containing the most IP addresses takes ~ 20 min in the browser.

This is the URL (internal only):

https://netbox.myorg.org/ipam/prefixes/13/

Child prefixes: 1851
Child IP Adresses: 4524
Child Ranges: 3513

Querying the object with the nbshell doesn't exhibit any noticeable lag. Assuming other institutions may maintain even larger networks in netbox, there is probably something odd with our setup. Any hints for investigating this further are much appreciated.

[reduzent]

BTW: This is on Python 3.9.2 | Django 4.1.10 | NetBox 3.5.6 | Debian 11

[candlerb]

I think we need to look at what SQL queries are being issued to the database. It's possible that while counting the child objects, Netbox is either querying all objects, or generating a separate query per object.

Doing this is very easy. Set DEBUG=True in configuration.py, then restart. This enables a debug tool button on the top-right corner, which you can click to expand.

For example, on my system (3.5.7), if I go to /ipam/prefixes/17/ which is an outer container prefix, I see tabs for Child Prefixes (11), Child Ranges (6), IP Addresses (73). The Debug panel tells me "29 queries in 71.42ms"

Debug mode does drastically slow down the system, so your 20 minutes could become longer again - so you might want to start with a tighter prefix.

Now, at a quick glance, it does look pretty broken to me. I was expecting to see select count(*) ... queries only, but I can see it's also iterating over all the child prefixes, IP addresses and ranges:

SELECT "ipam_prefix"."id",
       "ipam_prefix"."created",
       "ipam_prefix"."last_updated",
       "ipam_prefix"."custom_field_data",
       "ipam_prefix"."description",
       "ipam_prefix"."comments",
       "ipam_prefix"."prefix",
       "ipam_prefix"."site_id",
       "ipam_prefix"."vrf_id",
       "ipam_prefix"."tenant_id",
       "ipam_prefix"."vlan_id",
       "ipam_prefix"."status",
       "ipam_prefix"."role_id",
       "ipam_prefix"."is_pool",
       "ipam_prefix"."mark_utilized",
       "ipam_prefix"."_depth",
       "ipam_prefix"."_children"
  FROM "ipam_prefix"
 WHERE ("ipam_prefix"."prefix" << '10.12.0.0/16' AND "ipam_prefix"."vrf_id" IS NULL)
 ORDER BY "ipam_prefix"."vrf_id" ASC NULLS FIRST,
       "ipam_prefix"."prefix" ASC,
       "ipam_prefix"."id" ASC
...

SELECT "ipam_ipaddress"."id",
       "ipam_ipaddress"."created",
       "ipam_ipaddress"."last_updated",
       "ipam_ipaddress"."custom_field_data",
       "ipam_ipaddress"."description",
       "ipam_ipaddress"."comments",
       "ipam_ipaddress"."address",
       "ipam_ipaddress"."vrf_id",
       "ipam_ipaddress"."tenant_id",
       "ipam_ipaddress"."status",
       "ipam_ipaddress"."role",
       "ipam_ipaddress"."assigned_object_type_id",
       "ipam_ipaddress"."assigned_object_id",
       "ipam_ipaddress"."nat_inside_id",
       "ipam_ipaddress"."dns_name"
  FROM "ipam_ipaddress"
 WHERE CAST(HOST("ipam_ipaddress"."address") AS INET) <<= '10.12.0.0/16'
 ORDER BY INET(HOST("ipam_ipaddress"."address")) ASC 2 similar queries.  Duplicated 2 times.

SELECT "ipam_iprange"."id",
       "ipam_iprange"."created",
       "ipam_iprange"."last_updated",
       "ipam_iprange"."custom_field_data",
       "ipam_iprange"."description",
       "ipam_iprange"."comments",
       "ipam_iprange"."start_address",
       "ipam_iprange"."end_address",
       "ipam_iprange"."size",
       "ipam_iprange"."vrf_id",
       "ipam_iprange"."tenant_id",
       "ipam_iprange"."status",
       "ipam_iprange"."role_id",
       "ipam_iprange"."mark_utilized"
  FROM "ipam_iprange"
 WHERE (CAST(HOST("ipam_iprange"."end_address") AS INET) <<= '10.12.0.0/16' AND CAST(HOST("ipam_iprange"."start_address") AS INET) <<= '10.12.0.0/16' AND "ipam_iprange"."vrf_id" IS NULL)
 ORDER BY "ipam_iprange"."vrf_id" ASC NULLS FIRST,
       "ipam_iprange"."start_address" ASC,
       "ipam_iprange"."id" ASC 2 similar queries.  Duplicated 2 times.

There are no "LIMIT" clauses there, so it clearly is iterating over them all.

The backtrace for the child IP addresses is interesting. It shows a template expansion:

114 | </td>
115 | </tr>
116 | {% endwith %}
117 | {% with available_count=object.get_available_ips.size %}    <<< HIGHLIGHTED
118 | <tr>
119 | <th scope="row">Available IPs</th>
120 | <td>
121 | {# Use human-friendly words for counts greater than one million #}
**/opt/netbox/netbox/templates/ipam/prefix.html**

and

/opt/netbox/netbox/ipam/models/ip.py in get_available_ips(412)
  child_ips = netaddr.IPSet([ip.address.ip for ip in self.get_child_ips()])

I think it's doing an incredibly expensive calculation, just to work out the number of available IP addresses to show the prefix utilization: it has to query all IPs within the prefix.

Similarly, the query for all child prefixes:

102 | {% utilization_graph 100 warning_threshold=0 danger_threshold=0 %}
103 | <small>(Marked fully utilized)</small>
104 | {% else %}
105 | {% utilization_graph object.get_utilization %}    <<< HIGHLIGHTED
106 | {% endif %}
107 | </td>
108 | </tr>
109 | {% with child_ip_count=object.get_child_ips.count %}
**/opt/netbox/netbox/templates/ipam/prefix.html**

/opt/netbox/netbox/ipam/models/ip.py in get_utilization(456)
  child_prefixes = netaddr.IPSet([p.prefix for p in queryset])

And for child ranges:

114 | </td>
115 | </tr>
116 | {% endwith %}
117 | {% with available_count=object.get_available_ips.size %}    <<< HIGHLIGHTED
118 | <tr>
119 | <th scope="row">Available IPs</th>
120 | <td>
121 | {# Use human-friendly words for counts greater than one million #}

/opt/netbox/netbox/ipam/models/ip.py in get_available_ips(414)
  for iprange in self.get_child_ranges():

So your answer is: there's nothing odd with your setup, it's just that Netbox isn't optimised for large networks (and the use of an ORM is hiding the expensive queries it does in the background).

[reduzent]

Thanks a lot for your very detailed explanation.

Does that mean the experienced behavior is related to the fundamental system architecture of netbox and cannot easily addressed?

[candlerb]

Anything is possible in software, with enough time and energy.

One option is to replace get_available_ips.size with a new method, say get_available_ips_count, which pushes the counting down into the SQL query; the database will be able to do this efficiently if it has a suitable index. get_utilization would need changing in a similar way.

Another option is to cache the utilization counts on prefix objects, although that involves one of the Two Hard Problems in computer science.

[zombah]

Hello,
This is possible if you have multiple permission items per user, i don't know is this problem still actual, i had it long ago, then switched to single permission per user and voila performance of api and webui rendering increased.
What i mean exacly:

user xyz
  ipam-permission - ipam/can view
  dcim-permission - dcim/can view
  ...

slow variant.

user xyz
  user-xyz-permission - ipam/can view
                        dcim/can view
                        ...

fast variant.

[candlerb]

Faster when rendering which page, exactly?

The OP doesn't have a problem with the prefix listing page (where you can also limit the number of prefixes shown), but on the page which shows a single prefix.

I would expect prefix utilization to count all the child objects within the prefix, whether or not you have view permissions on the child objects - that is, the permissions aren't evaluated at all.

[reduzent]

@candlerb Do you think it is worthwile open an issue about that? My gut tells me that we are not a particularly large organization and that others might run into similar issues.