MemSQL is a distributed in-memory relational database designed for both transactional and analytical workloads.[01]
- Website
- https://www.memsql.com/[01]
- Tech Docs
- https://docs.singlestore.com[02]
- Developer
- Country of Origin
- US
- Project Type
- Commercial
- Supported Languages
- C++
- Inspired By
- Hekaton
- Compatible With
- MySQL
- Operating System
- Linux
- License
- Proprietary
- Wikipedia
- https://en.wikipedia.org/wiki/MemSQL[03]
MemSQL is a distributed in-memory relational database designed for both transactional and analytical workloads.[01]
Checkpoints[05]
MemSQL uses multi-version concurrency control and it's natural to create a consistent(non-fuzzy) snapshot without the need to block ongoing transactions.
Concurrency Control[06]
MemSQL uses multi-version concurrency control. Reads are not blocked, writes acquire row-level locks.
Data Model[06]
MemSQL is a distributed relational database. It also supports two-column key/value store.
Foreign Keys[07]
MemSQL currently supports foreign keys to assist sharding, but referential integrity is not enforced.
Indexes[08][09]
Skip list is the default index type in MemSQL. Skip list is lock free and thus leads extremely fast insert performance, and O(lg(n)) expected lookup/insert/delete performance. Unlike B+ tree, skip list is singly linked, thus reserve scan leads to twice as costly as forward scan. Skip list involves more pointer chasing than B+ tree which could potentially lead to more cache misses. In MemSQL, heuristics are applied to organize nearby nodes on the same physical page to mitigate penalties caused by pointer chasing. Lock-free hash table is also supported in MemSQL to perform fast exact-match queries.
Joins[11][12][13]
Nested loop join, index-nested loop join, merge join and hash join are supported in MemSQL. Joins between two Columnstore tables are often executed as sort merge join. For distributed join queries, if two tables are joined with identical shard key, the join will be performed locally; otherwise dataset is broadcast to other nodes via the network.
Logging[14]
MemSQL implements write-ahead-logging which records only committed transactions. It uses a transaction buffer pool as back-pressure mechanism so that a worker thread doesn't generate indefinite amount of logs. Replication is implemented based on log recovery.
Query Compilation[15][08]
Instead of the traditional interpreter based execution model, MemSQL 5 comes with a new code generation architecture, which compiles a SQL query to LLVM to machine code. When the MemSQL server encounters a SQL query, it parses SQL into AST and extracts parameters from the query, which is then transformed into a MemSQL-specific intermediate representation in MemSQL Plan Language(MPL). MemSQL then flattens MPL AST into a more compact format as MemSQL Bytecode(MBC). Plans in MBC format is then transformed into LLVM Bitcode, which LLVM uses to generate machine code. Such code generation architecture enables many low-level optimizations and avoids much less unnecessary work compared to interpreter-based execution. Compiled plans are also cached on disk for future use.
Query Execution[12]
MemSQL uses Tuple-at-a-Time Model for rowstore query execution, uses Vectorized Model for columnstore.
Query Interface[07]
MemSQL supports a subset of MySQL syntax, plus extensions for distributed SQL, geospatial and JSON queries. MySQL wire protocol is supported.
Storage Architecture[16]
In MemSQL, rowstore is completely in-memory and columnstore is disk-backed.
Storage Model[16]
In MemSQL, row segments in rowstore are stored in N-ary storage model in-memory. Column segments in columnstore are stored in decomposition storage model. Clustered columnar indexes are created for columnstore and compression is applied.
System Architecture[17][18]
MemSQL has a two-tier, clustered architecture. The nodes in upper tier are aggregators, which are cluster-aware query routers. One special node called Master Aggregator is responsible for clustering monitoring. The nodes in lower tier are leaves, which store and process partitioned shards. The aggregator sends extended SQL to leaves to perform distributed query execution.
Citations
21 sources- SingleStore | The Performance You Need for Enterprise AI memsql.com
- SingleStore Helios · SingleStore Documentation singlestore.com
- SingleStore - Wikipedia wikipedia.org
- http://y-combinator.silk.co/page/MemSQL silk.co
- https://docs.singlestore.com/docs/using-durability-and-recovery singlestore.com
- https://docs.singlestore.com/docs/how-memsql-works singlestore.com
- https://docs.singlestore.com/docs/mysql-features-unsupported-in-memsql singlestore.com
- Schedule - CMU 15-721 :: Database Systems (Spring 2016) cmu.edu
- https://docs.singlestore.com/docs/indexes singlestore.com
- https://docs.singlestore.com/docs/memsql-faq#section-what-isolation-levels-does-memsql-provide- singlestore.com
- https://docs.singlestore.com/docs/query-tuning singlestore.com
- https://docs.singlestore.com/4.0/concepts/columnstore singlestore.com
- https://docs.singlestore.com/docs/explain singlestore.com
- US9223805B2 - Durability implementation plan in an in-memory database system - Google Patents google.com
- https://docs.singlestore.com/docs/code-generation singlestore.com
- https://docs.singlestore.com/docs/columnstore singlestore.com
- https://docs.singlestore.com/docs/leaf singlestore.com
- https://docs.singlestore.com/docs/aggregator singlestore.com
- https://docs.singlestore.com/docs/create-view singlestore.com
- https://angel.co/memsql angel.co
- https://techcrunch.com/2011/07/12/y-combinator-alum-memsql-raises-2-1-million-from-ashton-kutcher-sv-angel-and-more techcrunch.com