Galera 4 in MariaDB 10.4 And a little bit in MySQL Seppo Jaakola - - PowerPoint PPT Presentation

▶
galera 4 in mariadb 10 4
SMART_READER_LITE
LIVE PREVIEW

Galera 4 in MariaDB 10.4 And a little bit in MySQL Seppo Jaakola - - PowerPoint PPT Presentation

Mar 08, 2024 258 likes •1.02k views

Galera 4 in MariaDB 10.4 And a little bit in MySQL Seppo Jaakola Codership 1 CEO Codership Developer role, 15 yrs with MySQL engineering Background: DBMS Engineering Data Security Seppo Jaakola www.galeracluster.com C o d e r s h i p

slide-1
SLIDE 1

1

in MariaDB 10.4

And a little bit in MySQL

Seppo Jaakola Codership

Galera 4

slide-2
SLIDE 2

www.galeracluster.com

Seppo Jaakola CEO Codership Developer role, 15 yrs with MySQL engineering Background:

DBMS Engineering Data Security

slide-3
SLIDE 3

C o d e r s h i p

Technology Company Since 2007 Replication for Open Source Databases Galera Cluster Business through MySQL / MariaDB Support & Consulting Technology Company, focus in R&D Since 2007 Replication for Open Source Databases Galera Cluster Business through MySQL / MariaDB Support & Consulting Stable continuous growth, team growing 3 → ~20

C o d e r s h i p

slide-4
SLIDE 4

4 www.galeracluster.com

Agenda

  • Galera Cluster Overview
  • Galera 4 in 10.4 Features
  • Upgrading 10.3 Cluster to 10.4
  • Galera 4 Streaming Replication
  • Additional Galera 4 Feature Road Map
slide-5
SLIDE 5

www.galeracluster.com

galera

  • Generic Replication Plugin for

database servers

  • Uses Replication API to interact with

DBMS (wsrep API project in github)

  • DBMS and Galera plugin must have same

wsrep API version

Galera Replication

slide-6
SLIDE 6

6 www.galeracluster.com

Galera Replication Versions

  • Major versions 1..2..3
  • Current production head version 3.26
  • wsrep API versions 1..25
  • Next major version is Galera 4 and using wsrep

API #26

  • wsrep API change requires rolling upgrade path
slide-7
SLIDE 7

7

Galera in MariaDB 10.4

slide-8
SLIDE 8

8 www.galeracluster.com

Galera 4 in MariaDB 10.4

  • MariaDB 10.4 RC has Galera 4 Replication
  • wsrep API #26
  • Impacts upgrading
  • wsrep patch integration in 10.4 codebase
  • Impacts code stability
  • bug fix & new features turnover
slide-9
SLIDE 9

9 www.galeracluster.com

Galera 4 in MariaDB 10.4

  • Streaming Replication
  • Support for large transactions
  • Platform for other new features
  • Group commit support
  • 10.4 Backup locks
  • mariabackup SST with “light weight lock”
slide-10
SLIDE 10

10

Streaming Replication

slide-11
SLIDE 11

11 www.galeracluster.com

Streaming Replication

  • Originally developed for supporting huge

transactions

  • In Galera 3, transaction processes in master node

until commit time

  • For large transactions, the write size will be big,

and is hard to handle

  • There are means to prevent too large transactions
  • wsrep_max_ws_size
slide-12
SLIDE 12

12 www.galeracluster.com

Streaming Replication

  • Streaming replication is new technology

developed for Galera Replication 4 to enable running transaction of unlimited size in cluster

  • Transaction size limits will remain, and cluster

can still reject too large transactions

slide-13
SLIDE 13

13 www.galeracluster.com

Streaming Replication

  • Transaction is replicated, gradually in small

fragments, during transaction processing

  • i.e. before actual commit, we replicate a number of small size

fragments

  • Size threshold for fragment replication is configurable
  • Replicated fragments are applied in slave threads

