Querys increasing database load

[leo-scs]

Hey guys,

In the last few days I have noticed a large increase in CPU usage in the maas database and analyzing the queries executed, I see several queries running in my environment from time to time, the query is the one below:

SELECT maasserver_node . id, maasserver_node . created, maasserver_node . updated, maasserver_node . system_id, maasserver_node . hardware_uuid, maasserver_node . hostname, maasserver_node . description, maasserver_node . pool_id, maasserver_node . domain_id, maasserver_node . address_ttl, maasserver_node . status, maasserver_node . previous_status, maasserver_node . status_expires, maasserver_node . owner_id, maasserver_node . bios_boot_method, maasserver_node . osystem, maasserver_node . distro_series, maasserver_node . architecture, maasserver_node . min_hwe_kernel, maasserver_node . hwe_kernel, maasserver_node . node_type, maasserver_node . parent_id, maasserver_node . agent_name, maasserver_node . error_description, maasserver_node . zone_id, maasserver_node . cpu_count, maasserver_node . cpu_speed, maasserver_node . memory, maasserver_node . swap_size, maasserver_node . bmc_id, maasserver_node . instance_power_parameters, maasserver_node . power_state, maasserver_node . power_state_queried, maasserver_node . power_state_updated, maasserver_node . last_image_sync, maasserver_node . error, maasserver_node . netboot, maasserver_node . ephemeral_deploy, maasserver_node . license_key, maasserver_node . dynamic, maasserver_node . boot_interface_id, maasserver_node . boot_cluster_ip, maasserver_node . boot_disk_id, maasserver_node . gateway_link_ipv4_id, maasserver_node . gateway_link_ipv6_id, maasserver_node . default_user, maasserver_node . install_rackd, maasserver_node . install_kvm, maasserver_node . register_vmhost, maasserver_node . enable_ssh, maasserver_node . skip_networking, maasserver_node . skip_storage, maasserver_node . url, maasserver_node . dns_process_id, maasserver_node . managing_process_id, maasserver_node . current_commissioning_script_set_id, maasserver_node . current_installation_script_set_id, maasserver_node . current_testing_script_set_id, maasserver_node . locked, maasserver_node . last_applied_storage_layout, maasserver_node . current_config_id, maasserver_node . enable_hw_sync, maasserver_node . sync_interval, maasserver_node . last_sync
FROM maasserver_node
INNER JOIN maasserver_nodeconfig ON ( maasserver_node . current_config_id = ? . id )
INNER JOIN maasserver_interface ON ( maasserver_nodeconfig . id = ? . node_config_id )
INNER JOIN maasserver_interface_ip_addresses ON ( maasserver_interface . id = ? . interface_id )
INNER JOIN maasserver_staticipaddress ON ( maasserver_interface_ip_addresses . staticipaddress_id = ? . id )
WHERE maasserver_staticipaddress . ip = ? :: inet
ORDER BY maasserver_node . id ASC
LIMIT ?

And checking these queries it is looking for IPs that do not exist in my MaaS environment (not even the IP range)

Has anyone noticed this behavior and can help us with the debug?

[r00ta]

@leo-scs

And checking these queries it is looking for IPs that do not exist in my MaaS environment (not even the IP range)

can you be more specific? Can you clarify what are your subnets, your ip ranges and the IPs that are in the queries?

[leo-scs]

We have several subnets, around 3600, but the IPs that return in this query are not part of our network, below are some examples:

