3 ms·
I believe we are talking different scales, neither MemSQL nor SQL Server are designed for scale-out high-throughput mixed workloads for many practical definitio
by jandrewrogers 8y ago
I believe we are talking different scales, neither MemSQL nor SQL Server are designed for scale-out high-throughput mixed workloads for many practical definitions of "scale-out" and "high-throughput".
First, online insertion (parsing, processing, indexing, disk storage) of millions of records/second should be per node, basically saturating the ingress path of each node's 10/25-GbE interface, and scale-out additively. You'll need the aggregate insertion throughput of several nodes for many live sensor data sources, never mind trying to load several petabytes of historical data in a reasonable amount of time. Second, by implication, the workload is far too large for an in-memory database -- those online indexes you are dynamically constructing need to be on disk and distributed. Indexing several columns through disk storage at wire speed under these parameters is not something either MemSQL or SQL Server (or any database really) is designed to do, which was my point. Being in-memory often has few implications for query performance in well-designed parallel database kernels; on modern hardware, storage I/O is rarely the throughput bottleneck for mixed workloads if you are doing everything else optimally.
The core problem is that online disk-backed secondary index construction in a distributed environment that can use the full bandwidth of the switch fabric isn't feasible. A modern storage engine and scheduler will get you the raw throughput in most cases but that is a separate question from how do you support low-latency queries at the same time. Like I alluded to with time-series databases, the way you avoid the low throughput of index construction is by not constructing indexes -- other, sometimes exotic, selectivity mechanism exist that have orders of magnitude lower construction cost than a classic index while providing relatively efficient search. Even if those search mechanisms are slightly less selective than a traditional index, you make up for it with the intrinsically higher throughput of those mechanisms.
re: time-series, I don't doubt that you can do sub-second queries on a time-series table with a trillion rows -- I've done it with tens of trillions of rows in a single table. Time-series is one of the easiest data models to scale if you have a modicum of cleverness. The challenge is ensuring this query performance while inserting tens of millions of records per second into the index at the same time and/or you need to index more dimensions than a timestamp.
- manigandham 8y agoYou have a fascinating comment history and I actually favorited your previous comments on SpaceCurve. It seems all I can find about this is from your blog - is there anywhere else to read how that database actually works with discrete topology? And is it only for geospatial?
- polskibus 8y agoCould you provide some pointers to "other, sometimes exotic, selectivity mechanism" ? I'm interested in reading more about alternatives to indexing.