Autoincrement with XtraDB-Cluster

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I’m brand new to database clusters (this is my first one), I just fired everything up yesterday and I began testing.

I have 3 database nodes all running Percona-XtraDB-Cluster with an HAProxy load balancer, after doing some research it appears that there may be some issues with using auto_increment. The problem is we don’t have a choice since we are using pre-packaged applications such as WHMCS and Multicraft.

After doing some testing I have found that auto-increments are skipping 2, which will cause problems with our application.

Example:
| 1093 | Node 1 write |
| 1096 | Node 1 write |
| 1099 | Node 1 write |
| 1102 | Node 1 write |
| 1105 | Node 1 write |
| 1108 | Node 1 write |
| 1111 | Node 1 write |
| 1114 | Node 1 write |
| 1117 | Node 1 write |
| 1120 | Node 1 write |
| 1123 | Node 1 write |
| 1126 | Node 1 write |
| 1128 | direct write |
| 1131 | direct write 2 |

Node 1 writes are writes from an automated PHP script running a while loop with a 0.5 second wait in between.
Direct writes are simply insert statements ran on the console for the master node.

What can we do about this?

2

If I understand correctly, gaps in auto increment values are problematic for your applications? This behaviour is by purpose and default in Percona XtraDB Cluster. It’s in order to avoid conflicts when you do inserts on many nodes simultaneously. With different auto increment ranges reserved for each node - there is no such problem.
You can find more details here:
[url]Percona XtraDB Cluster

You can change this behaviour and make insert values in auto-increment column sequentially (as default in normal MySQL), but if would be best if you do writes on one node only at the time.

1 Like
3

Hello,

And as you would to change the behavior of the auto-increment sequentially for?

I’ve tried putting the variables in my.cnf but it did not.

4

Did you set it off on all nodes of PXC ? As it works only when you set wsrep_auto_increment_control to OFF to entire cluster nodes.

5

Thanks,

really lacked only disable variable wsrep_auto_increment_control.