Настройка ClickHouse

Содержание

Содержание
Настройка операционной системы
Экономия оперативной памяти
Настройка сервера
Настройка таблиц
Настройка пользователей
Настройка клиента
Полезные материалы

Настройка операционной системы

Ниже в виде конкретных команд и действий описаны рекомендации, найденные в документе Managing ClickHouse / More / Usage Recommendations.

Настройка регулятора процессора

Рекомендуется избегать использования регулятора ondemand. Проверим, какой регулятор настроен на каждом из ядер процессора:

$ cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

Для достижения максимальной производительности можно воспользоваться регулятором performance. Настроить его для всех ядер процессоров можно с помощью следующей команды:

# for c in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor ; do echo performance > $c ; done

Для того, чтобы регулятор performance использовался по умолчанию после загрузки системы, нужно открыть файл /etc/default/grub и добавить в переменную GRUB_CMDLINE_LINUX опцию cpufreq.default_governor=performance. Затем нужно выполнить команду для обновления конфигурации загрузчика:

# update-grub

Настройка спекулятивного выделения памяти

Рекомендуется не отключать выделение памяти объёмом больше имеющейся в распоряжении системы. Настроить политику выделения памяти можно с помощью переменной ядра vm.overcommit_memory. Посмотреть текущее значение можно так:

# sysctl vm.overcommit_memory

Значения 0 и 1 разрешают выделять памяти больше, чем есть. Значение 2 запрещает. По умолчанию используется значение 0. Если это не так, поменять можно с помощью следующей команды:

# sysctl -w vm.overcommit_memory=0

Чтобы зафиксировать изменения, в файл /etc/sysctl.conf нужно вписать строчку:

vm.overcommit_memory = 0

Linux kernel documentation / vm / overcommit-accounting

Прозрачные огромные страницы

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

Отключить прозначные страницы, оставив возможность приложениям использовать их по явному запросу, можно следующим образом:

# echo 'madvise' > /sys/kernel/mm/transparent_hugepage/enabled

Чтобы это значение восстанавливалось при перезагрузке системы, нужно вписать в переменную GRUB_CMDLINE_LINUX_DEFAULT в файле /etc/default/grub дополнительную опцию transparent_hugepage=madvise и обновить конфигурацию загрузчика:

# update-grub

Время последнего доступа

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

# mount -o remount,defaults,noatime /srv

Чтобы файловая система всегда монтировалась с этой опцией, нужно вписать в строчку монтирования файловой системы в файле /etc/fstab соотвествующую опцию:

/dev/mapper/vg0-srv     /srv            ext4    defaults,noatime        0       2

Экономия оперативной памяти

Рекомендуется использовать компьютеры с 32 и более гигабайтами памяти. Если в системе меньше 16 гигабайт памяти, нужна настройка.

Объём кэша засечек

Рекомендуется уменьшить объём кэша засечек. По умолчанию используется объём 5 гигабайт, можно его можно уменьшить до 500 мегабайт, но не меньше. Сделать это можно, например, создав файл /etc/clickhouse-server/config.d/mark_cache.xml со следующим содержимым:

<clickhouse>
        <mark_cache_size replace="1">2147483648</mark_cache_size>
</clickhouse>

Размер блока

Когад ClickHouse обрабатывает запросы, он обабатывает их порциями по max_block_size строк. По умолчанию это значение равно 65409. Чтобы снизить использование оперативной памяти, можно уменьшить значение этой опции до 8192 или даже до 1024. Для этого можно создать файл /etc/clickhouse-server/config.d/max_block_size.xml со следующим содержимым:

<clickhouse>
        <max_block_size replace="1">8192</max_block_size>
</clickhouse>

1 поток для обработки запросов

За количество одновременно работающих потоков сервера ClickHouse отвечает опция max_threads, значение по умолчанию равно количеству ядер процессора. Поменять можно, создав файл /etc/clickhouse-server/config.d/max_threads.xml со следующим содержимым:

<clickhouse>
    <max_threads replace="1">8</max_threads>
</clickhouse>

Скорее имеет смысл использовать эту опцию при выполнении конкретного запроса. Для этого можно добавить в конец запроса выражение:

SETTINGS max_threads = 1

1 поток для скачивания

Для запросов, вставляющих данных из файлов, добавлять в конце запроса выражение:

SETTINGS max_download_threads = 1

