Оптимизация ClickHouse

ClickHouse – это высокопроизводительная аналитическая колоночная СУБД, и её оптимизация требует понимания её архитектуры и принципов работы. Оптимизация ClickHouse — это итеративный процесс. Проработав различные интернет-ресурсы, сформировал список (чек-лист) рекомендаций по оптимизации ClickHouse.

Оптимизация схемы базы данных и дизайна таблиц (Data Schema Optimization)

Правильный дизайн схемы и таблиц в ClickHouse позволяет в полной мере использовать возможности этой СУБД и достигать невероятной производительности при обработке огромных объёмов данных.

• Выбор правильного движка таблицы (Table Engine) по ситуации:
MergeTree семейство: это основной выбор для OLAP-задач, его производные:
MergeTree - базовый, общий случай;
ReplacingMergeTree - для удаления дубликатов по ORDER BY ключу и версии;
SummingMergeTree - для агрегации данных по ORDER BY ключу;
AggregatingMergeTree - для предварительной агрегации с помощью AggregateFunction типов;
CollapsingMergeTree - для удаления строк с противоположными знаками, например, дебет / кредит;
VersionedCollapsingMergeTree - улучшенный CollapsingMergeTree с поддержкой версий.
Distributed - не хранит данные, а распределяет запросы по шардам. Обязателен для масштабируемых кластеров.
Buffer - для асинхронной вставки в базовые таблицы, но может быть сложен в управлении.
MaterializedView - для создания предварительно агрегированных или трансформированных таблиц.

• Чем более точно заданы наименьшие типы данных для столбцов, тем легче сжимаются данные. Таблица будет занимать меньше места на диске, быстрее чтение (I/O) и поиск будет осуществляться быстрее. Использование наименьшего по размеру возможного типа данных:
UInt8, UInt16, Int32 вместо Int64. Кроме целых чисел различных размеров ClickHouse предлагает беззнаковые варианты, минимальное значение которых равно 0. Эти типы занимают меньше битов для колонки, например, UInt16 имеет максимальное значение 65535, что в два раза больше, чем Int16;
Float32 вместо Float64;
Date32, Date вместо DateTime для дат без времени;
DateTime (точность до секунд, использует 32 бита) вместо 64-битного DateTime64 (если не требуется точность до наносекунд);
FixedString - для строк фиксированной длины, например, UUID;
Enum - для строковых столбцов с очень малым и фиксированным набором значений. Тип Enum используется для эффективного кодирования и валидации перечисляемых значений. Enums могут быть 8 или 16 бит, в зависимости от количества уникальных значений, которые они должны хранить;
LowCardinality - используйте для строковых столбцов с ограниченным количеством (менее 10 000 или менее 1%) уникальных значений. ClickHouse применяет кодирование словаря к полям LowCardinality, что значительно уменьшает размер хранения и увеличивает производительность запросов. Избегать LowCardinality для часто меняющихся наборов значений, а также для очень коротких строк (менее 4 символов), для которых затраты на кодирование превышают преимущества;
Array эффективен для хранения списков однотипных элементов, когда порядок и количество элементов переменны.
Использование правильных типов данных обеспечит ожидаемую семантику при фильтрации и агрегации.

• Избегать Nullable-столбцов, где это возможно / сводить их количество к минимуму. Искушение использовать их часто велико, так как они делают механизм вставки данных более гибким. Nullable-столбец негативно влияет на производительность, так как каждый раз нужно обрабатывать дополнительную колонку. Для каждого Nullable-столбца автоматически создаётся скрытая колонка типа UInt8, невидимая для пользователей.
Используйте Nullable-столбцы только в том случае, если существует разница между "unknown" и "0 / пусто". По умолчанию для не-nullable столбцов Clickhouse будет преобразовывать NULL-значения в "нейтральный" для данного типа данных значения: 0 - для Integer, пустая строка ’’ - для String.

