4 ms·
While reading the article, I found that they are rediscovering the tricks, already well-known for ClickHouse: > The biggest mistake I made was that I kept the
by zX41ZdbW 4y ago
While reading the article, I found that they are rediscovering the tricks, already well-known for ClickHouse:
> The biggest mistake I made was that I kept the UPDATEs in our code (we update the previous pageview with the duration, remove the bounce, etc.). In a few weeks, I'll be moving to 100% append-only by utilizing negative numbers.
> For example, if you want to set bounce_rate to 0%, you would write a 1 for bounce_rate on the first pageview and then insert a duplicate with -1 for bounce_rate. And for the duplicate row, you'd have nothing set for pageviews, visits and uniques, so it would all group nicely.
This is nearly a copy-paste from ClickHouse documentation.
> We shard on UUID, and then we set SiteId as the sort key. We do this because we want to utilize something called "local joins" in SingleStore. Long story short, events can be joined with event_properties (allowing you to have thousands of dynamic properties per event you track), and it's fast.
This statement is slightly disappointing as well. No magic, no advantage to ClickHouse.
The outcome can be read as follows: "use SingleStore exactly as you'd use ClickHouse and maybe it will work alright".
SingleStore advantages:
- good compatibility with MySQL dialect;
- offers good UPDATE/DELETE for fresh data (invalidated by this article, as they dropped the usage of UPDATE for better performance);
- good support for JOINs (invalidated by this article, as they ended up using local JOINs);
SingleStore disadvantages:
- not open-source, worse brand recognition, the old brand (MemSQL) did not work well too;
- performance claims don't validate;
- older company and many initial developers no longer work there;
Fathom sounds like a toy use case as the data amount is too low. ClickHouse is almost universally selected for similar and larger use cases, with hundreds of billions of events each day.
PS. Nevertheless, the post is hilarious:
> I was suffering from low energy in the two weeks leading up to this migration, and I was feeling awful throughout migration week, especially on migration day. On Sunday 14th March 2021, two days after we had finished the migration, my wife showed me an already-half-used bag of coffee beans I had been drinking through most of this migration, and the bag said "DECAF." Divorce proceedings are underway.
- AdamProut 4y agoClickhouse is great a single table analytical queries (Group-by + aggregates + filters over a single table). It can match any top tier columnstore database at this, maybe even better at smaller scales, and if that is all you need it will do the job well Beyond this, it has a naive query optimizer and limited ability to run distributed joins. This is why you won't find well known analytical benchmark results like TPC-H and TPC-DS for clickhouse vs other SQL data warehouses. SinglestoreDB has been doing real time analytics much longer then clickhouse and has a much more mature feature set at this point[1][2]. Our go to market is more big enterprise driven so we are definitely much less well known among developers. Revenue share-wise I suspect we are the leader in real time analytics (see disclaimer below - I'm biased but our revenue is reaching thresholds for IPO readiness). Also, if you follow Jacks later posts you'll see he replaces DynamoDB and Redis with SinglestoreDB as well. Now were getting into places Clickhouse doesn't tread at all... Disclaimer: I'm one of the cofounders of MemSQL/SingleStoreDB (still working away on making it better all these years later...). [1] https://www.singlestore.com/blog/the-technical-capabilities-your-database-needs-for-real-time-analytics/ https://www.singlestore.com/blog/the-technical-capabilities-... [2] https://dl.acm.org/doi/abs/10.1145/3514221.3526055 https://dl.acm.org/doi/abs/10.1145/3514221.3526055
- qoega 4y agoCan you provide SingleStore result for TPC-H and TPC-DS? I can't find it. Why? [1] https://www.tpc.org/tpcds/results/tpcds_results5.asp?orderby=dbms&version=3 https://www.tpc.org/tpcds/results/tpcds_results5.asp?orderby... [2] https://www.tpc.org/tpch/results/tpch_results5.asp?orderby=dbms&version=3 https://www.tpc.org/tpch/results/tpch_results5.asp?orderby=d... Disclaimer: I work for ClickHouse
- AdamProut 4y agoAlmost no one does "official" TPC results these days (maybe other then Oracle and some of the Chinese vendors). Most other cloud DWs have public TPC-H or TPC-DS results that are easily googlable. Clickhouse is missing for a reason... - https://research.gigaom.com/report/data-warehouse-cloud-benchmark/ - https://www.databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html - https://celerdata.com/blog/starrocks-queries-outperform-clickhouse-apache-druid-and-trino - https://aws.amazon.com/blogs/big-data/amazon-redshift-continues-its-price-performance-leadership/ Our results are here: https://www.singlestore.com/blog/tpc-benchmarking-results/ https://www.singlestore.com/blog/tpc-benchmarking-results/
- hodgesrm 4y agoIt's worth noting that ClickHouse and MySQL work quite well together. Clickhouse has a similar SQL dialect and can read data directly from MySQL. (Awesome feature BTW; in some cases queries even run faster that way.) It's straightforward to build applications that do transaction processing in MySQL and analytics in ClickHouse. You can migrate data out of MySQL that does not belong there. You can also use CDC to mirror transaction data into ClickHouse for analytic processing. [0] The resulting applications are quite robust and have the advantage that different parts can scale independently. Building applications that use the strengths of each of the component databases is a natural way to scale systems. Disclaimer: I'm the author of the cited article and I work for Altinity. [0] https://altinity.com/blog/using-clickhouse-as-an-analytic-extension-for-mysql https://altinity.com/blog/using-clickhouse-as-an-analytic-ex...