SingleStore Platform

Tags intermediate guide singlestore sql-platform distributed olap htap cloud

SingleStore (formerly MemSQL) is a distributed SQL database designed for real-time analytics and transactions. It offers a unified architecture combining row-store for fast writes and columnstore for analytical queries, with MySQL wire protocol compatibility. BenchBox connects via the MySQL protocol using the official singlestoredb Python SDK and uses LOAD DATA LOCAL INFILE for high-throughput data ingestion. SQLGlot provides mysql dialect support for SQL translation.

SingleStore is deployed as both a fully managed cloud service (Helios) and as a self-managed cluster. It is used in production for financial services, gaming, retail analytics, and real-time operational dashboards.

Features

  • MySQL wire protocol - Standard MySQL connectivity via singlestoredb SDK (port 3306)

  • LOAD DATA LOCAL INFILE - High-throughput bulk loading (>100K rows/sec)

  • Columnstore analytics - Columnar storage for dramatic TPC query speedups

  • Shard key distribution - Automatic data distribution across leaf nodes

  • Reference tables - Fully replicated small dimension tables (nation, region) to eliminate broadcast joins

  • Full TPC-H support - All 22 queries with row count validation

  • Full TPC-DS support - All 99 queries with row count validation

  • Helios + self-managed - Works with both SingleStore Helios (cloud) and on-premises deployments

Quick Start

# Install singlestoredb dependency
uv add singlestoredb

# Or install via the SingleStore extra
uv add benchbox --extra singlestore

# Configure connection (SingleStore must be running)
export SINGLESTORE_HOST=localhost
export SINGLESTORE_PORT=3306
export SINGLESTORE_USER=root
export SINGLESTORE_PASSWORD=your_password

# Run TPC-H benchmark
benchbox run --platform singlestore --benchmark tpch --scale 0.01

Docker Quick Start (Self-Managed)

# Start SingleStore with Docker
docker run -d --name singlestoredb \
    -e ROOT_PASSWORD="your_password" \
    -p 3306:3306 \
    ghcr.io/singlestore-labs/singlestoredb-dev:latest

# Verify connectivity
mysql -h 127.0.0.1 -P 3306 -u root -p -e "SELECT @@memsql_version"

# Run TPC-H benchmark
benchbox run --platform singlestore --benchmark tpch --scale 0.01

Helios (Cloud) Connection

# Set Helios endpoint from your workspace connection string
export SINGLESTORE_HOST=xyz123.singlestore.com
export SINGLESTORE_PORT=3306
export SINGLESTORE_USER=admin
export SINGLESTORE_PASSWORD=your_helios_password
export SINGLESTORE_DATABASE=benchbox

# Run TPC-H benchmark
benchbox run --platform singlestore --benchmark tpch --scale 1

Connection Configuration

Parameter

Environment Variable

Default

Description

Host

SINGLESTORE_HOST

localhost

SingleStore node or Helios endpoint

Port

SINGLESTORE_PORT

3306

MySQL protocol port

Username

SINGLESTORE_USER or SINGLESTORE_USERNAME

root

Database user

Password

SINGLESTORE_PASSWORD

(empty)

Database password

Database

SINGLESTORE_DATABASE

(auto-generated)

Target database

Columnstore Tables

BenchBox creates all benchmark fact tables as columnstore tables for optimal analytical performance. Columnstore tables use columnar storage on leaf nodes, dramatically improving scan and aggregation performance compared to the default rowstore.

Shard Keys - Each fact table has a shard key on its primary join column, distributing rows evenly across leaf nodes and co-locating related rows for efficient distributed joins:

Table

Shard Key

lineitem

l_orderkey

orders

o_orderkey

customer

c_custkey

part

p_partkey

supplier

s_suppkey

partsupp

ps_partkey

Reference Tables - Small dimension tables (nation, region) are created as REFERENCE TABLE to replicate them to every leaf node, eliminating broadcast joins.

Bulk Loading

BenchBox uses LOAD DATA LOCAL INFILE for bulk data loading, which achieves >100K rows/sec throughput. TPC-format files (with trailing field separator) are pre-processed to remove trailing delimiters before loading.

The singlestoredb connection is created with local_infile=True to enable local file ingestion.

Benchmarks

Benchmark

Status

Notes

TPC-H

✅ Supported

All 22 queries, optimized shard/sort keys per table

TPC-DS

✅ Supported

All 99 queries, random shard distribution (no custom keys)

SSB

✅ Supported

Star Schema Benchmark, random shard distribution

ClickBench

✅ Supported

Web analytics benchmark, random shard distribution

Dependencies

singlestoredb>=1.0.0

Install via:

uv add benchbox --extra singlestore

Troubleshooting

ImportError: singlestoredb not available - Install the dependency:

uv add singlestoredb

Connection refused on port 3306 - Verify SingleStore is running and the host/port are correct:

mysql -h $SINGLESTORE_HOST -P $SINGLESTORE_PORT -u $SINGLESTORE_USER -p -e "SELECT 1"

LOAD DATA LOCAL INFILE disabled - Ensure the server has local_infile enabled:

SET GLOBAL local_infile = ON;

Helios connection issues - Confirm your workspace is active in the SingleStore portal and copy the exact hostname from the connection string shown in the portal.

References