1662422 | maas | maasv2 | active | SELECT “maasserver_node”.“id”, “maasserver_node”.“created”, “maasserver_node”.“updated”, “maasserver_node”.“system_id”, “maasserver_node”.“hardware_uuid”, “maa
sserver_node”.“hostname”, “maasserver_node”.“description”, “maasserver_node”.“pool_id”, “maasserver_node”.“domain_id”, “maasserver_node”.“address_ttl”, “maasserver_node”.“status”, “maasserver_node”.“pre
vious_status”, “maasserver_node”.“status_expires”, “maasserver_node”.“owner_id”, “maasserver_node”.“bios_boot_method”, “maasserver_node”.“osystem”, “maasserver_node”.“distro_series”, “maasserver_node”."
architecture", “maasserver_node”.“min_hwe_kernel”, “maasserver_node”.“hwe_kernel”, “maasserver_node”.“node_type”, “maasserver_node”.“parent_id”, “maasserver_node”.“agent_name”, “maasserver_node”.“error_
description”, “maasserver_node”.“zone_id”, “maasserver_node”.“cpu_count”, “maasserver_node”.“cpu_speed”, “maasserver_node”.“memory”, “maasserver_node”.“swap_size”, “maasserver_node”.“bmc_id”, “maasserve
r_node”.“instance_power_parameters”, “maasserver_node”.“power_state”, “maasserver_node”.“power_state_queried”, “maasserver_node”.“power_state_updated”, “maasserver_node”.“last_image_sync”, “maasserver_node”.“error”, “maasserver_node”.“netboot”, “maasserver_node”.“ephemeral_deploy”, “maasserver_node”.“license_key”, “maasserver_node”.“dynamic”, “maasserver_node”.“boot_interface_id”, “maasserver_node”.“boot_cluster_ip”, “maasserver_node”.“boot_disk_id”, “maasserver_node”.“gateway_link_ipv4_id”, “maasserver_node”.“gateway_link_ipv6_id”, “maasserver_node”.“default_user”, “maasserver_node”.“install_rackd”, “maasserver_node”.“install_kvm”, “maasserver_node”.“register_vmhost”, “maasserver_node”.“enable_ssh”, “maasserver_node”.“skip_networking”, “maasserver_node”.“skip_storage”, “maasserver_node”.“url”, “maasserver_node”.“dns_process_id”, “maasserver_node”.“managing_process_id”, “maasserver_node”.“current_commissioning_script_set_id”, “maasserver_node”.“current_installation_script_set_id”, “maasserver_node”.“current_testing_script_set_id”, “maasserver_node”.“locked”, “maasserver_node”.“last_applied_storage_layout”, “maasserver_node”.“current_config_id”, “maasserver_node”.“enable_hw_sync”, “maasserver_node”.“sync_interval”, “maasserver_node”.“last_sync” FROM “maasserver_node” INNER JOIN “maasserver_nodeconfig” ON (“maasserver_node”.“current_config_id” = “maasserver_nodeconfig”.“id”) INNER JOIN “maasserver_interface” ON (“maasserver_nodeconfig”.“id” = “maasserver_interface”.“node_config_id”) INNER JOIN “maasserver_interface_ip_addresses” ON (“maasserver_interface”.“id” = “maasserver_interface_ip_addresses”.“interface_id”) INNER JOIN “maasserver_staticipaddress” ON (“maasserver_interface_ip_addresses”.“staticipaddress_id” = “maasserver_staticipaddress”.“id”) WHERE “maasserver_staticipaddress”.“ip” = ‘45.195.251.243’::inet ORDER BY “maasserver_node”.“id” ASC LIMIT 1 | 2024-06-26 13:43:46.507046+00 | 2024-06-26 13:43:46.507048+00

