ок, спасибо!

Добрый вечер! Имеется вот такая таблица для хранения исторических рыночных данных:

CREATE TABLE instrument_market_data3 (
  `symbol` String, 
  `timestamp` DateTime('UTC'), 
  `open` Float64, 
  `high` Float64, 
  `low` Float64, 
  `close` Float64, 
  `volume` Float64, 
  `version` Int32
) 
ENGINE = ReplacingMergeTree(`version`) 
PARTITION BY toYYYYMM(`timestamp`) 
ORDER BY (`symbol`, `timestamp`)

На данный момент в ней всего 7,5 млн строк по 33 уникальным значениям в symbol. Крутится на машине c 2-мя ядрами и 4Гб оперативки.
Сейчас раз в минуту в эту таблицу пишутся новые данные одним куском, иногда перезаписывая старые данные в пределах последних 2-х суток.

По ней есть 2 вопроса.

1. Вот такой запрос иногда выполняется очень долго:
`
SELECT
   '1d' AS timeframe,
   argMin(open, timestamp) AS open,
   argMax(close, timestamp) AS close,
   close - open AS yield_absolute,
   ((close - open) / open) * 100 AS yield_relative
FROM instrument_market_data3
WHERE (symbol = 'MOEX:USD000UTSTOM:CETS') AND (timestamp >= '2021-01-27 00:00:00')
GROUP BY timeframe
UNION ALL
SELECT
   '1w' AS timeframe,
   argMin(open, timestamp) AS open,
   argMax(close, timestamp) AS close,
   close - open AS yield_absolute,
   ((close - open) / open) * 100 AS yield_relative
FROM instrument_market_data3
WHERE (symbol = 'MOEX:USD000UTSTOM:CETS') AND (timestamp >= '2021-01-20 00:00:00')
GROUP BY timeframe
UNION ALL
SELECT
   '1m' AS timeframe,
   argMin(open, timestamp) AS open,
   argMax(close, timestamp) AS close,
   close - open AS yield_absolute,
   ((close - open) / open) * 100 AS yield_relative
FROM instrument_market_data3
WHERE (symbol = 'MOEX:USD000UTSTOM:CETS') AND (timestamp >= '2020-12-27 00:00:00')
GROUP BY timeframe
UNION ALL
SELECT
   '3m' AS timeframe,
   argMin(open, timestamp) AS open,
   argMax(close, timestamp) AS close,
   close - open AS yield_absolute,
   ((close - open) / open) * 100 AS yield_relative
FROM instrument_market_data3
WHERE (symbol = 'MOEX:USD000UTSTOM:CETS') AND (timestamp >= '2020-10-29 00:00:00')
GROUP BY timeframe
UNION ALL
SELECT
   '1y' AS timeframe,
   argMin(open, timestamp) AS open,
   argMax(close, timestamp) AS close,
   close - open AS yield_absolute,
   ((close - open) / open) * 100 AS yield_relative
FROM instrument_market_data3
WHERE (symbol = 'MOEX:USD000UTSTOM:CETS') AND (timestamp >= '2020-01-28 00:00:00')
GROUP BY timeframe
UNION ALL
SELECT
   'max' AS timeframe,
   argMin(open, timestamp) AS open,
   argMax(close, timestamp) AS close,
   close - open AS yield_absolute,
   ((close - open) / open) * 100 AS yield_relative
FROM instrument_market_data3
WHERE symbol = 'MOEX:USD000UTSTOM:CETS'
GROUP BY timeframe

Query id: c2204d45-ce34-4b5a-abf4-2820be95f52a

┌─timeframe─┬────open─┬───close─┬─────yield_absolute─┬─────yield_relative─┐
│ 1d        │ 75.0675 │ 75.5825 │ 0.5150000000000006 │ 0.6860492223665375 │
└───────────┴─────────┴─────────┴────────────────────┴────────────────────┘
┌─timeframe─┬──open─┬───close─┬─────yield_absolute─┬────yield_relative─┐
│ 1w        │ 73.68 │ 75.5825 │ 1.9024999999999892 │ 2.582111834961983 │
└───────────┴───────┴─────────┴────────────────────┴───────────────────┘
┌─timeframe─┬────open─┬───close─┬─────yield_absolute─┬─────yield_relative─┐
│ 1m        │ 74.1425 │ 75.5825 │ 1.4399999999999977 │ 1.9422058873115928 │
└───────────┴─────────┴─────────┴────────────────────┴────────────────────┘
┌─timeframe─┬──open─┬───close─┬─────yield_absolute─┬─────yield_relative─┐
│ 3m        │ 78.78 │ 75.5825 │ -3.197500000000005 │ -4.058771261741565 │
└───────────┴───────┴─────────┴────────────────────┴────────────────────┘
┌─timeframe─┬────open─┬───close─┬─────yield_absolute─┬─────yield_relative─┐
│ 1y        │ 62.9475 │ 75.5825 │ 12.634999999999998 │ 20.072282457603556 │
└───────────┴─────────┴─────────┴────────────────────┴────────────────────┘
┌─timeframe─┬───open─┬───close─┬─────yield_absolute─┬─────yield_relative─┐
│ max       │ 31.185 │ 75.5825 │ 44.397499999999994 │ 142.36812570145904 │

└───────────┴────────┴─────────┴────────────────────┴────────────────────┘

6 rows in set. Elapsed: 3.911 sec. Processed 2.08 million rows, 104.22 MB (532.02 thousand rows/s., 26.65 MB/s.)
`