Dale McDiarmid, Tom Schreiber. Getting Data Into ClickHouse - Part 3 - Using S3

1 поток разбора данных

Для запросов, вставляющих данных из файлов, добавлять в конце запроса выражение:

SETTINGS input_format_parallel_parsing = 0

1 поток для форматирования данных

Для запросов, форматирующих результаты в каком-либо формате, добавлять в конце запроса выражение:

SETTINGS output_format_parallel_formatting = 0

Настройка сервера

Для настройки сервера ClickHouse используется XML-файл /etc/clickhouse-server/config.xml.

Настройка журналов

Для настройки журналирования используется секция clickhouse/logger:

<clickhouse>
    <logger>
        <level>warning</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
    </logger>
</clickhouse>

Назначение опций:

level - уровень журналирования: test - для использования только разработчиками, trace, debug, information, notice, warning, error, critical, fatal, none - отключение журналирования,
log - файл журнала со всеми сообдениями уровня level,
errorlog - файл журнала ошибок,
size - размер одного файла журнала, по достижении которого ClickHouse продолжит ведение журнала в новом файле,
count - количество архивных файлов журнала.

Также имеется возможность вести журнал через syslog. Подробности см. в официальной документации.

Документация ClickHouse / Конфигурационные параметры сервера / logger

Настройка доступа по протоколу MySQL

Для включения доступа к серверу ClickHouse по протоколу MySQL можно добавить в секцию clickhouse опцию mysql_port:

<clickhouse>
    <mysql_port>9004</mysql_port>
</clickhouse>

Документация ClickHouse / Конфигурационные параметры сервера / mysql_port

Настройка прослушиваемых адресов

Для настройки прослушиваемых IP-адресов предназначена опция listen_host, которую можно указывать более одного раза:

<clickhouse>
    <listen_host>::1</listen_host>
</clickhouse>

Возможные варианты:

:: - прослушивание всех адресов IPv4 и IPv6,
0.0.0.0 - прослушивание всех адресов только IPv4,
::1 - прослушивание локального петлевого интерфейса IPv6,
127.0.0.1 - прослушивание локального петлевого интерфейса IPv4.

По умолчанию клиент ClickHouse предпочитает использовать IPv6-адреса. Если сервер настроен на прослушивание только адресов IPv4, то подключиться без явного указания адреса IPv4 в таком случае не получится. Если вы не собираетесь использовать адреса IPv6, то для отключения IPv6, достаточно присвоить следующей переменной ядра значение 1:

net.ipv6.conf.all.disable_ipv6 = 1

После этого нужно перезапустить сервер ClickHouse, чтобы он закрыл сокет на адресе IPv6 и открыл сокет только на адресе IPv4:

# systemctl restart clickhouse-server

Документация ClickHouse / Конфигурационные параметры сервера / listen_host

Размер кэша несжатых данных

Для включения использования кэша несжатых данных и для настройки его размера предназначены следующие опции:

<clickhouse>
    <use_uncompressed_cache>0</use_uncompressed_cache>
    <uncompressed_cache_size>8589934592</uncompressed_cache_size>
</clickhouse>

Обратите внимание, что по умолчанию кэш несжатых данных отключен, а при включении его размер по умолчанию составит 8 гигабайт максимум. Кэш используется только для таблиц семейства MergeTree. Размер кэша будет увеличиваться по мере его наполнения, а по достижении максимального размера из него начнут удаляться наименее востребованные данные.

Документация ClickHouse / Конфигурационные параметры сервера / use_uncompressed_cache

Документация ClickHouse / Конфигурационные параметры сервера / uncompressed_cache_size

Размер кэша засечек

Для настройки размера кэша засечек используется опция mark_cache_size из секции clickhouse:

<clickhouse>
    <mark_cache_size>5368709120</mark_cache_size>
</clickhouse>

По умолчанию размер кэша ограничен 5 гигабайтами. Засечки являются разновидностью индексов, которые используются для таблиц семейства MergeTree. Каждая засечка в индексе соответствует некоторому количеству строк, упорядоченных по первичному ключу. Для поиска интересующих данных ClickHouse ищет засечки, между которыми могут находиться интересующие строки, после чего считывает и фильтрует сами строки. Размер кэша будет увеличиваться по мере его наполнения, а по достижении максимального размера из него начнут удаляться нименее востребованные засечки.

Документация ClickHouse / Конфигурационные параметры сервера / mark_cache_size

Настройка журнала запросов