• Ключ сортировки (ORDER BY) - самый важный элемент производительности. Он определяет, как данные таблицы отсортированы и физически хранятся на диске, является основой для создания разреженного индекса (первичного ключа). «Разреженный» означает, что индекс хранит «засечки» не для каждой строки, а для каждого блока данных (гранулы). ClickHouse использует этот индекс для быстрого пропуска блоков данных, которые не соответствуют условиям WHERE. Столбцы в ORDER BY должны быть теми, по которым чаще всего выполняется фильтрация, соединение, группировка и сортировка записей. В начале слева столбцы - наиболее "грубые" (часто используемые в WHERE) и заканчивая более "детализированными". Сначала колонки с низкой кардинальностью. Лучше упорядочивать записи в порядке возрастания кардинальности столбцов.
Не следует делать ORDER BY слишком длинным без необходимости, поскольку каждое поле в ORDER BY увеличивает стоимость операций слияния.

• PRIMARY KEY является префиксом ORDER BY. Первичный ключ не является уникальным, но создает разреженный индекс для быстрого поиска блоков данных. Разреженный индекс хранит указатели на первую строку каждой гранулы. Гранулы - это наименьшие единицы данных, читаемые во время выполнения запроса. Они содержат до фиксированного количества строк, определяемого index_granularity (по умолчанию 8192 строк). Гранулы хранятся последовательно и сортируются по первичному ключу. В ClickHouse каждая часть имеет свой собственный первичный индекс. Когда части сливаются, первичные индексы слитых частей также сливаются.

• ClickHouse не имеет понятия внешних ключей (Foreign Keys), что не запрещает соединения таблиц в запросах, но означает, что ссылочная целостность остаётся на ответственности пользовательских приложений.

• Золотое правило: чтобы разреженный индекс использовался, в запросе в условие WHERE должны указываться столбцы из ORDER BY без функций. Разреженный индекс не будет задействован для WHERE toMonth(transaction_date) = 7;

• PARTITION BY (ключ партиционирования) разделяет данные на физические партиции (папки на диске). При запросах ClickHouse будет пропускать целые партиции, если они не соответствуют условиям WHERE. Секционирование осуществлять по столбцам, по которым часто фильтруются большие диапазоны данных, например, toYYYYMM(transaction_date), transaction_date). Нужно избегать слишком большого или слишком малого количества партиций. Оптимально 10-100 партиций на таблицу, если нет специфичных требований, не превышало 1000. Размер партиции - от 1 до 300 Гб.

• Денормализация данных: в ClickHouse JOIN-ы значительно дороже, чем в традиционных СУБД. В ClickHouse фильтрация записей быстрее JOIN-ов. Денормализация таблиц (плоские широкие таблицы), т. е. хранение связанных данных в одной таблице, почти всегда предпочтительнее для аналитики. Комбинирование таблиц часто включает в себя перенос соединений с этапа запроса на этап вставки (Insert).

• Нагрузка любого алгоритма сжатия в большинстве случаев будет компенсирована сокращением I/O. Поэтому улучшение сжатия данных должно быть первым приоритетом при работе над обеспечением производительности запросов ClickHouse. Сжатие в ClickHouse зависит от 3 основных факторов: ключа сортировки, типов данных и используемых кодеков.
Кодеки сжатия (Compression Codecs): ClickHouse автоматически сжимает данные. Стандартные кодеки LZ4, ZSTD обычно очень эффективны. Можно указывать кодеки для отдельных столбцов, например, CODEC(ZSTD(19)), CODEC(Delta, ZSTD).
Кодеки DoubleDelta / Gorilla очень эффективны для чисел, которые меняются незначительно от строки к строке - временные ряды:
CREATE TABLE time_series
(timeseries DateTime CODEC(DoubleDelta), intValue UInt64 CODEC(Gorilla))
ENGINE = MergeTree
ORDER BY timeseries;
Применимы как к целочисленным, так и к float типам данных.

• Проверить степень сжатия по столбцам таблицы можно:
SELECT
column
,formatReadableSize(data_compressed_bytes) AS compressed_size
,formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
,data_compressed_bytes / data_uncompressed_bytes AS compression_ratio
FROM system.parts_columns
WHERE table = 'your_table_name'
ORDER BY compression_ratio

