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

Inefficient deletion of obsolete SwPortVlan records #1641

Closed
lunkwill42 opened this issue Dec 18, 2017 · 0 comments
Closed

Inefficient deletion of obsolete SwPortVlan records #1641

lunkwill42 opened this issue Dec 18, 2017 · 0 comments
Assignees
Labels
Milestone

Comments

@lunkwill42
Copy link
Member

This method appears to produce SQL statements that become extremely inefficient on large NAV installs (with many, many interfaces):

https://github.com/UNINETT/nav/blob/178616318edf35b7250b889b53f87d09229996fa/python/nav/topology/vlan.py#L409-L411

Most of the time, the same set of switch ports are being updated (or confirmed) in swportvlan, meaning deletions of obsolete records are likely rare. Yet, this method, through Django, can cause an SQL statement of over 500KB in size to be sent to the PostgreSQL server on an install with over 40K switch ports.

The statement produced follows the template

DELETE FROM "swportvlan" WHERE NOT ("swportvlan"."interfaceid" IN (1, 2, 3, 4, .... ));

Which seems to be really inefficiently processed by PostgreSQL (upwards of 30 seconds, resulting in no records deleted on the test installation). It seems that processing the set difference of interface primary keys in Python is a lot more efficient.

Calculating the difference in Python would also make debug logs more readable, since we can print exactly which records to delete, rather then try to parse a 500KB list of interface id's to NOT delete.

@lunkwill42 lunkwill42 added this to the 4.8.3 milestone Dec 19, 2017
@lunkwill42 lunkwill42 self-assigned this Dec 19, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

1 participant