Для отключения всех журналов, которые ClickHouse ведёт в таблицах базы данных, кроме таблиц query_log и query_thread_log, можно воспользоваться такими опциями:

<clickhouse>
    <asynchronous_metric_log remove="1"/>
    <metric_log remove="1"/>
    <part_log remove="1" />
    <session_log remove="1"/>
    <text_log remove="1" />
    <trace_log remove="1"/>
</clickhouse>

Для отключения журналов, которые ClickHouse ведёт в таблицах query_log и query_thread_log, можно воспользоваться такими опциями, которые отключат ведение журналов на уровне настроек по умолчанию для всех пользователей:

<clickhouse>
    <profiles>
        <default>
            <log_queries>0</log_queries>
            <log_query_threads>0</log_query_threads>
        </default>
    </profiles>
</clickhouse>

Для настройки таблицы, в которой будет вестись журнал запросов, предназначена секция yandex/query_log файла конфигурации:

<clickhouse>
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <engine>Engine = MergeTree PARTITION BY event_date ORDER BY event_time TTL event_date + INTERVAL 30 day</engine>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>
</clickhouse>

Назначение опций:

database - имя базы данных, в которой находится таблица журнала запросов,
table - имя таблицы журнала запросов,
partition_by - ключ секционирования таблицы, нельзя указывать вместе с опцией engine,
ttl - настройки времени хранения записей в таблице, нельзя указывать вместе с опцией engine,
engine - настройки таблицы семейства MergeTree, нельзя указывать вместе с опцией partition_by,
flush_interval_milliseconds - интервал, с которым записи из буфера будут помещаться в таблицу.

Возможные значения опции partition_by:

toStartOfHour(event_time) - почасовые секции,
event_date - посуточные секции,
toMonday(event_date) - понедельные секции, каждая секция начинается с понедельника и заканчивается воскресеньем,
toYYYYMM(event_date) - помесячные секции.

Значения опции ttl можно указывать, например, в следующем виде: event_date + INTERVAL 30 DAY DELETE.

Для настройки таблицы, в которой будет вестись журнал выполнения запросов отдельными потоками, предназначена секция yandex/query_thread_log, которая настраивается полностью аналогично предыдущей таблице.

Документация ClickHouse / Конфигурационные параметры сервера / query_log

Altinity Knowledge Base / System tables eat my disk

Настройка количества потоков для работы с фрагментами

При запуске ClickHouse считывает в оперативную память метаданные из каждого файла фрагмента каждой таблицы. Если фрагментов очень много, то ClickHouse запускается долго. По умолчанию чтение метаданных выполняется количеством потоков, равному количеству ядер процессоров. Однако, если сервер обладает достаточной производительностью процессора и дисковой подсистемы, то ускорить запуск можно увеличением количества потоков, которое настраивается с помощью параметра конфигурации max_part_loading_threads.

Оценить количество фрагментов можно с помощью такого запроса:

SELECT COUNT(*)
FROM system.parts;

В процессе работы ClickHouse помещает в отдельные файлы фрагментов таблиц данные из каждого запроса INSERT, а затем в фоновом режиме объединяет такие фрагменты. Результирующий фрагмент помечается как активный, а исходные фрагменты помечаются неактивными. Далее, опять же в фоновом режиме, происходит удаление неактивных фрагментов. По умолчанию удаление неактивных фрагментов выполняется количеством потоков, равному количеству ядер процессоров. Хотя, как правило, с удалением неактивных фрагментов может справиться даже один поток, в некоторых случаях может понадобиться увеличить количество потоков. Сделать это можно с помощью параметра конфигурации max_part_removal_threads.

Оценить соотношение количества и объёма активных и неактивных фрагментов можно с помощью следующего запроса:

SELECT active, COUNT(*), SUM(bytes_on_disk) / 1024 / 1024 / 1024
FROM system.parts
GROUP BY active;

Для настройки обоих параметров можно воспользоваться фрагментом конфигурации следующего типа:

<clickhouse>
    <merge_tree>
        <max_part_loading_threads>16</max_part_loading_threads>
        <max_part_removal_threads>16</max_part_removal_threads>
    </merge_tree>
</clickhouse>

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

ALTER TABLE db.table MODIFY SETTING old_parts_lifetime = 60;

Vu Dao. Clickhouse Server - Troubleshooting

Настройка асинхронной вставки данных

