Полезные скрипты в ClickHouse для анализа производительности SELECT

Используйте место в ClickHouse с пользой

Обрезайте ненужное

Посмотрите, на какие таблицы вы тратите место, попытайтесь оценить ценность таблиц vs сколько место они съедают, сколько ресурсов тратится на то, чтобы выдерживать SLA поставки этих таблиц. ClickHouse любит SSD, поэтому в целом хранение надо стараться оптимизировать. В Маркете мы пришли в итоге к тому, что нашли способ и идеи, как создать в соседней схеме «Песочницу» для аналитиков с TTL поменьше и поняли, что их таблицы наносят больше пользы, чем лежащие данные по ассортименту с 2020 года.

SELECT table,
formatReadableSize (sum(bytes))
size,
min (min_date) as min_date,
max (max_date) as max_date
FROM cluster('{cluster}', system. parts)
WHERE active
GROUP BY table order by sum(bytes) DESC

SELECT table,
formatReadableSize (sum(bytes))
size,
min (min_date) as min_date,
max (max_date) as max_date
FROM cluster('{cluster}', system. parts)
WHERE active
GROUP BY table order by sum(bytes) DESC

А все ли колонки нужны?

Часто бизнес-пользователи приходят и говорят — нам нужны все вот эти вот колонки! Ну если и так и так идет добавление новых справочников / разрезов, чаще всего идет балком добавление и все поля появляются в витрине — посмотрите на них внимательнее через 2-3 недели, а какие вообще никто не юзал с тех пор?

WITH
    'your_b' AS db_name,
    'your_table' AS tbl_name,
    concat(db_name, '.', tbl_name) AS full_table_name,
    column_usage_stats AS (
        SELECT
            splitByChar('.', full_column_name)[3] AS column_name,
            count() AS usage_count
        FROM cluster('{cluster}',system,query_log)
        ARRAY JOIN columns AS full_column_name
        WHERE
            -- за последние 30 дней
            event_date >= today() - 30
            -- уберем селекторы
            AND query not like 'SELECT DISTINCT%'
            AND startsWith(full_column_name, concat(full_table_name, '.'))
        GROUP BY
            column_name
    )
SELECT
    c.name AS column_name,
    c.type,
    ifNull(s.usage_count, 0) AS usage_count,
    bar(usage_count, 0, max(usage_count) OVER (), 30) AS popularity_bar
FROM system.columns AS c
LEFT JOIN column_usage_stats AS s ON c.name = s.column_name
WHERE
    c.database = db_name
    AND c.table = tbl_name
ORDER BY
    usage_count DESC,
    c.position ASC

WITH
    'your_b' AS db_name,
    'your_table' AS tbl_name,
    concat(db_name, '.', tbl_name) AS full_table_name,
    column_usage_stats AS (
        SELECT
            splitByChar('.', full_column_name)[3] AS column_name,
            count() AS usage_count
        FROM cluster('{cluster}',system,query_log)
        ARRAY JOIN columns AS full_column_name
        WHERE
            -- за последние 30 дней
            event_date >= today() - 30
            -- уберем селекторы
            AND query not like 'SELECT DISTINCT%'
            AND startsWith(full_column_name, concat(full_table_name, '.'))
        GROUP BY
            column_name
    )
SELECT
    c.name AS column_name,
    c.type,
    ifNull(s.usage_count, 0) AS usage_count,
    bar(usage_count, 0, max(usage_count) OVER (), 30) AS popularity_bar
FROM system.columns AS c
LEFT JOIN column_usage_stats AS s ON c.name = s.column_name
WHERE
    c.database = db_name
    AND c.table = tbl_name
ORDER BY
    usage_count DESC,
    c.position ASC

Замените в коде выше таблицу и Базу на свои и посмотрите, так ли нужны были эти колонки

А если колонки очень большие?

Простой скрипт понять, а где же мы больше всего тратим места на диске, это мягкий сигнал про то, что, скорее всего, работа с этими колонками тоже не очень простая

WITH 
    -- тут обязательно не дистрибьютед табличка, а настоящая, в дистрибьютед ж нет данных =)
    'some_table'as table_name 
SELECT
    name AS column_name,
    data_compressed_bytes AS compressed_size_bytes,
    data_uncompressed_bytes AS uncompressed_size_bytes,
    marks_bytes
