In 0.5.0 version of MySQL Group Replication plugin, we have introduced the Auto-increment configuration/handling feature through which auto increment variables (auto_increment_increment and auto_increment_offset) are auto configured by the plugin, so that different members of the group don’t generate duplicated auto increment values.
Auto increment variables – auto_increment_increment and auto_increment_offset
MySQL server already provides two auto increment variables: auto_increment_increment and auto_increment_offset, which can be used to generate different auto increment values on each member. MySQL circular (MySQL 5.0.2 onwards) replication already uses this approach, but the servers need to be configured manually.
auto_increment_increment: controls the interval between successive column values. The default value is 1.
auto_increment_offset: determines the starting point for the AUTO_INCREMENT column value. The default value is 1.
For example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> CREATE TABLE autoinc1 -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.04 sec) mysql> SET @@auto_increment_increment=10; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec) mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM autoinc1; +-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | +-----+ 4 rows in set (0.00 sec) |
Auto increment in Group Replication (before 0.5.0 version)
Now consider a scenario where clients are concurrently inserting records in the table with a auto increment field on different members of the group.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
================================================================ Members | Inserted value. Duplicates surrounded with * ================================================================ M1 | 1 4 --------------------------------------------------------------- M2 | 2 --------------------------------------------------------------- M3 | 3 --------------------------------------------------------------- M4 | 4 ================================================================ | 1 2 3 *4* ================================================================ |
where the Members column contains the group members in which data is inserted.
When using the default values for auto increment, there could be a situation when two members insert a new record at the same instant of time. In the example above value 4 was generated and inserted by member1 and member4 at the same instant of time, which would result in a deadlock error on one member and a successful commit on the other.
In versions earlier to 0.5.0, even though correctness was never compromised, when updating different members, there was always a potential for unnecessary rollbacks and degraded performance.
Auto increment in Group Replication (from 0.5.0 version)
In Group Replication 0.5.0 version, we have added changes which will automatically set auto_increment_increment and auto_increment_offset variables every time we start GR plugin, and reset every time we stop it. But what values should these variables take, when Group Replication starts? The value of the auto_increment_offset should somehow allow the generation of separated values for each group member. To accomplish this the simplest option is to set it to the server id on each member and so this value will be different for every server.
At the same time the variable auto_increment_increment should have a value that when incremented to each offset generates a new individual id. The group size is the best option, but what is the group size? As it is undesirable to reconfigure the variables at each group change, the auto_increment_increment should then have by default a value that is suitable for most user scenarios.
For this reason we introduced a new system variable:
- GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT
This variable will have a default value of ‘7’.
The default value for group_replication_auto_increment_increment was chosen as 7, as a valid compromise taking into account the maximum usable values for each possible auto_increment_increment and what is a normal group expected size.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
----------------------------------- | auto_inc | #values | %usable | |------------------------------ | | 1 | 4294967295 | 100.00% | | 3 | 1431655765 | 33.33% | | 5 | 858993459 | 20.00% | | 7 | 613566756 | 14.29% | | 9 | 477218588 | 11.11% | | 11 | 390451572 | 9.09% | | 13 | 330382100 | 7.69% | | 15 | 286331153 | 6.67% | | 17 | 252645135 | 5.88% | | 19 | 226050910 | 5.26% | ----------------------------------- |
Table 1 above shows worst case scenario of maximum usable values for different auto_increment_increment values.
The variable auto_increment_increment is set, by default, to the value of group_replication_auto_increment_increment , i.e., 7 and auto_increment_offset is set to server_id, every time GR plugin starts.
GR plugin also makes sure that:
- GR plugin will only set auto_increment_increment and auto_increment_offset
on GR plugin start if they are at their default values of 1. So making sure
that it doesn’t override user set auto_increment values. - GR plugin will only reset auto_increment_increment and auto_increment_offset
to default value of 1 on GR plugin stop, if they were modified earlier by GR
plugin and not by the user.
Example
Consider a three member group with following auto-increment configurations:
auto_increment_increment: 7 (size of group is 3)
auto_increment_offset: server_id ( where M1 = 1, M2 = 2, M3 = 3)
And for inserts as sequenced in diagram below, you will see following values inserted in table:
|
1
2
3
4
5
6
7
8
9
10
11
|
=============================================================== Members | Inserted Values =============================================================== M1 | 1 15 -------------------------------------------------------------- M2 | 2 9 23 30 -------------------------------------------------------------- M3 | 3 10 17 =============================================================== |1 2 3 9 10 15 17 23 30 =============================================================== |
where the Members column contains the group members in which data is inserted.
The arithmetic sequence with common difference of 7 for each member would be:
M1: 1, 8, 15, 22, 29, 36….
M2: 2, 9, 16, 23, 30, 37….
M3: 3, 10, 17, 24, 31, 38….
The next inserted value for auto_increment field will be picked from that member sequence and will be greater than last inserted value. So for example in Figure 2, after the insert of key 30, if the next insert is on M3, then inserted auto_increment value will be 31 (23+7), but if it is on M1 then the inserted value would be 36 (15+7+7+7). This approach will make sure that we don’t get duplicate auto_increment values, while inserting on any member of the group.
For example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
M2> START GROUP_REPLICATION; M2> SELECT @@SERVER_ID; +-------------+ | @@SERVER_ID | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) M1> SHOW VARIABLES LIKE "%group_replication_auto_increment_increment%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | group_replication_auto_increment_increment | 7 | +--------------------------------------------+-------+ M2> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 7 | | auto_increment_offset | 2 | +--------------------------+-------+ 2 rows in set (0.00 sec) M2> STOP GROUP_REPLICATION; M2> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) |
Override default auto_increment_increment value
If your group size is bigger or smaller than the default group_replication_auto_increment_increment value of 7 and you want to override it, you can always do that by modify its value before plugin start. This value cannot be changed when the plugin is running.
For example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
M1> SHOW VARIABLES LIKE "%group_replication_auto_increment_increment%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | group_replication_auto_increment_increment | 7 | +--------------------------------------------+-------+ M1> STOP GROUP_REPLICATION; Query OK, 0 rows affected (0.01 sec) M1> SET @@GLOBAL.group_replication_auto_increment_increment= 5; Query OK, 0 rows affected (0.00 sec) M1> SHOW VARIABLES LIKE "%group_replication_auto_increment_increment%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | group_replication_auto_increment_increment | 5 | +--------------------------------------------+-------+ 1 rows in set (0.01 sec) M1> START GROUP_REPLICATION; Query OK, 0 rows affected (0.04 sec) M1> SHOW GLOBAL VARIABLES LIKE "auto_increment_increment"; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 5 | +--------------------------+-------+ 1 rows in set (0.01 sec) |
Conclusion
Before this feature, there was no provision available to automate the setting of different auto_increment_increment and auto_increment_offset values on each member of group replication. This feature improves the overall performance of group replication as there will be no rollbacks due to duplicate auto increment values. Also these settings can be conveniently overridden by modifying group_replication_auto_increment_increment variable directly.
Go to labs releases and try latest MySQL Group Replication Plugin 0.5.0 version, and post your questions and feedback here.