В процессе работы ClickHouse помещает в отдельные файлы фрагментов таблиц данные из каждого запроса INSERT, а затем в фоновом режиме объединяет такие фрагменты. Рекомендуется, чтобы один запрос на вставку содержал не менее 1000 строк. Если это не так, то вставки приводят к неоправданно высокому росту нагрузки на дисковую подсистему. Избежать этого можно одним из двух способов: использовать буферные таблицы или асинхронную вставку данных. Если важно, чтобы вставленные данные немедленно становились доступными для запросов на выборку, то в таком случае стоит использовать буферные таблицы. Здесь обсуждается асинхронная вставка данных, при которой данные, попавшие в буфер, остаются недоступными для запросов на выборку данных до того момента, как они не будут записаны.

Включить асинхронную вставку можно при помощи параметра конфигурации async_insert. 0 - асинхронная вставка не используется, 1 - данные перед вставкой в таблицу накапливаются.

При асинхронной вставки можно также поменять поведение потока, который выполняет вставку данных. Если параметр конфигурации wait_for_async_insert принимает значение 0, то поток ждёт момента, когда накопленные в буфере данные будут записаны на диск. При значении 1 поток не дожидается записи накопленных данных на диск и сообщает клиенту об успешной вставке данных. С одной стороны это позволяет снизить количество одновременно работающих потоков, а стало быть снизить нагрузку на процессор и снизить потребление оперативной памтяи, а с другой стороны в таком режиме невозможно узнать об ошибке, если данные по каким-либо причином всё-таки не были записаны на диск.

Для управления моментом записи накопленных данных на диск предназначены два параметра конфигурации: async_insert_max_data_size - указывает минимальный объём накопленных данных в байтах, прежде чем данные будут записаны на диск, и async_insert_busy_timeout_ms - указывает минимальный интервал времени в миллисекундах, по прошествии которого накопленные данные будут записаны даже в том случае, если не был достигнут необходимый для их записи объём. По умолчанию запись на диск происходит при накоплении 100000 байт данных или по прошествии 200 миллисекунд.

Эти параметры конфигурации можно настроить в файлах конфигурации профилей пользователей. Например, чтобы поменять настройки профиля по умолчанию, можно воспользоваться следующим фрагментом файла конфигурации. Кроме этого, как оказалось, необходимо включить функцию асинхронной вставки на уровне типов таблиц с помощью опций async_insert в секциях merge_tree и replicated_merge_tree:

<clickhouse>
    <merge_tree>
            <async_insert>1</async_insert>
    </merge_tree>
    <replicated_merge_tree>
            <async_insert>1</async_insert>
    </replicated_merge_tree>
    <profiles>
        <default>
            <async_insert>1</async_insert>
            <wait_for_async_insert>0</wait_for_async_insert>
            <async_insert_max_data_size>10485760</async_insert_max_data_size>
            <async_insert_busy_timeout_ms>1000</async_insert_busy_timeout_ms>
        </default>
    </profiles>
</clickhouse>

Tom Schreiber, Tony Bonuccelli. Asynchronous Data Inserts in ClickHouse

Настройка таблиц

Буферные таблицы

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

Рассмотрим пример основной таблицы:

