Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

IP address API endpoint triggers excessive database queries, resulting in poor performance #1645

Closed
afics opened this issue Oct 25, 2017 · 1 comment
Labels
type: bug A confirmed report of unexpected behavior in the application
Milestone

Comments

@afics
Copy link

afics commented Oct 25, 2017

Issue type

[ ] Feature request
[x] Bug report
[ ] Documentation

Environment

  • Python version: 3.4.2
  • NetBox version: 2.2.1

Description

Certain API calls produce more SQL queries than they have to which results in those calls being "slow".
Example:

200 GET
/api/ipam/ip-addresses/?limit=10000
12106ms overall
2305ms on queries
2374 queries

This is fixed by #1535. While the Pull request is for an older version of Netbox, the newest version is also affected by this problem.
The same API call after applying the patch:

200 GET
/api/ipam/ip-addresses/
3215ms overall
255ms on queries
8 queries
@jeremystretch
Copy link
Member

The IP address serializer embeds the full serializer for the related interface rather than a nested serializer as in other cases. This was done to ensure the parent device was included as well but was probably a mistake. We can implement a slightly extended nested serializer to ensure only the necessary interface details are included. This would remove the need to extend the database query.

@jeremystretch jeremystretch added the type: bug A confirmed report of unexpected behavior in the application label Oct 25, 2017
@jeremystretch jeremystretch changed the title Reduce number of SQL queries for certain API calls IP address API endpoint triggers excessive database queries, resulting in poor performance Oct 25, 2017
@jeremystretch jeremystretch added this to the v2.3 milestone Nov 3, 2017
jeremystretch added a commit that referenced this issue Nov 15, 2017
funzoneq added a commit to openfibernet/netbox that referenced this issue Mar 8, 2018
* Closes netbox-community#1553: Introduced support for bulk object creation via the API