• Для столбцов, по которым часто фильтрация, использование индексов пропуска (Skipping Indexes) - вторичные индексы, позволяющие пропускать блоки данных, не являющиеся частью ORDER BY. Когда использовать: для очень больших таблиц со столбцами, по которым часто фильтруют, но которые не входят в первичный ключ. Типы: minmax, set, ngrambf_v1, tokenbf_v1. Вторичные индексы увеличивают время записи и объём хранимых данных.

• Когда избегать индексации:
- - Индекс бесполезен, если столбец имеет мало различных значений. В таком случае поиск по индексу может быть более ресурсоёмким, чем сканирование всех данных;
- - Частые изменения данных требуют обслуживания индекса, что может привести к снижению производительности;
- - Небольшая таблица может полностью поместиться в оперативной памяти. Сканирование этой таблицы может быть быстрее, чем поиск по её индексу.

• Физический порядок столбцов в таблице (Physical Column Order) не так критичен, но всё же столбцы, используемые в ORDER BY, PARTITION BY, TTL лучше располагать в начале схемы таблицы. Часто запрашиваемые столбцы также лучше располагать ближе к началу схемы таблицы. Далее компонуйте столбцы по смыслу.

• TTL (Time-To-Live): автоматически удаляет старые данные или перемещает их на более дешёвые носители, экономит дисковое пространство и ускоряет запросы, уменьшая объём данных для сканирования.

• Материализованные представления (Materialized Views) предварительно агрегируют или трансформируют данные при вставке в базовую таблицу, значительно ускоряют часто используемые агрегации и отчеты, так как данные уже готовы. Создавайте материализованные представления поверх MergeTree таблиц (часто SummingMergeTree или AggregatingMergeTree), отдельную агрегатную таблицу, в которую ClickHouse сам будет писать нужные группировки при каждом INSERT. Потребители читают уже готовые строки вместо GROUP BY по миллиардам строк.
Инкрементные материализованные представления - перенос стоимости вычислений с этапа запроса данных на этап вставки, включая возможность инкрементального вычисления агрегатных значений. Обновляемые материализованные представления - подобно материализованным представлениям в других СУБД позволяют периодически вычислять результаты запроса и кэшировать результат.

• Материализованный столбец (Materialized Column) для хранения сложно вычисляемых значений
ALTER TABLE my_table ADD COLUMN col_matrlzd String MATERIALIZED <выражение>;
и индекс по этому столбцу
ALTER TABLE my_table ADD INDEX idx_col_matrlzd_minmax col_matrlzd TYPE minmax GRANULARITY 4;
ускоряют чтение, фильтрацию по предвычисленному полю.

Оптимизация SQL-запросов (Query Optimization)

Поиск медленных запросов: ClickHouse собирает и регистрирует информацию о каждом выполненном запросе в журнале запросов, в таблице system.query_log. Для каждого выполненного запроса записывается статистика: время выполнения запроса, количество прочитанных строк, использование CPU, памяти, попадание в кэш файловой системы.

Найти 10 наиболее долго выполняющихся запросов зв последний час для базы данных my_database:
SELECT
type
,event_time
,query_duration_ms -- сколько времени выполнялся запрос
,read_rows
,tables
,query
FROM clusterAllReplicas(default, system.query_log)
WHERE has(databases, 'my_database')
AND type='QueryFinish'
AND event_time >= (now() - toIntervalMinute(60))
ORDER BY query_duration_ms DESC
LIMIT 10

10 запросов, которые больше всего расходуют оперативную память или утилизируют процессоры:
SELECT
type
,query_id
,event_time
,formatReadableSize(memory_usage) AS memory
,ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU
,ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU
,(ProfileEvents['CachedReadBufferReadFromCacheMicroseconds']) / 1000000 AS FromCacheSeconds
,(ProfileEvents['CachedReadBufferReadFromSourceMicroseconds']) / 1000000 AS FromSourceSeconds
,normalized_query_hash
FROM clusterAllReplicas(default, system.query_log)
WHERE has(databases, 'my_database')
AND type='QueryFinish'
AND event_time >= (now() - toIntervalDay(2))
AND event_time <= now()
AND user NOT ILIKE '%internal%'
ORDER BY memory_usage DESC
LIMIT 10