CREATE TABLE history_uint
(
    itemid UInt64,
    clock UInt32,
    ns UInt32,
    value UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(toDateTime(clock))
ORDER BY (itemid, clock);

Переименуем её и создадим вместо неё буферную таблицу:

RENAME TABLE history_uint TO real_history_uint;

CREATE TABLE history_uint
(
    itemid UInt64,
    clock UInt32,
    ns UInt32,
    value UInt64
) ENGINE = Buffer(zabbix, real_history_uint, 8, 30, 60, 8192, 65536, 262144, 67108864);

Рассмотрим по порядку все поля, который указываются при создании буфернйо таблицы для её настройки:

zabbix - имя базы данных, в которой находится реальная таблица,
real_history_uint - имя реальной таблицы, для которой буферная таблица выполняет буферизацию данных,
8 - количество слоёв буфера,
30 секунд - минимальное время, которое должно пройти со момента предыдущей записи в реальную таблицу, прежде чем буферная таблица запишет данные в реальную таблицу,
60 секунд - максимальное время с момента предыдущей записи в реальную таблицу, по прошествии которого операция записи будет выполнена вне зависимости от всех остальных условий,
8192 строк - минимальное количество записей, которое должно быть в буферной таблице, прежде чем буферная таблица запишет данные в реальную таблицу,
65536 строк - максимальное количество записей, которое должно быть в буферной таблице, по достижении которого операция записи будет выполнена вне зависимости от всех остальных условий,
256 килобайт - минимальный объём данных, который должен накопиться в буферной таблице, прежде чем буферная таблица запишет данные в реальную таблицу,
64 мегабайта - максимальный объём данных, который должен накопиться в буферной таблице, по достижении которого операция записи будет выполнена вне зависимости от всех остальных условий.

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

ClickHouse Engines / Движки таблиц / Специальные движки таблиц / Buffer

Время хранения данных

ClickHouse изначально предназначен для сбора данных, имеющих привязку ко времени. Как правило, интерес к таким данных убывает с течением времени. Наиболее востребованными оказываются самые свежие данные, а самые старые данные могут вовсе не представлять интереса. Чтобы не тратить впустую место на дисках, можно не хранить вечно устаревшие данные.

Для настройки времени хранения данных в таблицах предназначен параметр TTL. Например, для настройки хранения данных в течение трёх месяцев от отметки времени в поле date_create в таблице, можно воспользоваться таким выражением:

ALTER TABLE api_ufanet.api_fcm_notice MODIFY TTL date_create + INTERVAL 3 MONTH DELETE;

Или с использованием функций:

ALTER TABLE api_ufanet.api_fcm_notice MODIFY TTL date_create + toIntervalMonth(3);

Для указания интервалов времени предназначены следующие функции:

toIntervalYear() - интервал в годах,
toIntervalMonth() - интервал в месяцах,
toIntervalDay() - интервал в сутках,
toIntervalHour() - интервал в часах,
toIntervalMinute() - интервал в минутах,
toIntervalSecond() - интервал в секундах.

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

Деление таблиц на секции

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

PARTITION BY toYYYYMMDD(datetime)

Для задания имён секция можно пользоваться следующими функциями:

toYYYYMM(timestamp) - создаёт секции с именами вида ГГГГММ, где ГГГГ - 4 цифры года, а ММ - 2 цифры номер месяца, начиная с 01,
toYYYYMMDD(timestamp) - создаёт секции с именами вида ГГГГММДД, где ГГГГ - 4 цифры года, ММ - 2 цифры номер месяца, начиная с 01, а ДД - 2 цифры числа месяца,
toDate(timestamp) - создаёт секции с именами вида ГГГГ-ММ-ДД, где ГГГГ - 4 цифры года, ММ - 2 цифры номер месяца, начиная с 01, а ДД - 2 цифры числа месяца,
toStartOfInterval(timestamp, toIntervalHour(1)) - создаёт секции с именами вида ГГГГ-ММ-ДД ЧЧ:мм:СС, где ГГГГ - 4 цифры года, ММ - 2 цифры номер месяца, начиная с 01, ДД - 2 цифры числа месяца, ЧЧ - 2 цифры часов, мм - 2 цифры минут, СС - 2 цифры секунд. Второй аргумент функции указывает интервал, в качестве которого можно использовать число, сгенерированное одно из уже перечисленных выше функций.

Если время в таблице хранится не в естественном для ClickHouse формате DateTime, а в виде целого числа с количеством секунд от начала 1970 года, как это принято в системах, подобных Unix, то преобразовать такую отметку времени в формат DateTime можно с помощью функции toDateTime(clock).

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

Также не стоит создавать слишком мелкие секции, если сравнивать их со временем хранения данных, т.к. при чрезмерном дроблении данных на секции будет создано слишком большое количество файлов с фрагментами таблицы, в результате чего вырастет время выполнения запросов и запуска сервера. Количество одновременно открытых файлов ограничивается операционной системой, а из-за большого количества фрагментов серверу ClickHouse придётся слишком часто закрывать файлы с фрагментами, ставшие не нужными, и вместо них открывать файлы с фрагментами, которые нужны для завершения выполнения запроса.

Также при выборе размера секции стоит учитывать временные интервалы, по которым чаще всего происходит выборка. Оптимально, если большинство выполняемых запросов будут просматривать одну или две секции. При большем количестве секций, подлежащих просмотру, стоит подумать о том, чтобы увеличить размер одной секции, чтобы уменьшить количество файлов фрагментов, которые нужно просмотреть в процессе выполнения запроса, и результаты которых нужно ещё и объединить. Если же большинство запросов обращаются только к маленькой части одной секции, то стоит подумать о том, чтобы сделать секции более мелкими, т.к. при просмотре одной большой секции приходится просматривать большое количество данных, которые в итоге оказываются невостребованными. Особенно на описанные тут рекомендации стоит обратить внимание в том случае, если отметка времени не является первым полем в выражении ORDER BY, настраивающем порядок сортировки данных в секциях таблиц.

Оптимальное количество секций - от нескольких штук до нескольких сотен.

Удаление устаревших секций целиком

ClickHouse вычищает из таблиц устаревшие данные, путем отбора актуальных данных и формирования из них новых фрагментов. Количество потоков, занимающихся удалением из таблиц устаревших данных, настраивается с помощью опции max_part_removal_threads, которая была описана выше. Устаревшие данные можно удалять из таблиц не по мере устаревания каждой отдельной записи, а вместе со всей секцией целиком, когда в ней не останется актуальных данных. Таким образом можно снизить нагрузку на диски и на процессор, поскольку потокам удаления устаревших данных нужно будет удалять устаревшие секции целиком, не выполняя фильтрацию и запись актуальных данных в новый фрагмент.

Настроить удаление устаревших секций целиком можно с помощью запросов следующего вида:

ALTER TABLE db.table MODIFY SETTING ttl_only_drop_parts = 1;

Принудительное удаление устаревших данных

Чтобы заставить Clickhouse немедленно приступить к удалению устаревших данных, можно выполнить запрос следующего вида:

OPTIMIZE TABLE api_ufanet.api_fcm_notice FINAL;

Стоит, однако, отметить, что устаревшие данные не будут удалены сразу по завершении выполнения этого запроса. Удаление данных выполняется в фоновом режиме. Для того, чтобы проверить, какие ещё секции остались в таблице, можно воспользоваться запросом следующего вида:

SELECT DISTINCT partition
FROM system.parts
WHERE database = 'api_ufanet'
  AND table = 'api_fcm_notice';

Сортировка данных в таблицах

Представим, что имеется таблица следующего вида:

CREATE TABLE customer_notice
(
    `customer_id` UInt64,
    `date_create` DateTime,
    ...
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date_create)
ORDER BY (date_create, customer_id)
TTL date_create + toIntervalMonth(3)
SETTINGS index_granularity = 8192;

Настройка ORDER BY используется для сортировки данных перед их записью в файл-фрагмент таблицы и фактически явлется разрежённым кластерным индексом. Кластерный индекс - это такой индекс, который хранится вместе с данными и используется для упорядоченного досутпа к ним. Разрежённый индекс содержит значения не для каждой строки данных, а для интервалов строк.

Если поиск в таблице происходит с указанием интервала дат date_create и одного идентификатора customer_id, то при сортировке данных в таблице по полям date_create, customer_id сначала будут отобраны строки, соответствующие указанному интервалу дат, и лишь затем из результата будут отобраны строки, соответствующие идентификатору заказчика.

Поскольку таблица уже поделена на секции по месяцам, то может оказаться выгодным поменять порядок сортировки на customer_id, date_create и тогда при поиске данных сначала будут отобраны секции таблицы, соотвествующие интервалу дат, затем внутри них будут отобраны строки, соответствующие одному определённому идентификатору заказчика и интервалу дат. Отбрасывать лишние данные в этом случае не придётся, т.к. необходимые данные будут расположены одним непрерывным блоком.

Если в таблице не много данных, то можно перенести их в таблицу с новым порядком сортировки одним запросом вида INSERT INTO ... SELECT FROM, следующим образом:

CREATE TABLE customer_notice_new
(
    `customer_id` UInt64,
    `date_create` DateTime,
    ...
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date_create)
ORDER BY (customer_id, date_create)
TTL date_create + toIntervalMonth(3)
SETTINGS index_granularity = 8192;

INSERT INTO customer_notice_new(customer_id, date_create, ...)
SELECT customer_id, date_create, ... FROM customer_notice;

RENAME TABLE customer_notice TO customer_notice_bak;

RENAME TABLE customer_notice_new TO customer_notice;

Если же данных много и содержимое старой таблицы не умещается в оперативной памяти сервера ClickHouse целиком, то такой запрос придётся поделить на серию запросов с указанием интервалов дат в критерии фильтрации WHERE date_create BETWEEN ... AND ... и перенести, таким образом, данные по частям.

Настройка пользователей

Включение управления доступом через SQL-запросы

По умолчанию список пользователей и их права настраиваются с помощью файла /etc/clickhouse-server/users.xml. Однако ClickHouse умеет хранить эту информацию в базе данных. Для включения этой функциональности нужно вписать в файл конфигурации, в секцию, соответствующую пользователю default, следующую опцию:

<access_management>1</access_management>

Далее нужно перезапустить сервер ClickHouse:

# systemctl restart clickhouse-server

Goodbye XML, hello SQL! ClickHouse User Management Goes Pro

Заведение пользователей

Какой бы ни использовался метод управления пользователями - через файл /etc/clickhouse-server/users.xml или через SQL-запросы, может пригодиться вычислить хэш от пароля пользователя. Хэш в формате password_sha256_hex или sha256_hash можно вычислить одним из двух следующих способов:

В командной строке с помощью утилиты sha256sum:

$ echo -n '<password>' | sha256sum | tr -d '-'
dd81ca61fb57a4ff454c1cf89335a1f5e96afa849dfad4e0116b6ec35309fdea

В клиенте ClickHouse с помощью функций hex и SHA256:

SELECT HEX(SHA256('<password>'));

Далее полученный хэш пароля можно вписать в файл /etc/clickhouse-server/users.xml следующим образом (пример соответствует пользователю с именем default):

<yandex>
    <users>
        <default>
            <password_sha256_hex>dd81ca61fb57a4ff454c1cf89335a1f5e96afa849dfad4e0116b6ec35309fdea</password_sha256_hex>
        </default>
    <users>
</yandex>

Или этот же хэш пароля можно вписать в SQL-запрос для создания пользователя или изменения его пароля:

CREATE USER default IDENTIFIED WITH sha256_hash BY 'dd81ca61fb57a4ff454c1cf89335a1f5e96afa849dfad4e0116b6ec35309fdea';
ALTER USER default IDENTIFIED WITH sha256_hash BY 'dd81ca61fb57a4ff454c1cf89335a1f5e96afa849dfad4e0116b6ec35309fdea';

Creating Users and Roles in ClickHouse

Проверка учётной записи

Для проверки учётной записи можно воспользоваться клиентом ClickHouse для командной строки:

$ echo 'SELECT 1;' | clickhouse-client -h 192.168.1.2 -d db -u user --password 'p4$$w0rd'

Если на проверяемом узле не установлен клиент ClickHouse для командной строки, то для проверки учётной записи можно воспользоваться утилитой curl и сервером HTTP, встроенным в сервер ClickHouse:

$ echo 'SELECT 1;' | curl --data-binary @- -u 'user:p4$$w0rd' 'http://192.168.1.2:8123/?database=db'

В обеих командах:

192.168.1.2 - IP-адрес или доменное имя сервера ClickHouse,
db - название базы данных, к которой есть доступ у проверяемого пользователя,
user - имя проверяемого пользователя,
p4$$w0rd - пароль проверяемого пользователя.

Drivers and Interfaces / HTTP Interface

Настройка отображения хэшей паролей пользователей

По умолчанию команда отображения команды создания пользователя не выводит информацию о его пароле:

clickhouse.domain.tld :) SHOW CREATE USER zabbix;

