Michael, I see 2 problems: metrics (not metrics_rollups) and vim_performance_states vim_performance_states purging was just added to master. so that would explain it beign too big. commit 84ca04ce3c833cd94896ece5f6db662b4bb494ab Author: Nick Carboni <ncarboni> Date: Fri Jan 5 11:28:38 2018 -0500 Purge orphans from VimPerformanceState This uses the new "orhaned" purging mode to remove rows from vim_performance_states which have dangling pointers Fixes https://bugzilla.redhat.com/show_bug.cgi?id=1434918 metrics purging is not as efficient as it could be. which would explain it timing out Once it starts timing out - it is difficult to get out of that problem. I'll look into this Anything else you want to share to steer me correctly? Thanks, Keenan
I stand corrected, metric_rollups is the less efficient one. Which seems to be working for you. metrics on the other hand is tuned pretty well. I'm guessing table contention is the issue. The batch size is probably your best bet. That is controlled by performance.history.purge_window_size It defaults to 1000 - which seems low. I'd change that to 10k I had expected that it would be too high for you, and having table contention. but a value that low will require too many updates. a bigger batch size should speed that up
Question: Have you tried vacuuming the database? Reasoning: I went in to get a look at the tables and noticed that it took 14 seconds to do a count of just one of them. explain analyze select count(*) from metrics_21; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=496204.62..496204.64 rows=1 width=0) (actual time=14397.077..14397.077 rows=1 loops=1) -> Seq Scan on metrics_21 (cost=0.00..480741.90 rows=6185090 width=0) (actual time=9.232..13290.674 rows=6185090 loops=1) Planning time: 0.782 ms Execution time: 14397.241 ms The explain text for the truncate statement looks very large. will look into reducing that.
https://github.com/ManageIQ/manageiq/pull/17124
https://github.com/ManageIQ/manageiq/pull/17017
*** Bug 1547161 has been marked as a duplicate of this bug. ***
Verified on 5.10.0.22.