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:
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.