SHOW CREATE USER zabbix

Query id: b8d2cfd0-b280-4306-a761-2f2b2c756d2e

Connecting to database system at localhost:9000 as user default.
Connected to ClickHouse server version 24.5.3.

   ┌─CREATE USER zabbix─────────────────────────────────┐
1. │ CREATE USER zabbix IDENTIFIED WITH sha256_password │
   └────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

Чтобы включить отображение паролей пользователей в этой команде, нужно разрешить это в конфигурации сервера и включить в конфигурации клиента. Для разрешения на сервере нужно создать в каталоге /etc/clickhouse-server/config.d/ файл конфигурации, например, с именем show_secrets.xml и со следующим содержимым:

<clickhouse>
        <display_secrets_in_show_and_select>1</display_secrets_in_show_and_select>
</clickhouse>

Не забудьте поменять права доступа к новому файлу, чтобы сервер ClickHouse мог прочитать его:

# chown clickhouse:clickhouse /etc/clickhouse-server/config.d/show_secrets.xml

Теперь включим отображение паролей в конфигурации клиента. Для этого в файл настроек клиента в домашнем каталоге пользователя, например, /root/.clickhouse-client/config.xml нужно вписать опцию:

<config>
        <format_display_secrets_in_show_and_select>1</format_display_secrets_in_show_and_select>
