Используйте место в 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А все ли колонки нужны?
Часто бизнес-пользователи приходят и говорят — нам нужны все вот эти вот колонки! Ну если и так и так идет добавление новых справочников / разрезов, чаще всего идет балком добавление и все поля появляются в витрине — посмотрите на них внимательнее через 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
-- тут обязательно не дистрибьютед табличка, а настоящая, в дистрибьютед ж нет данных =)
'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Этот код нам выдаст самые популярные фильтры, в хорошей картине мира первым полем будет поле партицирования (надеюсь) и дальше внимательно смотрите на резкие падения в значениях, скорее всего, 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;А мы вообще попадаем в индексы?
Мы все сделали, проекции, индексы, скип индексы — встает вопрос, а мы вообще попадаем в них? простая конструкция, которая позволит вам проверять ваши запросы
EXPLAIN INDEXES = 1
-- YOUR SELECT FROM INSPECTORВот в качестве примера и видно, сколько блоков взяли из общего количества и на каких шагах
Вообще, детально советую посмотреть видео тут, в целом документация по многим пунктам у ClickHouse исчерпывающая =)
Удобный чарт для отслеживания в моменте нагрузки
не совсем скрипт, больше чарт, у нас такой чарт есть на дашборде «Здоровье ClickHouse»
- Создаем датасет со скриптом
select * from clusterAllReplicas('{cluster}', system.query_log)- Создаем барчарт, с формулой на оси Y
datetrunc([query_start_time], "minute",15)- выкидываем sum([query_duration_ms])/1000 в Y
- Выкидываем GET_ITEM([tables],1) в цвета
- Поставьте фильтр на метрику (HAVING), чтобы убрать совсем маленькие запросы
Можно быстро понять, кто DDOSил систему в режиме online