1658140 | maas | maasv2 | active | SELECT “maasserver_node”.“id”, “maasserver_node”.“created”, “maasserver_node”.“updated”, “maasserver_node”.“system_id”, “maasserver_node”.“hardware_uuid”, “maasserver_node”.“hostname”, “maasserver_node”.“description”, “maasserver_node”.“pool_id”, “maasserver_node”.“domain_id”, “maasserver_node”.“address_ttl”, “maasserver_node”.“status”, “maasserver_node”.“previous_status”, “maasserver_node”.“status_expires”, “maasserver_node”.“owner_id”, “maasserver_node”.“bios_boot_method”, “maasserver_node”.“osystem”, “maasserver_node”.“distro_series”, “maasserver_node”.“architecture”, “maasserver_node”.“min_hwe_kernel”, “maasserver_node”.“hwe_kernel”, “maasserver_node”.“node_type”, “maasserver_node”.“parent_id”, “maasserver_node”.“agent_name”, “maasserver_node”.“error_description”, “maasserver_node”.“zone_id”, “maasserver_node”.“cpu_count”, “maasserver_node”.“cpu_speed”, “maasserver_node”.“memory”, “maasserver_node”.“swap_size”, “maasserver_node”.“bmc_id”, “maasserver_node”.“instance_power_parameters”, “maasserver_node”.“power_state”, “maasserver_node”.“power_state_queried”, “maasserver_node”.“power_state_updated”, “maasserver_node”.“last_image_sync”, “maasserver_node”.“error”, “maasserver_node”.“netboot”, “maasserver_node”.“ephemeral_deploy”, “maasserver_node”.“license_key”, “maasserver_node”.“dynamic”, “maasserver_node”.“boot_interface_id”, “maasserver_node”.“boot_cluster_ip”, “maasserver_node”.“boot_disk_id”, “maasserver_node”.“gateway_link_ipv4_id”, “maasserver_node”.“gateway_link_ipv6_id”, “maasserver_node”.“default_user”, “maasserver_node”.“install_rackd”, “maasserver_node”.“install_kvm”, “maasserver_node”.“register_vmhost”, “maasserver_node”.“enable_ssh”, “maasserver_node”.“skip_networking”, “maasserver_node”.“skip_storage”, “maasserver_node”.“url”, “maasserver_node”.“dns_process_id”, “maasserver_node”.“managing_process_id”, “maasserver_node”.“current_commissioning_script_set_id”, “maasserver_node”.“current_installation_script_set_id”, “maasserver_node”.“current_testing_script_set_id”, “maasserver_node”.“locked”, “maasserver_node”.“last_applied_storage_layout”, “maasserver_node”.“current_config_id”, “maasserver_node”.“enable_hw_sync”, “maasserver_node”.“sync_interval”, “maasserver_node”.“last_sync” FROM “maasserver_node” INNER JOIN “maasserver_nodeconfig” ON (“maasserver_node”.“current_config_id” = “maasserver_nodeconfig”.“id”) INNER JOIN “maasserver_interface” ON (“maasserver_nodeconfig”.“id” = “maasserver_interface”.“node_config_id”) INNER JOIN “maasserver_interface_ip_addresses” ON (“maasserver_interface”.“id” = “maasserver_interface_ip_addresses”.“interface_id”) INNER JOIN “maasserver_staticipaddress” ON (“maasserver_interface_ip_addresses”.“staticipaddress_id” = “maasserver_staticipaddress”.“id”) WHERE “maasserver_staticipaddress”.“ip” = ‘43.249.28.206’::inet ORDER BY “maasserver_node”.“id” ASC LIMIT 1 | 2024-06-26 13:43:46.511954+00 | 2024-06-26 13:43:46.511956+00