preserving transaction’s state in all cluster nodes

  • Fragments hold locks in all nodes and cannot be conflicted later
slide-14
SLIDE 14

14 www.galeracluster.com

Streaming Replication

Huge transaction

Galera Replication

Node A Node B

Update, update, update.... Begin

Trx

slide-15
SLIDE 15

15 www.galeracluster.com

Streaming Replication

Huge transaction

Galera Replication

Node A Node B

WS

Update, update, update....

Trx SR Trx

slide-16
SLIDE 16

16 www.galeracluster.com

Streaming Replication

Huge transaction

Galera Replication

Node A Node B

Update, update, update....

WS Trx SR Trx

slide-17
SLIDE 17

17 www.galeracluster.com

Streaming Replication

Huge transaction

Galera Replication

Node A Node B commit

WS

C

Trx SR Trx

slide-18
SLIDE 18

18 www.galeracluster.com

Streaming Replication

Huge transaction

Galera Replication

Node A Node B OK

slide-19
SLIDE 19

19 www.galeracluster.com

Fragment Applying

SR transaction pool

SR#1 THD

WS SR trx :2 CF: 0

applier applier

certification

applier

apply

SR#2 THD SR#n THD

slide-20
SLIDE 20

20 www.galeracluster.com

Fragment Applying

SR transaction pool

SR#1 THD SR#2 THD

WS SR trx :2 CF: 0

applier applier

certification

applier

SR#n THD

apply Pull THD

slide-21
SLIDE 21

21 www.galeracluster.com

applier

Fragment Applying

SR transaction pool

SR#2 THD WS SR trx :2 CF: 0

ev→apply_event() … ev->apply_event()

wsrep_SR_store->append_frag_apply())

SR#1 THD SR#n THD

applier applier

WS SR trx :2 CF: 0

apply

slide-22
SLIDE 22

22 www.galeracluster.com

Fragment Applying

SR transaction pool

SR#1 THD SR#2 THD

WS SR trx :2 CF: 0

applier applier applier

SR#n THD

OK Push THD

slide-23
SLIDE 23

23 www.galeracluster.com

Fragment Committing

SR transaction pool

SR#1 THD SR#2 THD

WS SR trx :2 CF: 1

applier applier

certification

applier

SR#n THD

commit Pull THD

slide-24
SLIDE 24

24 www.galeracluster.com

applier

Fragment Committing

SR#2 THD WS SR trx :2 CF: 1

trans_commit()

wsrep_SR_store->append_frag_commit())

SR transaction pool

SR#1 THD SR#n THD

applier applier

WS SR trx :2 CF: 1

commit

slide-25
SLIDE 25

25 www.galeracluster.com

Fragment Committing

SR transaction pool

SR#1 THD

applier applier applier

SR#1nTHD

WS SR trx :2 CF: 1

OK

slide-26
SLIDE 26

26 www.galeracluster.com

Configuring Streaming Replication

wsrep_trx_fragment_unit Unit metrics for fragmenting, options are:

  • bytes WS size in bytes
  • rows # of rows modified
  • statements # of SQL statements issued

wsrep_trx_fragment_size

  • Threshold size (in units), when fragment will be

replicated

  • 0 = no streaming

Session variables and can be dynamically set

slide-27
SLIDE 27

27 www.galeracluster.com

Using Streaming Replication

  • Due to excessive logging and elevated

replication overhead, streaming replication will cause degraded transaction throughput rate

  • Best use case is to use streaming replication for

cutting large transactions

  • Set fragment size to ~10K rows
  • Fragment variables are session variables and

can be dynamically set

  • Intelligent application can set streaming

replication on/off on need basis

slide-28
SLIDE 28

28

New Meta Data

slide-29
SLIDE 29

29 www.galeracluster.com

wsrep Tables in mysql database

