New GTID functions in Galera 4

Dec 28, 2019

Mins to Read

All

Master Galera cluster management with Mydbops! Galera 4 came up with the lot of new features , which helps to effectively handle the cluster . In this blog I am going to explain in detail about the Galera 4 functions which were introduced for Global Transaction ID ( GTID ) .

GTID functions in Galera 4

wsrep_last_written_gtid()
wsrep_last_seen_gtid()
wsrep_sync_wait_upto_gtid()

Actually i have planned to test these functions on Percona XtraDB Cluster 8.0.15 experimental release. But, the function wsrep_sync_wait_upto_gtid was not supported in PXC 8 experimental release .

 
root@localhost [ mysql ] > select wsrep_sync_wait_upto_gtid('cc9d457b-gh33-12e7-91d7-be41124115fd:27', 100);
ERROR 1305 (42000): FUNCTION mysql.wsrep_sync_wait_upto_gtid does not exist

BUG link : https://jira.percona.com/browse/PXC-2688

So, I have installed MariaDB Galera cluster 10.4.11 and started my testing.

 
MariaDB [test_fun]> select @@version, @@version_comment\G
* 1. row *
@@version: 10.4.11-MariaDB-log
@@version_comment: MariaDB Server
1 row in set (0.000 sec)

wsrep_last_written_gtid

The function will returns the GTID of the last write transaction made by the client.
This can differ between different nodes in a single cluster group and different client sessions within a node.
This function can be used with wsrep_sync_wait_upto_gtid to identify the transaction upon which it should wait before unblocking the client.

 
MariaDB [test_fun]> select wsrep_last_seen_gtid()\G
* 1. row *
wsrep_last_seen_gtid(): 42e8c139-209a-11ea-a359-2bbb061b8859:1999
1 row in set (0.000 sec)

wsrep_last_seen_gtid

The function returns the GTID of the last committed transaction observed by the client.
This can differ between the different nodes within a cluster group as galera is Virtually Synchronous .
The function can be used with wsrep_sync_wait_upto_gtid to identify the transaction upon which it should wait before unblocking the client.

 
MariaDB [test_fun]> select wsrep_last_seen_gtid()\G
* 1. row *
wsrep_last_seen_gtid(): 42e8c139-209a-11ea-a359-2bbb061b8859:1999
1 row in set (0.000 sec)

wsrep_sync_wait_upto_gtid

This function can be used to blocks the other transactions ( sessions) until the node applies and commits the provided GTID.
We have the option to provide the timeout, If we don’t provide a timeout, it will take the defaults value of repl.causal_read_timeout.
This function is helpful in case, you don’t want to allow the writes to happen in the node until committing the give GTID.

 
MariaDB [test_fun]> select wsrep_sync_wait_upto_gtid('42e8c139-209a-11ea-a359-2bbb061b8859:1997',100)\G
* 1. row *
wsrep_sync_wait_upto_gtid('42e8c139-209a-11ea-a359-2bbb061b8859:1997',100): 1
1 row in set (0.001 sec)

Lab section

Let us simulate a work load to test out these wsrep functions.

Setup

2 Node Galera Cluster ( Same Hardware )
- 192.168.33.21
- 192.168.33.22
2 two tables ( sbtest1, sbtest2).
1M records in each table.

Steps in Test case:

Disable the cluster sync between the nodes.
Perform a full table UPDATE on both tables sbtest1 and sbtest2 (without any WHERE clause) on node1.
Fetch the last updated GTID on node1 using function wsrep_last_seen_gtid.
Enable sync on node2 with function wsrep_sync_wait_upto_gtid (ex, wsrep_last_seen_gtid,500) .
Make a write/read on node2 and validate it.

Testing phase

Validate the cluster nodes and load the data for testing.

 
MariaDB [test_fun]> select @@version , @@version_comment;
+---------------------+-------------------+
| @@version           | @@version_comment |
+---------------------+-------------------+
| 10.4.11-MariaDB-log | MariaDB Server    |
+---------------------+-------------------+
1 row in set (0.083 sec)MariaDB 

