Comments on: Should You Use ClickHouse as a Main Operational Database?

By: Adnan

Adnan — Thu, 28 Nov 2019 12:02:19 +0000

In reply to sourabh S. Exactly bro! i was thinking same, thats why i asked you why didn't you use ReplacingMergeTree .. So now its all stable for you ? But on merge new record, does it do it instantly ? or you have to wait ? Me too working on some telecom company stuff, so what i thought is to keep mysql and clickhouse on same application .. what i am trying is to select record from clickhouse, then update it and insert it back in the clickhouse table ReplacingMergeTree, i dont know if it will success but i am trying for it .. Share your opinion too it will be appreciated ;)

By: sourabh S

sourabh S — Mon, 25 Nov 2019 18:18:37 +0000

In reply to Adnan. I guess i failed to explain my point. MergeTree is not unreliable,its the best and the fastest engine which we should use. The only problem is the updates and deletes queries, which is not only the problem of MergreTree but the whole clickhouse. ,it doesnt matter if its MergeTree, ReplacingMergeTree or collapsingtree ,the performance is unpredictable about when will the changes actually happen and it gets worse as the table size grows.

By: sourabh S

sourabh S — Mon, 25 Nov 2019 18:15:16 +0000

In reply to Adnan. Regarding Update and Deletes : any table that requires constant updates/delete we migrated it back to MYSQL Galera Cluster, If we need to update any row in clickhouse we just replace the row with another row keeping the Primary key/touple the same ,for this reason we are mostly using Replacing MergeTree. we tried to use collapsingTree earlier but we changed it to ReplicatingMergeTree later , i dont really remember the reason why we favored ReplicatingMergeTree instead of collapisngTree right now. We completely quit the idea of using Alter Update and Alter Delee queries as they get extremely slow as the table size increases ,it doesnt matter if its replacaing or collapsing tree ,the permormace is unpredicatable about when will the changes actually happen and it gets worse as the table size grows.

By: Adnan

Adnan — Mon, 25 Nov 2019 17:06:46 +0000

In reply to sourabh S. BTW bro can you tell me why MergeTree is unreliable ? and did you try ReplacingMergeTree ?

By: Adnan

Adnan — Thu, 14 Nov 2019 23:50:02 +0000

In reply to sourabh S. Did you try ReplacingMergeTree or CollapsingMergeTree for update/delete ? Is your system working good now with update/delete ? which engine are you using now then ?

By: Adnan

Adnan — Thu, 14 Nov 2019 23:23:24 +0000

Well if we plan better before diving into CH database, i think we can avoid many issues, CH is very good for analytics, and also not bad for update/delete, we just need to follow their docs and limits. So we can achieve our goal with less hassles .. well your article is nice and i like it though 😉

By: sourabh S

sourabh S — Sun, 07 Jul 2019 01:41:25 +0000

we run adservers which process almost a several hunderd million to close to a billion requests per day.
we are using clickhouse for our analytical storage engine for over 1.5 yeras now.

few months ago when updated/deletes came out for clickhouse we tried to do exactly what is mentioned above .i.e convert everything to clickhouse from mysql , including user,product table etc.

we used clickhouse as our primary storage (replicated engines with kafka) in the development mode everything was running smoothly even the updates and deletes , so we were happy and pushed the code to production.. however this turned out to be a major blunder and almost ruined our production server.

What happened was there were to many updates queries piling up and choking up the server ,clickhouse was not able to process them in time. as a result , updates were taking more then 30 minutes .

we knew from he start that updates would be slow but we dint expected this. in short clickhouse must not be used for the table where updates are happening frequently.

Merge engine is very unreliable about when would it merge the duplicate entries. usage of ‘final’ keyword query is very bad ideas once you table starts growing.also not to mention frequent data corruption/loss due to replication errors.

By: David

David — Sat, 25 May 2019 17:33:39 +0000

Additional index simulation is just what I was looking for. Thanks!

By: Kevin English

Kevin English — Mon, 25 Feb 2019 20:49:16 +0000

GROUP BY is not OLAP. Please stop associating single-index column stores with OLAP. They are miles apart. If you can not analyze and pivot in N dimensions, it is not OLAP.

By: Denis Golius

Denis Golius — Tue, 15 Jan 2019 08:15:08 +0000

Alexander Rubin showed the worst things… So maybe you are not realy good in CH?