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

Poor performance in cable trace API calls: N+1 problem #10657

New Issue
Closed
opened 2025-12-29 21:34:18 +01:00 by adam · 19 comments
adam commented 2025-12-29 21:34:18 +01:00
Owner
Copy Link

Originally created by @Tishka17 on GitHub (Jan 16, 2025).

Originally assigned to: @Tishka17 on GitHub.

Deployment Type

Self-hosted

Triage priority

N/A

NetBox Version

v3.7, v4.2

Python Version

3.11

Steps to Reproduce

  1. Fill enough data for devices with cables and connections.
  2. Do API requests to load device, interfaces, ip-addresses like
    http://localhost:8000/api/dcim/interfaces/?device_id=3971&limit=100&offset=0

Expected Behavior

Data is loaded quite fast, <0.5 seconds. Number of db queries pretty small up to 10-20 per HTTP-request

Observed Behavior

Some requests are pretty slow. Up to 3 seconds.
130 sql queries are produced on 3.7 and 160 db queries on 4.2

CabledObjectModel.link_peers has N+1 problem: https://github.com/netbox-community/netbox/blob/v3.7.8/netbox/dcim/models/device_components.py#L192
CablePath._get_path has N+1 problem as well: https://github.com/netbox-community/netbox/blob/v3.7.8/netbox/dcim/models/cables.py#L746

I tried to comment out these methods and got only 20 db queries on the same http request

Additional info: n+1 problem should be eliminated

Originally created by @Tishka17 on GitHub (Jan 16, 2025). Originally assigned to: @Tishka17 on GitHub. ### Deployment Type Self-hosted ### Triage priority N/A ### NetBox Version v3.7, v4.2 ### Python Version 3.11 ### Steps to Reproduce 1. Fill enough data for devices with cables and connections. 2. Do API requests to load device, interfaces, ip-addresses like http://localhost:8000/api/dcim/interfaces/?device_id=3971&limit=100&offset=0 ### Expected Behavior Data is loaded quite fast, <0.5 seconds. Number of db queries pretty small up to 10-20 per HTTP-request ### Observed Behavior Some requests are pretty slow. Up to 3 seconds. 130 sql queries are produced on 3.7 and 160 db queries on 4.2 `CabledObjectModel.link_peers` has N+1 problem: https://github.com/netbox-community/netbox/blob/v3.7.8/netbox/dcim/models/device_components.py#L192 `CablePath._get_path` has N+1 problem as well: https://github.com/netbox-community/netbox/blob/v3.7.8/netbox/dcim/models/cables.py#L746 I tried to comment out these methods and got only 20 db queries on the same http request Additional info: n+1 problem should be eliminated
adam added the type: bugstatus: acceptedseverity: low labels 2025-12-29 21:34:18 +01:00
adam closed this issue 2025-12-29 21:34:18 +01:00
adam commented 2025-12-29 21:34:19 +01:00
Author
Owner
Copy Link

@bctiemann commented on GitHub (Jan 16, 2025):

It would be ideal if each of these identified points of improvement could be opened as a separate bug, so they can be individually fixed (i.e. if one is easier than another).

@bctiemann commented on GitHub (Jan 16, 2025): It would be ideal if each of these identified points of improvement could be opened as a separate bug, so they can be individually fixed (i.e. if one is easier than another).
adam commented 2025-12-29 21:34:19 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jan 17, 2025):

Data is loaded quite fast, <0.5 seconds. Number of db queries pretty small up to 10-20 per HTTP-request

I think we need something more explicit here. Asserting that the application should perform faster is not sufficient justification for a bug report. @Tishka17 please extend your post above to cite the specific optimizations you believe are missing.

@jeremystretch commented on GitHub (Jan 17, 2025): > Data is loaded quite fast, <0.5 seconds. Number of db queries pretty small up to 10-20 per HTTP-request I think we need something more explicit here. Asserting that the application should perform faster is not sufficient justification for a bug report. @Tishka17 please extend your post above to cite the specific optimizations you believe are missing.
adam commented 2025-12-29 21:34:20 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Jan 17, 2025):

@jeremystretch sorry, I thought the "N+1 problem" words with links to corresponding functions are enough. What else can I add? For me it is a performance issue, but according to a code - it's just a technical bug with improper related data loading

@Tishka17 commented on GitHub (Jan 17, 2025): @jeremystretch sorry, I thought the "N+1 problem" words with links to corresponding functions are enough. What else can I add? For me it is a performance issue, but according to a code - it's just a technical bug with improper related data loading
adam commented 2025-12-29 21:34:20 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jan 17, 2025):

What else can I add?

Please detail the specific changes you are proposing.

@jeremystretch commented on GitHub (Jan 17, 2025): > What else can I add? Please detail the specific changes you are proposing.
adam commented 2025-12-29 21:34:21 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Jan 17, 2025):

I am proposing to fix code in a way, that it has no N+1 problem or lazy loading. I am not Django developer, so I am not familiar how it's technics like prefetch_related can be used in this specific case

