Cleanup of MaaS database tables

Performance
1

I have a MaaS environment that was instantiated approximately three years ago, and has been fairly consistently upgraded during that time. It is now running 3.7.2 and has approximately 300 machines being managed.

The performance of this environment has been steadily getting worse over time, however. One of my teammates found this fantastic guide for cleaning up the database: https://maas.r00ta.com/troubleshooting/performance/index.html

We are in the process of paring down the maasserver_event table using the query that is described in the page, but were curious about the other two optimizations that are suggested. Is there a recommended method for cleaning up the discoveries as well as the DNS and IP address tables? I tried to clean up the DNS and IP addresses in a sandbox MaaS environment, but I ended up with a nonfunctional environment that refused to deploy anything because it couldn’t assign IP addresses.

Thank you in advance for your help!

2

Hey there,

In general without more context on what you are trying to clean up it’s hard to suggest a procedure. From MAAS 3.6 orphan records are cleaned up automatically at every startup, if you are trying to delete other records there is high probability that you break everything

3

Hi! Thank you very much for the quick reply. I suppose the most accurate answer is that I’m not entirely sure what precisely I’m trying to clean.

One of my teammates found your “Troubleshooting → Performance” page includes a fantastic set of instructions for cleaning up the maasserver_event table - which appears to need quite a bit of cleanup based on the row count:

maas=# select count(*) from maasserver_event;
   count   
-----------
 123203744
(1 row)

This table appears to consume the vast majority of the space in our database - to the tune of over 30 GiB collected over three years.

The page also references the need to clean up “discoveries” entries as well as “DNS and IP Addresses”. Is the discovery cleanup achieved by the following command obtained from this Discourse article?

maas $PROFILE discoveries clear all=true

If not - what discoveries are you recommending periodically cleaning, and how would I go about performing this cleanup? Or, is this cleanup no longer needed in MaaS 3.7.2?

The “DNS and IP Addresses” cleanup seems somewhat relevant to my environment based on the output of these SQL queries authored by somebody with a far higher level of SQL proficiency than I have:

maas=# select dr.name, count(dr.name) cnt from maasserver_staticipaddress a inner join maasserver_dnsresource_ip_addresses d on a.id=d.staticipaddress_id inner join maasserver_dnsresource dr on d.dnsresource_id=dr.id group by dr.name order by cnt desc limit 20;
     name     |  cnt
--------------+-------
 ubuntu       | 34613
 dc9f7f2e6b91 |  2767
 vm-38-13623  |  1545
 vm-34-13623  |  1418
 vm-39-13380  |  1417
 vm-38-13467  |  1395
 vm-36-13467  |  1381
 vm-38-13380  |  1347
 vm-37-13623  |  1303
 vm-40-13380  |  1296
 vm-39-13467  |  1270
 vm-35-13467  |  1230
 vm-34-13380  |  1195
 vm-34-13467  |  1177
 vm-33-13467  |  1149
 vm-40-13467  |  1140
 vm-32-13623  |  1089
 vm-37-13467  |  1088
 vm-32-13380  |  1055
 vm-39-13623  |  1043
(20 rows)

maas=# select ip, count(alloc_type) as cnt from maasserver_staticipaddress group by ip order by cnt desc limit 30;
      ip       |  cnt
---------------+--------
               | 248775
 172.21.66.179 |      2
 172.21.66.178 |      2
 172.21.66.195 |      2
 172.21.66.173 |      2
 172.21.66.169 |      2
 172.21.66.185 |      2
 172.21.66.183 |      2
 172.21.66.253 |      2
 172.21.66.194 |      2
 172.21.66.180 |      2
 172.21.66.19  |      1
 172.21.66.12  |      1
 172.21.66.20  |      1
 172.21.66.18  |      1
 172.21.66.17  |      1
 172.21.66.11  |      1
 172.21.66.6   |      1
 172.21.66.28  |      1
 172.21.66.16  |      1
 172.21.66.27  |      1
 172.21.66.29  |      1
 172.21.66.30  |      1
 172.21.66.31  |      1
 172.21.66.32  |      1
 172.21.66.33  |      1
 172.21.66.34  |      1
 172.21.66.35  |      1
 172.21.66.36  |      1
 172.21.66.26  |      1
(30 rows)

We have fewer than 300 machines in our environment, so it seems that we have some extra entries kicking around if we have thousands of references to some DNS names, and if we have 248,775 references that have no IP address. Are these records orphaned, or would I need to provide additional information to make that determination?

I do apologize if I’m not providing sufficient context, and I appreciate your help and patience in guiding me as I learn this system.

4

It may be helpful to understand that about four or so months ago, we realized that machines were unable to get IP addresses because the DHCP server was spending too much time restarting due to Bug #2149832 “Parallel deployments keeps restarting DHCP, causin...” : Bugs : MAAS. As a workaround, we changed the network interfaces of these machines from “Automatic” to “Dynamic” and let DHCP configure the IP address without requiring MaaS to update the dhcpd.conf file and restart the server.