1659445 | maas | maasv2 | active | SELECT “maasserver_node”.“id”, “maasserver_node”.“created”, “maasserver_node”.“updated”, “maasserver_node”.“system_id”, “maasserver_node”.“hardware_uuid”, “maasserver_node”.“hostname”, “maasserver_node”.“description”, “maasserver_node”.“pool_id”, “maasserver_node”.“domain_id”, “maasserver_node”.“address_ttl”, “maasserver_node”.“status”, “maasserver_node”.“previous_status”, “maasserver_node”.“status_expires”, “maasserver_node”.“owner_id”, “maasserver_node”.“bios_boot_method”, “maasserver_node”.“osystem”, “maasserver_node”.“distro_series”, “maasserver_node”.“architecture”, “maasserver_node”.“min_hwe_kernel”, “maasserver_node”.“hwe_kernel”, “maasserver_node”.“node_type”, “maasserver_node”.“parent_id”, “maasserver_node”.“agent_name”, “maasserver_node”.“error_description”, “maasserver_node”.“zone_id”, “maasserver_node”.“cpu_count”, “maasserver_node”.“cpu_speed”, “maasserver_node”.“memory”, “maasserver_node”.“swap_size”, “maasserver_node”.“bmc_id”, “maasserver_node”.“instance_power_parameters”, “maasserver_node”.“power_state”, “maasserver_node”.“power_state_queried”, “maasserver_node”.“power_state_updated”, “maasserver_node”.“last_image_sync”, “maasserver_node”.“error”, “maasserver_node”.“netboot”, “maasserver_node”.“ephemeral_deploy”, “maasserver_node”.“license_key”, “maasserver_node”.“dynamic”, “maasserver_node”.“boot_interface_id”, “maasserver_node”.“boot_cluster_ip”, “maasserver_node”.“boot_disk_id”, “maasserver_node”.“gateway_link_ipv4_id”, “maasserver_node”.“gateway_link_ipv6_id”, “maasserver_node”.“default_user”, “maasserver_node”.“install_rackd”, “maasserver_node”.“install_kvm”, “maasserver_node”.“register_vmhost”, “maasserver_node”.“enable_ssh”, “maasserver_node”.“skip_networking”, “maasserver_node”.“skip_storage”, “maasserver_node”.“url”, “maasserver_node”.“dns_process_id”, “maasserver_node”.“managing_process_id”, “maasserver_node”.“current_commissioning_script_set_id”, “maasserver_node”.“current_installation_script_set_id”, “maasserver_node”.“current_testing_script_set_id”, “maasserver_node”.“locked”, “maasserver_node”.“last_applied_storage_layout”, “maasserver_node”.“current_config_id”, “maasserver_node”.“enable_hw_sync”, “maasserver_node”.“sync_interval”, “maasserver_node”.“last_sync” FROM “maasserver_node” INNER JOIN “maasserver_nodeconfig” ON (“maasserver_node”.“current_config_id” = “maasserver_nodeconfig”.“id”) INNER JOIN “maasserver_interface” ON (“maasserver_nodeconfig”.“id” = “maasserver_interface”.“node_config_id”) INNER JOIN “maasserver_interface_ip_addresses” ON (“maasserver_interface”.“id” = “maasserver_interface_ip_addresses”.“interface_id”) INNER JOIN “maasserver_staticipaddress” ON (“maasserver_interface_ip_addresses”.“staticipaddress_id” = “maasserver_staticipaddress”.“id”) WHERE “maasserver_staticipaddress”.“ip” = ‘43.249.30.29’::inet ORDER BY “maasserver_node”.“id” ASC LIMIT 1 | 2024-06-26 13:43:46.512768+00 | 2024-06-26 13:43:46.51277+00

1482867 | maas | maasv2 | active | SELECT “maasserver_node”.“id”, “maasserver_node”.“created”, “maasserver_node”.“updated”, “maasserver_node”.“system_id”, “maasserver_node”.“hardware_uuid”, “maasserver_node”.“hostname”, “maasserver_node”.“description”, “maasserver_node”.“pool_id”, “maasserver_node”.“domain_id”, “maasserver_node”.“address_ttl”, “maasserver_node”.“status”, “maasserver_node”.“previous_status”, “maasserver_node”.“status_expires”, “maasserver_node”.“owner_id”, “maasserver_node”.“bios_boot_method”, “maasserver_node”.“osystem”, “maasserver_node”.“distro_series”, “maasserver_node”.“architecture”, “maasserver_node”.“min_hwe_kernel”, “maasserver_node”.“hwe_kernel”, “maasserver_node”.“node_type”, “maasserver_node”.“parent_id”, “maasserver_node”.“agent_name”, “maasserver_node”.“error_description”, “maasserver_node”.“zone_id”, “maasserver_node”.“cpu_count”, “maasserver_node”.“cpu_speed”, “maasserver_node”.“memory”, “maasserver_node”.“swap_size”, “maasserver_node”.“bmc_id”, “maasserver_node”.“instance_power_parameters”, “maasserver_node”.“power_state”, “maasserver_node”.“power_state_queried”, “maasserver_node”.“power_state_updated”, “maasserver_node”.“last_image_sync”, “maasserver_node”.“error”, “maasserver_node”.“netboot”, “maasserver_node”.“ephemeral_deploy”, “maasserver_node”.“license_key”, “maasserver_node”.“dynamic”, “maasserver_node”.“boot_interface_id”, “maasserver_node”.“boot_cluster_ip”, “maasserver_node”.“boot_disk_id”, “maasserver_node”.“gateway_link_ipv4_id”, “maasserver_node”.“gateway_link_ipv6_id”, “maasserver_node”.“default_user”, “maasserver_node”.“install_rackd”, “maasserver_node”.“install_kvm”, “maasserver_node”.“register_vmhost”, “maasserver_node”.“enable_ssh”, “maasserver_node”.“skip_networking”, “maasserver_node”.“skip_storage”, “maasserver_node”.“url”, “maasserver_node”.“dns_process_id”, “maasserver_node”.“managing_process_id”, “maasserver_node”.“current_commissioning_script_set_id”, “maasserver_node”.“current_installation_script_set_id”, “maasserver_node”.“current_testing_script_set_id”, “maasserver_node”.“locked”, “maasserver_node”.“last_applied_storage_layout”, “maasserver_node”.“current_config_id”, “maasserver_node”.“enable_hw_sync”, “maasserver_node”.“sync_interval”, “maasserver_node”.“last_sync” FROM “maasserver_node” INNER JOIN “maasserver_nodeconfig” ON (“maasserver_node”.“current_config_id” = “maasserver_nodeconfig”.“id”) INNER JOIN “maasserver_interface” ON (“maasserver_nodeconfig”.“id” = “maasserver_interface”.“node_config_id”) INNER JOIN “maasserver_interface_ip_addresses” ON (“maasserver_interface”.“id” = “maasserver_interface_ip_addresses”.“interface_id”) INNER JOIN “maasserver_staticipaddress” ON (“maasserver_interface_ip_addresses”.“staticipaddress_id” = “maasserver_staticipaddress”.“id”) WHERE “maasserver_staticipaddress”.“ip” = ‘45.112.205.102’::inet ORDER BY “maasserver_node”.“id” ASC LIMIT 1 | 2024-06-26 12:17:00.484634+00 | 2024-06-26 12:17:00.484636+00