@Tishka17 commented on GitHub (Jan 17, 2025): I am proposing to fix code in a way, that it has no N+1 problem or lazy loading. I am not Django developer, so I am not familiar how it's technics like prefetch_related can be used in this specific case
adam commented 2025-12-29 21:34:21 +01:00
Author
Owner
Copy Link

@n-rodriguez commented on GitHub (Jan 20, 2025):

https://dev.to/herchila/how-to-avoid-n1-queries-in-django-tips-and-solutions-2ajo

My 2 cents

@n-rodriguez commented on GitHub (Jan 20, 2025): https://dev.to/herchila/how-to-avoid-n1-queries-in-django-tips-and-solutions-2ajo My 2 cents
adam commented 2025-12-29 21:34:21 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Jan 20, 2025):

The problem is that some fields are calculated are not just generated based on model data, but explicitly do requests to a database.

I've tried to fix link_peers replacing .exclude query with filtering already loaded self.cable.terminations but it references then GenericForeignKey and we need its children in response..., so I did not succceded.

- peers = self.cable.terminations.exclude(cable_end=self.cable_end).prefetch_related('termination')
- return [peer.termination for peer in peers]
+ return [peer.termination for peer in self.cable.terminations if peer.cable_end!=self.cable_end]
@Tishka17 commented on GitHub (Jan 20, 2025): The problem is that some fields are calculated are not just generated based on model data, but explicitly do requests to a database. I've tried to fix `link_peers` replacing `.exclude` query with filtering already loaded `self.cable.terminations` but it references then `GenericForeignKey` and we need its children in response..., so I did not succceded. ```diff - peers = self.cable.terminations.exclude(cable_end=self.cable_end).prefetch_related('termination') - return [peer.termination for peer in peers] + return [peer.termination for peer in self.cable.terminations if peer.cable_end!=self.cable_end] ```
adam commented 2025-12-29 21:34:21 +01:00
Author
Owner
Copy Link

@jsenecal commented on GitHub (Jan 20, 2025):

To answer to that specific example (prefetch w/ generic foreignkeys) there was an upstream issue to try to mitigate this (
https://code.djangoproject.com/ticket/33651) which has been implemented in Django 4.0.

Not sure if the netbox codebase uses that everywhere, but it is implemented in https://github.com/netbox-community/netbox/blob/main/netbox/utilities/fields.py#L74

@jsenecal commented on GitHub (Jan 20, 2025): To answer to that specific example (prefetch w/ generic foreignkeys) there was an upstream issue to try to mitigate this ( https://code.djangoproject.com/ticket/33651) which has been implemented in Django 4.0. Not sure if the netbox codebase uses that everywhere, but it is implemented in https://github.com/netbox-community/netbox/blob/main/netbox/utilities/fields.py#L74
adam commented 2025-12-29 21:34:21 +01:00
Author
Owner
Copy Link

@github-actions[bot] commented on GitHub (Jan 31, 2025):

This is a reminder that additional information is needed in order to further triage this issue. If the requested details are not provided, the issue will soon be closed automatically.

@github-actions[bot] commented on GitHub (Jan 31, 2025): This is a reminder that additional information is needed in order to further triage this issue. If the requested details are not provided, the issue will soon be closed automatically.
adam commented 2025-12-29 21:34:22 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Jan 31, 2025):

Really?

@Tishka17 commented on GitHub (Jan 31, 2025): Really?
adam commented 2025-12-29 21:34:22 +01:00
Author
Owner
Copy Link

@bctiemann commented on GitHub (Feb 4, 2025):

@Tishka17 I do want to give some attention to this. There's a lot of work that's being prioritized for current and upcoming releases and things like this have to be tackled on an opportunistic basis.

We need to research @jsenecal 's comment and see whether get_prefetch_querysets can help in more places. If we can reproduce slowness in a specific area, and optimizations can be made piecemeal, they can be raised as individual issues/PRs which might be more actionable than an umbrella effort like this.

@bctiemann commented on GitHub (Feb 4, 2025): @Tishka17 I do want to give some attention to this. There's a lot of work that's being prioritized for current and upcoming releases and things like this have to be tackled on an opportunistic basis. We need to research @jsenecal 's comment and see whether `get_prefetch_querysets` can help in more places. If we can reproduce slowness in a specific area, and optimizations can be made piecemeal, they can be raised as individual issues/PRs which might be more actionable than an umbrella effort like this.
adam commented 2025-12-29 21:34:22 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Feb 19, 2025):

I am trying to implement Prefetch for path_objects field. Here is my experiment:

How it works?

  1. I store json in C.data field as an array of arrays of pairs "type id", "model id". This would be later similar to current _nodes.
  2. Custom field has a link to json data field and processes operations.
  3. In a command I insert some data and then read them back. Prefetch is processed by a field.

Currently it is not yet finished, I need to handle some cases, but I'll try to bring it as a solution if it will be accepted.

Another question from me is regarding GenericPrefetch. We are still on Netbox 3.7 and GenericPrefetch is not supported on django 4.2 (which is used in Netbox 3.7). Did anyone try to update Django on that version of netbox?

