Help me please clarify a very important question on which I can't find the answer. It only can be proven with tests.
Let's say we have XtraDB Cluster (or Galera Cluster) with three nodes. We have main user' table with the auto-increment primary key.
- Ids in this table on the first node goes like 1,4,7,10…
- In the second node – 2,5,8,11…
- In the third node – 3,6,9,12…
After some time we have that the first node has 10000 inserts, so its auto-increment value is around 30000.
But two other nodes were less loaded, so they have only about 100 inserts and their auto increment value is around 300.
Now we decided to add two more nodes to the cluster. Now the auto-increment offset is equaled five.
Obviously, two lesser loaded nodes can't fill the numbers from 300 to 30000 with their new offset, right?
So what will happen to the table's auto-increment on each node? How will the cluster behave?
Is it gonna start the auto-increment from 30000 for every node and unfilled ids from 300 to 30000 will remain unfilled?
Thank you for your time and experience.
Best Answer
Glad to know that you have figured out the trick.
BTW, to answer your 2nd question. PXC has a configuration (wsrep_auto_increment_control) that help you restore the auto_increment behavior similar to standalone.
If your workload doesn't have parallel writes going to the table then you can, of course, use this.
(Check more about it here http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-auto-increment-control)
You can also try it and see how it works.