FROM system.columns
WHERE table = table_name
  AND database = currentDatabase()
ORDER BY data_compressed_bytes DESC;

WITH 
    -- тут обязательно не дистрибьютед табличка, а настоящая, в дистрибьютед ж нет данных =)
    'some_table'as table_name 
SELECT
    name AS column_name,
    data_compressed_bytes AS compressed_size_bytes,
    data_uncompressed_bytes AS uncompressed_size_bytes,
    marks_bytes
FROM system.columns
WHERE table = table_name
  AND database = currentDatabase()
ORDER BY data_compressed_bytes DESC;

И сразу вопрос

- ну да, вот эта JSON очень большая, но она же мне нужна?
- а когда нужна?
- ну мы анализируем конверсию через пару дней после запуска компании так детально
- а давай TTL на колонку поставим 14 дней?
- о, круто, давай!

А как мне сортировать таблицу?

Если выше мы просто брали из логов columns, то с точки зрения оптимальной сортировки нам нужны колонки, которые были в секции WHERE. Тут скрипт станет другим, будем парсить query, как же я не люблю регулярки =)

WITH
    'your_table' AS tbl_name
        SELECT 
        tbl_name,
    replaceAll(arrayJoin(arrayDistinct(extractAll(coalesce(arrayElement(splitByString('WHERE',coalesce(replaceAll(query,'"',''),'')),2),''), 't1\\.([\w]+)'))),')','') as field_name,
        SUM(1) as select_count
        FROM  cluster('{cluster}',system,query_log)
        WHERE query ilike 'select%'||tbl_name||'%'
        AND query NOT like 'select distinct%'
        GROUP BY field_name
        ORDER BY select_count DESC

WITH
    'your_table' AS tbl_name
        SELECT 
        tbl_name,
    replaceAll(arrayJoin(arrayDistinct(extractAll(coalesce(arrayElement(splitByString('WHERE',coalesce(replaceAll(query,'"',''),'')),2),''), 't1\\.([\w]+)'))),')','') as field_name,
        SUM(1) as select_count
        FROM  cluster('{cluster}',system,query_log)
        WHERE query ilike 'select%'||tbl_name||'%'
        AND query NOT like 'select distinct%'
        GROUP BY field_name
        ORDER BY select_count DESC

Этот код нам выдаст самые популярные фильтры, в хорошей картине мира первым полем будет поле партицирования (надеюсь) и дальше внимательно смотрите на резкие падения в значениях, скорее всего, 3-4 поля будут сильно более популярные, чем остальные — это есть ваши претенденты на сортировку

На что мы тратим ресурсы?

Эту табличку очень люблю, написал на нее скрипт несколько лет назад и она у нас самая первая на дашборде «Здоровье ClickHouse», сделана через QL-чарт с параметрами, то есть такой вид чарта, где можно что угодно написать в SQL и это визуализировать, оно удобно в моменте посмотреть, кто сейчас нагнул машину

Со временем, когда мы начали подключать доп штуки в ClickHouse и DataLens(словари, умные справочники, разрыв селекторов между собой) добавлялись новые колонки, но все еще не умещается на 14′ монике =)

