Long-running Select When Expanding Prefix

[daneb]

Symptom:
We recently upgraded to 0.29.5.
After logging into Nipap and trying to expand the Prefix (eg. X.72.128.0/19), Nipap blocks for a really long time.

On the host I can see a long running SELECT generated in the OS Process List. Automatically stopping and starting Postgres resolves this (obviously). We are currently looking to simulate the process in our staging environment and will update this issue as we go.

If we do need to expand the prefix, we search for it through the UI, and then expand (+) works.

The Impact:
Unable to large expand prefixes.

[plajjan]

Can you please quantify how large is a large prefix? Is x.72.128.0/19 a reservation? How many children does it have?

[daneb]

@plajjan It is a reservation. In terms of children, 70 or so. There are about 500 assignments (interspersed with some reservations). Replicating this on Demo maybe slightly hard unless we could inject a similar tree structure via the DB. Hopefully our staging environment will be up soon.

This seems to be the long running query (not pasting all of it, but will on request):

SELECT
                id,
                vrf_id,
                vrf_rt,
                vrf_name,
                family,
                display,
                match,
                prefix,
                prefix_length,
                display_prefix::text AS display_prefix,
                description,
                comment,
                inherited_tags,
                tags,
                node,
                pool_id,
                pool_name,
                type,
                status,
                indent,
                country,
                order_id,
                customer_id,
                external_key,
                authoritative_source,
                alarm_priority,
                monitor,
                vlan,
                added,
                last_modified,
                children,
                total_addresses,
                used_addresses,
                free_addresses,
                avps,
                expires
            FROM (
                SELECT DISTINCT ON(vrf_rt_order(vrf.rt), p1.prefix) p1.id,
                                                                                                                                                                235,10-17     75%
            FROM (
                SELECT DISTINCT ON(vrf_rt_order(vrf.rt), p1.prefix) p1.id,
                    p1.prefix,
                    p1.display_prefix,
                    p1.description,
                    p1.comment,
                    COALESCE(p1.inherited_tags, '{}') AS inherited_tags,
                    COALESCE(p1.tags, '{}') AS tags,
                    p1.node,
                    pool.id AS pool_id,
                    pool.name AS pool_name,
                    p1.type,
                    p1.indent,
                    p1.country,
                    p1.order_id,
                    p1.customer_id,
                    p1.external_key,
                    p1.authoritative_source,
                    p1.alarm_priority,
                    p1.monitor,
                    p1.vlan,
                    p1.added,
                    p1.last_modified,
                    p1.children,
                    p1.total_addresses,
                    p1.used_addresses,
                    p1.free_addresses,
                    p1.status,
                    p1.avps,
                    p1.expires,
                    vrf.id AS vrf_id,
                    vrf.rt AS vrf_rt,
                    vrf.name AS vrf_name,
                    masklen(p1.prefix) AS prefix_length,
                    family(p1.prefix) AS family,
                    ((p1.prefix << p2.display_prefix OR p2.prefix <<= p1.prefix ) OR (p2.prefix >>= p1.prefix AND p1.indent BETWEEN p2.indent AND p2.indent + 1)) AS display,
                    CASE WHEN p1.prefix = p2.prefix THEN true ELSE false END AS match
                    FROM ip_net_plan AS p1
                    -- possible set LEFT OUTER JOIN, if we are doing a parent_prefix operation
                    LEFT OUTER JOIN ip_net_plan AS p2 ON
                    (
                        (
                            (p1.vrf_id = p2.vrf_id)
                            AND
                            (

[daneb]

Aloha

I've resolved this by performance tuning Postgres.

Two things were changed:

1. Shared Buffers - Increased this from 128MB to 4GB.
2. Work Mem - Increased this from 4MB to 20MB.