Creating devices takes an inordinate amount of time

[kkthxbye-code]

NetBox version

v3.4.4

Python version

3.10

Steps to Reproduce

This issue will be used to collect what I find while investigating the issue, as such the scenario is based on my test database and test devicetype.

1. Create a devicetype with 62 interfaces
2. Create a device based on the interface
3. Notice the responsetime

Expected Behavior

The device should be create within a reasonable time period.

Observed Behavior

The device takes many seconds to create.

Do note, that while the performance was never great, it was made worse by both the search indexing and the fact that we fixed a bug where changelog entries were not created for instantiated components.

[kkthxbye-code]

Some background around my test database:

Row counts:

extras_cachedvalue: 798217
dcim_device: 9622
dcim_interface: 97056

Rough response time (without debug) for a devicetype with 62 interfaces: 6.01 seconds

Initial findings:

A lot of the processing time is spent deleting from extras_cachedvalue:

It's slow because there's no index on object_id. Adding an index on object_id:

create index extras_cachedvalue_object_id_sdfsdf ON extras_cachedvalue(object_id);

Response time after adding the index: 2.36 s (~60% decrease).

Before index:

After index:

Insertions did not seem to be noticeably effected in my testing, but reindexing might be slower, will need to test. Otherwise this seems like a very easy win.

[kkthxbye-code]

I tested reindexing with and with an index on object_id and it seems like there's no difference.

With index:
python manage.py reindex 76,35s user 1,80s system 74% cpu 1:44,20 total

Without index:
python manage.py reindex 76,78s user 1,79s system 76% cpu 1:42,92 total

[DanSheps]

The device takes many seconds to create.

Do note, that while the performance was never great, it was made worse by both the search indexing and the fact that we fixed a bug where changelog entries were not created for instantiated components.

Wonder if we could push changelogging to the RQWorker (maybe include the option to spin up additional workers as well) for certain execution paths (anything in a RQ worker already can be completed there, anything web/api can go through the RQ).

This might also offer additional performance improvements, you just wouldn't get real-time change logging.

[jeremystretch]

Thanks for tackling this @kkthxbye-code!

As we discussed earlier today, we can sidestep the root issue here and avoid clearing any existing cache entries altogether when a new object is created (for which it's impossible to have any pre-existing entries). Of course, we should still add the index to serve cases where we do need to clear them.

Response time after adding the index: 2.36 s (~60 decrease).

I'm not able to reproduce such a stark reduction, but it's likely my cache just isn't large enough to impose that much of a delay. Can you say roughly how many object your test cache contains?

Wonder if we could push changelogging to the RQWorker

I opened #11202 to explore that idea recently, but haven't done any work on it yet.

[jeremystretch]

@kkthxbye-code can you test PR #11785 when you get a chance? I haven't added the index yet, but it should result in the same improvement as we're sidestepping the call to clear() entirely.

If this is deemed acceptable to resolve this specific issue, I'd like to do an audit to add indexes everywhere we use generic foreign keys.

[kkthxbye-code]

@jeremystretch

I'm not able to reproduce such a stark reduction, but it's likely my cache just isn't large enough to impose that much of a delay. Can you say roughly how many object your test cache contains?

I noted it in my first reply, but around 800k entries. Important to note that the 60% reduction is without DEBUG enabled. What does the reduction look like for you when just looking at the SQL tab in the debug toolbar?

I'll try the PR out!

[jeremystretch]

I noted it in my first reply, but around 800k entries.

Sorry, missed that. I'll need to generate a bunch of objects to increase the cache to test properly on my end.

What does the reduction look like for you when just looking at the SQL tab in the debug toolbar?

It shaves some time off, but I don't think I have a large enough cache yet for it to be noticeable.