SingleStore OperationsDevOps

SingleStore is useful when MySQL or MariaDB starts struggling with large tables, high write volume, or more expensive analytical queries.

Setup

Setup your server with Singlestore requirements in mind.

sysctl

/etc/sysctl.conf

vm.max_map_count=1000000000
vm.min_free_kbytes=1318193
vm.swappiness=10

net.core.rmem_max=8388606
net.core.wmem_max=8388606

Apply:

sudo sysctl -p

swap

sudo dd if=/dev/zero of=/swap_file bs=64GB count=32
sudo chmod 0600 /swap_file
sudo mkswap /swap_file
sudo swapon /swap_file

Tuning

Tune the database for better performance. Focusing on targeted queries and not compactations.

Columnstore segment rows

SELECT @@columnstore_segment_rows;
SET GLOBAL columnstore_segment_rows = 16384;

Queue depth

max_connection_threads should stay 25% above the desired queue depth.

SET GLOBAL max_connection_threads = 150;
SET GLOBAL workload_management_max_queue_depth = 120;

Aggregator sync auto increment

Use AGGREGATOR SYNC AUTO_INCREMENT after imports, restores, or manual inserts when the auto increment value may be behind the highest existing row ID. It resyncs the aggregator-side counter with the current table state.

Schema Notes

This section gathers the extra SingleStore schema features that help when structuring tables and columns.

Table types

These two choices are independent from the distribution type, so they can be combined as needed.

  • Columnstore: good for bulk updates and deletes. Default choice.
  • Rowstore: better for specific lookups and concurrent updates.

Distribution types

  • Sharded: partitioned across all nodes. Default choice.
  • Reference: every node keeps a copy of the table. Good for more advanced joins, but uses more space.

Sparse is not a table type. It only applies the sparse property to columns.

Column properties

Sparse column

Useful when a column has many NULL or zero values. This is common in tables with many optional columns.

$table->string('name')->nullable()->sparse();
$table->sparse();

Shard key

Works similarly to an index, but should be used where the selected column contains mostly unique values.

$table->string('name')->shardKey();
$table->shardKey(['f_name', 'l_name']);

Sort key

Used with columnstore tables. It groups repeating values into segments and can make queries faster.

A common case is indexing a price column in a products table. Queries that select by price or filter with WHERE price ... can run more efficiently because the data is organized in segments.

$table->string('name')->sortKey();
$table->sortKey(['f_name', 'l_name']);

Series timestamp

This is also a special index. Only one series timestamp can be defined per table, and it is used to optimize time-based queries such as FIRST(), LAST(), and TIME_BUCKET().

$table->datetime('created_at')->seriesTimestamp();

Migration

Mysqldump

Use this when you want a portable SQL dump.

Export on the old server:

mysqldump --complete-insert --extended-insert --quick --no-create-info --ignore-table=the_database_name.migrations -u user -h 127.0.0.1 -p database_main > backup.sql

Useful flags:

  • --extended-insert: writes INSERT statements using multiple-row syntax with several VALUES lists. This makes the dump smaller and speeds up inserts when the file is reloaded.
  • --quick: useful for large tables. It retrieves rows one at a time instead of buffering the full result set in memory before writing it.
  • --no-create-info: skips CREATE TABLE statements and exports only row data. Use this when the target schema already exists.
  • --complete-insert: writes complete INSERT statements with column names. This is useful when fixing insert issues related to non-default values.

Transfer the dump to the new server over SSH:

rsync -avz --progress backup.sql user@192.168.100.20:/home/user/backup.sql

Import on the new server:

mysql -h 127.0.0.1 -u root -p the_database_name < /home/user/backup.sql

Backup and restore

Use this when moving a SingleStore database directly between servers.

Important note: this method speed depends on the number of partitions in the database. If the database has 8 partitions, backup can use 8 cores. If it has 32 partitions, it can use all 32 cores and run much faster.

From the source server:

mkdir /home/user/dbbackup
chmod 777 /home/user/dbbackup

BACKUP DATABASE database_main TO '/home/user/dbbackup';

sudo chmod -R 777 /home/user/dbbackup

On the destination server:

sudo apt install parallel

Standard transfer:

rsync -avz --progress user@192.168.100.10:/home/user/dbbackup/ /home/destination/dbbackup/

Parallel transfer:

mkdir /home/destination/dbbackup

ssh user@192.168.100.10 "find /home/user/dbbackup -type f" | \
parallel --tag --line-buffer -j 4 rsync -avz --progress user@192.168.100.10:"{}" /home/destination/dbbackup/

Fix permissions:

chmod -R 777 /home/destination/dbbackup

Restore:

RESTORE DATABASE database_main AS database_main_backup FROM '/home/destination/dbbackup/'

Replicate

REPLICATE DATABASE database_main FROM user:'db_password'@192.168.100.10:3306;

This method is Enterprise only.

Chunked copy script

Useful for copying large datasets in parallel inside SingleStore.

import singlestoredb as s2
from concurrent.futures import ThreadPoolExecutor
import math

chunk_size = 200000
host = '127.0.0.1'
user = 'user'
password = 'pass'

old_database = 'database_old'
new_database = 'database_new'
tables = [
    'test_table',
]

def connect(database):
    return s2.connect(
        host=host,
        port=3306,
        user=user,
        password=password,
        database=database
    )

def copy_table(table_name):
    source_conn = connect(old_database)

    with source_conn.cursor() as cursor:
        cursor.execute(f"SELECT MIN(id), MAX(id) FROM {old_database}.{table_name}")
        min_id, max_id = cursor.fetchone()

    total_chunks = math.ceil((max_id - min_id) / chunk_size)

    def process_chunk(chunk_num):
        target_conn = connect(new_database)

        start_id = min_id + (chunk_num * chunk_size)
        end_id = start_id + chunk_size

        with target_conn.cursor() as cursor:
            query = f"""
            INSERT INTO {new_database}.{table_name}
            SELECT * FROM {old_database}.{table_name}
            WHERE id >= {start_id} AND id < {end_id}
            """
            cursor.execute(query)
            target_conn.commit()

        target_conn.close()
        print(f"✓ {table_name}: chunk {chunk_num + 1}/{total_chunks} completed (IDs {start_id}-{end_id})")

    with ThreadPoolExecutor(max_workers=8) as executor:
        executor.map(process_chunk, range(total_chunks))

    source_conn.close()

for table_name in tables:
    copy_table(table_name)

print("Migration complete!")

Sources

Performance Cases