Nautobot Mysql timeout

[rithikyennam]

Hi,

We are using planning to use Nautbot for managing subnets we initially tested it with 200 subnets and it was working fine.

However, it is now loaded with 12000 subnets(perfixes tab) and each prefix has 5 custom_fields. After loading it we are unable to open the prefixes tab in Nutobot, the request is getting timed out.

Upon further analysis, we found out that there was an issue with the database.
We found out that Nautobot is running very expensive queries to pull the subnets.

To pull data for the 12,000 prefixes, it is examining(299207003) rows in the prefix table and hence the request is timing out because of this expensive query.

Did anyone face this issue or is anyone aware of any solution on how to resolve this ?
I have attached the query details below

This is the query:
Time: 2023-04-20T14:59:53.759852Z
User@Host: nautobot[nautobot] @ localhost [] Id: 9

Query_time: 118.651131 Lock_time: 0.000032 Rows_sent: 50 Rows_examined: 299207003

SET timestamp=1682002675;

SELECT ipam_prefix.id, ipam_prefix.created, ipam_prefix.last_updated, ipam_prefix._custom_field_data, ipam_prefix.status_id, ipam_prefix.network, ipam_prefix.broadcast, ipam_prefix.prefix_length, ipam_prefix.site_id, ipam_prefix.location_id, ipam_prefix.vrf_id, ipam_prefix.tenant_id, ipam_prefix.vlan_id, ipam_prefix.role_id, ipam_prefix.is_pool, ipam_prefix.description, (SELECT COUNT() AS count FROM ipam_prefix U0 WHERE (U0.prefix_length < ipam_prefix.prefix_length AND U0.network <= ipam_prefix.network AND U0.broadcast >= ipam_prefix.broadcast AND COALESCE(U0.vrf_id, '26b193e1e73a4769a32e116a060a0ad6') = COALESCE(ipam_prefix.vrf_id, '26b193e1e73a4769a32e116a060a0ad6')) LIMIT 1) AS parents, (SELECT COUNT() AS count FROM ipam_prefix U0 WHERE (U0.prefix_length > ipam_prefix.prefix_length AND U0.network >= ipam_prefix.network AND U0.broadcast <= ipam_prefix.broadcast AND COALESCE(U0.vrf_id, '26b193e1e73a4769a32e116a060a0ad6') = COALESCE(ipam_prefix.vrf_id, '26b193e1e73a4769a32e116a060a0ad6')) LIMIT 1) AS children FROM ipam_prefix LEFT OUTER JOIN ipam_vrf ON (ipam_prefix.vrf_id = ipam_vrf.id) ORDER BY ipam_vrf.name ASC, ipam_prefix.network ASC, ipam_prefix.prefix_length ASC LIMIT 50;

[glennmatthews]

Performance in this area will be improved substantially in the coming 2.0 release. In the meantime you probably want to use the DISABLE_PREFIX_LIST_HIERARCHY setting.

[rithikyennam]

Thanks Glen, it worked