Local and Unlimited Database Storage Concepts

You can store data locally in a SingleStore cluster or remotely in an object store. When you create a database, you specify where to store the data.

Local Storage Databases

Local storage databases are stored in your SingleStore cluster. To create a local storage database, use the CREATE DATABASE command.

To backup or restore a local storage database, use the BACKUP DATABASE and RESTORE DATABASE commands, respectively.

Unlimited Storage Databases

Note

This feature is not available in all editions of SingleStore. For more information, see SingleStore DB Editions.

Unlimited storage databases (also known as "bottomless" databases) are kept on remote object storage. The use of remote storage separates where data is stored (in an object store external to the SingleStore cluster) from where the data is processed (in a SingleStore cluster). Because unlimited storage databases are stored remotely, their size is not limited by the size of local cluster storage, but rather only by available external object storage. On public cloud object stores, this is for all practical purposes unlimited.

The cache size is set by the maximum_blob_cache_size_mb variable which is 80% of disk space by default.

Cache item replacement policy is a modified LRU strategy, wherein, one large scan does not flush the whole cache if the scanned data is bigger than the cache.

In an unlimited storage database:

  • All columnstore data is stored externally in an object store. Recently accessed columnstore data objects are also cached locally on the cluster's local storage.

  • Columnstore data can be committed locally, so you do not have to wait for it to get uploaded, which helps keep the latency as low as possible.

  • All rowstore data is stored locally in cluster memory and externally in an object store.

  • Data updates made on the cluster are flushed to the object store asynchronously. Typically, the object store will be no more than one minute behind the latest update on the cluster. Data is imported into an on-disk rowstore, and then asynchronously exported into unlimited storage on the cloud. This is the key to the high performance as data is ingested into the rowstore, "cold" data is put into long term online storage, keeping the "hot" data on disk where it is faster to access.

  • Data is compressed (lz4) upon insertion into remote storage and decompressed upon retrieval. This seamless process can help reduce network bandwidth and storage costs. The compression ratio is highly data-dependent. For rowstore data, it can be as much as 20x. For columnstores, the data is already compressed, but lz4 can compress it further, often in the range of 20% to up to 2x.

  • Data is always stored in an object store but may only have a subset in local storage in your cluster. Querying data that is in object store but not in local storage is comparatively slower the first time as it has to bring the data into the local storage.  Hence it is useful to know how much data and what data is in the cache. 

  • When you resume a cluster the data does not automatically fault back into the local storage. You will have to run queries to "warm the cache" on a resume.

Notice

You cannot use the REPLICATE DATABASE command to replicate an unlimited storage database. For an alternative, see Replicate an Unlimited Storage Database.

Local Disk Space Recommendations for Unlimited Databases

There’s no one-size-fits-all recommendation because every application has different access and locality-of-reference patterns. For the best performance, provide enough local disk to hold the entire database. In addition to allowing you to have more data than will fit locally on the cluster, the remote object storage is there to provide additional benefits including continuous backup, history tracking for PITR, ability to pause and resume, and so on. Providing enough disk to hold the whole database locally is especially important if all parts of the database have relatively similar access frequency, or if you do a lot of random seeking into the database without a lot of locality of reference.

If you have a very large database and you know that you have high locality of reference, then having the local storage just, say, 20% larger than your working set size can be a good choice. For example, if you have a 10TB database (of actual compressed data) that has 3 years of data, but you almost never touch data older than one year, then having 3.33TB of local disk plus 20% more as buffer, or about 4TB of local storage, should be adequate.

To monitor the rate of blob cache fetch activity, you can query information about fetch events in the mv_blob_cache_events information schema table.

You can also query mv_bottomless_status_extended.blob_cache_download_mb_per_sec which tells you how much data is being downloaded from the blob cache per second. If these seem high given the size of your database and scale of your hardware, consider increasing the local cache size.

Use the following query to find the total number of GB for an entire database:

SELECT SUM(mv_columnstore_files.size)/(1024*1024*1024) AS dataSizeGB
FROM mv_columnstore_files
WHERE mv_columnstore_files.database_name = ‘database_name';

Use the following query to find the amount of data in GB that is cached in local disk in a cluster:

SELECT  COUNT(*) AS numSegments,SUM(mv_cached_blobs.size)/(1024*1024*1024) AS blobdataSizeGB
FROM mv_cached_blobs
WHERE mv_cached_blobs.database_name = 's2_dataset_tpch'
AND type = ‘primary';

You have to run the above two queries against the information_schema database.  These queries are per database but you can roll it up by cluster if you remove the database name filters.  Also, these files are after compression (not raw data).