This configuration ran beautifully for at least a month, until we started to see instability such as slow responses to machine list queries as well as 499 errors (NGINX’s code for Client Disconnect) when machines attempting to PXE boot were attempting and failing to read cloud init and other PXE boot resources. Note that the 499 seems to indicate that the client gave up and closed the connection, implying the server simply hadn’t yet responded within the approximately 10 second window that the client was apparently willing to wait.

We have since switched back to “Automatic” network interface configuration in an attempt to stop what seemed to be the growth of null IP address entries in the maasserver_staticipaddress table, though I’m sorry to say we haven’t carefully tracked this growth to see if this latest change had the desired effect.

I hope this background provides some helpful context. As I previously mentioned, I’d be very happy to provide any additional information that would be helpful in determining the best course of action (if any) to take to address what seems to be a number of seemingly orphaned database entries.

5

For the discoveries yes, the cli command you wrote is the correct one.

As for the dns/ip cleanup, !Action required: this cleanup might speedup your MAAS (3.4.0 and below) environments is the underlying problem and cleanup procedure that is executed at every startup (you can just restart the snap to execute it). Probably we should execute it every day instead of at every restart.

6

TL;DR:

  1. Thank you!
  2. MaaS appears to leak maasserver_staticipaddress rows when a machine’s interface is set to Dynamic and the machine is released, the DHCP lease expires, and then the machine is deployed again.

Again, thank you very much! I read through the topic that you referenced, and indeed the count of rows that would be cleared is now zero. However, I still see this count of static IP address rows:

maas=# select alloc_type, count(alloc_type) from maasserver_staticipaddress  group by alloc_type;
 alloc_type | count  
------------+--------
          0 |    262
          1 |     49
          4 |      2
          5 |      2
          6 | 248710
(5 rows)

It appears that the vast majority of these IP address entries have no IP address at all:

maas=# select count(id) from maasserver_staticipaddress where ip is null;
 count  
--------
 248869
(1 row)

With a LOT of help from some others with a LOT more SQL experience than I have, I stumbled across this query which lists the number of maasserver_interface_ip_addresses rows associated with specific interface IDs, sorted by the highest count:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_node node on node.boot_interface_id = iface.id
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count
-----+-------------+-------
 695 | VM-38-13623 |  1808
 515 | VM-39-13380 |  1663
 462 | VM-38-13467 |  1582
 691 | VM-34-13623 |  1579
 514 | VM-38-13380 |  1579
 171 | vm-06-12578 |  1572
 460 | VM-36-13467 |  1562
 694 | VM-37-13623 |  1538
 510 | VM-34-13380 |  1535
 178 | vm-13-12578 |  1521
 207 | vm-01-12574 |  1500
 516 | VM-40-13380 |  1496
 265 | vm-21-12577 |  1490
 463 | VM-39-13467 |  1482
 459 | VM-35-13467 |  1465
 274 | vm-03-12575 |  1426
 223 | vm-17-12574 |  1402
 251 | vm-07-12577 |  1393
 457 | VM-33-13467 |  1393
 193 | vm-11-12579 |  1380
(20 rows)

I ran an experiment with one particular machine whose network interface is configured with an Automatic address assignment. When that machine was initially in the Ready state, it had 1216 static IP address associations:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id                      
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

All of these static IP addresses rows have null IP address fields, however:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id and static.ip is null
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

Deploying the machine resulted in one of the static IP address rows being assigned an IP address:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id                      
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id and static.ip is null
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1215
(1 row)

Releasing this machine did not change the total count of static IP address entries, but the IP address field of each one of them returned to null:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id                      
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id and static.ip is null
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

Here’s where things get really interesting… I changed the interface from Automatic to Dynamic which prevents the DHCP server from restarting after the dhcpd.conf file is updated:

image
image771×220 13.7 KB

Then I deployed the machine again - and the total number of IP addresses grew by one:

maas=# select iface.id, node.hostname, count(rel.interface_id)                                                                              
from maasserver_interface_ip_addresses rel                                                                                                  
join maasserver_interface iface on iface.id = rel.interface_id                                                                              
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id                                                                
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'                                            
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1217
(1 row)

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id and static.ip is null
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

Releasing the machine does not appear to have released the IP address until the DHCP lease expires:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id                      
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1217
(1 row)

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id and static.ip is null
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1216
(1 row)

If this machine is redeployed before the DHCP lease expires, it is assigned the same IP address and the row in the database is “reused.” However if the DHCP lease does expire before the node is redeployed, the static IP address count increases by one:

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id                      
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1218
(1 row)

maas=# select iface.id, node.hostname, count(rel.interface_id)
from maasserver_interface_ip_addresses rel
join maasserver_interface iface on iface.id = rel.interface_id
join maasserver_staticipaddress static on static.id = rel.staticipaddress_id and static.ip is null
join maasserver_node node on node.boot_interface_id = iface.id and node.hostname = 'vm-04-12578'
group by iface.id, node.hostname
order by count(rel.interface_id) desc
limit 20;
 id  |  hostname   | count 
-----+-------------+-------
 169 | vm-04-12578 |  1217
(1 row)