Comparison with ElasticSearch

[kk00ss]

Great work, thanks for opensourcing it.
It's rare for an open source project to have an abundance of documentation 👍
But for some of the queries in presented benchmark it might be better to use inverted index,
like coun() on all the advertisements from a particular advertisement provider.
If filtering clicks for advertisement provider is the most complex stage of the query - inverted index would serve as precomputed filter. Faceting/Aggregation cause random IO which might be slow if there are a lot of clicks for that advertisement provider. So it's rather unclear which approach is better.
I'll probably compare them myself, but I'm interested in your opinion on this topic.

[ei-grad]

+1 It would be great to see elasticsearch in official benchmarks, I'd probably have some time next week to add it, if nobody will do that before me...

@alexey-milovidov что нужно подготовить, чтобы кто-нибудь потом мог запустить бенчмарк для эластика на том же железе что остальные? я к вам могу в офисе зайти если что с вопросами? вижу dbms/benchmarks, но не совсем понятно с чего начать вообще :-)

[kk00ss]

I've spent some time thinking how to represent data in ElasticSearch in the best way. And came up with conclusion that similar denormalization is possible in other systems (any DBMS probably) to implement manually, but it wouldn't be fair, in my opinion, because analytical queries are unknown in advance.
Some queries from the benchmark, like those with SomeID <>0 probably won't benefit from inverted index, but if we add queries for specific ID's, like SomeID == 1538 then inverted index will be of great help. And results of other DBMS could also change.

[dedok]

@ei-grad я тут на этих выходных планирую погонять на AWS'е, план такой:
Делаешь load (cli у Click'а и ES) данных (я планирую dump вики взять) ну и бомбишь join'ы в кучу потоков в ES через http и С++ демона Click'а - api там вроде понятное.
Думаю для тестовых join, union идеально подойдут категории википедии.

P.S. Можем вместе провести эксперимент )
P.S.S. Если mraa не займет все время, то точно в эти выходные.

[ei-grad]

Есть же уже готовый бенчмарк в dbms/benchmark, как я понимаю это он https://clickhouse.yandex/benchmark.html, без всяких join'ов, чисто под стандартный паттерн аналитики по событиям в веб/мобайл приложениях, под эластик отлично ложится, только надо загрузчик данных и запросы написать.Но было бы клево иметь возможность самому на амазоне его прогнать, да.

[alexey-milovidov]

@alexey-milovidov что нужно подготовить, чтобы кто-нибудь потом мог запустить бенчмарк для эластика на том же железе что остальные? я к вам могу в офисе зайти если что с вопросами? вижу dbms/benchmarks, но не совсем понятно с чего начать вообще :-)

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

[buremba]

It would be great if you could write your posts in english because we don't know russian. :)

[dedok]

@ei-grad готовый есть, но мне интересно на другом дата сете попробовать, с сложными join'ми.

[ei-grad]

@buremba sorry, @dedok wants to proceed with his own test case containing complex join queries, and looking how clickhouse and elasticsearch deal with them.

I think it doesn't make a good sense, since nor ES, nor ClickHouse, are not designed to be good in such operations. Also, there is a good official benchmark (https://clickhouse.yandex/benchmark.html), which is also good for ES, and I don't see any need to reinvent the weel.

The russian part in my first message was about possibility of my communication with clickhouse devs IRL, since I work in the same office as they are.

[bogdanovich]

@dedok elasticsearch doesn't support complex join queries. Only for specified parent-child relationships (defined in mapping) where parent table is ideally should be a low cardinality table. And parent-child id relationships index is actually gets fully loaded into memory during these 'join queries'. I would say it doesn't support joins well enough.

[buremba]

I think the only fair comparisons would be simple cardinality and group by queries.

[dedok]

@bogdanovich fair enough.

[kk00ss]

ES could be better at filtering IMHO, for constraints like SomeID == 123 or SomeField == "abcd".

[synhershko]

Comparing ClickHouse to Elasticsearch would be comparing oranges to apples. One is a columnar store and the other is a document-based search server.

[bogdanovich]

@synhershko Elasticsearch gets promoted as an analytics solution too.

[matthiasg]

This does bring up the question how good clickhouse is with joins... I saw it being mentioned it might not be good at that?