Update statistics to address slow queries (#16838)

* Run analyze on aclk_alert tables
Add analyze option -W sqlite-analyze

* Remove empty line

* Remove analyze during runtime

* Remove health_log_entries_written

* Replace index

* Remove forced index skip

* Change version and run database analyze

* Adjust analyze to run on specific tables
Fix previous migration v14 -> v15 typo

* Fix v15 -> v16 migration message

* Fix v15 -> v16 migration message (typo)

* Increase analysis limit

(cherry picked from commit 0398c6cff4d611eb53d923d7f464e0ce15ceba9e)

7 files changed, 67 insertions, 64 deletions

diff --git a/daemon/main.c b/daemon/main.c
index 4051070aba..a74e1e1984 100644
--- a/daemon/main.c
+++ b/daemon/main.c
@@ -810,6 +810,7 @@ int help(int exitcode) {
             "  -W unittest              Run internal unittests and exit.\n\n"
             "  -W sqlite-meta-recover   Run recovery on the metadata database and exit.\n\n"
             "  -W sqlite-compact        Reclaim metadata database unused space and exit.\n\n"
+            "  -W sqlite-analyze        Run update statistics and exit.\n\n"
 #ifdef ENABLE_DBENGINE
             "  -W createdataset=N       Create a DB engine dataset of N seconds and exit.\n\n"
             "  -W stresstest=A,B,C,D,E,F,G\n"
@@ -1516,6 +1517,11 @@ int main(int argc, char **argv) {
                             return 0;
                         }
 
+                        if(strcmp(optarg, "sqlite-analyze") == 0) {
+                            sql_init_database(DB_CHECK_ANALYZE, 0);
+                            return 0;
+                        }
+
                         if(strcmp(optarg, "unittest") == 0) {
                             unittest_running = true;
 
diff --git a/database/rrd.h b/database/rrd.h
index 1d94a5d8c9..5f4bee0375 100644
--- a/database/rrd.h
+++ b/database/rrd.h
@@ -1141,7 +1141,6 @@ typedef struct health {
     time_t health_delay_up_to;                     // a timestamp to delay alarms processing up to
     STRING *health_default_exec;                   // the full path of the alarms notifications program
     STRING *health_default_recipient;              // the default recipient for all alarms
-    int health_log_entries_written;                // the number of alarm events written to the alarms event log
     uint32_t health_default_warn_repeat_every;     // the default value for the interval between repeating warning notifications
     uint32_t health_default_crit_repeat_every;     // the default value for the interval between repeating critical notifications
     unsigned int health_enabled;                   // 1 when this host has health enabled
diff --git a/database/sqlite/sqlite_aclk_alert.c b/database/sqlite/sqlite_aclk_alert.c
index 30e487a2ea..9bd060f965 100644
--- a/database/sqlite/sqlite_aclk_alert.c
+++ b/database/sqlite/sqlite_aclk_alert.c
@@ -97,7 +97,7 @@ done:
 //decide if some events should be sent or not
 #define SQL_SELECT_ALERT_BY_ID                                                                                             \
     "SELECT hld.new_status, hl.config_hash_id, hld.unique_id FROM health_log hl, aclk_alert_%s aa, health_log_detail hld " \
-    "WHERE hl.host_id = @host_id AND +hld.unique_id = aa.filtered_alert_unique_id "                                        \
+    "WHERE hl.host_id = @host_id AND hld.unique_id = aa.filtered_alert_unique_id "                                         \
     "AND hld.alarm_id = @alarm_id AND hl.health_log_id = hld.health_log_id "                                               \
     "ORDER BY hld.rowid DESC LIMIT 1"
 
diff --git a/database/sqlite/sqlite_db_migration.c b/database/sqlite/sqlite_db_migration.c
index 00a1e10558..29da6c249c 100644
--- a/database/sqlite/sqlite_db_migration.c
+++ b/database/sqlite/sqlite_db_migration.c
@@ -382,14 +382,49 @@ static int do_migration_v14_v15(sqlite3 *database)
     }
 
     BUFFER *wb = buffer_create(128, NULL);
-    while (sqlite3_step_monitored(res) == SQLITE_ROW)
-        buffer_sprintf(wb, "DROP INDEX IF EXISTS %s", (char *) sqlite3_column_text(res, 0));
+    size_t count = 0;
+    while (sqlite3_step_monitored(res) == SQLITE_ROW) {
+        buffer_sprintf(wb, "DROP INDEX IF EXISTS %s; ", (char *)sqlite3_column_text(res, 0));
+        count++;
+    }
 
     rc = sqlite3_finalize(res);
     if (unlikely(rc != SQLITE_OK))
         error_report("Failed to finalize statement when dropping unused indices, rc = %d", rc);
 
-    (void) db_execute(database, buffer_tostring(wb));
+    if (count)
+        (void) db_execute(database, buffer_tostring(wb));
+
+    buffer_free(wb);
+    return 0;
+}
+
+static int do_migration_v15_v16(sqlite3 *database)
+{
+    char sql[256];
+
+    int rc;
+    sqlite3_stmt *res = NULL;
+    snprintfz(sql, sizeof(sql) - 1, "SELECT name FROM sqlite_schema WHERE type = \"table\" AND name LIKE \"aclk_alert_%%\"");
+    rc = sqlite3_prepare_v2(database, sql, -1, &res, 0);
+    if (rc != SQLITE_OK) {
+        error_report("Failed to prepare statement to drop unused indices");
+        return 1;
+    }
+
+    BUFFER *wb = buffer_create(128, NULL);
+    size_t count = 0;
+    while (sqlite3_step_monitored(res) == SQLITE_ROW) {
+        buffer_sprintf(wb, "ANALYZE %s ; ", (char *)sqlite3_column_text(res, 0));
+        count++;
+    }
+
+    rc = sqlite3_finalize(res);
+    if (unlikely(rc != SQLITE_OK))
+        error_report("Failed to finalize statement when running ANALYZE on aclk_alert_tables, rc = %d", rc);
+
+    if (count)
+        (void) db_execute(database, buffer_tostring(wb));
 
     buffer_free(wb);
     return 0;
@@ -491,6 +526,7 @@ DATABASE_FUNC_MIGRATION_LIST migration_action[] = {
     {.name = "v12 to v13",  .func = do_migration_v12_v13},
     {.name = "v13 to v14",  .func = do_migration_v13_v14},
     {.name = "v14 to v15",  .func = do_migration_v14_v15},
+    {.name = "v15 to v16",  .func = do_migration_v15_v16},
     // the terminator of this array
     {.name = NULL, .func = NULL}
 };
diff --git a/database/sqlite/sqlite_functions.c b/database/sqlite/sqlite_functions.c
index f08c6d3501..e3537bf5a6 100644
--- a/database/sqlite/sqlite_functions.c
+++ b/database/sqlite/sqlite_functions.c
@@ -4,7 +4,7 @@
 #include "sqlite3recover.h"
 #include "sqlite_db_migration.h"
 
-#define DB_METADATA_VERSION 15
+#define DB_METADATA_VERSION 16
 
 const char *database_config[] = {
     "CREATE TABLE IF NOT EXISTS host(host_id BLOB PRIMARY KEY, hostname TEXT NOT NULL, "
@@ -64,7 +64,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_9 ON health_log_detail (unique_id DESC, health_log_id)",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_6 on health_log_detail (health_log_id, when_key)",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_7 on health_log_detail (alarm_id)",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_8 on health_log_detail (new_status, updated_by_id)",
@@ -84,6 +84,7 @@ const char *database_cleanup[] = {
     "DROP INDEX IF EXISTS alert_hash_index",
     "DROP INDEX IF EXISTS health_log_d_ind_4",
     "DROP INDEX IF EXISTS health_log_d_ind_1",
+    "DROP INDEX IF EXISTS health_log_d_ind_5",
     NULL
 };
 
@@ -428,6 +429,20 @@ int sql_init_database(db_check_action_type_t rebuild, int memory)
         return 1;
     }
 
+    if (rebuild & DB_CHECK_ANALYZE) {
+        netdata_log_info("Running ANALYZE on %s", sqlite_database);
+        rc = sqlite3_exec_monitored(db_meta, "ANALYZE", 0, 0, &err_msg);
+        if (rc != SQLITE_OK) {
+            error_report("Failed to execute ANALYZE rc = %d (%s)", rc, err_msg);
+            sqlite3_free(err_msg);
+        }
+        else {
+            (void) db_execute(db_meta, "select count(*) from sqlite_master limit 0");
+            (void) sqlite3_close(db_meta);
+        }
+        return 1;
+    }
+
     netdata_log_info("SQLite database %s initialization", sqlite_database);
 
     rc = sqlite3_create_function(db_meta, "u2h", 1, SQLITE_ANY | SQLITE_DETERMINISTIC, 0, sqlite_uuid_parse, 0, 0);
@@ -477,7 +492,7 @@ void sql_close_database(void)
 
     add_stmt_to_list(NULL);
 
-    (void) db_execute(db_meta, "PRAGMA analysis_limit=1000");
+    (void) db_execute(db_meta, "PRAGMA analysis_limit=10000");
     (void) db_execute(db_meta, "PRAGMA optimize");
 
     rc = sqlite3_close_v2(db_meta);
diff --git a/database/sqlite/sqlite_functions.h b/database/sqlite/sqlite_functions.h
index 9cd1f7ad47..40b5af4647 100644
--- a/database/sqlite/sqlite_functions.h
+++ b/database/sqlite/sqlite_functions.h
@@ -21,8 +21,9 @@ struct node_instance_list {
 typedef enum db_check_action_type {
     DB_CHECK_NONE          = (1 << 0),
     DB_CHECK_RECLAIM_SPACE = (1 << 1),
-    DB_CHECK_CONT          = (1 << 2),
-    DB_CHECK_RECOVER       = (1 << 3),
+    DB_CHECK_ANALYZE       = (1 << 2),
+    DB_CHECK_CONT          = (1 << 3),
+    DB_CHECK_RECOVER       = (1 << 4),
 } db_check_action_type_t;
 
 #define SQL_MAX_RETRY (100)
diff --git a/database/sqlite/sqlite_health.c b/database/sqlite/sqlite_health.c
index 27f37596bb..7d79ff70b3 100644
--- a/database/sqlite/sqlite_health.c
+++ b/database/sqlite/sqlite_health.c
@@ -357,7 +357,6 @@ static void sql_health_alarm_log_insert(RRDHOST *host, ALARM_ENTRY *ae) {
     }
 
     ae->flags |= HEALTH_ENTRY_FLAG_SAVED;
-    host->health.health_log_entries_written++;
 
 failed:
     if (unlikely(sqlite3_finalize(res) != SQLITE_OK))
@@ -378,48 +377,6 @@ void sql_health_alarm_log_save(RRDHOST *host, ALARM_ENTRY *ae)
     }
 }
 
-/* Health related SQL queries
-   Get a count of rows from health log table
-*/
-#define SQL_COUNT_HEALTH_LOG_DETAIL "SELECT count(1) FROM health_log_detail hld, health_log hl " \
-        "where hl.host_id = @host_id and hl.health_log_id = hld.health_log_id"
-
-static int sql_health_alarm_log_count(RRDHOST *host) {
-    sqlite3_stmt *res = NULL;
-    int rc;
-
-    if (unlikely(!db_meta)) {
-        if (default_rrd_memory_mode == RRD_MEMORY_MODE_DBENGINE)
-            error_report("Database has not been initialized");
-        return -1;
-    }
-
-    int entries_in_db = -1;
-
-    rc = sqlite3_prepare_v2(db_meta, SQL_COUNT_HEALTH_LOG_DETAIL, -1, &res, 0);
-    if (unlikely(rc != SQLITE_OK)) {
-        error_report("Failed to prepare statement to count health log entries from db");
-        goto done;
-    }
-
-    rc = sqlite3_bind_blob(res, 1, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK)) {
-        error_report("Failed to bind host_id for SQL_COUNT_HEALTH_LOG.");
-        goto done;
-    }
-
-    rc = sqlite3_step_monitored(res);
-    if (likely(rc == SQLITE_ROW))
-        entries_in_db = (int) sqlite3_column_int64(res, 0);
-
-done:
-    rc = sqlite3_finalize(res);
-    if (unlikely(rc != SQLITE_OK))
-        error_report("Failed to finalize the prepared statement to count health log entries from db");
-
-    return entries_in_db;
-}
-
 /*
  *
  * Health related SQL queries
@@ -490,10 +447,6 @@ void sql_health_alarm_log_cleanup(RRDHOST *host, bool claimed) {
     if (unlikely(rc != SQLITE_DONE))
         error_report("Failed to cleanup health log detail table, rc = %d", rc);
 
-    int rows = sql_health_alarm_log_count(host);
-    if (rows >= 0)
-        host->health.health_log_entries_written = rows;
-
     if (aclk_table_exists)
         sql_aclk_alert_clean_dead_entries(host);
 
@@ -767,8 +720,6 @@ void sql_health_alarm_log_load(RRDHOST *host)
     int ret;
     ssize_t errored = 0, loaded = 0;
 
-    host->health.health_log_entries_written = 0;
-
     if (unlikely(!db_meta)) {
         if (default_rrd_memory_mode == RRD_MEMORY_MODE_DBENGINE)
             error_report("HEALTH [%s]: Database has not been initialized", rrdhost_hostname(host));
@@ -937,11 +888,6 @@ void sql_health_alarm_log_load(RRDHOST *host)
     ret = sqlite3_finalize(res);
     if (unlikely(ret != SQLITE_OK))
         error_report("Failed to finalize the health log read statement");
-
-    int rows = sql_health_alarm_log_count(host);
-
-    if (rows >= 0)
-        host->health.health_log_entries_written = rows;
 }
 
 /*