Upgrade to Pro — share decks privately, control downloads, hide ads and more …

8 Group Replication features that will make you smile

56bfeb78c1b08840a258eca23fc6c326?s=47 Tiago Vale
February 01, 2019

8 Group Replication features that will make you smile

Eight MySQL 8 features that will make the life of a DevOps or DBA easier.

56bfeb78c1b08840a258eca23fc6c326?s=128

Tiago Vale

February 01, 2019
Tweet

More Decks by Tiago Vale

Other Decks in Technology

Transcript

  1. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 8 Group Replication features that will make you smile Tiago Vale Software Engineer MySQL Replication February 1st, 2019
  2. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  3. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Program Agenda Introduction Features: Observability Features: Troubleshooting Features: Operations Features: Consistency 1 2 3 4 5 3
  4. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Introduction 4
  5. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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.
  6. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  7. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Observability: Consensus message cache Feature #1 of 8 7 8.0.12
  8. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consensus message cache 8 Server GR GCS
  9. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consensus message cache mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE event_name LIKE "GCS_XCom::xcom_cache"\G *********************** 1. row *********************** EVENT_NAME: memory/group_rpl/GCS_XCom::xcom_cache COUNT_ALLOC: 68659 COUNT_FREE: 18660 SUM_NUMBER_OF_BYTES_ALLOC: 39798266 SUM_NUMBER_OF_BYTES_FREE: 10809354 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 49999 HIGH_COUNT_USED: 50000 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 28988912 HIGH_NUMBER_OF_BYTES_USED: 28989568 1 row in set (0,02 sec) 9 Server GR GCS
  10. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Troubleshooting: GCS trace file Feature #2 of 8 10 8.0.11
  11. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  12. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  13. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Operations: Change the primary member Feature #3 of 8 13 8.0.13
  14. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the primary member 14 Primary Secondary A B C
  15. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the primary member 15 Primary Secondary A B C A B C I want A to be the new primary
  16. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the primary member 16 Primary Secondary A B C A B C A B C I want A to be the new primary A is the new primary
  17. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  18. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Operations: Change the group mode Feature #4 of 8 18 8.0.13
  19. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the group mode 19 Primary Secondary
  20. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the group mode 20 Primary Secondary I want multi-primary mode
  21. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the group mode 21 Primary Secondary I want multi-primary mode Multi-primary it is!
  22. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Change the group mode 22 Primary Secondary I want multi-primary mode Multi-primary it is! group_replication_switch_to_multi_primary_mode()
  23. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  24. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Operations: Fine-tune member expulsion timeouts Feature #5 of 8 24 8.0.13
  25. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Fine-tune member expulsion timeouts 25 Hmm, X seems to have failed. X is UNREACHABLE
  26. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Fine-tune member expulsion timeouts 26 Hmm, X seems to have failed. X is UNREACHABLE Let us wait for X a bit more to give it a chance.
  27. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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!
  28. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  29. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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…
  30. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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!
  31. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency: Abort server when it drops out of the group Feature #6 of 8 31 8.0.12
  32. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Abort server when it drops out of the group 32 Primary Secondary
  33. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Abort server when it drops out of the group 33 Leaves the group involuntarily (failed to apply changes, network partitioned, etc). Primary Secondary
  34. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  35. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  36. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Abort server when it drops out of the group 36 Use case example: problem Unaware server is out, keeps on reading stale data. Primary Secondary
  37. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Abort server when it drops out of the group 37 Use case example: solution Sees server down, fails over to another. Primary Secondary
  38. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency: Consistency on primary fail over Feature #7 of 8 38 8.0.14
  39. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency on primary fail over 39 Primary Secondary Secondaries have a backlog of transactions to apply.
  40. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency on primary fail over 40 Primary Secondary Secondaries have a backlog of transactions to apply. Primary fails and group elects new primary.
  41. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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.
  42. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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.
  43. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  44. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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 .
  45. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency on primary fail over 45 Primary Secondary Use case example: solution Write . Yep, there’s my ! Read from the primary after having written .
  46. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency: Consistent reads (enforced on read) Feature #8.1 of 8 46 8.0.14
  47. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on read) 47 Primary Secondary Secondary holds incoming operation...
  48. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on read) 48 Primary Secondary Secondary holds incoming operation... ...until it applies .
  49. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on read) 49 Primary Secondary Secondary holds incoming operation... ...until it applies . Executes the held operation, and replies.
  50. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  51. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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 .
  52. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on read) 52 Primary Secondary Use case example: solution Write . Yep, there’s my ! Read from a secondary after having written .
  53. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistency: Consistent reads (enforced on write) Feature #8.2 of 8 53 8.0.14
  54. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on write) 54 Primary Secondary Write .
  55. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on write) 55 Primary Secondary Write . Primary holds reply until secondaries apply .
  56. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Consistent reads (enforced on write) 56 Primary Secondary Write . Primary holds reply until secondaries apply . Replies. All subsequent operations will see .
  57. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  58. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  59. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  62. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Thank you for your time 62
  63. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | 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
  64. None