1484792 | maas | maasv2 | active | SELECT “maasserver_node”.“id”, “maasserver_node”.“created”, “maasserver_node”.“updated”, “maasserver_node”.“system_id”, “maasserver_node”.“hardware_uuid”, “maa
sserver_node”.“hostname”, “maasserver_node”.“description”, “maasserver_node”.“pool_id”, “maasserver_node”.“domain_id”, “maasserver_node”.“address_ttl”, “maasserver_node”.“status”, “maasserver_node”.“pre
vious_status”, “maasserver_node”.“status_expires”, “maasserver_node”.“owner_id”, “maasserver_node”.“bios_boot_method”, “maasserver_node”.“osystem”, “maasserver_node”.“distro_series”, “maasserver_node”."
architecture", “maasserver_node”.“min_hwe_kernel”, “maasserver_node”.“hwe_kernel”, “maasserver_node”.“node_type”, “maasserver_node”.“parent_id”, “maasserver_node”.“agent_name”, “maasserver_node”.“error_
description”, “maasserver_node”.“zone_id”, “maasserver_node”.“cpu_count”, “maasserver_node”.“cpu_speed”, “maasserver_node”.“memory”, “maasserver_node”.“swap_size”, “maasserver_node”.“bmc_id”, “maasserve
r_node”.“instance_power_parameters”, “maasserver_node”.“power_state”, “maasserver_node”.“power_state_queried”, “maasserver_node”.“power_state_updated”, “maasserver_node”.“last_image_sync”, “maasserver_n
ode”.“error”, “maasserver_node”.“netboot”, “maasserver_node”.“ephemeral_deploy”, “maasserver_node”.“license_key”, “maasserver_node”.“dynamic”, “maasserver_node”.“boot_interface_id”, “maasserver_node”.“b
oot_cluster_ip”, “maasserver_node”.“boot_disk_id”, “maasserver_node”.“gateway_link_ipv4_id”, “maasserver_node”.“gateway_link_ipv6_id”, “maasserver_node”.“default_user”, “maasserver_node”.“install_rackd”
, “maasserver_node”.“install_kvm”, “maasserver_node”.“register_vmhost”, “maasserver_node”.“enable_ssh”, “maasserver_node”.“skip_networking”, “maasserver_node”.“skip_storage”, “maasserver_node”.“url”, “m
aasserver_node”.“dns_process_id”, “maasserver_node”.“managing_process_id”, “maasserver_node”.“current_commissioning_script_set_id”, “maasserver_node”.“current_installation_script_set_id”, “maasserver_no
de”.“current_testing_script_set_id”, “maasserver_node”.“locked”, “maasserver_node”.“last_applied_storage_layout”, “maasserver_node”.“current_config_id”, “maasserver_node”.“enable_hw_sync”, “maasserver_node”.“sync_interval”, “maasserver_node”.“last_sync” FROM “maasserver_node” INNER JOIN “maasserver_nodeconfig” ON (“maasserver_node”.“current_config_id” = “maasserver_nodeconfig”.“id”) INNER JOIN “maasserver_interface” ON (“maasserver_nodeconfig”.“id” = “maasserver_interface”.“node_config_id”) INNER JOIN “maasserver_interface_ip_addresses” ON (“maasserver_interface”.“id” = “maasserver_interface_ip_addresses”.“interface_id”) INNER JOIN “maasserver_staticipaddress” ON (“maasserver_interface_ip_addresses”.“staticipaddress_id” = “maasserver_staticipaddress”.“id”) WHERE “maasserver_staticipaddress”.“ip” = ‘45.195.251.12’::inet ORDER BY “maasserver_node”.“id” ASC LIMIT 1 | 2024-06-26 12:17:00.481874+00 | 2024-06-26 12:17:00.481875+00