MariaDB [(none)]> show tables in mysql like 'wsrep%'; +--------------------------+ | Tables_in_mysql (wsrep%) | +--------------------------+ | wsrep_cluster | | wsrep_cluster_members | | wsrep_streaming_log | +--------------------------+ 3 rows in set (0.005 sec)

slide-30
SLIDE 30

30 www.galeracluster.com

wsrep Tables in mysql database

MariaDB [(none)]> select * from mysql.wsrep_cluster\G *************************** 1. row *************************** cluster_uuid: 0be6f4d6-35da-11e9-b8a0-d6501fb08579 view_id: 2 view_seqno: 2 protocol_version: 4 capabilities: 184703 1 row in set (0.005 sec)

slide-31
SLIDE 31

31 www.galeracluster.com

wsrep Tables in mysql database

MariaDB [(none)]> select * from mysql.wsrep_cluster_members\G *************************** 1. row *************************** node_uuid: ea306990-35b0-11e9-9841-366f30fba24f cluster_uuid: ea317655-35b0-11e9-9ac4-9f27c3510851 node_name: labrador node_incoming_address: 127.0.0.1:16000 *************************** 2. row *************************** node_uuid: eb29a68c-35b0-11e9-ac23-e2418aae257d cluster_uuid: ea317655-35b0-11e9-9ac4-9f27c3510851 node_name: poodle node_incoming_address: 127.0.0.1:16001 *************************** 3. row *************************** node_uuid: eb29e56c-35b0-11e9-a328-fe229f8ae4cb cluster_uuid: ea317655-35b0-11e9-9ac4-9f27c3510851 node_name: rotweiler node_incoming_address: 127.0.0.1:16002 3 rows in set (0.006 sec)

slide-32
SLIDE 32

32 www.galeracluster.com

wsrep Tables in mysql database

