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

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

[github-actions[bot]]

This issue has been automatically closed due to lack of activity. In an effort to reduce noise, please do not comment any further. Note that the core maintainers may elect to reopen this issue at a later date if deemed necessary.