| Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2
| Database replication 5 INSERT ... INSERT ... INSERT ... Replication: “The process of generating and reproducing Multiple copies of data at one or more sites.” Database Systems: A Practical Approach to Design, Implementation, and Management, Thomas M. Connolly, Carolyn E. Begg, Third Edition, 2002.
| MySQL Group Replication (GR) 6 Highly-available infrastructure with ease Dynamically grow or shrink # of servers Automatic consistent view of membership Single-primary or multi-primary modes Automatic primary/secondary roles
| GCS trace file ::set_xcom_group_information():: Configuring XCom group: XCom Group Id=60819465 Name=ff056972-2bc1-418f-8f3a-afb31c69c522 ::get_xcom_group_information():: Configuring XCom group: XCom Group Id=60819465 Name=NULL Initializing peers ::initialize_peer_nodes():: Configured Peer Nodes: 127.0.0.1:42012 ::initialize_peer_nodes():: Configured Peer Nodes: 127.0.0.1:42022 ::initialize_peer_nodes():: Configured Peer Nodes: 127.0.0.1:42032 Configured total number of peers: 3 Configured Local member: 127.0.0.1:42012 Configured Bootstrap: true Configured waiting time(s): 30 Gcs_xcom_engine::initialize invoked! Configured number of attempts to join: 0 Configured time between attempts to join: 5 Set member expel timeout to 0 seconds (0 ns). ::configure_suspicions_mgr():: Set member expel timeout to 0 seconds wake_suspicions_processing_thread: Locked mutex! wake_suspicions_processing_thread: Signaled cond! Return= 0 wake_suspicions_processing_thread: Unlocked mutex! ::configure_msg_stages():: Set compression threshold to 1000000 Joining a group. Started executing during regular phase: 0x7f2a8014b900 11 data/dir/GCS_DEBUG_TRACE Server GR GCS
| GCS debug file ::set_xcom_group_information():: Configuring XCom group: XCom Group Id=60819465 Name=ff056972-2bc1-418f-8f3a-afb31c69c522 ::get_xcom_group_information():: Configuring XCom group: XCom Group Id=60819465 Name=NULL Initializing peers ::initialize_peer_nodes():: Configured Peer Nodes: 127.0.0.1:42012 ::initialize_peer_nodes():: Configured Peer Nodes: 127.0.0.1:42022 ::initialize_peer_nodes():: Configured Peer Nodes: 127.0.0.1:42032 Configured total number of peers: 3 Configured Local member: 127.0.0.1:42012 Configured Bootstrap: true Configured waiting time(s): 30 Gcs_xcom_engine::initialize invoked! Configured number of attempts to join: 0 Configured time between attempts to join: 5 Set member expel timeout to 0 seconds (0 ns). ::configure_suspicions_mgr():: Set member expel timeout to 0 seconds wake_suspicions_processing_thread: Locked mutex! wake_suspicions_processing_thread: Signaled cond! Return= 0 wake_suspicions_processing_thread: Unlocked mutex! ::configure_msg_stages():: Set compression threshold to 1000000 Joining a group. Started executing during regular phase: 0x7f2a8014b900 12 Server GR GCS data/dir/GCS_DEBUG_TRACE group_replication_communication_debug_options dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_communication_debug_options group_replication_communication_debug_options group_replication_communication_debug_options
| Change the primary member 17 Primary Secondary A B C A B C A B C I want A to be the new primary A is the new primary group_replication_set_as_primary(member_uuid) dev.mysql.com/doc/refman/8.0/en/group-replication-changing-primary-member.html
| Change the group mode 23 Primary Secondary I want multi-primary mode Multi-primary it is! group_replication_switch_to_multi_primary_mode() group_replication_switch_to_single_primary_mode() group_replication_switch_to_single_primary_mode(member_uuid) dev.mysql.com/doc/refman/8.0/en/group-replication-changing-group-mode.html
| Fine-tune member expulsion timeouts 27 Hmm, X seems to have failed. X is UNREACHABLE Let us wait for X a bit more to give it a chance. X is back ONLINE X is back. Good thing we waited!
| Fine-tune member expulsion timeouts 28 Hmm, X seems to have failed. X is UNREACHABLE Let us wait for X a bit more to give it a chance. X is back ONLINE X is back. Good thing we waited! group_replication_member_expel_timeout = seconds dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_member_expel_timeout
| Fine-tune member expulsion timeouts 29 E.g. • X is undergoing some maintenance task • X is physically far away from others (e.g. WAN) Use case example: problem X seems to have failed. Kick it out! But I was not gone…
| Fine-tune member expulsion timeouts 30 Let us wait for X a bit more to give it a chance. Use case example: solution E.g. • X is undergoing some maintenance task • X is physically far away from others (e.g. WAN) Yay, still part of the group!
| Abort server when it drops out of the group 34 Leaves the group involuntarily (failed to apply changes, network partitioned, etc). Shuts itself down, thus becoming effectively unavailable for both reads and writes. Primary Secondary
| Abort server when it drops out of the group 35 Leaves the group involuntarily (failed to apply changes, network partitioned, etc). Shuts itself down, thus becoming effectively unavailable for both reads and writes. Primary Secondary group_replication_exit_state_action = ABORT_SERVER dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_exit_state_action
| Consistency on primary fail over 40 Primary Secondary Secondaries have a backlog of transactions to apply. Primary fails and group elects new primary.
| Primary fails and group elects new primary. Consistency on primary fail over 41 Primary Secondary Secondaries have a backlog of transactions to apply. New primary holds incoming operation until it catches up.
| Primary fails and group elects new primary. Consistency on primary fail over 42 Primary Secondary Secondaries have a backlog of transactions to apply. New primary holds incoming operation until it catches up. Executes the held operation, and replies.
| Primary fails and group elects new primary. Consistency on primary fail over 43 Primary Secondary Secondaries have a backlog of transactions to apply. New primary holds incoming operation until it catches up. Executes the held operation, and replies. group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_consistency
| Consistency on primary fail over 44 Primary Secondary Use case example: problem Write . Didn’t I just write !? Read from the primary after having written .
| Consistency on primary fail over 45 Primary Secondary Use case example: solution Write . Yep, there’s my ! Read from the primary after having written .
| Consistent reads (enforced on read) 49 Primary Secondary Secondary holds incoming operation... ...until it applies . Executes the held operation, and replies.
| Consistent reads (enforced on read) 50 Primary Secondary Secondary holds incoming operation... ...until it applies . Executes the held operation, and replies. group_replication_consistency = BEFORE dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_consistency
| Consistent reads (enforced on read) 51 Primary Secondary Use case example: problem Write . Didn’t I just write !? Read from a secondary after having written .
| Consistent reads (enforced on read) 52 Primary Secondary Use case example: solution Write . Yep, there’s my ! Read from a secondary after having written .
| Consistent reads (enforced on write) 56 Primary Secondary Write . Primary holds reply until secondaries apply . Replies. All subsequent operations will see .
| Consistent reads (enforced on write) 57 Primary Secondary Write . Primary holds reply until secondaries apply . Replies. All subsequent operations will see . group_replication_consistency = AFTER dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_consistency
| Consistent reads trade offs • BEFORE puts burden on readers • Better when #writes > #reads • E.g. – Ensure that critical transaction observes fresh data on a secondary – Take an fresh snapshot from a secondary • AFTER puts burden on writers • Better when #reads > #writes • E.g. – Infrequent write pays for frequent reads to see fresh data – Load balance reads to fresh data 58
| Consistent reads trade offs • BEFORE puts burden on readers • Better when #writes > #reads • E.g. – Ensure that critical transaction observes fresh data on a secondary – Take an fresh snapshot from a secondary • AFTER puts burden on writers • Better when #reads > #writes • E.g. – Infrequent write pays for frequent reads to see fresh data – Load balance reads to fresh data 59 We also have BEFORE_AND_AFTER
| 60 Conclusion • MySQL GR: high availability and ease of use • New features: – Observe into GCS – Troubleshoot GCS – Change group configuration online – Fine-tune member expulsions – Freshness guarantees for reads Server GR GCS
| 61 Where to go from here? • Packages – mysql.com/downloads • Documentation – dev.mysql.com/doc/refman/8.0/en • Blogs from engineers (news, technical information, and more) – mysqlhighavailability.com
| Safe Harbor Statement The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 63
tvale
linyows
bbqallstars
ryder472
syntasso
miu_crescent
chanyou0311
stajima
oracle4engineer
salaboy
toridori_dev
sansantech
nobutomurata
trallard
chrislema
addyosmani
philnash
mclloyd
cassininazir
lara
reverentgeek
tammyeverts
lauravandoore
smashingmag
chriscoyier