@Tishka17 commented on GitHub (Feb 19, 2025): I am trying to implement Prefetch for `path_objects` field. Here is my experiment: * special `Field` class https://github.com/Tishka17/django-generic-array/blob/f303550/genericarray/main/myfk.py * model https://github.com/Tishka17/django-generic-array/blob/f303550/genericarray/main/models.py * example of usage https://github.com/Tishka17/django-generic-array/blob/f303550/genericarray/main/management/commands/mine.py How it works? 1. I store json in `C.data` field as an array of arrays of pairs "type id", "model id". This would be later similar to current `_nodes`. 2. Custom field has a link to json data field and processes operations. 3. In a command I insert some data and then read them back. Prefetch is processed by a field. Currently it is not yet finished, I need to handle some cases, but I'll try to bring it as a solution if it will be accepted. Another question from me is regarding GenericPrefetch. We are still on Netbox 3.7 and GenericPrefetch is not supported on django 4.2 (which is used in Netbox 3.7). Did anyone try to update Django on that version of netbox?
adam commented 2025-12-29 21:34:23 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Feb 21, 2025):

Update on my draft:

  1. I've backported GenericForeignKey together with GenericPrefetch from Django 5 to 4.2.
  2. I've updated my custom field to support array of arrays. Probably it should be unified, but it at least works.
  3. I've updated views to have more prefetches.

It look, that we do not have more N+1 for Interfaces, though I need to test it more and cleanup all code.

https://github.com/Tishka17/netbox/pull/3/files

Some of this files are not needed for latest NetBox versions as they use actual Django, but there are more different fields, so I need to rework it. Please, have a look now and I will continue working and create a PR for actual NetBox as well.

@Tishka17 commented on GitHub (Feb 21, 2025): Update on my draft: 1. I've backported GenericForeignKey together with GenericPrefetch from Django 5 to 4.2. 2. I've updated my custom field to support array of arrays. Probably it should be unified, but it at least works. 3. I've updated views to have more prefetches. It look, that we do not have more N+1 for Interfaces, though I need to test it more and cleanup all code. https://github.com/Tishka17/netbox/pull/3/files Some of this files are not needed for latest NetBox versions as they use actual Django, but there are more different fields, so I need to rework it. Please, have a look now and I will continue working and create a PR for actual NetBox as well.
adam commented 2025-12-29 21:34:23 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Feb 24, 2025):

The result on current moment:
I have a device with many connections and cables. I am doing a request http://localhost:8000/api/dcim/interfaces/?device_id=3926&limit=50
On master branch it generates 409 SELECT queries
On my branch it is 28 SELECT with identical json output

https://github.com/netbox-community/netbox/pull/18719

@Tishka17 commented on GitHub (Feb 24, 2025): The result on current moment: I have a device with many connections and cables. I am doing a request http://localhost:8000/api/dcim/interfaces/?device_id=3926&limit=50 On master branch it generates **409** `SELECT` queries On my branch it is **28** `SELECT` with identical json output https://github.com/netbox-community/netbox/pull/18719
adam commented 2025-12-29 21:34:23 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Feb 26, 2025):

@bctiemann did you get a chance to have a look on my PR? I do not know about your release plan, but we are interested in solving this issue. If you see any problems, I am eager to fix them

@Tishka17 commented on GitHub (Feb 26, 2025): @bctiemann did you get a chance to have a look on my PR? I do not know about your release plan, but we are interested in solving this issue. If you see any problems, I am eager to fix them
adam commented 2025-12-29 21:34:23 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Feb 28, 2025):

@bctiemann what information do you need from me to reopen and merge PR?

@Tishka17 commented on GitHub (Feb 28, 2025): @bctiemann what information do you need from me to reopen and merge PR?
adam commented 2025-12-29 21:34:24 +01:00
Author
Owner
Copy Link

@bctiemann commented on GitHub (Mar 6, 2025):

I'm going to go ahead and accept this for backlog work; I don't want to lose track of the PR. Please go ahead and reopen it and we'll review it as resources permit.

@bctiemann commented on GitHub (Mar 6, 2025): I'm going to go ahead and accept this for backlog work; I don't want to lose track of the PR. Please go ahead and reopen it and we'll review it as resources permit.
adam commented 2025-12-29 21:34:24 +01:00
Author
Owner
Copy Link

@Tishka17 commented on GitHub (Apr 24, 2025):

I see a regression in /interfaces call in latest release, so I sent a bunch of new fixes. Should I create new issue?

@Tishka17 commented on GitHub (Apr 24, 2025): I see a regression in `/interfaces` call in latest release, so I sent a bunch of new fixes. Should I create new issue?
adam commented 2025-12-29 21:34:24 +01:00
Author
Owner
Copy Link

@bctiemann commented on GitHub (Apr 24, 2025):

@Tishka17 Yes, please open a new issue, and target it specifically at the area(s) where you see problems. Please don't submit a PR until the issue has been triaged and accepted. Thanks!

@bctiemann commented on GitHub (Apr 24, 2025): @Tishka17 Yes, please open a new issue, and target it specifically at the area(s) where you see problems. Please don't submit a PR until the issue has been triaged and accepted. Thanks!
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 severity: low status: accepted type: bug
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#10657
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?