Actions
Bug #8912
closed
Foreman 1.7.1 Host/Nic seems to cause extra database load
Status:
Resolved
Priority:
High
Assignee:
-
Category:
Performance
Target version:
-
Description
Since upgrading to Foreman 1.7.1 last Wednesday, we have experienced 200% additional database load. We increased our database server to 8 vcpus from 4.
Additionally, setting "ignore_puppet_facts_for_provisioning" did not reduce the database load.
It seems a lot of our foreman front ends are running this query:
foreman=# SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;
cnt | usename | current_query
-----+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---
257 | foreman | <IDLE>
17 | foreman | <IDLE> in transaction
2 | foreman | COMMIT
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:0a:f7:43:51:e2' AND "nics"."id" != 139343 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT "nics".* FROM "nics" WHERE "nics"."host_id" = 25033 AND "nics"."mac" = '40:f2:e9:94:98:fa' AND "nics"."virtual" = 't' AND "nics"."identifier" = 'bond0' ORDER BY identifier LIMIT
1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:16:3e:8d:1a:11' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:9e:1a:45' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:29:17:44' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:25:2b:ac' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:0a:f7:1d:ac:8e' AND "nics"."id" != 139723 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:90:fa:71:7b:36' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:27:71:63' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:2a:49:08' AND "nics"."id" != 138420 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:0a:6a:16' AND "nics"."id" != 138466 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:90:fa:19:8b:a4' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:90:fa:71:82:24' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:26:ba:93' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:90:fa:1a:bb:c2' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:dc:38:25' AND "nics"."id" != 138968 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:75:56:8c' AND "nics"."id" != 138427 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:16:3e:b2:ea:11' AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE "nics"."type" IN ('Nic::Interface', 'Nic::Managed', 'Nic::BMC', 'Nic::Bond', 'Nic::Bootable') AND "nics"."ip" = '192.168.59.178' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:da:2a:33' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:26:d6:b3' AND "nics"."id" != 138837 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:26:b2:24' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:26:82:55' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:94:38:6d' AND "nics"."id" != 138453 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:95:33:33' AND "nics"."id" != 138971 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:9b:b5:9b' AND "nics"."id" != 138861 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:6a:68:20' AND "nics"."id" != 139055 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:21:5e:ed:39:be' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:0a:f7:43:54:50' AND "nics"."id" != 139195 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:26:62:73' AND "nics"."id" != 138725 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:21:5e:ed:45:b2' AND "nics"."id" != 138451 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:0a:f7:43:6d:be' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '6c:ae:8b:3e:bc:3d' AND "nics"."id" != 139002 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:26:2e:6d' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:2a:78:66' AND "nics"."id" != 138605 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '6c:ae:8b:3e:b8:04' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:19:71:18' AND "nics"."id" != 138396 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:0a:f7:43:58:72' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:da:2e:6b' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:0a:f7:1d:7d:9a' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:0a:f7:1d:8d:66' AND "nics"."id" != 139093 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:9d:d3:f3' AND "nics"."id" != 138689 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:16:3e:9b:aa:31' AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:0a:f7:1d:8f:58' AND "nics"."id" != 139348 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:26:c6:c5' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:9e:1a:bb' AND "nics"."id" != 138853 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '40:f2:e9:94:98:83' AND "nics"."id" != 138838 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:90:fa:19:61:7c' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:75:8e:10' AND "nics"."id" != 30 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '00:0a:f7:20:0a:c8' LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '40:f2:e9:9e:da:eb' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:75:65:90' AND "nics"."id" != 139181 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT mac FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."mac" = '98:be:94:3c:61:cb' LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = '00:90:fa:19:c0:84' AND "nics"."id" != 138642 AND "nics"."virtual" = 'f') LIMIT 1
1 | foreman | SELECT 1 AS one FROM "nics" WHERE ("nics"."mac" = 'd8:9d:67:60:08:20' AND "nics"."id" != 138872 AND "nics"."virtual" = 'f') LIMIT 1
1 | postgres | SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;
Updated by Martin Jackson over 9 years ago
Note: We discovered this while we were troubleshooting #7405
Updated by Martin Jackson over 9 years ago
We have a substantial number of Xen vhosts (around 12,000) that seem to trigger the bug identified by #7405. This led to a lot of noise in the audit logs for Primary Interfaces changing. Our ideal scenario would be to have a way to toggle the host/nic provisioning behavior. The use of ignore_puppet_facts_for_provisioning seems reasonable to me; at least in terms of the documented intent of that setting.
Updated by Dominic Cleal over 9 years ago
- Description updated (diff)
- Category set to Performance
Updated by Dominic Cleal over 9 years ago
- Related to Tracker #2409: Networking added
Updated by Dominic Cleal about 9 years ago
In Foreman 1.8, #9104 caused ignore_puppet_facts_for_provisioning to stop updating interfaces/NICs, which will significantly reduce database load when it's changed from the default.
Updated by Anonymous almost 7 years ago
- Status changed from New to Resolved
should be better (or at least different ;)) with recent versions
Actions