SingleStore supports Flexible Parallelism, which allows multiple cores on the same node to access the same database partition. To use Flexible Parallelism, it must be enabled prior to database creation.

With Flexible Parallelism, as database partitions are created, they are divided into sub-partitions. As a query runs on a leaf node, multiple cores working on behalf of the query can process different sub-partitions of a partition in parallel.

As an example, if you are currently at one partition per core with Flexible Parallelism, doubling the size of your cluster and then rebalancing will result in two cores for each partition. As a result, a simple query that scans and aggregates all the data in a single partition will now execute more quickly than it did before.

Typically, analytic or hybrid transactional/analytic workloads will gain some benefits from Flexible Parallelism, especially over time as you add more hardware and rebalance clusters. Strictly OLTP workloads will tend to not benefit from enabling Flexible Parallelism.

As you add cores to your SingleStore system, you can reach a point where you have more cores than database partitions on each leaf node. Without Flexible Parallelism, there is a limit of one core accessing each database partition; therefore, the maximum ratio of cores to partitions is 1:1. This can lead to a kind of bottleneck as you can have idle cores on a node where you have queries accessing a single partition.

The default setting query_parallelism_per_leaf_core = 1 indicates a parallelism of one thread per core for each query. With this setting all cores would be used for a query.

Flexible Parallelism is disabled by default in SingleStore in version 7.8 and previous.

SELECT * FROM information_schema.DISTRIBUTED_DATABASES;

+-------------+---------------+----------------+--------------------+------------+-------------+---------+--------------------+-----------+---------+
| DATABASE_ID | DATABASE_NAME | NUM_PARTITIONS | NUM_SUB_PARTITIONS | CLUSTER_ID | REMOTE_NAME | IS_SYNC | IS_SYNC_DURABILITY | IS_PAUSED | SITE_ID |
+-------------+---------------+----------------+--------------------+------------+-------------+---------+--------------------+-----------+---------+
|           4 | db            |              3 |                 24 |          0 |             |       1 |                  0 |         0 |       1 |
+-------------+---------------+----------------+--------------------+------------+-------------+---------+--------------------+-----------+---------+

EXPLAIN SELECT * FROM example_table;

+--------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                          |
+--------------------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0 parallelism_level:segment                                   |
| Project [example_table.a, example_table.b]                                                       |
| ColumnStoreScan example_db.example_table, SORT KEY __UNORDERED () table_type:sharded_columnstore |
+--------------------------------------------------------------------------------------------------+