MariaDB [test_fun]> show tables like '%sb%';        
+---------------------------+
| Tables_in_test_fun (%sb%) |
+---------------------------+
| sbtest1                   |
| sbtest2                   |
+---------------------------+
2 rows in set (0.039 sec)

Disabling the cluster sync between the nodes.

 
MariaDB [test_fun]> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| mydbopslabs22 |
+---------------+
1 row in set (0.072 sec)

MariaDB [test_fun]> set global wsrep_cluster_address=''; set global wsrep_on=0;
Query OK, 0 rows affected (2.083 sec)
Query OK, 0 rows affected (0.000 sec)

Performing a full table UPDATE on tables.

 
MariaDB [test_fun]> update sbtest1 set k = 100000; update sbtest2 set k = 100000;
Query OK, 1000000 rows affected (1 min 29.171 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0
Query OK, 1000000 rows affected (1 min 39.855 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

Fetching the last executed gtid in node1 and Enabling sync on node2 with function wsrep_sync_wait_upto_gtid.

On node 1,

 
MariaDB [test_fun]> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| mydbopslabs21 |
+---------------+
1 row in set (0.197 sec)
MariaDB [test_fun]> select wsrep_last_seen_gtid();
+-------------------------------------------+
| wsrep_last_seen_gtid()                    |
+-------------------------------------------+
| 42e8c139-209a-11ea-a359-2bbb061b8859:2757 |
+-------------------------------------------+
1 row in set (0.041 sec)

On node2

 
MariaDB [test_fun]> set global wsrep_on=1; set global wsrep_cluster_address='gcomm://192.168.33.21,192.168.33.22'; 
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.600 sec)MariaDB [test_fun]> 

MariaDB [test_fun]> select wsrep_sync_wait_upto_gtid('42e8c139-209a-11ea-a359-2bbb061b8859:2757',500);
+----------------------------------------------------------------------------+
| wsrep_sync_wait_upto_gtid('42e8c139-209a-11ea-a359-2bbb061b8859:2757',500) |
+----------------------------------------------------------------------------+
| 1                                                                          |
+----------------------------------------------------------------------------+
1 row in set (3 min 5.372 sec)

wsrep_sync_wait_upto_gtid took 3 mins 5 sec to apply the UPDATE events .

When the wsrep_sync_wait_upto_gtid was in progress ,cluster prevents the operations by new client session (read/write).

 
MariaDB [test_fun]> show processlist ;
| 14 | root        | localhost | test_fun | Query   |  144 | Executing                                  | select wsrep_sync_wait_upto_gtid('42e8c139-209a-11ea-a359-2bbb061b8859:2757',500) |    0.000 |
| 20 | system user |           | NULL     | Sleep   |    0 | Update_rows_log_event::ha_update_row(2757) | update sbtest2 set k = 100000                                                     |    0.000 |
9 rows in set (0.152 sec)

MariaDB [test_fun]> insert into fun_gtid (id) values (23); 
ERROR 1047 (08S01): WSREP has not yet prepared node for application use

MariaDB [test_fun]> select * from test_fun limit 1;
ERROR 1047 (08S01): WSREP has not yet prepared node for application use

Note : Based on my experience, it was affecting the newly created MySQL sessions, the existing client sessions were not affected.

I have also created the BUG with MariaDB to understand this behaviour

BUG link : https://jira.mariadb.org/browse/MDEV-21338

Additionally , We can also integrate these functions with the existing MySQL GTID functions.

i,e ) To know the lag ( flow control ) between two servers , you can use the below

 
select gtid_substract(node2_wsrep_last_seen_gtid , node1_wsrep_last_seen_gtid);

wait_for_executed_gtid_set ( for GTID replication ) function is the partially equivalent to wsrep_sync_wait_upto_gtid ( for Galera replication )

Hope this blog helps to someone who is looking for the Galera functions for GTID . At Mydbops, We are keep testing the new things on MySQL and related tools, will be coming back with new blog soon.

Empower Your Galera Cluster with Mydbops!

Mydbops offers comprehensive MySQL services to elevate your database environment.

No items found.