MemSQL is Great for Use Cases where Performance (Latency and Data Freshness) Needs are High
September 01, 2021
MemSQL is Great for Use Cases where Performance (Latency and Data Freshness) Needs are High
Score 9 out of 10
Vetted Review
Verified User
Overall Satisfaction with SingleStore (formerly MemSQL)
We use MemSQL for a particular slice of our OLAP use cases: client-facing UI dashboards that have solid query latency and data freshness requirements (generally 4 9s under 2 seconds, although we try to get under 1, and data replication and recalculation in under a second), and on small to medium size data sets (10s of GB for fact data, GBs or 10s of GBs of dimension data). It is currently owned by a data platform team that supports client-facing use cases from a number of product teams.
- Data replication.
- Joins.
- Small data set processing in memory.
- User defined functions.
- Providing optimization options.
- Meeting latency and data freshness SLAs.
Vertica, Snowflake, SQL Server, Azure Data Warehouse, PowerBI, Aerospike, etc.
From what I've seen MemSQL is well worth the cost when latency and data freshness needs are high, i.e. you need a lot of queries to run with UI latency (the query itself takes less than a second or so), with very fresh streaming fact and dimensional data. It will be more expensive per "unit of performance" but if you need that performance then it'll get the job done.
From what I've seen MemSQL is well worth the cost when latency and data freshness needs are high, i.e. you need a lot of queries to run with UI latency (the query itself takes less than a second or so), with very fresh streaming fact and dimensional data. It will be more expensive per "unit of performance" but if you need that performance then it'll get the job done.
- On-prem Vertica (note, not Eon) provides more knobs for optimizing a particular data set and set of queries against it and performs as well or better in a single table, fact table queries. It will also scale to data size more cheaply due to its on-disk model. For large queries against large data sets where data freshness isn't as important (and latency either is or isn't), I'd take Vertica, although if you need to do a lot of joins that will struggle). However, as they still are exclusively columnar, dimension table updates, and recalls based on them, can only be tuned to happen so fast (we could do much better than 10 seconds with 10-100 updates per second for raw replication, and Vertica's joins are always slow so recalls were worse).
- Snowflake suffers similarly to Vertica in the data freshness, replication, and re-calc area; SF also doesn't give as many knobs to turn as Vertica for data set optimization but seems to be better at joins. If you have a lot of queries to run against a lot of data and joins are limited, you need query latency low and consistent but you don't need a ton of freshness, I'd stick with Vertica. If joins matter more, or you can accept notably-but-not-terribly worse performance, then Snowflake is fine and cheaper from what we've seen. (Again, I can't speak to SF vs Vertica Eon).
- SQL Server and ADW we couldn't get to perform as well as the other options, but I'll say we didn't try that hard on those.
- Aerospike is amazing as a KV store; however for OLAP use cases where you want to balance performance against the flexibility of queries against general event (time series) data (i.e. be able to roll up to different grains) then KV becomes challenging.
- PBI is great if you want an integrated BI tool, but if you want an OLAP solution to build against, with some particular scale or performance needs to be mentioned above, I'd go with one of these other solutions. It really can be great for letting non-tech folks build relatively small data sets and quick insights for customers (internal or external), great leverage in that case.
We haven't used it for predictive analytics, see other comments for fast query and analytics in general.
Not much comment here: MemSQL can scale horizontally effectively, but with more of an in-memory model it is more expensive, so again I'd recommend scaling it for high performance (both latency and data freshness) cases.
Do you think SingleStore delivers good value for the price?
Yes
Are you happy with SingleStore's feature set?
Yes
Did SingleStore live up to sales and marketing promises?
Yes
Did implementation of SingleStore go as expected?
Yes
Would you buy SingleStore again?
Yes