MariaDB [(none)]> show create table mysql.wsrep_streaming_log\G *************************** 1. row *************************** Table: wsrep_streaming_log Create Table: CREATE TABLE `wsrep_streaming_log` ( `node_uuid` char(36) NOT NULL, `trx_id` bigint(20) NOT NULL, `seqno` bigint(20) NOT NULL, `flags` int(11) NOT NULL, `frag` longblob NOT NULL, PRIMARY KEY (`node_uuid`,`trx_id`,`seqno`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.004 sec)

slide-33
SLIDE 33

33

Rolling Upgrade

slide-34
SLIDE 34

34 www.galeracluster.com

Galera 3

Galera Rolling Upgrades

Galera Replication

read & write

MariaDB 10.3

Galera 3

read & write

MariaDB 10.3

Galera 3

MariaDB 10.3

Wsrep-API 25

read & write

slide-35
SLIDE 35

35 www.galeracluster.com

Galera 3

Galera Rolling Upgrades

Galera Replication

read & write

MariaDB 10.3

Galera 3

read & write

MariaDB 10.3

Galera 3

MariaDB 10.3

Wsrep-API 25 Upgrade for 10.4 Shutdown server Install 10.4 Start with wsrep_provider mysql_upgrade Shutdown server Start without wsrep_provider Isolate node

Galera 4

MariaDB 10.4

Allow read only access

read only

slide-36
SLIDE 36

36 www.galeracluster.com

Galera 3

Galera Rolling Upgrades

Galera Replication

read & write

MariaDB 10.3

Galera 3

MariaDB 10.3

Wsrep-API 25

Galera 4

MariaDB 10.4

read only

Galera 4

MariaDB 10.4

read only

Upgrade for 10.4 Upgrade node 2

slide-37
SLIDE 37

37 www.galeracluster.com

Galera Rolling Upgrades

Galera Replication

Wsrep-API 25

Galera 4

MariaDB 10.4

read only

Galera 4

MariaDB 10.4

read only

Wsrep-API 26 Upgrade for 10.4 Shutdown node 1 Allow read-write Complete node1 upgrade

wsrep API #26 features now Enabled in replication

slide-38
SLIDE 38

38 www.galeracluster.com

Galera Rolling Upgrades

Galera Replication

read & write

Wsrep-API 25

Galera 4

MariaDB 10.4

Galera 4

MariaDB 10.4

Wsrep-API 26

read & write read & write

Galera 4

MariaDB 10.4

slide-39
SLIDE 39

39 www.galeracluster.com

Galera Rolling Upgrades

Galera Replication

read & write

Wsrep-API 25

Galera 4

MariaDB 10.4

Galera 4

MariaDB 10.4

Wsrep-API 26

read & write read & write

Galera 4

MariaDB 10.4

j

  • i

n

Galera 3

MariaDB 10.3

slide-40
SLIDE 40

40

Galera 4 Road Map

slide-41
SLIDE 41

41 www.galeracluster.com

4.0 Release Status

  • Part of Galera 4 feature set was merged in

MariaDB 10.4 beta 2 release and is now in the 10.4 RC and later releases

  • Schedule of remaining Galera 4 features is not

yet confirmed

slide-42
SLIDE 42

42 www.galeracluster.com

Features Missed in MariaDB 10.4

  • Gcache encryption
  • Data at rest encryption for full replication pipeline
  • XA transaction support
  • Will be needed in sharding cluster
  • Non blocking DDL
  • Co-incides with other MariaDB non-locking DDL work
  • Cluster error voting
  • Galera library feature, may be in later 10.4.* releases
slide-43
SLIDE 43

43 www.galeracluster.com

R A M

Data at rest encryption

Client thread Client thread Client thread Buffer pool gcache ibdata ibdata redo log redo log redo log binlog D I S K

slide-44
SLIDE 44

44 www.galeracluster.com

R A M

Data at rest encryption

Client thread Client thread Client thread Buffer pool gcache D I S K ibdata ibdata redo log redo log redo log binlog

slide-45
SLIDE 45

45 www.galeracluster.com

R A M

Data at rest encryption

Client thread Client thread Client thread Buffer pool gcache D I S K ibdata ibdata redo log redo log redo log binlog

slide-46
SLIDE 46

46 www.galeracluster.com

Features Missed in MariaDB 10.4

  • Gcache encryption
  • Data at rest encryption for full replication pipeline
  • XA transaction support
  • Will be needed in sharding cluster
  • Non blocking DDL
  • Co-incides with other MariaDB non-locking DDL work
  • Cluster error voting
  • Galera library feature, may be in later 10.4.* releases
slide-47
SLIDE 47

47

XA Transactions In Galera 3

slide-48
SLIDE 48

48 www.galeracluster.com

XA Transactions with Galera 3

TM RM RM

XA start ‘foo’ XA start ‘bar’ foo bar Work on ‘foo’ Work on ‘bar’ XA prepare ‘foo’ XA prepare ‘bar’ XA commit ‘foo’ XA commit ‘bar’ foo bar

MariaDB

slide-49
SLIDE 49

49 www.galeracluster.com

XA Transactions with Galera 3

  • Galera replication is eager to replicate in

MariaDB 2PC prepare stage

  • Prepared XA transaction cannot be rolled back

anymore

slide-50
SLIDE 50

50 www.galeracluster.com

XA Transactions with Galera 3

XA Start

Node A Node B

smith smith XA trans

slide-51
SLIDE 51

51 www.galeracluster.com

XA Transactions with Galera 3

XA Insert into persons ‘’jones’

Node A Node B

smith smith

jones XA trans

slide-52
SLIDE 52

52 www.galeracluster.com

XA Transactions with Galera 3

XA Prepare

Node A Node B

smith smith

jones XA trans

slide-53
SLIDE 53

53 www.galeracluster.com

XA Transactions with Galera 3

XA Prepare

Node A Node B

smith smith

jones XA trans

WS

jones

slide-54
SLIDE 54

54 www.galeracluster.com

XA Transactions with Galera 3

XA Prepare

Node A Node B

smith smith jones

jones XA trans apply

slide-55
SLIDE 55

55 www.galeracluster.com

XA Transactions with Galera 3

OK

Node A Node B

smith smith jones

jones XA trans

slide-56
SLIDE 56

56 www.galeracluster.com

XA Transactions with Galera 3

XA Rollback

Node A Node B

smith smith jones jones XA trans

slide-57
SLIDE 57

57 www.galeracluster.com

XA Transactions with Galera 3

Node A Node B

smith smith jones

inconsistency

slide-58
SLIDE 58

58 www.galeracluster.com

XA prepare Safety

  • XA prepare should guarantee that transaction

can commit

  • However, galera multi-master conflict resolving

does honor XA transactions

slide-59
SLIDE 59

59 www.galeracluster.com

XA Transactions with Galera 3

XA Start

Node A Node B

smith smith XA trans

slide-60
SLIDE 60

60 www.galeracluster.com

XA Transactions with Galera 3

UPDATE smith=kit

Node A Node B

smith smith XA trans kit

slide-61
SLIDE 61

61 www.galeracluster.com

XA Transactions with Galera 3

Node A Node B

smith smith XA trans

WS

kit

UPDATE smith=hannibal

WS

hannibal

XA Prepare

kit

slide-62
SLIDE 62

62 www.galeracluster.com

XA Transactions with Galera 3

Node A Node B

hannibal hannibal XA trans

OK

slide-63
SLIDE 63

63 www.galeracluster.com

XA Transactions with Galera 3

Node A Node B

hannibal hannibal

XA Commit

slide-64
SLIDE 64

64

XA by Streaming Replication

slide-65
SLIDE 65

65 www.galeracluster.com

XA Transaction Support

XA Start

Node A Node B

smith smith XA trans

slide-66
SLIDE 66

66 www.galeracluster.com

XA Transaction Support

XA Insert into persons ‘’jones’

Node A Node B

smith smith XA trans XA trans XA trans XA trans jones

slide-67
SLIDE 67

67 www.galeracluster.com

XA Transaction Support

XA Prepare

Node A Node B

smith smith XA trans SR trans

WS

jones jones

slide-68
SLIDE 68

68 www.galeracluster.com

XA Transaction Support

XA Rollback Node A Node B

smith smith XA trans SR trans

WS rollback

jones jones

slide-69
SLIDE 69

69 www.galeracluster.com

XA Transaction Support

Node A Node B

smith smith

slide-70
SLIDE 70

70 www.galeracluster.com

XA Transaction Conflict Resolution

Node A Node B

smith smith XA trans

UPDATE smith=hannibal XA Prepare

kit SR trans kit

slide-71
SLIDE 71

71 www.galeracluster.com

Features Missed in MariaDB 10.4

  • Gcache encryption
  • Data at rest encryption for full replication pipeline
  • XA transaction support
  • Will be needed in sharding cluster
  • Non blocking DDL
  • Co-incides with other MariaDB non-locking DDL work
  • Cluster error voting
  • Galera library feature, may be in later 10.4.* releases
slide-72
SLIDE 72

72 www.galeracluster.com

MariaDB 10.5

  • Galera work for 10.5 is ongoing
  • Feature scoping is not complete, may come

with Galera 4 or new Galera 5

slide-73
SLIDE 73

73 www.galeracluster.com

Galera 4 on MySQL

  • Galera 4 is integrated in MySQL 5.6 and 5.7

and 8.0 versions

  • Public releases after MariaDB 10.4 GA is

released

slide-74
SLIDE 74

74 www.galeracluster.com

Summary

  • MariaDB 10.4.5 has significant new

Galera 4 features

  • Streaming Replication implements new replication

protocol opening possibilities for many unforeseen replication features

  • Other new Galera 4 related features to come with

later 10.4 and 10.5 releases

  • MySQL version is coming
  • Rolling cluster upgrade supported and requires care
slide-75
SLIDE 75

75

4

Happy Clustering :-) Thank you for listening!