Problem:
We keep a single parameter “nodestatus” set to one of several value(s) on every host within foreman. This parameter drives many things for us, mainly monitoring suppression, loadbalancer inclusion, etc. We typically run a foreman API search to return the following query
“params.nodestatus != active”
This was always “slow” but recently started taking >300 seconds - meaning the UI times it out before it returns (we have ~15,000 hosts). While 15k hosts is “a lot” i’m still thinking that this “should” be a simple query that returns in a few seconds, max?
General Process:
- Enter search term in foreman API: https://myforemanfqdn.fqdn.com/api/v2/hosts?search=params.nodestatus+!%3D+active&per_page=99999&thin=true
- SQL query run(s) for ~180 seconds to gather up ID(s)
Query: https://pastebin.com/TNHymctD
Explain Select: https://pastebin.com/dgNKfRB2 - Second SQL Query runs for ~200+ seconds to gather up results using OR statement with ID’s
Query: https://pastebin.com/bvuRyYa9
Explain Select: https://pastebin.com/44nrgcKj - API times out with Error: Mysql2::Error: Lost connection to MySQL server during query: SELECT COUNT(*)…
Expected outcome:
I generally expect this to return “very quickly” - especially since I have specified the thin=true API parameter - I only want hostname(s) here. This query “should” be returning around 2500 hosts currently in my environment.
As a quick comparison - querying the nearly identical (in my opinion - maybe backend is very different) “os != RedHat” https://myforemanfqdn.fqdn.com/api/v2/hosts?search=os+!%3D+RedHat&per_page=99999&thin=true
Returns 6500 hosts in <1 second.
Foreman and Proxy versions:
Foreman 1.19.1
MariaDB 10.2.X
RHEL7