From 1934696c45fbcd35d453b5f026fc278bab40d5f7 Mon Sep 17 00:00:00 2001 From: Stelios Fragkakis <52996999+stelfrag@users.noreply.github.com> Date: Thu, 19 Jan 2023 15:18:09 +0200 Subject: Remove archivedcharts endpoint, optimize indices (#14296) Remove undocumented archivedcharts endpoint. Use context endpoint instead Remove unused functions to lookup chart and dimension UUIDs Drop/Add new index for dimension and chart tables --- database/sqlite/sqlite_functions.c | 322 +------------------------------------ database/sqlite/sqlite_functions.h | 5 - web/api/web_api_v1.c | 13 -- web/api/web_api_v1.h | 2 - 4 files changed, 5 insertions(+), 337 deletions(-) diff --git a/database/sqlite/sqlite_functions.c b/database/sqlite/sqlite_functions.c index 749dd99dbf..fc32853182 100644 --- a/database/sqlite/sqlite_functions.c +++ b/database/sqlite/sqlite_functions.c @@ -22,9 +22,8 @@ const char *database_config[] = { "multiplier int, divisor int , algorithm int, options text);", "CREATE TABLE IF NOT EXISTS metadata_migration(filename text, file_size, date_created int);", - "CREATE INDEX IF NOT EXISTS ind_d1 on dimension (chart_id, id, name);", - "CREATE INDEX IF NOT EXISTS ind_c1 on chart (host_id, id, type, name);", - "CREATE INDEX IF NOT EXISTS ind_c2 on chart (host_id, context);", + "CREATE INDEX IF NOT EXISTS ind_d2 on dimension (chart_id);", + "CREATE INDEX IF NOT EXISTS ind_c3 on chart (host_id);", "CREATE TABLE IF NOT EXISTS chart_label(chart_id blob, source_type int, label_key text, " "label_value text, date_created int, PRIMARY KEY (chart_id, label_key));", "CREATE TABLE IF NOT EXISTS node_instance (host_id blob PRIMARY KEY, claim_id, node_id, date_created);", @@ -55,6 +54,9 @@ const char *database_cleanup[] = { "DELETE FROM host_info WHERE host_id NOT IN (SELECT host_id FROM host);", "DELETE FROM host_label WHERE host_id NOT IN (SELECT host_id FROM host);", "DROP TRIGGER IF EXISTS tr_dim_del;", + "DROP INDEX IF EXISTS ind_d1;", + "DROP INDEX IF EXISTS ind_c1;", + "DROP INDEX IF EXISTS ind_c2;", NULL }; @@ -504,211 +506,6 @@ skip: return result; } - - -// -// Support for archived charts (TO BE REMOVED) -// -#define SELECT_DIMENSION "select d.id, d.name from dimension d where d.chart_id = @chart_uuid;" - -static void sql_rrdim2json(sqlite3_stmt *res_dim, uuid_t *chart_uuid, BUFFER *wb, size_t *dimensions_count) -{ - int rc; - - rc = sqlite3_bind_blob(res_dim, 1, chart_uuid, sizeof(*chart_uuid), SQLITE_STATIC); - if (rc != SQLITE_OK) - return; - - int dimensions = 0; - buffer_sprintf(wb, "\t\t\t\"dimensions\": {\n"); - - while (sqlite3_step_monitored(res_dim) == SQLITE_ROW) { - if (dimensions) - buffer_strcat(wb, ",\n\t\t\t\t\""); - else - buffer_strcat(wb, "\t\t\t\t\""); - buffer_strcat_jsonescape(wb, (const char *) sqlite3_column_text(res_dim, 0)); - buffer_strcat(wb, "\": { \"name\": \""); - buffer_strcat_jsonescape(wb, (const char *) sqlite3_column_text(res_dim, 1)); - buffer_strcat(wb, "\" }"); - dimensions++; - } - *dimensions_count += dimensions; - buffer_sprintf(wb, "\n\t\t\t}"); -} - -#define SELECT_CHART "select chart_id, id, name, type, family, context, title, priority, plugin, " \ - "module, unit, chart_type, update_every from chart " \ - "where host_id = @host_uuid and chart_id not in (select chart_id from chart_active) order by chart_id asc;" - -void sql_rrdset2json(RRDHOST *host, BUFFER *wb) -{ - // time_t first_entry_t = 0; //= rrdset_first_entry_t(st); - // time_t last_entry_t = 0; //rrdset_last_entry_t(st); - static char *custom_dashboard_info_js_filename = NULL; - int rc; - - sqlite3_stmt *res_chart = NULL; - sqlite3_stmt *res_dim = NULL; - time_t now = now_realtime_sec(); - - rc = sqlite3_prepare_v2(db_meta, SELECT_CHART, -1, &res_chart, 0); - if (unlikely(rc != SQLITE_OK)) { - error_report("Failed to prepare statement to fetch host archived charts"); - return; - } - - rc = sqlite3_bind_blob(res_chart, 1, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) { - error_report("Failed to bind host parameter to fetch archived charts"); - goto failed; - } - - rc = sqlite3_prepare_v2(db_meta, SELECT_DIMENSION, -1, &res_dim, 0); - if (unlikely(rc != SQLITE_OK)) { - error_report("Failed to prepare statement to fetch chart archived dimensions"); - goto failed; - }; - - if(unlikely(!custom_dashboard_info_js_filename)) - custom_dashboard_info_js_filename = config_get(CONFIG_SECTION_WEB, "custom dashboard_info.js", ""); - - buffer_sprintf(wb, "{\n" - "\t\"hostname\": \"%s\"" - ",\n\t\"version\": \"%s\"" - ",\n\t\"release_channel\": \"%s\"" - ",\n\t\"os\": \"%s\"" - ",\n\t\"timezone\": \"%s\"" - ",\n\t\"update_every\": %d" - ",\n\t\"history\": %ld" - ",\n\t\"memory_mode\": \"%s\"" - ",\n\t\"custom_info\": \"%s\"" - ",\n\t\"charts\": {" - , rrdhost_hostname(host) - , rrdhost_program_version(host) - , get_release_channel() - , rrdhost_os(host) - , rrdhost_timezone(host) - , host->rrd_update_every - , host->rrd_history_entries - , rrd_memory_mode_name(host->rrd_memory_mode) - , custom_dashboard_info_js_filename - ); - - size_t c = 0; - size_t dimensions = 0; - - while (sqlite3_step_monitored(res_chart) == SQLITE_ROW) { - char id[512]; - sprintf(id, "%s.%s", sqlite3_column_text(res_chart, 3), sqlite3_column_text(res_chart, 1)); - RRDSET *st = rrdset_find(host, id); - if (st && !rrdset_flag_check(st, RRDSET_FLAG_ARCHIVED)) - continue; - - if (c) - buffer_strcat(wb, ",\n\t\t\""); - else - buffer_strcat(wb, "\n\t\t\""); - c++; - - buffer_strcat(wb, id); - buffer_strcat(wb, "\": "); - - buffer_sprintf( - wb, - "\t\t{\n" - "\t\t\t\"id\": \"%s\",\n" - "\t\t\t\"name\": \"%s\",\n" - "\t\t\t\"type\": \"%s\",\n" - "\t\t\t\"family\": \"%s\",\n" - "\t\t\t\"context\": \"%s\",\n" - "\t\t\t\"title\": \"%s (%s)\",\n" - "\t\t\t\"priority\": %ld,\n" - "\t\t\t\"plugin\": \"%s\",\n" - "\t\t\t\"module\": \"%s\",\n" - "\t\t\t\"enabled\": %s,\n" - "\t\t\t\"units\": \"%s\",\n" - "\t\t\t\"data_url\": \"/api/v1/data?chart=%s\",\n" - "\t\t\t\"chart_type\": \"%s\",\n", - id //sqlite3_column_text(res_chart, 1) - , - id // sqlite3_column_text(res_chart, 2) - , - sqlite3_column_text(res_chart, 3), sqlite3_column_text(res_chart, 4), sqlite3_column_text(res_chart, 5), - sqlite3_column_text(res_chart, 6), id //sqlite3_column_text(res_chart, 2) - , - (long ) sqlite3_column_int(res_chart, 7), - (const char *) sqlite3_column_text(res_chart, 8) ? (const char *) sqlite3_column_text(res_chart, 8) : (char *) "", - (const char *) sqlite3_column_text(res_chart, 9) ? (const char *) sqlite3_column_text(res_chart, 9) : (char *) "", (char *) "false", - (const char *) sqlite3_column_text(res_chart, 10), id //sqlite3_column_text(res_chart, 2) - , - rrdset_type_name(sqlite3_column_int(res_chart, 11))); - - sql_rrdim2json(res_dim, (uuid_t *) sqlite3_column_blob(res_chart, 0), wb, &dimensions); - - rc = sqlite3_reset(res_dim); - if (unlikely(rc != SQLITE_OK)) - error_report("Failed to reset the prepared statement when reading archived chart dimensions"); - buffer_strcat(wb, "\n\t\t}"); - } - - buffer_sprintf(wb - , "\n\t}" - ",\n\t\"charts_count\": %zu" - ",\n\t\"dimensions_count\": %zu" - ",\n\t\"alarms_count\": %zu" - ",\n\t\"rrd_memory_bytes\": %zu" - ",\n\t\"hosts_count\": %zu" - ",\n\t\"hosts\": [" - , c - , dimensions - , (size_t) 0 - , (size_t) 0 - , rrdhost_hosts_available() - ); - - if(unlikely(rrdhost_hosts_available() > 1)) { - rrd_rdlock(); - - size_t found = 0; - RRDHOST *h; - rrdhost_foreach_read(h) { - if(!rrdhost_should_be_removed(h, host, now) && !rrdhost_flag_check(h, RRDHOST_FLAG_ARCHIVED)) { - buffer_sprintf(wb - , "%s\n\t\t{" - "\n\t\t\t\"hostname\": \"%s\"" - "\n\t\t}" - , (found > 0) ? "," : "" - , rrdhost_hostname(h) - ); - - found++; - } - } - - rrd_unlock(); - } - else { - buffer_sprintf(wb - , "\n\t\t{" - "\n\t\t\t\"hostname\": \"%s\"" - "\n\t\t}" - , rrdhost_hostname(host) - ); - } - - buffer_sprintf(wb, "\n\t]\n}\n"); - - rc = sqlite3_finalize(res_dim); - if (unlikely(rc != SQLITE_OK)) - error_report("Failed to finalize the prepared statement when reading archived chart dimensions"); - -failed: - rc = sqlite3_finalize(res_chart); - if (unlikely(rc != SQLITE_OK)) - error_report("Failed to finalize the prepared statement when reading archived charts"); -} - void db_execute(const char *cmd) { int rc; @@ -1268,112 +1065,3 @@ int sql_metadata_cache_stats(int op) netdata_thread_enable_cancelability(); return count; } - -#define SQL_FIND_CHART_UUID \ - "SELECT chart_id FROM chart WHERE host_id = @host AND type=@type AND id=@id AND (name IS NULL OR name=@name) AND chart_id IS NOT NULL;" - -#define SQL_FIND_DIMENSION_UUID \ - "SELECT dim_id FROM dimension WHERE chart_id=@chart AND id=@id AND name=@name AND LENGTH(dim_id)=16;" - - -//Do a database lookup to find the UUID of a chart -//If found store it in store_uuid and return 0 -int sql_find_chart_uuid(RRDHOST *host, RRDSET *st, uuid_t *store_uuid) -{ - static __thread sqlite3_stmt *res = NULL; - int rc; - - const char *name = string2str(st->parts.name); - - if (unlikely(!db_meta) && default_rrd_memory_mode != RRD_MEMORY_MODE_DBENGINE) - return 1; - - if (unlikely(!res)) { - rc = prepare_statement(db_meta, SQL_FIND_CHART_UUID, &res); - if (rc != SQLITE_OK) { - error_report("Failed to prepare statement to lookup chart UUID in the database"); - return 1; - } - } - - rc = sqlite3_bind_blob(res, 1, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - rc = sqlite3_bind_text(res, 2, string2str(st->parts.type), -1, SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - rc = sqlite3_bind_text(res, 3, string2str(st->parts.id), -1, SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - rc = sqlite3_bind_text(res, 4, name && *name ? name : string2str(st->parts.id), -1, SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - int status = 1; - rc = sqlite3_step_monitored(res); - if (likely(rc == SQLITE_ROW)) { - uuid_copy(*store_uuid, sqlite3_column_blob(res, 0)); - status = 0; - } - - rc = sqlite3_reset(res); - if (unlikely(rc != SQLITE_OK)) - error_report("Failed to reset statement when searching for a chart UUID, rc = %d", rc); - - return status; - -bind_fail: - error_report("Failed to bind input parameter to perform chart UUID database lookup, rc = %d", rc); - rc = sqlite3_reset(res); - if (unlikely(rc != SQLITE_OK)) - error_report("Failed to reset statement when searching for a chart UUID, rc = %d", rc); - return 1; -} - -int sql_find_dimension_uuid(RRDSET *st, RRDDIM *rd, uuid_t *store_uuid) -{ - static __thread sqlite3_stmt *res = NULL; - int rc; - int status = 1; - - if (unlikely(!db_meta) && default_rrd_memory_mode != RRD_MEMORY_MODE_DBENGINE) - return 1; - - if (unlikely(!res)) { - rc = prepare_statement(db_meta, SQL_FIND_DIMENSION_UUID, &res); - if (rc != SQLITE_OK) { - error_report("Failed to bind prepare statement to lookup dimension UUID in the database"); - return 1; - } - } - - rc = sqlite3_bind_blob(res, 1, st->chart_uuid, sizeof(*st->chart_uuid), SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - rc = sqlite3_bind_text(res, 2, rrddim_id(rd), -1, SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - rc = sqlite3_bind_text(res, 3, rrddim_name(rd), -1, SQLITE_STATIC); - if (unlikely(rc != SQLITE_OK)) - goto bind_fail; - - rc = sqlite3_step_monitored(res); - if (likely(rc == SQLITE_ROW)) { - uuid_copy(*store_uuid, *((uuid_t *) sqlite3_column_blob(res, 0))); - status = 0; - } - - rc = sqlite3_reset(res); - if (unlikely(rc != SQLITE_OK)) - error_report("Failed to reset statement find dimension uuid, rc = %d", rc); - return status; - -bind_fail: - error_report("Failed to bind input parameter to perform dimension UUID database lookup, rc = %d", rc); - return 1; -} diff --git a/database/sqlite/sqlite_functions.h b/database/sqlite/sqlite_functions.h index 5731d5c9e1..26bdef1e2c 100644 --- a/database/sqlite/sqlite_functions.h +++ b/database/sqlite/sqlite_functions.h @@ -65,16 +65,11 @@ int get_host_id(uuid_t *node_id, uuid_t *host_id); struct node_instance_list *get_node_list(void); void sql_load_node_id(RRDHOST *host); char *get_hostname_by_node_id(char *node_id); -int sql_find_chart_uuid(RRDHOST *host, RRDSET *st, uuid_t *store_uuid); -int sql_find_dimension_uuid(RRDSET *st, RRDDIM *rd, uuid_t *store_uuid); // Help build archived hosts in memory when agent starts void sql_build_host_system_info(uuid_t *host_id, struct rrdhost_system_info *system_info); DICTIONARY *sql_load_host_labels(uuid_t *host_id); -// For queries: To be removed when context queries are implemented -void sql_rrdset2json(RRDHOST *host, BUFFER *wb); - // TODO: move to metadata int update_node_id(uuid_t *host_id, uuid_t *node_id); diff --git a/web/api/web_api_v1.c b/web/api/web_api_v1.c index 35c3c43786..a4d4836221 100644 --- a/web/api/web_api_v1.c +++ b/web/api/web_api_v1.c @@ -561,18 +561,6 @@ inline int web_client_api_request_v1_charts(RRDHOST *host, struct web_client *w, return HTTP_RESP_OK; } -inline int web_client_api_request_v1_archivedcharts(RRDHOST *host __maybe_unused, struct web_client *w, char *url) { - (void)url; - - buffer_flush(w->response.data); - w->response.data->contenttype = CT_APPLICATION_JSON; -#ifdef ENABLE_DBENGINE - if (host->rrd_memory_mode == RRD_MEMORY_MODE_DBENGINE) - sql_rrdset2json(host, w->response.data); -#endif - return HTTP_RESP_OK; -} - inline int web_client_api_request_v1_chart(RRDHOST *host, struct web_client *w, char *url) { return web_client_api_request_single_chart(host, w, url, rrd_stats_api_v1_chart); } @@ -1620,7 +1608,6 @@ static struct api_command { { "charts", 0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_charts }, { "context", 0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_context }, { "contexts", 0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_contexts }, - { "archivedcharts", 0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_archivedcharts }, // registry checks the ACL by itself, so we allow everything { "registry", 0, WEB_CLIENT_ACL_NOCHECK, web_client_api_request_v1_registry }, diff --git a/web/api/web_api_v1.h b/web/api/web_api_v1.h index e6682c99ce..9dd6a1c231 100644 --- a/web/api/web_api_v1.h +++ b/web/api/web_api_v1.h @@ -9,7 +9,6 @@ #include "web/api/health/health_cmdapi.h" #include "web/api/queries/weights.h" -#define MAX_CHART_LABELS_FILTER (32) RRDR_OPTIONS web_client_api_request_v1_data_options(char *o); void web_client_api_request_v1_data_options_to_buffer(BUFFER *wb, RRDR_OPTIONS options); void web_client_api_request_v1_data_options_to_string(char *buf, size_t size, RRDR_OPTIONS options); @@ -24,7 +23,6 @@ int web_client_api_request_single_chart(RRDHOST *host, struct web_client *w, cha int web_client_api_request_v1_alarm_variables(RRDHOST *host, struct web_client *w, char *url); int web_client_api_request_v1_alarm_count(RRDHOST *host, struct web_client *w, char *url); int web_client_api_request_v1_charts(RRDHOST *host, struct web_client *w, char *url); -int web_client_api_request_v1_archivedcharts(RRDHOST *host, struct web_client *w, char *url); int web_client_api_request_v1_chart(RRDHOST *host, struct web_client *w, char *url); int web_client_api_request_v1_data(RRDHOST *host, struct web_client *w, char *url); int web_client_api_request_v1_registry(RRDHOST *host, struct web_client *w, char *url); -- cgit v1.2.3