Query is in idle state and taking all the cpu resources of db

[ashokthangaraj]

Hi Team,

We use netbox as source of truth for all the internal applications and we started observing the api calls taking 2-4seconds for loading a devices/racks.when we look on the db side we found that there are queries which are in idle state for more than 3 hrs and also there is a continuos api call to db from netbox application to fetch all the ipam, device details.Can some one kindly help on what we missed here or how to close the transaction after the query completed.

Appliction server
3 vm's 8cpu/16GB Memory
DB:
1 vm 8cpu/16GB Memory
Redis:
1 VM 4cpu/8GB Memory

All the below queries are from netbox db user and source is from application server.

Sample query:
SELECT "ipam_ipaddress"."id", "ipam_ipaddress"."created", "ipam_ipaddress"."last_updated", "ipam_ipaddress"."custom_field_data", "ipam_ipaddress"."description", "ipam_ipaddress"."comments", "ipam_ipaddress"."address", "ipam_ipaddress"."vrf_id", "ipam_ipaddress"."tenant_id", "ipam_ipaddress"."status", "ipam_ipaddress"."role", "ipam_ipaddress"."assigned_object_type_id", "ipam_ipaddress"."assigned_object_id", "ipam_ipaddress"."nat_inside_id", "ipam_ipaddress"."dns_name" FROM "ipam_ipaddress" WHERE ( "ipam_ipaddress"."id" IN ( SELECT "ipam_ipaddress"."id" FROM "ipam_ipaddress" ) AND HOST("ipam_ipaddress"."address") IN ($1) ) ORDER BY INET(HOST("ipam_ipaddress"."address")) ASC LIMIT $2

24923 | 00:01:10.639227 | SELECT "extras_customfield"."id", "extras_customfield"."created", "extras_customfield"."last_updated", "extras_customfield"."type", "extra s_customfield"."object_type_id", "extras_customfield"."name", "extras_customfield"."label", "extras_customfield"."group_name", "extras_customfield"."description", "extras_cu stomfield"."required", "extras_customfield"."search_weight", "extras_customfield"."filter_logic", "extras_customfield"."default", "extras_customfield"."weight", "extras_cust omfield"."validation_minimum", "extras_customfield"."validation_maximum", "extras_customfield"."validation_regex", "extras_customfield"."choice_set_id", "extras_customfield" ."ui_visible", "extras_customfield"."ui_editable", "extras_customfield"."is_cloneable" FROM "extras_customfield" INNER JOIN "extras_customfield_content_types" ON ("extras_cu stomfield"."id" = "extras_customfield_content_types"."customfield_id") WHERE "extras_customfield_content_types"."contenttype_id" = 5 ORDER BY "extras_customfield"."group_nam e" ASC, "extras_cust
17698 | 00:01:00.214811 | SELECT "core_configrevision"."id", "core_configrevision"."created", "core_configrevision"."comment", "core_configrevision"."data" FROM "co re_configrevision" ORDER BY "core_configrevision"."created" ASC LIMIT 1
Thanks and Appreciate team help here.