Чтобы ускорить производительность запросов, ClickHouse использует кэширование, что может скрыть потенциальные узкие места ввода-вывода или плохую схему таблицы. При отладке запросов рекомендуется отключить кеш файловой системы:
set enable_filesystem_cache = 0

• В запросах на чтение данных надлежит выбирать только необходимые столбцы: чем меньше столбцов - тем быстрее чтение (I/O).
В продуктивной среде избегать запросов SELECT * FROM ...

• PREWHERE вместо WHERE (применимо для некоторых случаев). PREWHERE сначала фильтрует данные, а затем загружает остальные столбцы для отфильтрованных строк. Может быть быстрее, если фильтр очень селективен и уменьшает количество строк. WHERE загружает все столбцы, а затем фильтрует. ClickHouse часто сам оптимизирует WHERE до PREWHERE, но иногда явное указание помогает.
В запросах сначала - PREWHERE, а потом JOIN.
В подзапросах следует исключать лишние строки до объединения, поскольку это уменьшает объём пересылки между шардами.

• Фильтрация и уникализация записей на ранних этапах. Имеет значение количество строк, выдаваемых подзапросами, поэтому наружу следует передавать меньше строк. Это же касается и отсечения дубликатов строк.

• LIMIT n - останавливает чтение данных после достижения указанного лимита. LIMIT BY очень эффективен для "Топ-N" запросов.

• SAMPLE n или SAMPLE n BY expr - для приблизительных результатов для получения данных из части таблицы, если точные результаты не требуются. Значительное ускорение запросов к очень большим таблицам.

• Избегать OFFSET для больших значений. OFFSET заставляет ClickHouse считывать и отбрасывать N строк, для больших N это очень неэффективно. Лучше фильтровать по ключам или использовать LIMIT ... OFFSET ... с ORDER BY и фильтрами по последнему значению.

• Размещать столбцы с малым количеством уникальных значений (низкой кардинальностью) в начале GROUP BY и ORDER BY для лучшей производительности. ClickHouse агрегирует данные в оперативной памяти (достаточно памяти - см. max_memory_usage).

• Группировка (GROUP BY) по столбцам с высокой кардинальностью значений потребляет очень много оперативной памяти;

• Использование FINAL с осторожностью: модификатор FINAL для MergeTree движков (особенно ReplacingMergeTree, CollapsingMergeTree) гарантирует, что все части данных объединены и обработаны. Он очень ресурсоемок и медленен, поскольку требует полной агрегации всех данных. Используйте его только тогда, когда вам абсолютно необходимы последние / уникальные записи и нет других способов (например, фильтрация по _version или _sign).

• Для достижения оптимальной производительности рекомендуется использовать умеренное количество таблиц (2-3 таблицы) в запросе с JOIN-ами.

• Если без JOIN не обойтись, то выполнять JOIN по столбцам, которые есть в первичном ключе обеих таблиц;

• Если дубли не важны, то ANY LEFT JOIN вместо LEFT JOIN;

• Мелкую таблицу / справочник поместить в GLOBAL IN / JOIN, чтобы распределить её на все шарды один раз;

• Рекомендуется всегда соединять таблицы по столбцам с одинаковыми типами данных. В данном запросе для преобразования типа данных потребуются дополнительные системные ресурсы:
SELECT ... FROM a INNER JOIN b ON toInt16(a.unit) = b.unit