* Removed prefix `parent` filter (see netbox-community#1684)

* Restored search method on prefix filter

* Extended prefix 'available-ips' endpoint to accept multiple objects (related to netbox-community#1553)

* Standardize on JSON data format for all POST/PUT test client requests

* netbox-community#1694: Initial work on "next available" prefix provisioning

* Closes netbox-community#1706: Added deprecation warning for Python 2

* Merge branch '150-interface-vlans' into develop-2.3

* Fixed up validation of Interface VLAN assignments

* Fixes netbox-community#1645: Simplified interface serialzier for IP addresses and optimized API view queryset

* Cleaned up bulk IP provisioning a bit

* Add Tenancy to Rack Reservations; Fixes netbox-community#1592 (netbox-community#1672)

* fixed prefix header to represent new serial "vlan_vid"

* shows option in creation now

* fixed visibility on rack page

* cleanup

* Added view to Tenant page

* Moved migration for update from netbox-community#1666 and fixed tenant enumeration in FilterForm

* Fixed conflict #1

* Fixed filters from merge and made migration merge

* added tenant to api

* Fixed migrations problem

* Added Tenant to bulkedit option

* Resolved migration collision from netbox-community#1672

* A bit of cosmetic cleanup from netbox-community#1672

* Added nested representations of user and tenant to the rack reservation serializer

* Fixed version number

* Initial work on virtual chassis support

* Moved VC master designation to membership model

* Added initial UI views for virtual chassis assignment

* Fixes netbox-community#1727: Added missing import for M2M_FIELD_TYPES

* Added a form to edit virtual chassis

* Added views for editing/deleting VCMemberships

* Display member interfaces when viewing VC master device

* Added virtual_chassis_id API filter for interfaces

* Obsoleted ComponentEditView and ComponentDeleteView

* Added virtual chassis tests

* Allow designating primary IPs assigned to a device's peer VC members

* Return all VC member interfaces when filtering for the master device; remove virtual_chassis_id filter

* Ignore VC member interfaces where mgmt_only=True

* PEP8 fixes

* Closes netbox-community#1744: Allow associating a platform with a specific manufacturer

* Closes netbox-community#1283: Added a time zone field to the site model

* Fixes netbox-community#1136: Enforce model validation during bulk update

* Closes netbox-community#1321: Added created and last_updated fields for relevant models to their API serializers

* Cleaned up component tables and checkbox toggling

* More table cleanup

* Converted remaining legacy views to class-based views for the DCIM app

* Added bulk creation API tests

* Fix bulk creation of VCMemberships via API

* Fix bulk creation of Secrets via API

* Closes netbox-community#1781: Enable bulk renaming of device components

* Implemented a view for adding individual devices to an existing virtual chassis

* Completed virtual chassis API serializers

* Reflect virtual chassis membership in display_name

* Extend IP address device filter to match virtual chassis members

* Added VirtualChassis to the docs

* Allow interface mode to be null (for routed interfaces)

* Highlight renamed components and allow for multiple previews

* Renamed device status constants for clarity

* Closes netbox-community#1758: Added 'status' field to Site model

* Closes netbox-community#1821: Added 'description' field to Site model

* Removed support for NAPALM 1.x

* Fixes netbox-community#1847: Fix RecursionError when VC master device is unnamed

* Fixes netbox-community#1848: Allow null value for interface encapsulation mode

* netbox-community#1843: Allow assignment of VC member interfaces to VC master LAG

* Fixes netbox-community#1838: Fix KeyError when attempting to create a VirtualChassis with no devicesselected

* Collapsed VCMembership into the Device model (WIP)

* Added virtual chassis member add view

* Added virtual chassis member remove view

* Cleaned up API for virtual chassis

* Added virtual chassis tests

* Additional validation cleanup

* Allow assignment of services to IPs on any VC member

* Closes netbox-community#1864: Added a 'status' field to the circuit model

* Fixes netbox-community#1867: Allow filtering on device status with multiple values

* Upgraded jquery to v3.3.1

* Fix for bulk interface edit form 802.1Q settings (netbox-community#1882)

* fixes netbox-community#1881 - bulk interface 802.1Q settings form

* fix PEP8 newline

* PEP8 fixup

* Fixes netbox-community#1884: Provide additional context to identify devices when creating/editing avirtual chassis

* VirtualChassis form validation cleanup

* Fixed typo in template

* Exclude devices already assigned to a VC from the list of potential VC members

* Finished VirtualChassis list view

* Post-release version bump

* Initial model for Packages model

* Add package_list

* Add migrations

* Adding package add form

* Import CSV Packages feature

* Adding filters

* Working bulkedit functionality for packages

* Require a package for a customer circuit

* Add edit and delete views for packages

* Add API support

* Expose package detail via the API endpoint

* Formatting correction

* Corrected order of arguments on DeviceVCMembershipForm

* Closes netbox-community#1899: Prefer binary package of psycopg2

* Updated requirements list

* Fixes netbox-community#1907: Allow removing an IP as the primary for a device when editing the IP directly

* Release v2.3.0

* NETOPS-704 Rename Tenant Group to Service Providers and Tenants to Customers

* NETOPS-704 Some small detail names fixed

* Fixes
funzoneq added a commit to openfibernet/netbox that referenced this issue Mar 8, 2018
* Closes netbox-community#1553: Introduced support for bulk object creation via the API

* Removed prefix `parent` filter (see netbox-community#1684)

* Restored search method on prefix filter

* Extended prefix 'available-ips' endpoint to accept multiple objects (related to netbox-community#1553)

* Standardize on JSON data format for all POST/PUT test client requests

* netbox-community#1694: Initial work on "next available" prefix provisioning

* Closes netbox-community#1706: Added deprecation warning for Python 2

* Merge branch '150-interface-vlans' into develop-2.3

* Fixed up validation of Interface VLAN assignments

* Fixes netbox-community#1645: Simplified interface serialzier for IP addresses and optimized API view queryset

* Cleaned up bulk IP provisioning a bit

* Add Tenancy to Rack Reservations; Fixes netbox-community#1592 (netbox-community#1672)

* fixed prefix header to represent new serial "vlan_vid"

* shows option in creation now

* fixed visibility on rack page

* cleanup

* Added view to Tenant page

* Moved migration for update from netbox-community#1666 and fixed tenant enumeration in FilterForm

* Fixed conflict #1

* Fixed filters from merge and made migration merge

* added tenant to api

* Fixed migrations problem

* Added Tenant to bulkedit option

* Resolved migration collision from netbox-community#1672

* A bit of cosmetic cleanup from netbox-community#1672

* Added nested representations of user and tenant to the rack reservation serializer

* Fixed version number

* Initial work on virtual chassis support

* Moved VC master designation to membership model

* Added initial UI views for virtual chassis assignment

* Fixes netbox-community#1727: Added missing import for M2M_FIELD_TYPES

* Added a form to edit virtual chassis

* Added views for editing/deleting VCMemberships

* Display member interfaces when viewing VC master device

* Added virtual_chassis_id API filter for interfaces

* Obsoleted ComponentEditView and ComponentDeleteView

* Added virtual chassis tests

* Allow designating primary IPs assigned to a device's peer VC members

* Return all VC member interfaces when filtering for the master device; remove virtual_chassis_id filter

* Ignore VC member interfaces where mgmt_only=True

* PEP8 fixes

* Closes netbox-community#1744: Allow associating a platform with a specific manufacturer

* Closes netbox-community#1283: Added a time zone field to the site model

* Fixes netbox-community#1136: Enforce model validation during bulk update

* Closes netbox-community#1321: Added created and last_updated fields for relevant models to their API serializers

* Cleaned up component tables and checkbox toggling

* More table cleanup

* Converted remaining legacy views to class-based views for the DCIM app

* Added bulk creation API tests

* Fix bulk creation of VCMemberships via API

* Fix bulk creation of Secrets via API

* Closes netbox-community#1781: Enable bulk renaming of device components

* Implemented a view for adding individual devices to an existing virtual chassis

* Completed virtual chassis API serializers

* Reflect virtual chassis membership in display_name

* Extend IP address device filter to match virtual chassis members

* Added VirtualChassis to the docs

* Allow interface mode to be null (for routed interfaces)

* Highlight renamed components and allow for multiple previews

* Renamed device status constants for clarity

* Closes netbox-community#1758: Added 'status' field to Site model

* Closes netbox-community#1821: Added 'description' field to Site model

* Removed support for NAPALM 1.x

* Fixes netbox-community#1847: Fix RecursionError when VC master device is unnamed

* Fixes netbox-community#1848: Allow null value for interface encapsulation mode

* netbox-community#1843: Allow assignment of VC member interfaces to VC master LAG

* Fixes netbox-community#1838: Fix KeyError when attempting to create a VirtualChassis with no devicesselected

* Collapsed VCMembership into the Device model (WIP)

* Added virtual chassis member add view

* Added virtual chassis member remove view

* Cleaned up API for virtual chassis

* Added virtual chassis tests

* Additional validation cleanup

* Allow assignment of services to IPs on any VC member

* Closes netbox-community#1864: Added a 'status' field to the circuit model

* Fixes netbox-community#1867: Allow filtering on device status with multiple values

* Upgraded jquery to v3.3.1

* Fix for bulk interface edit form 802.1Q settings (netbox-community#1882)

* fixes netbox-community#1881 - bulk interface 802.1Q settings form

* fix PEP8 newline

* PEP8 fixup

* Fixes netbox-community#1884: Provide additional context to identify devices when creating/editing avirtual chassis

* VirtualChassis form validation cleanup

* Fixed typo in template

* Exclude devices already assigned to a VC from the list of potential VC members

* Finished VirtualChassis list view

* Post-release version bump

* Formatting correction

* Corrected order of arguments on DeviceVCMembershipForm

* Closes netbox-community#1899: Prefer binary package of psycopg2

* Updated requirements list

* Fixes netbox-community#1907: Allow removing an IP as the primary for a device when editing the IP directly

* Release v2.3.0

* Fixes netbox-community#1915: Redirect to device view after deleting a component

* Fixes netbox-community#1919: Prevent exception when attempting to create a virtual machine without selecting devices

* Closes netbox-community#1918: Add note about copying media directory to upgrade doc

* Fixes netbox-community#1924: Include VID in VLAN lists when editing an interface

* fixed netbox-community#1921 - create interfaces with 801.1q in api

* Post-release version bump (a bit late)

* fixed form bound check for site and vlan group

* Fixes netbox-community#1927: Include all VC member interaces on A side when creating a new interface connection

* Fixes netbox-community#1921: Ignore ManyToManyFields when validating a new object created via the API

* refactor to handle M2M validation in ValidatedModelSerializer

* Fixes netbox-community#1935: Correct API validation of VLANs assigned to interfaces

* Fixes netbox-community#1934: Fixed exception when rendering export template on an object type with custom fields assigned

* Fixes netbox-community#1926: Prevent reassignment of parent device when bulk editing VC member interfaces

* Closes netbox-community#1910: Added filters for cluter group and cluster type

* Fixes netbox-community#1936: Trigger validation error when attempting to create a virtual chassis without specifying member positions

* Release v2.3.1
@lock lock bot locked as resolved and limited conversation to collaborators Jan 18, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
type: bug A confirmed report of unexpected behavior in the application
Projects
None yet
Development

No branches or pull requests

2 participants