New GTID functions in Galera 4

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s