Infrastructure at your Service

Saïd Mendi

Galera Cluster 4 with MariaDB 10.4.8: part 2

My previous blog was about the MariaDB Galera cluster new features (version 10.4).
Now I will only focus on the streaming replication and give you an overview of all the tests I performed.

How to enable streaming replication?

Quite easy, there are only 2 parameters:
wsrep_trx_fragment_unit and wsrep_trx_fragment_size
Then you have to choose a replication unit between the followings:
. bytes and the fragment size is in bytes
. rows and the fragment size is as big as the number of rows defined
. statements and the fragment size is as the number of statements in a fragment
Per default, the wsrep_trx_fragment_unit is in “bytes”. To enable the streaming replication, set the wsrep_trx_fragment_size to a value different from 0.
Now, depending on your activity, select the right one to manage your big transactions.
The best way is to test all three units with different fragment size.
For my tests, I use a MariaDB Galera Cluster 10.4.8 of 3 master nodes running on CentOS Linux release 7.5,
I export a table of more than 400.000 rows, delete all rows and reimport it using different scenarios.

Without the streaming Replication

MariaDB [mysql]> select @@wsrep_trx_fragment_unit, @@wsrep_trx_fragment_size;
+------------------------------------+-------------------------------------+
| @@wsrep_trx_fragment_unit          | @@wsrep_trx_fragment_size           |
+------------------------------------+-------------------------------------+
| bytes                              |                                   0 |
+------------------------------------+-------------------------------------+
1 row in set (0.000 sec);
MariaDB [employees]> select * into outfile '/tmp/titles.txt' fields terminated by ',' optionally enclosed by '"'
-> lines terminated by '\n' from employees.titles;
Query OK, 443308 rows affected (0.338 sec)
MariaDB [employees]> delete from titles;
Query OK, 443308 rows affected (5.780 sec)
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (6.230 sec)
Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0

With streaming replication and statement unit

MariaDB [employees]> set session wsrep_trx_fragment_unit='statements';SET SESSION wsrep_trx_fragment_size=3;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (6.717 sec)
Records: 443308  Deleted: 0  Skipped: 0  Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='statements';SET SESSION wsrep_trx_fragment_size=5;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (6.476 sec)
Records: 443308  Deleted: 0  Skipped: 0  Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='statements';SET SESSION wsrep_trx_fragment_size=10;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (6.473 sec)
MariaDB [employees]> set session wsrep_trx_fragment_unit='statements';SET SESSION wsrep_trx_fragment_size=100;
MariaDB [employees]> delete from titles;
Query OK, 443308 rows affected (6.638 sec)
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (6.392 sec)
Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0

We can see here that the time to delete all rows and to reimport the table are almost the same. Galera did not use, whatever the fragment size, the streaming replication.
Another way to check and to be sure of it, is to  query the system table mysql.wsrep_streaming_log.
If it is always empty, the streaming replication is never used.

MariaDB [mysql]> select count(*) from mysql.wsrep_streaming_log;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set (0.000 sec)

With streaming replication and rows then bytes unit

MariaDB [employees]> set session wsrep_trx_fragment_unit='rows';SET SESSION wsrep_trx_fragment_size=3;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (6 min 7.889 sec)
Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='rows';SET SESSION wsrep_trx_fragment_size=5;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (3 min 19.940 sec)
Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='rows';SET SESSION wsrep_trx_fragment_size=100;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (22.546 sec)
Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='rows';SET SESSION wsrep_trx_fragment_size=200;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (15.449 sec)
Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='rows';SET SESSION wsrep_trx_fragment_size=2000;
MariaDB [employees]> delete from titles;
Query OK, 443308 rows affected (8.405 sec)
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (7.443 sec)
Records: 443308  Deleted: 0  Skipped: 0  Warnings: 0
MariaDB [employees]> set session wsrep_trx_fragment_unit='bytes';SET SESSION wsrep_trx_fragment_size=10000;
MariaDB [employees]> delete from titles;
Query OK, 443308 rows affected (10.722 sec)
MariaDB [employees]> set session wsrep_trx_fragment_unit='bytes';SET SESSION wsrep_trx_fragment_size=20000;
MariaDB [employees]> load data infile '/tmp/titles.txt' into table titles fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 443308 rows affected (8.899 sec)
Records: 443308  Deleted: 0  Skipped: 0  Warnings: 0

We can see for both units, streaming replication is always used. The more fragments you have, the faster it will be to delete and reload the table.

Conclusion:

To use the streaming replication, you first identify large and long running transactions. Execute then the same transaction without activating the streaming replication as a reference. Set the streaming replication at the session level. Perfom tests against this transaction using different units and fragment size. Check if the streaming replication is running by querying the system table mysql.wsrep_streaming_log.
When the execution time
is closed to the referenced one then use these values.
One last thing, do not forget to disable the streaming replication from your current session. Reset to 0 the wsrep_trx_fragment_size, otherwise following transactions could suffer from these settings.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Saïd Mendi
Saïd Mendi

Consultant