So much so that if we run the queries manually in the database, they return empty.

[r00ta]

is there any possibility that you actually had a subnet containing that IP that was then deleted? Can you like query your DB and run

select * from maasserver_staticipaddress where ip='45.195.251.12';

?

[leo-scs]

@r00ta

is there any possibility that you actually had a subnet containing that IP that was then deleted? Can you like query your DB and run

I’ve already run the queries looking for IPs, but they all return empty

select * from maasserver_staticipaddress where ip='45.195.251.12';
 id | created | updated | ip | alloc_type | subnet_id | user_id | lease_time | temp_expires_on 
----+---------+---------+----+------------+-----------+---------+------------+-----------------
(0 rows)

[r00ta]

select * from maasserver_neighbour where ip='45.195.251.12';

?

[leo-scs]

@r00ta

select * from maasserver_neighbour where ip='45.195.251.12';

empty too

maasv2=# select * from maasserver_neighbour where ip='45.195.251.12';
 id | created | updated | ip | time | vid | count | mac_address | interface_id 
----+---------+---------+----+------+-----+-------+-------------+--------------
(0 rows)

[r00ta]

Without access to the environment I’m out of ideas. If you are lucky somebody else in the community has already faced it or has some other ideas

[leo-scs]

In this post below I saw that there are logs of the query we are facing here in our environment, but even deleting the empty fabrics, we still have performance problems in the database

Postgresql runs 99% cpu, maas ui/api hangs

Hi MAAS community, i run the following installation $ snap list maas Name Version Rev Tracking Publisher Notes maas 3.4.0-14321-g.1027c7664 32469 3.4/stable canonical✓ - few days ago i have upgrade MAAS from 3.3 to 3.4 using channel stable, since then MAAS behave slowly UI and API are slow to respond, i have notice the following process consuming most CPU top - 14:42:26 up 3:13, 1 user, load average: 3.83, 3.92, 4.05 Tasks: 519 total, 5 running, 513 sle…

[r00ta]

A bug that impacts performances has been recently spotted Bug #2070304 “regiond at 100% CPU after UI reconnect causing API...” : Bugs : MAAS . Maybe you are in a similar situation

[leo-scs]

I saw that there was a fix in the 3.4 backport, we are using 3.4.0, do we have to do something on our side? update version?

[r00ta]

If you are still on 3.4.0 you might be impacted by !Action required: this cleanup might speedup your MAAS (3.4.0 and below) environments .

The fix for it is included from 3.4.1 onwards. You can still cleanup your 3.4.0 env with the steps described in that post

[leo-scs]

We upgraded to 3.4.1 a few months ago and rolled back to 3.4.0 because it caused several problems, so this query to clean this data in the database was already executed in the update

[r00ta]

if I may ask, what was the problems you were facing in 3.4.1 that are not present in 3.4.0?

[leo-scs]

We had this problem of high CPU usage by regiond and postgres, and as maas was not stable, we decided to roll back to the previous version that was, but now it started showing this query behavior

[leo-scs]

So do we need to take any action? because the problem is still happening from time to time as you can see in the screenshot below.