Skip to content

External IP allocation query needs some TLC #1468

Open
@bnaecker

Description

@bnaecker

We added a "next-item" style query as part of #1298 to find the next available external IP address. This currently searches over all pools, and all ranges in those pools. Because of the implementation in CRDB, this is particularly bad for performance. Subqueries are fully-materialized and stored in memory before the surrounding query is run. So, if a user creates an IP Pool with an IPv6 /64 range, which is a pretty natural thing to do, the query will absolutely grind to a halt. The database doesn't appear to fall over now, but the query will basically never complete. Some of this is referred to in #1371.

Additionally, as part of #1458, we modified the query to now search all pools that are "available" to an instance, e.g., pools with a NULL project ID or with the project ID equal to the instance's project ID. That's fine, but we're also currently duplicating the project ID in the parent ip_pool table and the ip_pool_range table. That denormalizes the database a bit, but it's to avoid yet another join in the already-terrible query, which would be used to get the project ID from the parent table (joining on the ip_pool_range.ip_pool_id foreign-key into the ip_pool table). This was discussed here.

The query is here. It should just be...better...but it's not clear exactly how yet.

Metadata

Metadata

Assignees

No one assigned

    Labels

    databaseRelated to database access

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions