Unnecessary DB queries for API Endpoints

[JCWasmx86]

Deployment Type

Self-hosted

NetBox Version

v4.3.2

Python Version

3.12

Steps to Reproduce

• For each of these endpoints:
  • api/ipam/services
  • api/ipam/vlan-groups
  • api/virtualization/clusters
• Create a few objects of each type and enable e.g. the Django debug toolbar to count the number of queries
• Note the number of queries
• Add a few more objects

Expected Behavior

• Number of DB queries stays roughly the same (Like e.g. for the devices endpoint)

Observed Behavior

• Number of queries increases by roughly the same number as the number of objects added (I guess some prefetch_related/select_related are missing)

Service: One query per parent
VLAN-Group: One query per scope
Cluster: One query per site

[jnovinger]

@JCWasmx86 , please flesh this out more. What queries are unnecessary? Which querysets do you believe need to have a modified/added .prefetch_related()? Also please include objects to create/modify to trigger the behavior--"create a few objects" is pretty vague. I've found reports that include a small CSV snippet that is easily importable make reproduction much easier.

Please note that we generally do not use .select_related() except in very specific cases, which usually include within a .prefetch_related() dealing with a generic foreign key.

[JCWasmx86]

Ok. Full reproduction instructions for the Service issue (That's the one I care most about due to the data involved):

• Create a fully new Netbox (Empty; for me it runs on 4vCPUs and 8 GB RAM on ARM) I really just blindly copy-pasted from your website.
• Create API token
  Use this script for the services:

import pynetbox

netbox = pynetbox.api(
    url="http://localhost:8000", token="!!!API_TOKEN_HERE!!!"
)
# The numbers are so big
# in order to make reproduction easier
# and increasing variability of data
for i in range(5):
    tenant_name = f"tenant-{i}"
    tenant = netbox.tenancy.tenants.get(name=tenant_name)
    if not tenant:
        tenant = netbox.tenancy.tenants.create(name=tenant_name, slug=tenant_name)
    site_name = f"site-{i}"
    site = netbox.dcim.sites.get(name=site_name)
    if not site:
        site = netbox.dcim.sites.create(name=site_name, slug=site_name)
    for j in range(150):
        vm_name = f"vm-{tenant_name}-{j}"
        vm = netbox.virtualization.virtual_machines.get(name=vm_name)
        if not vm:
            vm = netbox.virtualization.virtual_machines.create(
                name=vm_name, tenant=tenant.id, site=site.id
            )

        num_services = 5 + (i + j) % 8
        for k in range(num_services):
            service_name = f"service-{k}"
            service = netbox.ipam.services.get(
                name=service_name, virtual_machine_id=vm.id
            )
            if not service:
                netbox.ipam.services.create(
                    name=service_name,
                    parent_object_id=vm.id,
                    ports=[443],
                    parent_object_type="virtualization.virtualmachine",
                    protocol="tcp",
                )

• Go grab a coffee (or reduce numbers there)
• Data for services should be imported now
• Go to /api/ipam/services/?format=json&limit=1000 and measure the load time in the browser tools (Around 4.3 seconds)
• Enable the debug bar
• Open /api/ipam/services/. By default it should show 50 services and in the toolbar ~62 queries
• Open /api/ipam/services/?limit=100. It should show ~111 queries
• Open /api/ipam/services/?limit=1000. It should show ~1011 queries
  Example queries:

SELECT "virtualization_virtualmachine"."id",
       "virtualization_virtualmachine"."created",
       "virtualization_virtualmachine"."last_updated",
       "virtualization_virtualmachine"."custom_field_data",
       "virtualization_virtualmachine"."description",
       "virtualization_virtualmachine"."comments",
       "virtualization_virtualmachine"."local_context_data",
       "virtualization_virtualmachine"."config_template_id",
       "virtualization_virtualmachine"."site_id",
       "virtualization_virtualmachine"."cluster_id",
       "virtualization_virtualmachine"."device_id",
       "virtualization_virtualmachine"."tenant_id",
       "virtualization_virtualmachine"."platform_id",
       "virtualization_virtualmachine"."name",
       "virtualization_virtualmachine"."status",
       "virtualization_virtualmachine"."role_id",
       "virtualization_virtualmachine"."primary_ip4_id",
       "virtualization_virtualmachine"."primary_ip6_id",
       "virtualization_virtualmachine"."vcpus",
       "virtualization_virtualmachine"."memory",
       "virtualization_virtualmachine"."disk",
       "virtualization_virtualmachine"."serial",
       "virtualization_virtualmachine"."interface_count",
       "virtualization_virtualmachine"."virtual_disk_count"
  FROM "virtualization_virtualmachine"
 WHERE "virtualization_virtualmachine"."id" = 10
 LIMIT 21
-- 1000 similar queries. Duplicated 6 times. 

Change the ServiceViewSet from:

class ServiceViewSet(NetBoxModelViewSet):
    queryset = Service.objects.all()
    serializer_class = serializers.ServiceSerializer
    filterset_class = filtersets.ServiceFilterSet

to

class ServiceViewSet(NetBoxModelViewSet):
    queryset = Service.objects.all().prefetch_related('parent')
    serializer_class = serializers.ServiceSerializer
    filterset_class = filtersets.ServiceFilterSet

For 1000 elements you should now see only 12 queries.

• Disable debug mode again
• Go to /api/ipam/services/?format=json&limit=1000 and measure the load time in the browser tools (Around 2 seconds)

For better testing: (I know it's not perfect but it shows a clear direction)

time for i in {0..10}; do curl -H "Authorization: Token xxx" http://$IP:8000/api/ipam/services/?format=json&limit=1000 >/dev/null 2>&1; done

Before my change:

real 0m44.924s
user 0m0.049s
sys 0m0.077s

After my change:
real 0m26.907s
user 0m0.043s
sys 0m0.075s

So around 40% faster, this can make a huge difference. The other two will follow in the next comments

[JCWasmx86]

For the VLAN-Groups:

import pynetbox

netbox = pynetbox.api(
    url="http://localhost:8000", token="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
)

for i in range(5):
    tenant_name = f"tenant-{i}"
    tenant = netbox.tenancy.tenants.get(name=tenant_name)
    if not tenant:
        tenant = netbox.tenancy.tenants.create(name=tenant_name, slug=tenant_name)

    site_name = f"site-{i}"
    site = netbox.dcim.sites.get(name=site_name)
    if not site:
        site = netbox.dcim.sites.create(name=site_name, slug=site_name)

    for j in range(250):
        vlan_group_name = f"vlan-group-{i}-{j}"
        vlan_group = netbox.ipam.vlan_groups.get(name=vlan_group_name)
        if not vlan_group:
            vlan_group = netbox.ipam.vlan_groups.create(
                name=vlan_group_name, slug=vlan_group_name, site=site.id, scope_type="dcim.site", scope_id=site.id
            )

• Assure you are not in DEBUG mode.
• Execute the measuring code: time for i in {0..10}; do curl -H "Authorization: Token xxx" http://$IP:8000/api/ipam/vlan-groups/?format=json&limit=1000 >/dev/null 2>&1; done
  real 0m43.552s
  user 0m0.045s
  sys 0m0.079s
• Switch to debug mode
• Go to /api/ipam/vlan-groups. It should show 50 elements. Number of queries ~61
• Go to /api/ipam/vlan-groups/?limit=100 Number of queries ~110
• Go to /api/ipam/vlan-groups/?limit=1000 Number of queries ~1010

Example query:

SELECT "dcim_site"."id",
       "dcim_site"."created",
       "dcim_site"."last_updated",
       "dcim_site"."custom_field_data",
       "dcim_site"."description",
       "dcim_site"."comments",
       "dcim_site"."name",
       "dcim_site"."slug",
       "dcim_site"."status",
       "dcim_site"."region_id",
       "dcim_site"."group_id",
       "dcim_site"."tenant_id",
       "dcim_site"."facility",
       "dcim_site"."time_zone",
       "dcim_site"."physical_address",
       "dcim_site"."shipping_address",
       "dcim_site"."latitude",
       "dcim_site"."longitude"
  FROM "dcim_site"
 WHERE "dcim_site"."id" = 2
 LIMIT 21
-- 1000 similar queries. Duplicated 250 times. 

Edit the VLANGroupViewSet to:

class VLANGroupViewSet(NetBoxModelViewSet):
    queryset = VLANGroup.objects.annotate_utilization().prefetch_related('scope')
    serializer_class = serializers.VLANGroupSerializer
    filterset_class = filtersets.VLANGroupFilterSet

Number of queries for 1000 elements: 12

• Disable debug mode
• Execute measurement code:
  real 0m28.084s
  user 0m0.038s
  sys 0m0.076s

[JCWasmx86]

For clusters:

import pynetbox

netbox = pynetbox.api(
    url="http://localhost:8000", token="xxx"
)

cluster_type_name = "dummy"
cluster_type = netbox.virtualization.cluster_types.get(name=cluster_type_name)
if not cluster_type:
    cluster_type = netbox.virtualization.cluster_types.create(name=cluster_type_name, slug=cluster_type_name)

for i in range(5):
    tenant_name = f"tenant-{i}"
    tenant = netbox.tenancy.tenants.get(name=tenant_name)
    if not tenant:
        tenant = netbox.tenancy.tenants.create(name=tenant_name, slug=tenant_name)

    site_name = f"site-{i}"
    site = netbox.dcim.sites.get(name=site_name)
    if not site:
        site = netbox.dcim.sites.create(name=site_name, slug=site_name)

    for j in range(250):
        cluster_name = f"cluster-{i}-{j}"
        cluster = netbox.virtualization.clusters.get(name=cluster_name)
        if not cluster:
            cluster = netbox.virtualization.clusters.create(
                name=cluster_name, type=cluster_type.id, site=site.id, tenant=tenant.id, scope_type="dcim.site", scope_id=site.id
            )

Basically the same procedure as before.

Before editing code:
time for i in {0..10}; do curl -H "Authorization: Token xxx" http://$IP:8000/api/virtualization/clusters/?format=json&limit=1000 >/dev/null 2>&1; done

real 0m45.285s
user 0m0.042s
sys 0m0.083s

/api/virtualization/clusters/ => ~50 queries
/api/virtualization/clusters/?limit=100 => ~50 queries
/api/virtualization/clusters/?limit=1000 => ~950 queries

SELECT "dcim_site"."id",
       "dcim_site"."created",
       "dcim_site"."last_updated",
       "dcim_site"."custom_field_data",
       "dcim_site"."description",
       "dcim_site"."comments",
       "dcim_site"."name",
       "dcim_site"."slug",
       "dcim_site"."status",
       "dcim_site"."region_id",
       "dcim_site"."group_id",
       "dcim_site"."tenant_id",
       "dcim_site"."facility",
       "dcim_site"."time_zone",
       "dcim_site"."physical_address",
       "dcim_site"."shipping_address",
       "dcim_site"."latitude",
       "dcim_site"."longitude"
  FROM "dcim_site"
 WHERE "dcim_site"."id" = 1
 LIMIT 21
-- 941 similar queries. Duplicated 191 times. 

Modify ClusterViewSet to:

class ClusterViewSet(NetBoxModelViewSet):
    queryset = Cluster.objects.prefetch_related('virtual_machines', 'scope').annotate(
        allocated_vcpus=Sum('virtual_machines__vcpus'),
        allocated_memory=Sum('virtual_machines__memory'),
        allocated_disk=Sum('virtual_machines__disk'),
    )
    serializer_class = serializers.ClusterSerializer
    filterset_class = filtersets.ClusterFilterSet

After editing:
/api/virtualization/clusters/?limit=1000 => 15 queries

Executing the measurement code:
real 0m30.702s
user 0m0.041s
sys 0m0.082s

[jnovinger]

@JCWasmx86 , excellent, thank you for the details! Is this something you'd like to contribute (as you've already outlined the changes)?

[JCWasmx86]

Yes, sure