0

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
1

We've made some tests. It turns out that auto-increment thing works completely different, so my concerns were meaningless.

With three nodes each insert into the table updates auto-increment index of a table completely. So you're always have not used values, only in rear cases where two inserts from different nodes are coming simultaneously then ids can appear one after another.

This brings us to another thought. If we have five nodes we're gonna be out of INT(11) five times sooner than with an ordinary non-cluster table.

Improve this answer
3
  • To add to this, I thought I'd share this article about monitoring auto_increment values as I think it might offer some insight for your research percona.com/blog/2017/11/08/…
    – greenweeds
    Apr 19, 2018 at 12:43
  • This is helpful even for not in cluster tables. Thank you =)
    – Taggart Comet
    Apr 20, 2018 at 6:10
  • There are a lot of things that "burn" auto_inc values -- INSERT IGNORE, DELETE, REPLACE, etc.
    – Rick James
    May 6, 2018 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.