Slow prefix search without filter

[nextgenkitty]

I have about 60000 total prefixes. When there is no cache and when I don't use search filter, loading the prefix page takes like 10 minutes. I extended nginx timeout value to a big value but waiting this time is not acceptable for my users. My use case is the only Prefix in NetBox. I am sure Postgresql query is taking a long time in the background but what can I tweak?

[candlerb]

60,000 prefixes in 10 minutes is only 100 per second; that seems very low.

You need to find out whether the problem is with postgres or with Netbox processing the results (although I expect it would do a "limit" query so actually only the first page-full would need to be processed).

Can you start by doing a query log, either in postgres or in django, to see exactly what SQL query(ies) are being sent by Netbox to Postgres?

Also, how is your Postgres database deployed? Is it backed by hard drives or SSDs?

[jeremystretch]

• What version of NetBox are you running?
• How are you making the request? (Web UI, REST API, other?)

[estockwellgd]

I've seen the same problem with 55,000 prefixes. This is with the Web UI (ipam/prefixes/)
Currently we're running v2.10.4, and we've seen it in earlier versions over the past year.
After 10 minutes or so, performance is much better. I assume we're hitting the cache at that point.

[robbagithub]

We have only 1500 prefixes but it's taking 45 seconds to retrieve those and We're running 3.2.9.

The database storage is on nvme SSD with plenty of RAM. Is this a normal level of performance or are there areas we can look at to speed this up?

Regards,
Rob.

[kkthxbye-code]

As always, make sure you don't have results per page set to 1000. Additionally, try to remove the utilization column from the table and see if that helps.

[candlerb]

Also, see if you can turn on SQL query logging - either at the Netbox side or the Postgres side. It's possible that for each prefix, one or more additional queries are being triggered via the Django ORM. Seeing what those queries are would help isolate the issue.

[robbagithub]

@kkthxbye-code I'll change that down to something more reasonable for sure, and I'll remove utilization too.

@candlerb I'll ask the DBAs to turn on the query logging and grab and dump the sql here. Cheers, Rob.

[robbagithub]

@kkthxbye-code Holy smoke, that went from 45 seconds to 4 secs when setting the amount to return to 100!