Why my auto_increment_increment is 3 in percona galera cluster (PXC)

 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.