• Запросы с JOIN-ами больших таблиц исполняются значительно медленнее или вовсе могут упасть из-за недостатка оперативной памяти. Во время JOIN таблица слева присоединяется через Lookup с хэш-таблицей, поэтому желательно помещать меньшую таблицу справа. Меньшая по количеству строк или объёму данных таблица - всегда справа от JOIN, так как она размещается в памяти.
Для эффективного соединения больших таблиц имеются следующие алгоритмы выполнения:
- * hash - создает единственную таблицу в памяти;
- * parallel hash - разделяет данные на секции (Buckets) и параллельно создает в памяти несколько таблиц;
- * grace hash - по механизму работы схож с parallel hash, однако при недостатке памяти способен временно использовать дисковое пространство и создавать большее число таблиц;
- * partial merge - только правая таблица сортируется перед JOIN-ом; сортировка таблицы происходит в памяти, а если её недостаточно, данные могут сгружаться на диск;
- * full sorting merge - обе таблицы сортируются перед JOIN-ом; по производительности лишь немного уступает классическому hash алгоритму, при этом не приводит к исключению при исчерпании оперативной памяти;
- * direct - будет использован, если справа джойнится таблица одного из типов: словарь (Dictionary), созданная на основе Join-движка таблица, EmbededRockDB таблица. Эти таблицы полностью хранятся в памяти. Direct джойны по производительности очень даже могут превышать hash-алгоритмы, при этом существенно менее агрессивно используя оперативную память.
Для выбора используемого алгоритма достаточно указать его в конце запроса: SETTINGS join_algorithm = 'hash'
Какой алгоритм использовать?
Если какой-то конкретный SQL-запрос с JOIN-ом происходит часто, имеет смысл поместить правую таблицу полностью в RAM как словарь или JOIN-Engine таблицу. Используемый при этом direct алгоритм - наиболее быстрый, оптимизированный по сравнению с остальными.
Если такой возможности нет, то стоит использовать hash-алгоритмы, но при условии, что правая таблица может полностью уместиться в оперативной памяти.
Если имеется риск переиспользования RAM, то следует рассмотреть grace hash алгоритм или алгоритмы с сортировкой.
Если JOIN выполняется по сортированным ключам, то, возможно, более подходящим вариантом будет full_sorting_merge алгоритм.

• Использование GLOBAL JOINs для распределённых запросов, когда нужно, чтобы таблица справа была отправлена на все шарды.

• ASOF JOIN для временных рядов.

• Использование более лёгких таблиц: чем меньше записей в таблице, тем меньше ресурсов будет затрачено на их чтение.

• Использование встроенных, высокооптимизированных функций ClickHouse (они векторизованы). Избегать пользовательских функций (UDF) или очень сложных выражений, если есть более простые встроенные аналоги.

• Производители рекомендуют проводить все вычисления, агрегировать данные на стороне ClickHouse, а не на бэкенде. В этом случае передаётся меньше данных по сети и увеличивается скорость обработки.

• Переопределение глобальных настроек на уровне запроса (SET настройка):
SELECT ... SETTINGS max_memory_usage = 1000000000
полезно для отладки / настройки поведения конкретного особо требовательного запроса.

• В запросе данных столбцов, чувствительных к регистру, необходимо учитывать регистр. В противном случае запрос может выдать неверный результат и, возможно, привести к проблемам с производительностью. Таблица system.data_type_families содержит информацию о том, какие типы данных чувствительны к регистру, а какие - нет. Например, тип данных "String" чувствителен к регистру. Используйте правильную функцию для поиска позиции внутри строки:
position – с учётом регистра, positionCaseInsensitive – без учёта регистра;
используйте правильный оператор для выполнения полнотекстового поиска:
Like – с учётом регистра, iLike – без учёта регистра.

• UPDATE в ClickHouse работают медленно! ClickHouse не модифицирует существующий кусок данных, а:
а) читает нужные данные из кусков,
б) обновляет данные, создавая новый кусок данных,
в) помечает старые куски как неактивные,
г) процессы слияния затем периодически удаляют неактивные куски.
UPDATE без WHERE condition выдаст ошибку. Нельзя обновлять столбцы, входящие в первичный ключ. Нельзя обновлять столбцы, входящие в PARTITION BY.

Оптимизация вставки данных (INSERTs Optimization)

• Избегать вставку (INSERT INTO ... VALUES (...)) в таблицу по одной строке. Время вставки одной строки и 10 000 строк одинаково в ClickHouse. Добавление в таблицу 10 000 записей по одной займёт в 10 000 раз больше времени, чем пакетная вставка всего массива за один раз. Надлежит вставлять данные большими пакетами (Batch Inserts) от 1000 до 100 000+ записей.
Меньшее количество, но более крупных вставок снижает количество записанных частей, минимизируя нагрузку на их слияние и снижая общее использование ресурсов системы.

