example.sql

WITH
--     0.5 AS quantile_level,
--     0.95 AS quantile_level,
    0.99 AS quantile_level,
    [(3, '2048m')] AS keeper_container_cpu_memory,
    [3] AS _keeper_bench_config_concurrency,
    [10000, 40000,  160000,  640000] AS _keeper_bench_config_iterations,
    3 AS _keeper_count,
    'm6a.4xlarge' AS _host_info,
    10 AS latest_n_runs,
    -- max CPU rate per benchmark_id
    -- a bit tricky as metric is a counter and therefore it requires diff between consecutive values per host
    T0 AS
    (
        SELECT
            benchmark_id,
            max(rate) AS cpu_rate_max
        FROM
        (
            SELECT
                benchmark_id,
                container_hostname,
                groupArray(ts) AS tss,
                groupArray(value) AS values,
                arrayDifference(values) AS rates
            FROM
            (
                SELECT
                    benchmark_id,
                    container_hostname,
                    toStartOfInterval(prometheus_ts, toIntervalSecond(1)) AS ts,
                    max(value) AS value
                FROM keeper_bench_metric
                WHERE metric = 'container_cpu_usage_seconds_total'
                GROUP BY
                    benchmark_id,
                    container_hostname,
                    ts
                ORDER BY
                    benchmark_id ASC,
                    container_hostname ASC,
                    ts ASC
            )
            GROUP BY
                benchmark_id,
                container_hostname
        )
        ARRAY JOIN rates AS rate
        GROUP BY benchmark_id
    ),
    -- runtime + max memory usage per benchmark_id
    T1 AS
    (
        SELECT
            keeper_container_cpu,
            keeper_container_memory,
            keeper_bench_config_concurrency,
            keeper_bench_config_iterations,
            keeper_bench_config_multi,
            benchmark_id,
            any(experiment_id) AS experiment_id,
            any(benchmark_ts) AS benchmark_ts,
            any(keeper_type) AS keeper_type,
            min(prometheus_ts) AS ts_min,
            max(prometheus_ts) AS ts_max,
            dateDiff('second', ts_min, ts_max) AS runtime_seconds,
            maxIf(value, metric = 'container_memory_working_set_bytes') AS memory_bytes_max,
            DENSE_RANK() OVER (PARTITION BY experiment_id, keeper_type ORDER BY benchmark_ts DESC) AS _benchmark_id
        FROM keeper_bench_info AS kbi
        INNER JOIN keeper_bench_metric AS kbr ON kbi.benchmark_id = kbr.benchmark_id
        WHERE (keeper_count = _keeper_count) AND (host_info = _host_info)
        GROUP BY
            keeper_container_cpu,
            keeper_container_memory,
            keeper_bench_config_concurrency,
            keeper_bench_config_iterations,
            keeper_bench_config_multi,
            benchmark_id
        ORDER BY
            keeper_container_cpu ASC,
            keeper_container_memory ASC,
            keeper_bench_config_concurrency ASC,
            keeper_bench_config_iterations ASC,
            keeper_bench_config_multi ASC
    ),
    -- add max CPU usages per benchmark_id
    T2 AS
    (
        SELECT *
        FROM T1 AS t1
        INNER JOIN T0 AS t0 ON t1.benchmark_id = t0.benchmark_id
    ),
    T3 AS
    (
        SELECT
            keeper_container_cpu,
            keeper_container_memory,
            keeper_bench_config_concurrency,
            keeper_bench_config_iterations,
            keeper_bench_config_multi,
            _benchmark_id,
            anyIf(runtime_seconds, keeper_type = 'chkeeper') AS runtime_seconds_ck,
            anyIf(runtime_seconds, keeper_type = 'zookeeper') AS runtime_seconds_zk,
            anyIf(memory_bytes_max, keeper_type = 'chkeeper') AS memory_bytes_max_ck,
            anyIf(memory_bytes_max, keeper_type = 'zookeeper') AS memory_bytes_max_zk,
            anyIf(cpu_rate_max, keeper_type = 'chkeeper') AS cpu_rate_max_ck,
            anyIf(cpu_rate_max, keeper_type = 'zookeeper') AS cpu_rate_max_zk,
            countIf(keeper_type = 'chkeeper') AS count_ck,
            countIf(keeper_type = 'zookeeper') AS count_zk
        FROM T2
        WHERE _benchmark_id <= latest_n_runs
        GROUP BY
            keeper_container_cpu,
            keeper_container_memory,
            keeper_bench_config_concurrency,
            keeper_bench_config_iterations,
            keeper_bench_config_multi,
            _benchmark_id
        ORDER BY
            keeper_container_cpu ASC,
            keeper_container_memory ASC,
            keeper_bench_config_concurrency ASC,
            keeper_bench_config_iterations ASC,
            keeper_bench_config_multi ASC,
            _benchmark_id ASC
    ),
    ALL AS
    (
        SELECT
            keeper_container_cpu AS c,
            keeper_container_memory AS m,
            keeper_bench_config_concurrency AS n,
            keeper_bench_config_iterations AS r,
            quantile(quantile_level)(runtime_seconds_ck) AS rt_ck_quantile,
            quantile(quantile_level)(runtime_seconds_zk) AS rt_zk_quantile,
            quantile(quantile_level)(memory_bytes_max_ck) AS mem_ck_quantile,
            quantile(quantile_level)(memory_bytes_max_zk) AS mem_zk_quantile,
            quantile(quantile_level)(cpu_rate_max_ck) AS cpu_ck_quantile,
            quantile(quantile_level)(cpu_rate_max_zk) AS cpu_zk_quantile,
            round(avg(runtime_seconds_zk) / avg(runtime_seconds_ck), 2) AS fac_rt,
            round(avg(memory_bytes_max_zk) / avg(memory_bytes_max_ck), 2) AS fac_mem,
            round(avg(cpu_rate_max_zk) / avg(cpu_rate_max_ck), 2) AS fac_cpu,
            countIf(count_ck = 1) as _count_ck,
            countIf(count_zk = 1) as _count_zk
        FROM T3
        WHERE
            (keeper_container_cpu, keeper_container_memory) IN (keeper_container_cpu_memory)
            AND keeper_bench_config_concurrency IN (_keeper_bench_config_concurrency)
            AND keeper_bench_config_iterations IN (_keeper_bench_config_iterations)
        GROUP BY
            keeper_container_cpu,
            keeper_container_memory,
            keeper_bench_config_concurrency,
            keeper_bench_config_iterations
        ORDER BY
            keeper_container_cpu ASC,
            keeper_container_memory ASC,
            keeper_bench_config_concurrency ASC,
            keeper_bench_config_iterations ASC
    )
SELECT
    c as keeper_cpu_cores,
    m as keeper_memory,
    n as keeper_clients,
    r as keeper_requests,
    leftPad(concat(CAST(round(rt_ck_quantile, 0), 'String'), ' s'), 5) AS runtime_chkeeper ,
    leftPad(concat(CAST(round(rt_zk_quantile, 0), 'String'), ' s'), 5) AS runtime_zookeeper,
    formatReadableSize(mem_ck_quantile) AS memory_chkeeper,
    formatReadableSize(mem_zk_quantile) AS memory_zookeeper,
    leftPad(concat(CAST(round(cpu_ck_quantile * 100, 0), 'String'), ' %'), 5) AS cpu_chkeeper,
    leftPad(concat(CAST(round(cpu_zk_quantile * 100, 0), 'String'), ' %'), 5) AS cpu_zookeeper,
    fac_rt AS factor_runtime,
    fac_mem AS factor_memory,
    fac_cpu AS factor_cpu
--     ,_count_ck as _runs_ck ,_count_zk as _runs_zk
FROM ALL
FORMAT PrettyCompactMonoBlock;