People often have a doubt when they move from native MySQL replication to PXC / clusters.
Why is my auto_increment_increment value 3?
People usually realize this question when their primary key (auto_increment) has reached its limit and gives an error.
The above scenario will arise due to poor design of their databases. People often use smallint / mediumint for their auto_inc primary key. Before migrating to PXC their database might be in M-S / M-M cluster setup. Most of the configuration for M-M setup won’t be an actual M-M setup. It will be used as an active-passive where people often forget to declare the auto_increment_increment & auto_increment_offset values properly.
For an M-S
We can have,
auto_increment_increment = 1
auto_increment_offset = 1
For an M-M
We should have, On both servers as follows
Server 1
auto_increment_increment = 1
auto_increment_offset = 1
Server 2
auto_increment_increment = 2
auto_increment_offset = 2
The difference between these variables are
auto_increment_offset : determines the starting point for the AUTO_INCREMENT column value
auto_increment_increment : controls the interval between successive column values
For eg,
If you have the variables declared as below
auto_increment_offset = 1
auto_increment_increment = 3
And your table structure is,
When you insert a record to the table the first id will store as 1 and for the second insert it will store as 4 and third entry as 7 and fourth entry as 10 and so on .
The series will be 1,4,7,10,13,16,........
Hope we are good with ideas between these variables.
Now coming to the actual point, Why my auto_increment_increment is 3 in Percona Galera Cluster (PXC).
PXC has the inbuilt algorithm which is handled by variable wsrep_auto_increment_control to control the auto_increment_increment value whenever a new node is joined to the cluster it will change the value of the variable accordingly.
This can be seen here,
We have a three-node galera cluster setup ready, once the first node is bootstrapped, you can see the cluster size and auto-increment values assigned
Now after starting the second node
Finally starting the third node,
Similarly, if a node is removed from the cluster the variable will be adjusted automatically.
When you have a three-node cluster your variable values will be declared as below (the offset value can change depending on which node was started first/last),
Node 1:
auto_increment_offset = 1
auto_increment_increment = 3
Node 2:
auto_increment_offset = 2
auto_increment_increment = 3
Node 3:
auto_increment_offset = 3
auto_increment_increment = 3
This is because to avoid duplicate entries inside the cluster. In the above scenario, each node will insert records in the series
Node 1 : 1,4,7,10,13,.....
Node 2 : 2,5,8,11,14,.....
Node 3 : 3,6,9,12,15,....
In this method, you won’t face duplicate entries inside your cluster. This is the reason why a three-node PXC auto_increment_increment has value 3. You can’t control this variable value manually, even if you change it will revert back to the actual value.
