API optimizations for tagged objects

[remyleone]

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

[Armadill0]

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.

[remyleone]

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.

[sdktr]

/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?

[aruhier]

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

[jeremystretch]

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.

[sdktr]

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.

[remyleone]

@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.

[aruhier]

@sdktr: thanks a lot for the benchmark!