• Для эффективной стратегии синхронной вставки требуется пакетирование на стороне клиентского приложения.

• max_insert_block_size - параметр конфигурации, влияющий на размер блока данных, формируемого для вставки;
buffer engine (с осторожностью) может использоваться для временного буферирования и асинхронной вставки в базовые таблицы, но усложняет мониторинг и отладку.

• Рекомендуется удерживать количество инструкций на вставку на уровне около 1 запроса на вставку в секунду, так как только что созданные части сливаются в более крупные части в фоновом режиме, а отправка слишком большого количества вставок в секунду может привести к ситуациям, когда фоновое слияние может не справиться с количеством новых частей.

• Когда невозможно пакетирование на стороне клиентского приложения, то асинхронные вставки позволяют более высокий темп вставок в секунду. Асинхронные вставки особенно хороши, когда сотни, тысячи агентов непрерывно вливают данные (логи, метрики).
wait_for_async_insert - настройка уточняет поведение асинхронных вставок. По умолчанию, для большинства производственных сценариев wait_for_async_insert = 1 - ClickHouse подтверждает вставку только после того, как данные успешно сброшены на диск, что обеспечивает гарантии долговечности и упрощает обработку ошибок. Если что-то пойдет не так во время сброса, клиенту возвращается ошибка.
wait_for_async_insert = 0 включает режим "fire-and-forget": сервер подтверждает вставку сразу после буферизации данных без ожидания их достижения до хранения. Это предлагает вставки с ультранизкой задержкой и максимальную пропускную способность, идеально подходит для данных с высокой скоростью и низкой критичностью. Есть риск сохранения данных, ошибки могут проявляться только во время сброса, трудно отследить неудачные вставки.

Настоятельная рекомендация: использовать async_insert=1, wait_for_async_insert=1 для асинхронных вставок.

• В случае реплицированных таблиц установка insert_quorum > 1 повышает надежность, но замедляет вставку, поскольку нужно ждать подтверждения от нескольких реплик.

• Native формат (бинарный) – самый быстрый для вставки и чтения, например, через clickhouse-client или драйверы; CSV, TabSeparated – хорошие "скоростные" текстовые форматы.

• Следует избегать JSONEachRow для больших объёмов данных, так как парсинг текста замедляет.

Настройка Linux

Настройка ядра Linux может значительно повысить производительность ClickHouse. Ниже приведены некоторые параметры ядра Linux, которые можно настроить для оптимизации производительности ClickHouse:

• Transparent Huge Pages (THP) - это функция управления памятью в Linux, которая потенциально может повысить производительность за счёт уменьшения количества ошибок страниц. При включенном THP в ClickHouse может наблюдаться более высокая загрузка процессора, увеличение времени выполнения запросов и снижение пропускной способности, особенно при рабочих нагрузках, связанных с большими объёмами данных. Чтобы избежать этих проблем, рекомендуется отключить THP для ClickHouse, установив для параметра transparent_hugepage значение "never". Отключив THP, ClickHouse может избежать задержек, вызванных отображением памяти, и повысить производительность при выполнении рабочих нагрузок при больших объёмах данных. Но отключение THP может увеличить использование памяти и может потребовать дополнительной настройки других системных параметров, таких как размер кэша страниц и объём доступной памяти.

• ClickHouse предназначен для быстрого выполнения множества операций в оперативной памяти, но может сбрасывать страницы памяти на диск в файлы подкачки. Параметр vm.swappiness определяет, насколько интенсивно Linux производит подкачку памяти. Более высокое значение делает систему склонной к подкачке. Можно установить низкое значение параметра (например, 10 или даже 1), чтобы уменьшить количество подкачек:
sysctl -w vm.swappiness=10
Каталог данных и файлы подкачки должны находиться на разных дисках, чтобы не было конкуренции за ввод-вывод.
Если версия Linux поддерживает Zswap или Zram, то включить их, чтобы сжимали данные подкачки, сокращая объём фактического дискового ввода-вывода.