WITH
    'cubes.cubes_clickhouse__' AS prefix_text,
    'cubes' as db_name,
    max(sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')])) OVER () AS max_user_cpu
SELECT
    replace(tables[1], prefix_text, '') || ',' || replace(tables[2], prefix_text, '') AS tables,
    CASE WHEN query ILIKE '%dictGet%' THEN 'dict' ELSE '-' END AS dicts,
    bar(
        sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')]),
        0,
        max_user_cpu,
        12
    ) AS barchik,
    formatReadableQuantity(sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')])) AS userCPU,
    bar(
        sum(CASE WHEN query LIKE '%DISTINCT%' THEN `ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')] ELSE 0 END),
        0,
        max_user_cpu,
        12
    ) AS "distinct bar",
    formatReadableQuantity(
        sum(CASE WHEN query LIKE '%DISTINCT%' THEN `ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')] ELSE 0 END)
    ) AS "userCPU distincts",
    ROUND(sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')]) / count(*) / 1000000) AS "per query",
    formatReadableSize(sum(memory_usage)) AS "Memory eaten",
    SUM(query_duration_ms / 1000) AS seconds,
    AVG(CASE WHEN NOT query LIKE '%DISTINCT%' THEN result_rows ELSE 0 END) AS "chart rows",
    uniq(CASE WHEN query LIKE '%DISTINCT%' THEN query ELSE '' END) - 1 AS "distinct count selectors",
    sum(1) AS cnt,
    avg(read_rows) as "read rows"
FROM cluster('{cluster}', system.query_log)
WHERE
    event_time > {{left_datetime}}
    AND is_initial_query = 1
    AND type = 'QueryFinish'
    AND query ilike '%'||db_name||'.%'
GROUP BY tables, dicts
ORDER BY sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')]) DESC
LIMIT 30;

WITH
    'cubes.cubes_clickhouse__' AS prefix_text,
    'cubes' as db_name,
    max(sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')])) OVER () AS max_user_cpu
SELECT
    replace(tables[1], prefix_text, '') || ',' || replace(tables[2], prefix_text, '') AS tables,
    CASE WHEN query ILIKE '%dictGet%' THEN 'dict' ELSE '-' END AS dicts,
    bar(
        sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')]),
        0,
        max_user_cpu,
        12
    ) AS barchik,
    formatReadableQuantity(sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')])) AS userCPU,
    bar(
        sum(CASE WHEN query LIKE '%DISTINCT%' THEN `ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')] ELSE 0 END),
        0,
        max_user_cpu,
        12
    ) AS "distinct bar",
    formatReadableQuantity(
        sum(CASE WHEN query LIKE '%DISTINCT%' THEN `ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')] ELSE 0 END)
    ) AS "userCPU distincts",
    ROUND(sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')]) / count(*) / 1000000) AS "per query",
    formatReadableSize(sum(memory_usage)) AS "Memory eaten",
    SUM(query_duration_ms / 1000) AS seconds,
    AVG(CASE WHEN NOT query LIKE '%DISTINCT%' THEN result_rows ELSE 0 END) AS "chart rows",
    uniq(CASE WHEN query LIKE '%DISTINCT%' THEN query ELSE '' END) - 1 AS "distinct count selectors",
    sum(1) AS cnt,
    avg(read_rows) as "read rows"
FROM cluster('{cluster}', system.query_log)
WHERE
    event_time > {{left_datetime}}
    AND is_initial_query = 1
    AND type = 'QueryFinish'
    AND query ilike '%'||db_name||'.%'
GROUP BY tables, dicts
ORDER BY sum(`ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')]) DESC
LIMIT 30;

А мы вообще попадаем в индексы?

Мы все сделали, проекции, индексы, скип индексы — встает вопрос, а мы вообще попадаем в них? простая конструкция, которая позволит вам проверять ваши запросы

EXPLAIN INDEXES = 1
-- YOUR SELECT FROM INSPECTOR

EXPLAIN INDEXES = 1
-- YOUR SELECT FROM INSPECTOR

Вот в качестве примера и видно, сколько блоков взяли из общего количества и на каких шагах

Вообще, детально советую посмотреть видео тут, в целом документация по многим пунктам у ClickHouse исчерпывающая =)

Удобный чарт для отслеживания в моменте нагрузки

не совсем скрипт, больше чарт, у нас такой чарт есть на дашборде «Здоровье ClickHouse»

Создаем датасет со скриптом

select * from clusterAllReplicas('{cluster}', system.query_log)

select * from clusterAllReplicas('{cluster}', system.query_log)

Создаем барчарт, с формулой на оси Y

datetrunc([query_start_time], "minute",15)

datetrunc([query_start_time], "minute",15)

выкидываем sum([query_duration_ms])/1000 в Y
Выкидываем GET_ITEM([tables],1) в цвета
Поставьте фильтр на метрику (HAVING), чтобы убрать совсем маленькие запросы

Можно быстро понять, кто DDOSил систему в режиме online

Полезные скрипты в ClickHouse для анализа производительности SELECT

Используйте место в ClickHouse с пользой

А как мне сортировать таблицу?

На что мы тратим ресурсы?

А мы вообще попадаем в индексы?

Удобный чарт для отслеживания в моменте нагрузки

Комментарии

Добавить комментарий Отменить ответ

Больше записей

Editor API

DataLens Editor

Полезные скрипты в ClickHouse для анализа производительности SELECT

Merge — Легкое объединение разных таблиц в CH