Но чаще запрос отрабатывает и за 0.2-0.5 сек, если, например, данные не писались в таблицу какое-то время или запрос закешировался. Пробовал партиции делать так PARTITION BY (symbol, toYYYYMM(`timestamp`)) но не сильно помогло.

Что можно улучшить в это таблице, чтобы повысить производительность?

2. Правильно ли будет использовать идею с версионированием строк, если планируется писать в реальном времени и часто перезаписывать последние строки в контексте текущей временной метки (по нескольк раз в секунду)? Или есть более производительный способ?

Добрый вечер, установил clickhouse на виртуалку, пытаюсь подключиться через dbeaver, но выдает ошибку java.net.ConnectException: Connection refused (Connection refused), можете подсказать как решить проблему?

в файле config.xml расскоментировал <listen port> ::<listen port>

Посмотрите в логах кликхауса ошибку

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

CREATE TABLE instrument_market_data3 (
  `symbol` String, 
  `timestamp` DateTime('UTC'), 
  `open` Float64, 
  `high` Float64, 
  `low` Float64, 
  `close` Float64, 
  `volume` Float64, 
  `version` Int32
) 
ENGINE = ReplacingMergeTree(`version`) 
PARTITION BY toYYYYMM(`timestamp`) 
ORDER BY (`symbol`, `timestamp`)

вы данные заливаете раз в сутки, делайте optimize в цикле пока не останется один парт в партиции, после этого вам не нужен argMAx
4ГБ это слезы. Это издевательство.

рестарт КХ нужен чтобы другой порт/ip слушать

виртуалка должна выпихнуть порт на хост

Опа, спасибо
Я пробовал- не получилось, но ща затестирую ещё раз, пасиба!

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

Есть необходимость скопировать 360кк записей из mysql в ch
Для этого создаю в таблицу в ch с движком mysql и делаю insert from select
Миграция проходит без ошибок, но в ch таблице появляется только 100-120кк записей в зависимости от настроения
Есть rabbitmq но через него столько данных гнать не хочется, как победить малой кровью? Хотелось бы обойтись все же mysql table engine

версия КХ ?

Данные льются каждую минуту. Агрегация по данным возможна со срезам в виде интервала каждых N-минут для построения там свечей, расчета индикаторов и так далее.

> 4ГБ это слезы. Это издевательство.
Сейчас контейнер с кликхаусом потребляет около 200 Мб. Реально 4Гб мало для столь легкой (пока еще) таблицы?

бинарник кх занимает 1ГБ озу сам по себе, 5ГБ кеш засечек, еще куча кешей всяческих, проверьте ваш запрос на нормальном сервере.

а зачем union all ?

сначала обновитесь до 20.9.7.11

спасибо попробуем

Ок, спасибо, проверю.

> а зачем union all ?

Не удалось построить запрос так, чтобы все тоже самое выполнялось более компактным и единственным запросом. Буду благодарен, если подскажите / направите куда тут копать, чтобы можно было сделать агрегацию с результатом как выше.