• Максимального количества открытых файловых дескрипторов по умолчанию в Linux может быть недостаточно для ClickHouse, выполняющего большое количество операций ввода-вывода с диска. Чтобы увеличить количество файловых дескрипторов, добавить в файл /etc/security/limits.conf строку:
* hard nofile 1000000

• Коэффициенты Dirty Ratio и Dirty Background Ratio определяют процент системной памяти, который может быть использован для записи данных на диск. Значения по умолчанию могут не подходить для работы ClickHouse с высокой нагрузкой на запись, поэтому их можно увеличить, добавив следующие строки в файл /etc/sysctl.conf:
vm.dirty_ratio=10
vm.dirty_background_ratio=5

• ClickHouse интенсивно работает с сетью, и настройка параметров TCP может повысить его производительность. Добавить в файл /etc/sysctl.conf следующие строки:
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_sack = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1

• ClickHouse выполняет множество операций дискового ввода-вывода, и выбор планировщика ввода-вывода может существенно повлиять на производительность. Для ClickHouse рекомендуется использовать планировщик ввода-вывода "noop". Для этого добавить строку в файл /etc/rc.local:
echo noop > /sys/block/диск/queue/scheduler
Замените диск на название вашего устройства хранения данных.

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

Прочие методы

• В большинстве случаев слияния (Merges) частей происходят автоматически в фоновом режиме. OPTIMIZE TABLE принудительно запускает слияние всех частей в партиции. OPTIMIZE TABLE может оказаться полезным после массивных вставок записей / удаления данных через TTL. Действовать надо с осторожностью, так как это ресурсоемкая операция.

• Если постоянно проводится анализ по всем полям таблиц, то производительность ClickHouse будет ближе к производительности традиционных реляционных СУБД.

• В обычной СУБД конкретную строку можно выбрать по значению первичного ключа, но СУБД ClickHouse не рассчитана на поиск отдельной строки, такой прицельный поиск занимает время. ClickHouse предназначен для работы с огромным количеством строк.

• Аккуратное использование временных таблиц, например, для быстрого хранения промежуточных результатов небольших объёмов данных для переиспользования в последующих запросах, для упрощения сложных больших запросов посредством их разбиения на более простые. Временные таблицы хранятся в оперативной памяти сервера:
CREATE TEMPORARY TABLE temp_tbl (<определение столбцов>);
или так:
CREATE TABLE temp_tbl (<определение столбцов>) ENGINE = Memory;
Временные таблицы видны только в текущей сессии пользователя и автоматически удаляются при её завершении.

• Регулярное обновление ClickHouse до последних стабильных версий. Разработчики постоянно добавляют оптимизации и новые функции.

Оптимизация аппаратного обеспечения и инфраструктуры (Hardware Optimization)

Когда вышеперечисленные приёмы исчерпали себя можно заняться наращиванием аппаратных серверных мощностей.

• Быстрое дисковое хранилище (Disk I/O):
SSD / NVMe: абсолютно критично. Чем быстрее диски, тем лучше. NVMe-накопители значительно превосходят SATA SSD.
RAID 0/10 для производительности и отказоустойчивости.

• Достаточный объем оперативной памяти (RAM): ClickHouse активно использует RAM. Чем больше RAM, тем больше данных может храниться в кэше.

• Мощные CPU: большее количество ядер и высокая тактовая частота процессора способствуют более быстрому выполнению сжатия /декомпрессии данных, агрегаций, вычислительных операций, параллельным операциям и фоновым задачам.

• Горизонтальное масштабирование: добавление новых шард, когда один сервер не справляется с нагрузкой; обеспечение равномерного распределения данных по шардам.

• Быстрая сеть: для распределённых кластеров (шардинг), репликации и клиентских подключений необходима высокоскоростная сеть (10 Gb или выше), особенно для больших объёмов передаваемых данных.

В качестве заключения: наибольший эффект даёт оптимизация схемы и дизайна таблиц (ORDER BY, PARTITION BY, типы данных, материализованные представления), после этого следует сосредоточиться на написании эффективных запросов и пакетной вставке данных, ну и решение "в лоб" - наращивание аппаратного обеспечения (быстрые SSD-диски, увеличение оперативной памяти, быстродействующих процессоров).