Reduce workload during cleanup (#15919)

* Add index to improve health cleanup

* Re arrange query to use index

* Check less entries during cleanup to prevent CPU spike

3 files changed, 6 insertions, 4 deletions

diff --git a/database/sqlite/sqlite_functions.c b/database/sqlite/sqlite_functions.c
index cc41cf6872..1b37ca9525 100644
--- a/database/sqlite/sqlite_functions.c
+++ b/database/sqlite/sqlite_functions.c
@@ -59,6 +59,7 @@ const char *database_config[] = {
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_2 ON health_log_detail (global_id);",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_3 ON health_log_detail (transition_id);",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_5 ON health_log_detail (health_log_id, unique_id DESC);",
+    "CREATE INDEX IF NOT EXISTS health_log_d_ind_6 on health_log_detail (health_log_id, when_key)",
 
     NULL
 };
diff --git a/database/sqlite/sqlite_health.c b/database/sqlite/sqlite_health.c
index e2d06c9c6b..104168144f 100644
--- a/database/sqlite/sqlite_health.c
+++ b/database/sqlite/sqlite_health.c
@@ -425,7 +425,7 @@ done:
  */
 
 #define SQL_CLEANUP_HEALTH_LOG_DETAIL_NOT_CLAIMED "DELETE FROM health_log_detail WHERE health_log_id IN " \
-    "(SELECT health_log_id FROM health_log WHERE host_id = @host_id) AND when_key + @history < unixepoch() " \
+    "(SELECT health_log_id FROM health_log WHERE host_id = @host_id) AND when_key < unixepoch() - @history " \
     "AND updated_by_id <> 0 AND transition_id NOT IN " \
     "(SELECT last_transition_id FROM health_log hl WHERE hl.host_id = @host_id);"
 
@@ -434,7 +434,7 @@ done:
     "AND unique_id IN (SELECT hld.unique_id FROM health_log hl, health_log_detail hld WHERE " \
     "hl.host_id = @host_id AND hl.health_log_id = hld.health_log_id) " \
     "AND health_log_id IN (SELECT health_log_id FROM health_log WHERE host_id = @host_id) " \
-    "AND when_key + @history < unixepoch() " \
+    "AND when_key < unixepoch() - @history " \
     "AND updated_by_id <> 0 AND transition_id NOT IN " \
     "(SELECT last_transition_id FROM health_log hl WHERE hl.host_id = @host_id);", guid
 
diff --git a/database/sqlite/sqlite_metadata.c b/database/sqlite/sqlite_metadata.c
index 71e444db3c..0751026e7a 100644
--- a/database/sqlite/sqlite_metadata.c
+++ b/database/sqlite/sqlite_metadata.c
@@ -730,7 +730,8 @@ static bool run_cleanup_loop(
 
     time_t start_running = now_monotonic_sec();
     bool time_expired = false;
-    while (!time_expired && sqlite3_step_monitored(res) == SQLITE_ROW && *total_deleted < cleanup_threshold) {
+    while (!time_expired && sqlite3_step_monitored(res) == SQLITE_ROW && *total_deleted < cleanup_threshold &&
+           *total_checked < cleanup_threshold) {
         if (unlikely(metadata_flag_check(wc, METADATA_FLAG_SHUTDOWN)))
             break;
 
@@ -745,7 +746,7 @@ static bool run_cleanup_loop(
         (*total_checked)++;
         time_expired = ((now_monotonic_sec() - start_running) > run_threshold);
     }
-    return time_expired;
+    return time_expired || (*total_deleted == cleanup_threshold) || (*total_checked == cleanup_threshold);
 }