query the 'reports' table as little as possible
|Branch:||Affected Dashboard version:|
I’ve noticed that one of the slowest Dashboard web UI operations is opening the page for a single node. Looking at MySQL at the time, the query is:
SELECT * FROM
reports WHERE (
reports.node_id = 49) AND (
reports.node_id = 49) ORDER BY time DESC LIMIT 30
Looking at my DB this is not surprising (although worrying considering I will be expanding): 66 nodes, 33k rows in
reports and the table is a little under 4GB in size – and I prune reports older than a week.
I guess that the report column is selected and parsed in order to generate the table of node stats like Total and Failed, however if a report never changes then loading the entire YAML and parsing it seems a lot of overhead.
You may have something planned like this already, but when a report is first sent to Dashboard by a Puppet master, why not parse it then and store a bunch of data in a smaller, faster table that can be queried instead? The only reading of the reports table should be when opening an individual report.
Such a table would also pave the way for more functionality, like showing all nodes that ‘changed’ something in the last day, or all nodes that took longer than X seconds.