starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

API optimizations for tagged objects #2010

New Issue
Closed
opened 2025-12-29 17:21:27 +01:00 by adam · 8 comments
adam commented 2025-12-29 17:21:27 +01:00
Owner
Copy Link

Originally created by @remyleone on GitHub (Sep 17, 2018).

Environment

  • Python version: 3.7.0
  • NetBox version: 2.4.5-dev

Steps to Reproduce

  • Insert over 4000 devices in NetBox and fetch a device listing.
  • When you open the debug toolbar you can observe that a large number of SQL queries are performed.

Expected Behavior

I expected the number of SQL queries to not grow as the number of devices grows

Observed Behavior

The amount of SQL queries increased

#2437

Originally created by @remyleone on GitHub (Sep 17, 2018). ### Environment * Python version: 3.7.0 * NetBox version: 2.4.5-dev ### Steps to Reproduce - Insert over 4000 devices in NetBox and fetch a device listing. - When you open the debug toolbar you can observe that a large number of SQL queries are performed. ### Expected Behavior I expected the number of SQL queries to not grow as the number of devices grows ### Observed Behavior The amount of SQL queries increased #2437
adam added the status: acceptedtype: feature labels 2025-12-29 17:21:27 +01:00
adam closed this issue 2025-12-29 17:21:27 +01:00
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@Armadill0 commented on GitHub (Sep 20, 2018):

Do you have custom fields for your devices? Afaik each custom field generates another query for each device into the custom fields table. This is why the usage of custom fields should be limited.

@Armadill0 commented on GitHub (Sep 20, 2018): Do you have custom fields for your devices? Afaik each custom field generates another query for each device into the custom fields table. This is why the usage of custom fields should be limited.
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@remyleone commented on GitHub (Sep 21, 2018):

I think @anthony25 can answer that but I don't think we got custom fields. Also with the fix provided ( #2437 ) we reduced the query load with the same data.

@remyleone commented on GitHub (Sep 21, 2018): I think @anthony25 can answer that but I don't think we got custom fields. Also with the fix provided ( #2437 ) we reduced the query load with the same data.
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@sdktr commented on GitHub (Sep 24, 2018):

/offtopic: hi @sieben , could you provide some help to get this 'debug toolbar' to show up? I'm assuming that the debug=True flag should do the trick with the buildin django-debug plugin, but that alone doesn't change my web interface at all. How did you troubleshoot this?

@sdktr commented on GitHub (Sep 24, 2018): /offtopic: hi @sieben , could you provide some help to get this 'debug toolbar' to show up? I'm assuming that the ```debug=True``` flag should do the trick with the buildin django-debug plugin, but that alone doesn't change my web interface at all. How did you troubleshoot this?
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@aruhier commented on GitHub (Sep 24, 2018):

We do have custom fields, but it's the tags that are not prefetched which is causing the issue. As the default behavior of django is to fetch the foreign objects JIT, it has to do 1 query per item to select the associated tags. We managed to fix that for the devices in our PR, but it has to be done for every model that uses tags.

Edit: FYI, we managed to decrease the number of queries from ~1000 to 10, with 1000 devices listed from the API

@aruhier commented on GitHub (Sep 24, 2018): We do have custom fields, but it's the tags that are not prefetched which is causing the issue. As the default behavior of django is to fetch the foreign objects JIT, it has to do 1 query per item to select the associated tags. We managed to fix that for the devices in our PR, but it has to be done for every model that uses tags. Edit: FYI, we managed to decrease the number of queries from ~1000 to 10, with 1000 devices listed from the API
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Sep 26, 2018):

Have you done any benchmarking before and after the change? For example, populate 1000 devices with 10 tags each. I'm curious what the overall response time is before and after the change to prefetch_related.

@jeremystretch commented on GitHub (Sep 26, 2018): Have you done any benchmarking before and after the change? For example, populate 1000 devices with 10 tags each. I'm curious what the overall response time is before and after the change to `prefetch_related`.
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@sdktr commented on GitHub (Sep 27, 2018):

I benchmarked on the API call api/ipam/ip-addresses/?limit=1000&offset=0;

Without the 'tags' being prefetched in the IPAddressViewSet on my setup it takes 15000ms to fetch 1000 ipaddresses. The amount of SQL queries (measured using django-debug-toolbar) is 1009 taking 6029ms total.

Fixing the IPAddressViewSet to include 'tags' in the prefetch these numbers go down to
Total load time of 2033ms, containing 10 SQL queries (which take 230ms total)

prefetch_related(
        'nat_outside', 'tags'
)

File patched to gain these improvements: bcf22831e2/netbox/ipam/api/views.py (L248)
Note that in my tests there are no actual tags assigned to the ipaddresses.

@sdktr commented on GitHub (Sep 27, 2018): I benchmarked on the API call `api/ipam/ip-addresses/?limit=1000&offset=0`; Without the 'tags' being prefetched in the `IPAddressViewSet` on my setup it takes 15000ms to fetch 1000 ipaddresses. The amount of SQL queries (measured using django-debug-toolbar) is 1009 taking 6029ms total. Fixing the `IPAddressViewSet` to include 'tags' in the prefetch these numbers go down to Total load time of 2033ms, containing 10 SQL queries (which take 230ms total) ``` prefetch_related( 'nat_outside', 'tags' ) ``` File patched to gain these improvements: https://github.com/digitalocean/netbox/blob/bcf22831e2cc8a0c3021f0a73afd0ddc4a1b6b8d/netbox/ipam/api/views.py#L248 Note that in my tests there are no actual tags assigned to the ipaddresses.
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@remyleone commented on GitHub (Sep 27, 2018):

@sdktr Thank you very much for the benchmark :) Could you see other places where we should add the tags? I can upgrade #2437 to fix it in several places.

@remyleone commented on GitHub (Sep 27, 2018): @sdktr Thank you very much for the benchmark :) Could you see other places where we should add the tags? I can upgrade #2437 to fix it in several places.
adam commented 2025-12-29 17:21:28 +01:00
Author
Owner
Copy Link

@aruhier commented on GitHub (Sep 28, 2018):

@sdktr: thanks a lot for the benchmark!

@aruhier commented on GitHub (Sep 28, 2018): @sdktr: thanks a lot for the benchmark!
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label status: accepted type: feature
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#2010
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?