Problem:
When checking the code for 1.18 - the following indexes “should” be created (i think): https://github.com/theforeman/foreman/blob/1.18-stable/db/migrate/20090920043521_add_index_to_host.rb
When we upgraded - we followed the upgrade directions and had no rake task failures or other step failures, however the following indexes are not present in our current database:
add_index “hosts”, “puppet_status”
add_index “hosts”, :domain_id, :name => ‘host_domain_id_ix’
Other than creating them manually - what can i run to “retry” that file/code/upgrade step, and ideally create the proper indexes?
Expected outcome:
Indexes defined in code are created upon upgrade/rake task execution.
Foreman and Proxy versions:
1.18.2
This is a very old migration (from 2009), if you follow all of the migrations you will find that the database structure has changed quite a bit since then. Specifically the columns you are referring to:
- domain_id has been moved to the nics table, allowing a host to have multiple interfaces in different domains.
- puppet_status has been extracted to the host_status table, which allows multiple types of statuses.
Hey @tbrisker
Thanks for the info. TBH, I should have caught that based on the file date.
This stems from investigation we were doing yesterday regarding high access times around the taxable_taxonomies table - which had grown to 14 million row(s) with 90 day audit retention.
We found that enabling “set Foreman NIC data from fact uploads” was creating churn. On machines with multiple NIC’s - they come in in a random order each puppet run, which Foreman doesn’t (seem to) account for with a unique identifier - such as MAC address. I was seeing Audit(s) around foreman NIC’s swapping IP’s and other data every 30 minutes, and each of these was generating 1 or more audits, and therefore one or more entries in taxable_taxonomies. We have flipped this back off for the time being while we investigate further to determine if this is an issue on our end or if a PR is needed.
As the table grew - we saw several common/frequent queries requiring full table scan(s) of this now 14 million row table due to lack of indexes (on mysql). Our DBA’s added an index (the reverse of an existing one) and our DB utilization on a 16 core mariadb 10.2 server dropped from 1500-1600% down to 50-100%
I think we are submitting a PR shortly to add this additional index to the taxable_taxonomies table. While it “slightly” reduces write performance with an additional index, and it only really causes issues in larger installations (where you end up with millions of records in that table) it helped us immensely with this specific frequent query, so we will be advocating it be added as we believe the benefits outweigh the potential con.
1 Like
Sounds like a very worthy contribution, thank you for the investigation!
Regarding the nics, you have a setting called Ignore interfaces with matching identifier which you can use to skip fact parsing for interfaces you do not care about - such as virtual interfaces on container hosts.