</config>

Теперь при выполнении команды просмотра команды создания пользователя выводится информация о его пароле или хэше пароля:

clickhouse.domain.tld :) SHOW CREATE USER zabbix;

SHOW CREATE USER default

Query id: 24ca9299-d5ae-4a9a-91a2-8054a5954f44

   ┌─CREATE USER default───────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE USER default IDENTIFIED WITH plaintext_password BY '$3cr3tP4$$w0rd' SETTINGS PROFILE `default` │
   └───────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

Для вывода команд для создания всех пользователей и назначения их текущих прав можно создать скрипт ch-show-grants.sh, названный так по аналогии с названием утилиты pt-show-grants, со следующим содержимым:

#!/bin/sh

clickhouse-client -d system -q 'SHOW USERS FORMAT RAW;' 2>/dev/null \
        | while read USER ; do
                echo "--- $USER ---"
                clickhouse-client -d system -q "SHOW CREATE USER $USER FORMAT RAW;" 2>/dev/null | sed 's/$/;/g'
                clickhouse-client -d system -q "SHOW GRANTS FOR $USER FORMAT RAW;" 2>/dev/null | sed 's/$/;/g'
        done

Для запуска скрипта нужно назначить ему права выполнимости:

# chmod +x ch-show-grants.sh

Пример результатов работы скрипта приведён ниже:

