General

What are license units and how do they apply to my cluster?

SingleStore (MemSQL) versions 6.7.13 and later employ licenses based on the concept of a unit, which is based on 8 vCPUs, 64GB of RAM, and 1TB of persistent cache (block storage). These dimensions are used to calculate the number of license units required for a host that runs leaf nodes. Conversely, the dimensions for hosts that run aggregator nodes can be of any size, and these hosts can be used in any quantity. Each host must meet the minimum system requirements before it can be added to a cluster.

These licensing terms only apply to new licenses, and a cluster’s current licensing terms remain in effect until renewal. Upon renewal, these new licensing terms are used to calculate the required license units. Note that the persistent cache (block storage) size refers to the total storage size, not to the maximum blob cache size.

Use the following table to help calculate the number of license units required for hosts that run leaf nodes. Regardless of whether hyper-threading is enabled, the number of virtual cores available to a processor must be included in the calculation. Round decimal results up to the nearest positive integer. The greatest of these three results is the number of license units required.

When adding a new leaf node to a cluster, the host’s dimensions can be adjusted to help conform to a given number of license units. This is especially true when the host is a virtual machine. For example:

The following table lists a variety of host dimensions and the number of license units required by each combination.

Refer to Determining capacity and usage for your license for more information.

What is the relationship between SingleStore and MySQL?

SingleStore is a standalone distributed database that is compatible with MySQL client software.

SingleStore is not a storage engine or other component of MySQL, nor is MySQL a component of SingleStore, and the SingleStore and MySQL servers do not share any code. The SingleStore server includes its own storage engine and SQL-based execution engine built around scalable distributed execution, lock-free data structures, and machine code generation.

Users use MySQL client software to connect to the SingleStore server. SingleStore uses the same wire protocol as MySQL and supports similar SQL syntax as MySQL for compatibility with MySQL client software.

Why do I see the MemSQL version as 5.5.8, even though that’s not the version of MemSQL I installed?

For compatibility with MySQL client software, SingleStore reports the version engine variable as 5.5.8. Client drivers look for this version to determine how to interact with the server.

This is not the SingleStore server version. The SingleStore server version can be found in the memsql_version engine variable, which can be queried like SELECT @@memsql_version.

As a result, certain MySQL clients may display the server version as 5.5.8, even though this is not the SingleStore version. For example, the mysql command line client may display

Server version: 5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)

when you connect to SingleStore.

Is SingleStore vulnerable to MySQL server security issues?

No. The SingleStore and MySQL servers are separate database engines which do not share any code, so security issues in the MySQL server are not applicable to SingleStore.

SingleStore implements the MySQL wire protocol in order to provide compatibility with MySQL client software. A part of this compatibility involves reporting a MySQL version number as described above. As a result of this, numerous third-party security scanners that use the reported version number as a vulnerability marker report false positives, as they’re looking at a version number, not at the actual presence of a vulnerability. Our engineering team as well as a third-party security analysis have confirmed that SingleStore is not susceptible to these MySQL vulnerabilities.

Is SingleStore a row-based or column-based store?

SingleStore provides both in-memory row-based and on-disk column-based stores.

The in-memory row store:

• Works best for mixed transactional and analytical workloads

• Provides low latency and highly concurrent reads and writes of individual rows as well as sophisticated analytical SQL queries

• Supports PRIMARY and UNIQUE keys

• Supports geospatial indexes

• Has longer recovery times (as the entire table needs to be loaded into memory)

The on-disk column store:

• Works best for analytical workloads

• Allows tables larger than the amount of available RAM in the cluster

• Uses compression (which lowers disk usage and accelerates replication)

• Provides fast and efficient scans of large datasets

• Provides sorted columnstore indexes

• Is optimized for batch UPDATE and DELETE queries

• Requires more expensive query compilation (as compared to the row store)

How does SingleStore’s in-memory lock-free storage engine work?

SingleStore’s storage engine uses multi-version concurrency control (MVCC) with lock-free skip lists and lock-free hash tables to enable high concurrency and throughput.  MVCC allows SELECT queries to run without being blocked by UPDATE queries by maintaining multiple versions of data. The old versions remain accessible to transactions that started before the UPDATE.

Lock-free data structures maximize performance overhead by avoiding traditional locks, while logical locks handle conflicts during concurrent updates. Delayed cleanup ensures that disk space is free once all transactions that depend on the old data are finished. It ensures the safe removal of outdated versions through garbage collection.

What is the advantage of SingleStore over traditional databases like Oracle, SQL Server or MySQL with data in a ramdisk or large buffer pools?

Two common techniques for leveraging large amounts of memory in traditional databases are storing data files on a ramdisk or running a disk-based storage engine with a large buffer pool. For example, MySQL performs much better with InnoDB configured to use a large buffer pool.

While running an existing storage engine like InnoDB in memory can alleviate some of the bottlenecks involved with disk, SingleStore has four distinguishing memory-optimized features that enable it to perform significantly better than disk-based storage engines running in memory:

• SingleStore is a distributed scale-out system. SingleStore scales to thousands of machines on commodity hardware.

• No buffer pool. Traditional databases manage a global buffer pool since they assume that the dataset can’t fit into memory. The buffer pool is a resource shared across all databases and all tables, which creates significant contention.

• Lock-free data structures. SingleStore uses memory-optimized, lock-free skip lists and hash tables as its indexes. Unlike B+ Trees, these data structures are designed from the ground up to be fast in memory.

• Code generation. Lock-free data structures are so fast that dynamic SQL interpretation quickly becomes the limiting factor for query execution. With code generation, SingleStore compiles SQL down to native code for maximum performance.

What is the advantage of SingleStore over other distributed databases?

• Full SQL. SingleStore supports full SQL and transactional semantics.

• Storage pyramid. SingleStore combines row and column store engines tuned for memory and flash storage.

• Scales on commodity hardware. SingleStore does not require exotic hardware and can run on-premises or in the Cloud.

• Enterprise ready. SingleStore supports a large number of enterprise security and manageability features.

What is SingleStore not for?

SingleStore excels at real-time and high throughput query use cases. It is a general purpose database for running both transactional and analytic workloads. However, there are use cases which SingleStore is not designed to run. Some of these are listed below:

• Object store.SingleStore is not designed to be a blob store. It is designed for high value data that is structured or semi-structured and ready to query. SingleStore has open-source connectors for integrating with a variety of object stores, including Amazon S3 and Hadoop File System (HDFS).

  See Load Data for more information.

• Running on low hardware.SingleStore is not designed to run on micro instances, mobile phones or other low-powered computers. It is designed to run on machines with at least 4 cores and 8GB of RAM. The easiest way to run SingleStore for development is to use the SingleStore Dev Image.

• In-process database.SingleStore is not run as a library or in-process with an application. SingleStore is a distributed database which runs in separate processes from the application, and applications connect to SingleStore via a client driver.