Bug #20929
closedHuge table "fact_names" makes Foreman crawl
Description
OS: CentOS 7.3
Foreman: 1.15.3
salt-master: 2017.7.1 (Nitrogen)
salt smart proxy: 2.1.8
Scenario:
We have a few docker hosts that spawn lots and lots of short lived docker containers. Each docker container create a veth<random string> interface on linux.
Everytime when "salt '*' state.highstate test.true" runs from cron all these interfaces are added to the "fact_names" and "fact_values" tables in the foreman postgres DB.
Eventually these tables get bloated with thousands and thousands of interfaces that are long gone. (at one point >500.000 network interfaces).
This will result in the behavior that the command "/usr/bin/foreman-node <some-node>" will take >30 seconds to return with data, which in it's turn will cause to lock-up all the salt-master's worker threads which in it's turn will cause foreman to not function.
The query which is responsible for this long delay is:
SELECT MAX("fact_names"."id") AS maximum_id, "fact_names"."name" AS fact_names_name FROM "fact_names" WHERE "fact_names"."type" IN ('ForemanSalt::FactName') GROUP BY "fact_names"."name" ORDER BY fact_names.name;
Workaround:
Now i regulary clear the unnessecary data from the "fact_names" and "fact_values" with the queries:
delete from fact_values where fact_name_id in ( select id from fact_names where name like '%interfaces::veth%' ) ; delete from fact_names where name like '%interfaces::veth%'; delete from fact_values where fact_name_id in ( select id from fact_names where name like '%interfaces::br-%' ) ; delete from fact_names where name like '%interfaces::br-%'; delete from fact_values where fact_name_id in ( select id from fact_names where name like '%interfaces::docker%' ) ; delete from fact_names where name like '%interfaces::docker%';
What I think what should happen:
I think that Foreman should not save those interfaces in "fact_names" / "fact_values" according to the setting:
"Administer -> Settings -> Provisioning -> 'Ignore interfaces with matching identifier'".
This might be related to bug #20927 however the patch mentioned in that issue does not resolve the issue for the tables "fact_names" / "fact_values".