--- default ---
CREATE USER default IDENTIFIED WITH sha256_hash BY '447F4B0DC96B51E966B81845FF6D075A851338E20D316EF0464B3C0C0D6F71CD' SETTINGS PROFILE `default`
GRANT SHOW, SELECT, INSERT, ALTER, CREATE, DROP, UNDROP TABLE, TRUNCATE, OPTIMIZE, BACKUP, KILL QUERY, KILL TRANSACTION, MOVE PARTITION BETWEEN SHARDS, ACCESS MANAGEMENT, SYSTEM, dictGet, displaySecretsInShowAndSelect, INTROSPECTION, SOURCES, CLUSTER ON *.* TO default WITH GRANT OPTION
GRANT TABLE ENGINE ON * TO default WITH GRANT OPTION
GRANT SET DEFINER ON * TO default WITH GRANT OPTION
GRANT ALTER NAMED COLLECTION, CREATE NAMED COLLECTION, DROP NAMED COLLECTION, SHOW NAMED COLLECTIONS, NAMED COLLECTION ON * TO default WITH GRANT OPTION
--- zabbix ---
CREATE USER zabbix IDENTIFIED WITH plaintext_password BY '$3cr3tP4$$w0rd'
GRANT SELECT ON system.asynchronous_metrics TO zabbix
GRANT SELECT ON system.events TO zabbix
GRANT SELECT ON system.metrics TO zabbix
GRANT SELECT ON system.processes TO zabbix

Настройка клиента

Для настройки консольного клиента ClickHouse для входа без пароля можно создать в домашнем каталоге подкаталог .clickhouse-client и поместить внутрь него файл config.xml со следующим содержимым:

<config>
    <host>192.168.1.2</host>
    <port>9000</port>
    <user>user</user>
    <password>p4$$w0rd</password>
    <database>db</database>
    <!-- <format>PrettyCompactMonoBlock</format>
    <multiline>1</multiline>
    <multiquery>1</multiquery>
    <stacktrace>1</stacktrace>
    <pager>less -SR</pager>
    <max_parser_depth>2500</max_parser_depth> -->
</config>

Где:

192.168.1.2 - IP-адрес или доменное имя сервера ClickHouse, к которому клиент будет подключаться по умолчанию,
9000 - порт сервера ClickHouse, на котором он принимает подключения от клиентов, работающих по его двоичному протоколу,
db - название базы данных, к которой пользователь будет подключен по умолчанию,
user - имя пользователя,
p4$$w0rd - пароль пользователя.

В файле конфигурации можно указать значение для любой опции командной строки: несколько примеров приведено в закомментированном блоке между символами .

Clients and Drivers / Command-Line Client

Полезные материалы

Артемий Кравцов. Краеугольные камни ClickHouse - есть общая информация о том, как работает ClickHouse, по оптимизации запросов и по формату файлов засечек
ClickHouse: как устроен MergeTree - есть информация о формате хранения данных