{question}
Why isn’t my auto_increment column sequential?
{question}
{answer}
The auto_increment values in sharded tables in SingleStore are range-partitioned by the aggregator, in order to ensure fast performance.
One of the inherent challenges in designing a high-performance distributed database is ensuring the uniqueness of data (such as primary keys or auto-incrementing values). This is because uniqueness is a global property: a unique data value on one particular node in the database that cannot overlap with other data on any node. When inserting unique values, this would create potentially significant performance degradation if every node had to query every other node in the cluster in order to guarantee uniqueness.
SingleStore avoids this issue with auto_increment data by keeping track of the unique auto_increment value on the aggregator node on which the insert is performed. This saves the leaf nodes from having to do any work to ensure uniqueness, because it is guaranteed by the aggregator beforehand. However, since a cluster may have multiple aggregators that “do not know” about each other’s auto_increment values, the range of auto_increment values used by each aggregator is partitioned to eliminate the possibility of inserts on different aggregators attempting to generate the same auto_increment value.
In practice, this is implemented by requiring a BIGINT data type for auto_increment tables and assigning a range of values for each aggregator. This is the reason the value will appear to “jump around” when using multiple aggregators for inserts.
The implementation of AUTO_INCREMENT column in a sharded table uses the variable `aggregator_auto_inc_id` to avoid duplication of values . Each aggregator generates values from the range associated with its current value of `aggregator_auto_inc_id`.
In SingleStore each aggregator is assigned a distinct range of 2^50 possible values. This is combined with aggregator_auto_inc_id to generate unique values on each aggregator.
The range for an aggregator with aggregator_auto_inc_id = a will be computed as:
[(a - 1) * 2^50, a * 2^50 - 1]
The aggregator_auto_inc_id for a MA/CA can be determined using the following query. This variable is set internally when aggregator membership changes.
SELECT g.node_id,
m.ip_addr,
m.type,
g.variable_value AS aggregator_auto_inc_id
FROM information_schema.mv_global_status AS g
JOIN information_schema.mv_nodes AS m
ON g.node_id = m.id
WHERE g.variable_name = 'Aggregator_auto_inc_id'
AND m.type IN ('MA', 'CA')
ORDER BY m.type, g.node_id;The aggregator_auto_inc_id of a CA/Master can change due to reprovisioning or CA/MASTER membership changes. This implies that the range of auto_increment values generated on an aggregator can change as aggregator_auto_inc_id changes on that node.
If aggregator_auto_inc_id was previously assigned to a MA/CA and it get reassigned to a different non-leaf node then the auto-increment values generated on this node will continue to track the values from the previous assignment.
A notable exception to this behavior is in Reference tables, which are not sharded and always use the aggregator_auto_inc_id=1 range.
{answer}