Multi-Tier database backend for long term metrics storage (#13263)

* Tier part 1

* Tier part 2

* Tier part 3

* Tier part 4

* Tier part 5

* Fix some ML compilation errors

* fix more conflicts

* pass proper tier

* move metric_uuid from state to RRDDIM

* move aclk_live_status from state to RRDDIM

* move ml_dimension from state to RRDDIM

* abstracted the data collection interface

* support flushing for mem db too

* abstracted the query api

* abstracted latest/oldest time per metric

* cleanup

* store_metric for tier1

* fix for store_metric

* allow multiple tiers, more than 2

* state to tier

* Change storage type in db. Query param to request min, max, sum or average

* Store tier data correctly

* Fix skipping tier page type

* Add tier grouping in the tier

* Fix to handle archived charts (part 1)

* Temp fix for query granularity when requesting tier1 data

* Fix parameters in the correct order and calculate the anomaly based on the anomaly count

* Proper tiering grouping

* Anomaly calculation based on anomaly count

* force type checking on storage handles

* update cmocka tests

* fully dynamic number of storage tiers

* fix static allocation

* configure grouping for all tiers; disable tiers for unittest; disable statsd configuration for private charts mode

* use default page dt using the tiering info

* automatic selection of tier

* fix for automatic selection of tier

* working prototype of dynamic tier selection

* automatic selection of tier done right (I hope)

* ask for the proper tier value, based on the grouping function

* fixes for unittests and load_metric_next()

* fixes for lgtm findings

* minor renames

* add dbengine to page cache size setting

* add dbengine to page cache with malloc

* query engine optimized to loop as little are required based on the view_update_every

* query engine grouping methods now do not assume a constant number of points per group and they allocate memory with OWA

* report db points per tier in jsonwrap

* query planer that switches database tiers on the fly to satisfy the query for the entire timeframe

* dbegnine statistics and documentation (in progress)

* calculate average point duration in db

* handle single point pages the best we can

* handle single point pages even better

* Keep page type in the rrdeng_page_descr

* updated doc

* handle future backwards compatibility - improved statistics

* support &tier=X in queries

* enfore increasing iterations on tiers

* tier 1 is always 1 iteration

* backfilling higher tiers on first data collection

* reversed anomaly bit

* set up to 5 tiers

* natural points should only be offered on tier 0, except a specific tier is selected

* do not allow more than 65535 points of tier0 to be aggregated on any tier

* Work only on actually activated tiers

* fix query interpolation

* fix query interpolation again

* fix lgtm finding

* Activate one tier for now

* backfilling of higher tiers using raw metrics from lower tiers

* fix for crash on start when storage tiers is increased from the default

* more statistics on exit

* fix bug that prevented higher tiers to get any values; added backfilling options

* fixed the statistics log line

* removed limit of 255 iterations per tier; moved the code of freezing rd->tiers[x]->db_metric_handle

* fixed division by zero on zero points_wanted

* removed dead code

* Decide on the descr->type for the type of metric

* dont store metrics on unknown page types

* free db_metric_handle on sql based context queries

* Disable STORAGE_POINT value check in the exporting engine unit tests

* fix for db modes other than dbengine

* fix for aclk archived chart queries destroying db_metric_handles of valid rrddims

* fix left-over freez() instead of OWA freez on median queries

Co-authored-by: Costa Tsaousis <costa@netdata.cloud>
Co-authored-by: Vladimir Kobal <vlad@prokk.net>

20 files changed, 879 insertions, 371 deletions

diff --git a/web/api/badges/web_buffer_svg.c b/web/api/badges/web_buffer_svg.c
index 8a539aa680..14759863d5 100644
--- a/web/api/badges/web_buffer_svg.c
+++ b/web/api/badges/web_buffer_svg.c
@@ -1111,7 +1111,7 @@ int web_client_api_request_v1_badge(RRDHOST *host, struct web_client *w, char *u
                                       points, after, before, group, group_options, 0, options,
                                       NULL, &latest_timestamp,
                                       NULL, NULL,
-                                      &value_is_null, NULL, 0);
+                                      &value_is_null, NULL, 0, 0);
 
         // if the value cannot be calculated, show empty badge
         if (ret != HTTP_RESP_OK) {
diff --git a/web/api/formatters/json_wrapper.c b/web/api/formatters/json_wrapper.c
index 4f9c75e357..04cace2fb3 100644
--- a/web/api/formatters/json_wrapper.c
+++ b/web/api/formatters/json_wrapper.c
@@ -90,7 +90,7 @@ void rrdr_json_wrapper_begin(RRDR *r, BUFFER *wb, uint32_t format, RRDR_OPTIONS
                    , kq, kq, sq, web_client_api_request_v1_data_group_to_string(group_method), sq
                    , kq, kq, sq);
 
-    web_client_api_request_v1_data_options_to_string(wb, options);
+    web_client_api_request_v1_data_options_to_string(wb, r->internal.query_options);
 
     buffer_sprintf(wb, "%s,\n   %sdimension_names%s: [", sq, kq, kq);
 
@@ -343,12 +343,21 @@ void rrdr_json_wrapper_begin(RRDR *r, BUFFER *wb, uint32_t format, RRDR_OPTIONS
 
     rrdr_buffer_print_format(wb, format);
 
+    buffer_sprintf(wb, "%s,\n"
+                       "   %sdb_points_per_tier%s: [ "
+                   , sq
+                   , kq, kq
+                   );
+
+    for(int tier = 0; tier < storage_tiers ; tier++)
+        buffer_sprintf(wb, "%s%zu", tier>0?", ":"", r->internal.tier_points_read[tier]);
+
+    buffer_strcat(wb, " ]");
+
     if((options & RRDR_OPTION_CUSTOM_VARS) && (options & RRDR_OPTION_JSON_WRAP)) {
-        buffer_sprintf(wb, "%s,\n   %schart_variables%s: ", sq, kq, kq);
+        buffer_sprintf(wb, ",\n   %schart_variables%s: ", kq, kq);
         health_api_v1_chart_custom_variables2json(r->st, wb);
     }
-    else
-        buffer_sprintf(wb, "%s", sq);
 
     buffer_sprintf(wb, ",\n   %sresult%s: ", kq, kq);
 
diff --git a/web/api/formatters/rrd2json.c b/web/api/formatters/rrd2json.c
index 623fc106fc..72cbfd024d 100644
--- a/web/api/formatters/rrd2json.c
+++ b/web/api/formatters/rrd2json.c
@@ -1,6 +1,7 @@
 // SPDX-License-Identifier: GPL-3.0-or-later
 
 #include "web/api/web_api_v1.h"
+#include "database/storage_engine.h"
 
 static inline void free_single_rrdrim(ONEWAYALLOC *owa, RRDDIM *temp_rd, int archive_mode)
 {
@@ -18,7 +19,18 @@ static inline void free_single_rrdrim(ONEWAYALLOC *owa, RRDDIM *temp_rd, int arc
         }
     }
 
-    onewayalloc_freez(owa, temp_rd->state);
+    for(int tier = 0; tier < storage_tiers ;tier++) {
+        if(!temp_rd->tiers[tier]) continue;
+
+        if(archive_mode) {
+            STORAGE_ENGINE *eng = storage_engine_get(temp_rd->tiers[tier]->mode);
+            if (eng)
+                eng->api.free(temp_rd->tiers[tier]->db_metric_handle);
+        }
+
+        onewayalloc_freez(owa, temp_rd->tiers[tier]);
+    }
+
     onewayalloc_freez(owa, temp_rd);
 }
 
@@ -89,7 +101,12 @@ void build_context_param_list(ONEWAYALLOC *owa, struct context_param **param_lis
         RRDDIM *rd = onewayalloc_memdupz(owa, rd1, sizeof(RRDDIM));
         rd->id = onewayalloc_strdupz(owa, rd1->id);
         rd->name = onewayalloc_strdupz(owa, rd1->name);
-        rd->state = onewayalloc_memdupz(owa, rd1->state, sizeof(*rd->state));
+        for(int tier = 0; tier < storage_tiers ;tier++) {
+            if(rd1->tiers[tier])
+                rd->tiers[tier] = onewayalloc_memdupz(owa, rd1->tiers[tier], sizeof(*rd->tiers[tier]));
+            else
+                rd->tiers[tier] = NULL;
+        }
         rd->next = (*param_list)->rd;
         (*param_list)->rd = rd;
     }
@@ -168,6 +185,7 @@ int rrdset2value_api_v1(
         , int *value_is_null
         , uint8_t *anomaly_rate
         , int timeout
+        , int tier
 ) {
     int ret = HTTP_RESP_INTERNAL_SERVER_ERROR;
 
@@ -175,7 +193,7 @@ int rrdset2value_api_v1(
 
     RRDR *r = rrd2rrdr(owa, st, points, after, before,
                        group_method, group_time, options, dimensions, NULL,
-                       group_options, timeout);
+                       group_options, timeout, tier);
 
     if(!r) {
         if(value_is_null) *value_is_null = 1;
@@ -232,6 +250,7 @@ int rrdset2anything_api_v1(
         , long group_time
         , uint32_t options
         , time_t *latest_timestamp
+        , int tier
 )
 {
     BUFFER *wb = query_params->wb;
@@ -250,7 +269,7 @@ int rrdset2anything_api_v1(
         dimensions ? buffer_tostring(dimensions) : NULL,
         query_params->context_param_list,
         group_options,
-        query_params->timeout);
+        query_params->timeout, tier);
     if(!r) {
         buffer_strcat(wb, "Cannot generate output with these parameters on this chart.");
         return HTTP_RESP_INTERNAL_SERVER_ERROR;
diff --git a/web/api/formatters/rrd2json.h b/web/api/formatters/rrd2json.h
index e90c13ba90..fa82463e17 100644
--- a/web/api/formatters/rrd2json.h
+++ b/web/api/formatters/rrd2json.h
@@ -78,6 +78,7 @@ extern int rrdset2anything_api_v1(
         , long group_time
         , uint32_t options
         , time_t *latest_timestamp
+        , int tier
 );
 
 extern int rrdset2value_api_v1(
@@ -99,6 +100,7 @@ extern int rrdset2value_api_v1(
         , int *value_is_null
         , uint8_t *anomaly_rate
         , int timeout
+        , int tier
 );
 
 extern void build_context_param_list(ONEWAYALLOC *owa, struct context_param **param_list, RRDSET *st);
diff --git a/web/api/formatters/value/value.c b/web/api/formatters/value/value.c
index d8e0c87b3d..84d8e11ef6 100644
--- a/web/api/formatters/value/value.c
+++ b/web/api/formatters/value/value.c
@@ -3,11 +3,7 @@
 #include "value.h"
 
 
-inline NETDATA_DOUBLE
-rrdr2value(RRDR *r, long i, RRDR_OPTIONS options, int *all_values_are_null, uint8_t *anomaly_rate, RRDDIM *temp_rd) {
-    if (r->st_needs_lock)
-        rrdset_check_rdlock(r->st);
-
+inline NETDATA_DOUBLE rrdr2value(RRDR *r, long i, RRDR_OPTIONS options, int *all_values_are_null, uint8_t *anomaly_rate, RRDDIM *temp_rd) {
     long c;
     RRDDIM *d;
 
diff --git a/web/api/queries/average/average.c b/web/api/queries/average/average.c
index d21b4a1b0a..0719d57fa8 100644
--- a/web/api/queries/average/average.c
+++ b/web/api/queries/average/average.c
@@ -11,7 +11,7 @@ struct grouping_average {
 };
 
 void grouping_create_average(RRDR *r, const char *options __maybe_unused) {
-    r->internal.grouping_data = callocz(1, sizeof(struct grouping_average));
+    r->internal.grouping_data = onewayalloc_callocz(r->internal.owa, 1, sizeof(struct grouping_average));
 }
 
 // resets when switches dimensions
@@ -23,7 +23,7 @@ void grouping_reset_average(RRDR *r) {
 }
 
 void grouping_free_average(RRDR *r) {
-    freez(r->internal.grouping_data);
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
diff --git a/web/api/queries/countif/countif.c b/web/api/queries/countif/countif.c
index 9b000bf007..369b20be95 100644
--- a/web/api/queries/countif/countif.c
+++ b/web/api/queries/countif/countif.c
@@ -37,7 +37,7 @@ static size_t countif_greaterequal(NETDATA_DOUBLE v, NETDATA_DOUBLE target) {
 }
 
 void grouping_create_countif(RRDR *r, const char *options __maybe_unused) {
-    struct grouping_countif *g = callocz(1, sizeof(struct grouping_countif));
+    struct grouping_countif *g = onewayalloc_callocz(r->internal.owa, 1, sizeof(struct grouping_countif));
     r->internal.grouping_data = g;
 
     if(options && *options) {
@@ -106,7 +106,7 @@ void grouping_reset_countif(RRDR *r) {
 }
 
 void grouping_free_countif(RRDR *r) {
-    freez(r->internal.grouping_data);
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
diff --git a/web/api/queries/des/des.c b/web/api/queries/des/des.c
index 39e32cc7ed..a6c4e4051d 100644
--- a/web/api/queries/des/des.c
+++ b/web/api/queries/des/des.c
@@ -37,16 +37,16 @@ static inline NETDATA_DOUBLE window(RRDR *r, struct grouping_des *g) {
     NETDATA_DOUBLE points;
     if(r->group == 1) {
         // provide a running DES
-        points = r->internal.points_wanted;
+        points = (NETDATA_DOUBLE)r->internal.points_wanted;
     }
     else {
         // provide a SES with flush points
-        points = r->group;
+        points = (NETDATA_DOUBLE)r->group;
     }
 
     // https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average
     // A commonly used value for alpha is 2 / (N + 1)
-    return (points > max_window_size) ? max_window_size : points;
+    return (points > (NETDATA_DOUBLE)max_window_size) ? (NETDATA_DOUBLE)max_window_size : points;
 }
 
 static inline void set_alpha(RRDR *r, struct grouping_des *g) {
@@ -70,7 +70,7 @@ static inline void set_beta(RRDR *r, struct grouping_des *g) {
 }
 
 void grouping_create_des(RRDR *r, const char *options __maybe_unused) {
-    struct grouping_des *g = (struct grouping_des *)mallocz(sizeof(struct grouping_des));
+    struct grouping_des *g = (struct grouping_des *)onewayalloc_mallocz(r->internal.owa, sizeof(struct grouping_des));
     set_alpha(r, g);
     set_beta(r, g);
     g->level = 0.0;
@@ -92,7 +92,7 @@ void grouping_reset_des(RRDR *r) {
 }
 
 void grouping_free_des(RRDR *r) {
-    freez(r->internal.grouping_data);
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
diff --git a/web/api/queries/incremental_sum/incremental_sum.c b/web/api/queries/incremental_sum/incremental_sum.c
index 26bdb540d7..afca530c30 100644
--- a/web/api/queries/incremental_sum/incremental_sum.c
+++ b/web/api/queries/incremental_sum/incremental_sum.c
@@ -12,7 +12,7 @@ struct grouping_incremental_sum {
 };
 
 void grouping_create_incremental_sum(RRDR *r, const char *options __maybe_unused) {
-    r->internal.grouping_data = callocz(1, sizeof(struct grouping_incremental_sum));
+    r->internal.grouping_data = onewayalloc_callocz(r->internal.owa, 1, sizeof(struct grouping_incremental_sum));
 }
 
 // resets when switches dimensions
@@ -25,7 +25,7 @@ void grouping_reset_incremental_sum(RRDR *r) {
 }
 
 void grouping_free_incremental_sum(RRDR *r) {
-    freez(r->internal.grouping_data);
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
diff --git a/web/api/queries/max/max.c b/web/api/queries/max/max.c
index 31004703ca..73cf9fa66a 100644
--- a/web/api/queries/max/max.c
+++ b/web/api/queries/max/max.c
@@ -11,7 +11,7 @@ struct grouping_max {
 };
 
 void grouping_create_max(RRDR *r, const char *options __maybe_unused) {
-    r->internal.grouping_data = callocz(1, sizeof(struct grouping_max));
+    r->internal.grouping_data = onewayalloc_callocz(r->internal.owa, 1, sizeof(struct grouping_max));
 }
 
 // resets when switches dimensions
@@ -23,7 +23,7 @@ void grouping_reset_max(RRDR *r) {
 }
 
 void grouping_free_max(RRDR *r) {
-    freez(r->internal.grouping_data);
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
diff --git a/web/api/queries/median/median.c b/web/api/queries/median/median.c
index 140106a9d4..d5fa1e5b6b 100644
--- a/web/api/queries/median/median.c
+++ b/web/api/queries/median/median.c
@@ -10,14 +10,15 @@ struct grouping_median {
     size_t series_size;
     size_t next_pos;
 
-    NETDATA_DOUBLE series[];
+    NETDATA_DOUBLE *series;
 };
 
 void grouping_create_median(RRDR *r, const char *options __maybe_unused) {
     long entries = r->group;
     if(entries < 0) entries = 0;
 
-    struct grouping_median *g = (struct grouping_median *)callocz(1, sizeof(struct grouping_median) + entries * sizeof(NETDATA_DOUBLE));
+    struct grouping_median *g = (struct grouping_median *)onewayalloc_callocz(r->internal.owa, 1, sizeof(struct grouping_median));
+    g->series = onewayalloc_mallocz(r->internal.owa, entries * sizeof(NETDATA_DOUBLE));
     g->series_size = (size_t)entries;
 
     r->internal.grouping_data = g;
@@ -31,7 +32,10 @@ void grouping_reset_median(RRDR *r) {
 }
 
 void grouping_free_median(RRDR *r) {
-    freez(r->internal.grouping_data);
+    struct grouping_median *g = (struct grouping_median *)r->internal.grouping_data;
+    if(g) onewayalloc_freez(r->internal.owa, g->series);
+
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
@@ -39,7 +43,8 @@ void grouping_add_median(RRDR *r, NETDATA_DOUBLE value) {
     struct grouping_median *g = (struct grouping_median *)r->internal.grouping_data;
 
     if(unlikely(g->next_pos >= g->series_size)) {
-        error("INTERNAL ERROR: median buffer overflow on chart '%s' - next_pos = %zu, series_size = %zu, r->group = %ld.", r->st->name, g->next_pos, g->series_size, r->group);
+        g->series = onewayalloc_doublesize( r->internal.owa, g->series, g->series_size * sizeof(NETDATA_DOUBLE));
+        g->series_size *= 2;
     }
     else
         g->series[g->next_pos++] = (NETDATA_DOUBLE)value;
diff --git a/web/api/queries/min/min.c b/web/api/queries/min/min.c
index 1af7e8c2e7..1752e9e0c9 100644
--- a/web/api/queries/min/min.c
+++ b/web/api/queries/min/min.c
@@ -11,7 +11,7 @@ struct grouping_min {
 };
 
 void grouping_create_min(RRDR *r, const char *options __maybe_unused) {
-    r->internal.grouping_data = callocz(1, sizeof(struct grouping_min));
+    r->internal.grouping_data = onewayalloc_callocz(r->internal.owa, 1, sizeof(struct grouping_min));
 }
 
 // resets when switches dimensions
@@ -23,7 +23,7 @@ void grouping_reset_min(RRDR *r) {
 }
 
 void grouping_free_min(RRDR *r) {
-    freez(r->internal.grouping_data);
+    onewayalloc_freez(r->internal.owa, r->internal.grouping_data);
     r->internal.grouping_data = NULL;
 }
 
diff --git a/web/api/queries/query.c b/web/api/queries/query.c
index 95d6aaf1b6..1e13dc5a7d 100644
--- a/web/api/queries/query.c
+++ b/web/api/queries/query.c
@@ -3,7 +3,6 @@
 #include "query.h"
 #include "web/api/formatters/rrd2json.h"
 #include "rrdr.h"
-#include "database/ram/rrddim_mem.h"
 
 #include "average/average.h"
 #include "countif/countif.h"
@@ -50,6 +49,8 @@ static struct {
     // continue after a flush as if nothing changed, for others a
     // cleanup of the internal structures may be required).
     NETDATA_DOUBLE (*flush)(struct rrdresult *r, RRDR_VALUE_FLAGS *rrdr_value_options_ptr);
+
+    TIER_QUERY_FETCH tier_query_fetch;
 } api_v1_data_groups[] = {
         {.name = "average",
                 .hash  = 0,
@@ -59,7 +60,8 @@ static struct {
                 .reset = grouping_reset_average,
                 .free  = grouping_free_average,
                 .add   = grouping_add_average,
-                .flush = grouping_flush_average
+                .flush = grouping_flush_average,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "mean",                           // alias on 'average'
                 .hash  = 0,
@@ -69,7 +71,8 @@ static struct {
                 .reset = grouping_reset_average,
                 .free  = grouping_free_average,
                 .add   = grouping_add_average,
-                .flush = grouping_flush_average
+                .flush = grouping_flush_average,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name  = "incremental_sum",
                 .hash  = 0,
@@ -79,7 +82,8 @@ static struct {
                 .reset = grouping_reset_incremental_sum,
                 .free  = grouping_free_incremental_sum,
                 .add   = grouping_add_incremental_sum,
-                .flush = grouping_flush_incremental_sum
+                .flush = grouping_flush_incremental_sum,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "incremental-sum",
                 .hash  = 0,
@@ -89,7 +93,8 @@ static struct {
                 .reset = grouping_reset_incremental_sum,
                 .free  = grouping_free_incremental_sum,
                 .add   = grouping_add_incremental_sum,
-                .flush = grouping_flush_incremental_sum
+                .flush = grouping_flush_incremental_sum,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "median",
                 .hash  = 0,
@@ -99,7 +104,8 @@ static struct {
                 .reset = grouping_reset_median,
                 .free  = grouping_free_median,
                 .add   = grouping_add_median,
-                .flush = grouping_flush_median
+                .flush = grouping_flush_median,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "min",
                 .hash  = 0,
@@ -109,7 +115,8 @@ static struct {
                 .reset = grouping_reset_min,
                 .free  = grouping_free_min,
                 .add   = grouping_add_min,
-                .flush = grouping_flush_min
+                .flush = grouping_flush_min,
+                .tier_query_fetch = TIER_QUERY_FETCH_MIN
         },
         {.name = "max",
                 .hash  = 0,
@@ -119,7 +126,8 @@ static struct {
                 .reset = grouping_reset_max,
                 .free  = grouping_free_max,
                 .add   = grouping_add_max,
-                .flush = grouping_flush_max
+                .flush = grouping_flush_max,
+                .tier_query_fetch = TIER_QUERY_FETCH_MAX
         },
         {.name = "sum",
                 .hash  = 0,
@@ -129,7 +137,8 @@ static struct {
                 .reset = grouping_reset_sum,
                 .free  = grouping_free_sum,
                 .add   = grouping_add_sum,
-                .flush = grouping_flush_sum
+                .flush = grouping_flush_sum,
+                .tier_query_fetch = TIER_QUERY_FETCH_SUM
         },
 
         // standard deviation
@@ -141,7 +150,8 @@ static struct {
                 .reset = grouping_reset_stddev,
                 .free  = grouping_free_stddev,
                 .add   = grouping_add_stddev,
-                .flush = grouping_flush_stddev
+                .flush = grouping_flush_stddev,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "cv",                           // coefficient variation is calculated by stddev
                 .hash  = 0,
@@ -151,7 +161,8 @@ static struct {
                 .reset = grouping_reset_stddev,  // not an error, stddev calculates this too
                 .free  = grouping_free_stddev,   // not an error, stddev calculates this too
                 .add   = grouping_add_stddev,    // not an error, stddev calculates this too
-                .flush = grouping_flush_coefficient_of_variation
+                .flush = grouping_flush_coefficient_of_variation,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "rsd",                          // alias of 'cv'
                 .hash  = 0,
@@ -161,7 +172,8 @@ static struct {
                 .reset = grouping_reset_stddev,  // not an error, stddev calculates this too
                 .free  = grouping_free_stddev,   // not an error, stddev calculates this too
                 .add   = grouping_add_stddev,    // not an error, stddev calculates this too
-                .flush = grouping_flush_coefficient_of_variation
+                .flush = grouping_flush_coefficient_of_variation,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
 
         /*
@@ -173,7 +185,8 @@ static struct {
                 .reset = grouping_reset_stddev,
                 .free  = grouping_free_stddev,
                 .add   = grouping_add_stddev,
-                .flush = grouping_flush_mean
+                .flush = grouping_flush_mean,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         */
 
@@ -186,7 +199,8 @@ static struct {
                 .reset = grouping_reset_stddev,
                 .free  = grouping_free_stddev,
                 .add   = grouping_add_stddev,
-                .flush = grouping_flush_variance
+                .flush = grouping_flush_variance,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         */
 
@@ -194,44 +208,48 @@ static struct {
         {.name = "ses",
                 .hash  = 0,
                 .value = RRDR_GROUPING_SES,
-                .init = grouping_init_ses,
+                .init  = grouping_init_ses,
                 .create= grouping_create_ses,
                 .reset = grouping_reset_ses,
                 .free  = grouping_free_ses,
                 .add   = grouping_add_ses,
-                .flush = grouping_flush_ses
+                .flush = grouping_flush_ses,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "ema",                         // alias for 'ses'
                 .hash  = 0,
                 .value = RRDR_GROUPING_SES,
-                .init = NULL,
+                .init  = NULL,
                 .create= grouping_create_ses,
                 .reset = grouping_reset_ses,
                 .free  = grouping_free_ses,
                 .add   = grouping_add_ses,
-                .flush = grouping_flush_ses
+                .flush = grouping_flush_ses,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
         {.name = "ewma",                        // alias for ses
                 .hash  = 0,
                 .value = RRDR_GROUPING_SES,
-                .init = NULL,
+                .init  = NULL,
                 .create= grouping_create_ses,
                 .reset = grouping_reset_ses,
                 .free  = grouping_free_ses,
                 .add   = grouping_add_ses,
-                .flush = grouping_flush_ses
+                .flush = grouping_flush_ses,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
 
         // double exponential smoothing
         {.name = "des",
                 .hash  = 0,
                 .value = RRDR_GROUPING_DES,
-                .init = grouping_init_des,
+                .init  = grouping_init_des,
                 .create= grouping_create_des,
                 .reset = grouping_reset_des,
                 .free  = grouping_free_des,
                 .add   = grouping_add_des,
-                .flush = grouping_flush_des
+                .flush = grouping_flush_des,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
 
         {.name = "countif",
@@ -242,7 +260,8 @@ static struct {
                 .reset = grouping_reset_countif,
                 .free  = grouping_free_countif,
                 .add   = grouping_add_countif,
-                .flush = grouping_flush_countif
+                .flush = grouping_flush_countif,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         },
 
         // terminator
@@ -254,7 +273,8 @@ static struct {
                 .reset = grouping_reset_average,
                 .free  = grouping_free_average,
                 .add   = grouping_add_average,
-                .flush = grouping_flush_average
+                .flush = grouping_flush_average,
+                .tier_query_fetch = TIER_QUERY_FETCH_AVERAGE
         }
 };
 
@@ -306,22 +326,24 @@ static void rrdr_set_grouping_function(RRDR *r, RRDR_GROUPING group_method) {
     int i, found = 0;
     for(i = 0; !found && api_v1_data_groups[i].name ;i++) {
         if(api_v1_data_groups[i].value == group_method) {
-            r->internal.grouping_create= api_v1_data_groups[i].create;
-            r->internal.grouping_reset = api_v1_data_groups[i].reset;
-            r->internal.grouping_free  = api_v1_data_groups[i].free;
-            r->internal.grouping_add   = api_v1_data_groups[i].add;
-            r->internal.grouping_flush = api_v1_data_groups[i].flush;
+            r->internal.grouping_create  = api_v1_data_groups[i].create;
+            r->internal.grouping_reset   = api_v1_data_groups[i].reset;
+            r->internal.grouping_free    = api_v1_data_groups[i].free;
+            r->internal.grouping_add     = api_v1_data_groups[i].add;
+            r->internal.grouping_flush   = api_v1_data_groups[i].flush;
+            r->internal.tier_query_fetch = api_v1_data_groups[i].tier_query_fetch;
             found = 1;
         }
     }
     if(!found) {
         errno = 0;
         internal_error(true, "QUERY: grouping method %u not found. Using 'average'", (unsigned int)group_method);
-        r->internal.grouping_create = grouping_create_average;
-        r->internal.grouping_reset  = grouping_reset_average;
-        r->internal.grouping_free   = grouping_free_average;
-        r->internal.grouping_add    = grouping_add_average;
-        r->internal.grouping_flush  = grouping_flush_average;
+        r->internal.grouping_create  = grouping_create_average;
+        r->internal.grouping_reset   = grouping_reset_average;
+        r->internal.grouping_free    = grouping_free_average;
+        r->internal.grouping_add     = grouping_add_average;
+        r->internal.grouping_flush   = grouping_flush_average;
+        r->internal.tier_query_fetch = TIER_QUERY_FETCH_AVERAGE;
     }
 }
 
@@ -380,7 +402,7 @@ static void rrdr_disable_not_selected_dimensions(RRDR *r, RRDR_OPTIONS options,
 
     // check if all dimensions are hidden
     if(unlikely(!dims_not_hidden_not_zero && dims_selected)) {
-        // there are a few selected dimensions
+        // there are a few selected dimensions,
         // but they are all zero
         // enable the selected ones
         // to avoid returning an empty chart
@@ -424,281 +446,671 @@ static inline void rrdr_done(RRDR *r, long rrdr_line) {
 
 
 // ----------------------------------------------------------------------------
-// fill RRDR for a single dimension
+// tier management
+
+static int rrddim_find_best_tier_for_timeframe(RRDDIM *rd, time_t after_wanted, time_t before_wanted, long points_wanted) {
+    if(unlikely(storage_tiers < 2))
+        return 0;
+
+    if(unlikely(after_wanted == before_wanted || points_wanted <= 0 || !rd || !rd->rrdset)) {
+
+        if(!rd)
+            internal_error(true, "QUERY: NULL dimension - invalid params to tier calculation");
+        else
+            internal_error(true, "QUERY: chart '%s' dimension '%s' invalid params to tier calculation",
+                           (rd->rrdset)?rd->rrdset->name:"unknown", rd->name);
+
+        return 0;
+    }
+
+    //BUFFER *wb = buffer_create(1000);
+    //buffer_sprintf(wb, "Best tier for chart '%s', dim '%s', from %ld to %ld (dur %ld, every %d), points %ld",
+    //               rd->rrdset->name, rd->name, after_wanted, before_wanted, before_wanted - after_wanted, rd->update_every, points_wanted);
+
+    long weight[storage_tiers];
+
+    for(int tier = 0; tier < storage_tiers ; tier++) {
+