8 Group Replication features that will make you smile

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

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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

   View Slide

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.
   

   View Slide

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
   

   View Slide

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

   View Slide

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

   View Slide

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
   

   View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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!
    

    View Slide

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()
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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!
    

    View Slide

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
    

    View Slide

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…
    

    View Slide

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!
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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 .
    

    View Slide

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 .
    

    View Slide

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
    

    View Slide

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

    View Slide

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 .
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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 .
    

    View Slide

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 .
    

    View Slide

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
    

    View Slide

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

    View Slide

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 .
    

    View Slide

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 .
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

64. View Slide