Geographically Dispersed Percona XtraDB Cluster Deployment Marco - - PowerPoint PPT Presentation

Geographically Dispersed Percona XtraDB Cluster Deployment

Marco (the Grinch) Tusa September 2017 Dublin

2

Marco “The Grinch”

• Open source enthusiast
• Percona consulting Team Leader

About me

3

Agenda

• What is PXC
• When nodes interacts
• Let us clarify, geo dispersed- What to keep in mind then
• How to measure latency correctly
• Use the right way (sync/async)
• Use help like replication manager

4

What is PXC/Galera?

(Virtually) Synchronous Replication:

• True multi-master
• No slave lag
• No master-slave failover or VIP
• Multi-threaded applayers
• Automatic node provisioning
• Elastic scale (in – out)
• Geographic distributed (with segments)
• Mix with Async replication

Galera Balancer Web traffic

5

What PXC/Galera is NOT?

Not Write-scalable solution Not great for a high amount of parallel, small requests Not great for working with Foreign Keys Not good for sharding Data (each node has the entire dataset)

Galera Balancer Web traffic

6

What is a Node

Standard MySQL Replication

Master Slave Slave

• Galera MySQL Replication

Node Node Node

9cba28fa-a8be-11e4-8f41-9f963e1dbf4f

7

Segments

A segment is a logical grouping of nodes. Replication between Segment is

• ptimized (writeset - some level of

communication)

Traffic and messaging is reduced In case of SST, the donor is chosen by proximity

8

More nodes more problems

Use a two phase commit, or distributed locking with capacity formula: m = n x o x t (where messages/sec = number of nodes due to process o number of

• peration with t transaction

throughput)

9

When nodes interacts

• Keepalive and checks for cluster health
• Writeset on writer commit
• Certification results
• Ack on local apply
• Flow Control
• IST/SST

10

Let us clarify, geo dispersed 1

Geo dispersed

• r multi-site, cluster is a cluster configuration used to help ensure high

system and application availability in the event of site disaster. In this configuration, servers are separated geographically and the physical storage (quorum diskor) DATA is synchronously replicated between sites.


(http://www.expertglossary.com/storage/definition/geo-dispersed-cluster)

11

Let us clarify, geo dispersed 2

For some environments, latency is the sole focus of performance. As an example of latency, shows a network transfer, such as an HTTP GET request, with the time split into latency and data transfer components.

12

Geo dispersed

Geo dispersed is determinate by the latency existing between nodes NOT by the geographic location itself.

13

How to measure latency correctly 1

• wsrep_evs_repl_latency 


(It measures latency from the time point when a message is sent out to the time point when a message is received.)

• wsrep_replicated/wsrep_replicated
• netperf

14

How to measure latency correctly 2

14

How to measure latency correctly 2

PING

14

How to measure latency correctly 2

PING

14

How to measure latency correctly 2

Why?

15

Brief digression

Ref: https://goo.gl/kDTYnW

15

Brief digression

Ref: https://goo.gl/kDTYnW

15

Brief digression

Ref: https://goo.gl/kDTYnW

16

Brief digression

• PING use ICMP (Internet Control Message Protocol) NOT TCP over IP
• Default data size is 56 bytes plus header (8 bytes)

16

Brief digression

• PING use ICMP (Internet Control Message Protocol) NOT TCP over IP
• Default data size is 56 bytes plus header (8 bytes)

16

Brief digression

• PING use ICMP (Internet Control Message Protocol) NOT TCP over IP
• Default data size is 56 bytes plus header (8 bytes)

ping -M do -s 1473 -c 3 192.168.0.34

16

Brief digression

• PING use ICMP (Internet Control Message Protocol) NOT TCP over IP
• Default data size is 56 bytes plus header (8 bytes)

ping -M do -s 1473 -c 3 192.168.0.34

16

Brief digression

• PING use ICMP (Internet Control Message Protocol) NOT TCP over IP
• Default data size is 56 bytes plus header (8 bytes)

Not good enough!

17

Brief digression

17

Brief digression

TCP means Transmission Control Protocol and as the name says, it is design to control the data transmission happening between source and destination. TCP implementations use the IP protocol encapsulation for the transmission of the data:

17

Brief digression

TCP means Transmission Control Protocol and as the name says, it is design to control the data transmission happening between source and destination. TCP implementations use the IP protocol encapsulation for the transmission of the data:

17

Brief digression

TCP means Transmission Control Protocol and as the name says, it is design to control the data transmission happening between source and destination. TCP implementations use the IP protocol encapsulation for the transmission of the data:

Looks the same thing than before right?

17

Brief digression

TCP means Transmission Control Protocol and as the name says, it is design to control the data transmission happening between source and destination. TCP implementations use the IP protocol encapsulation for the transmission of the data:

Looks the same thing than before right?

WRONG!

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize:

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented Establish a connection

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented Establish a connection Monitor the data transfer

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented Establish a connection Monitor the data transfer Buffered transmission

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented Establish a connection Monitor the data transfer Buffered transmission Unstructured stream

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented Establish a connection Monitor the data transfer Buffered transmission Unstructured stream Full-duplex connection

18

Brief digression

A TCP implementation has several characteristics that make sense to summarize: Is stream oriented Establish a connection Monitor the data transfer Buffered transmission Unstructured stream Full-duplex connection Stream as a sequence of octet split in segments

19

Brief digression

TCP dispatcher use Dynamic Slide Window

19

Brief digression

TCP dispatcher use Dynamic Slide Window

19

Brief digression

TCP dispatcher use Dynamic Slide Window Dispatcher manages three pointers associated to each connection: The first pointer indicate the start of the sliding window The second pointer indicates the higher octet that can be dispatchtet. The third pointer indicates the window limit

20

How to measure latency correctly 3

Back to us

20

How to measure latency correctly 3

Back to us

• Check for gateway MTU cut
• ping -M do -s 1473 -c 3 192.168.0.34

20

How to measure latency correctly 3

Back to us

• Check for gateway MTU cut
• ping -M do -s 1473 -c 3 192.168.0.34
• Consider sent and received messages (IE Wsrep replicated bytes & Wsrep received

bytes)

20

How to measure latency correctly 3

Back to us

• Check for gateway MTU cut
• ping -M do -s 1473 -c 3 192.168.0.34
• Consider sent and received messages (IE Wsrep replicated bytes & Wsrep received

bytes)

• Check Kernel settings for:
• Buffering
• Congestion control
• Frame utilization

20

How to measure latency correctly 3

Back to us

• Check for gateway MTU cut
• ping -M do -s 1473 -c 3 192.168.0.34
• Consider sent and received messages (IE Wsrep replicated bytes & Wsrep received

bytes)

• Check Kernel settings for:
• Buffering
• Congestion control
• Frame utilization
• Test with netperf (IE)
• netperf -H 192.168.1.51 -t TCP_RR -v 2 -l 60 -- -b 2 -r 250K -R 1M -s 250K,10M -S 10K,256K

20

How to measure latency correctly 3

Back to us

• Check for gateway MTU cut
• ping -M do -s 1473 -c 3 192.168.0.34
• Consider sent and received messages (IE Wsrep replicated bytes & Wsrep received

bytes)

• Check Kernel settings for:
• Buffering
• Congestion control
• Frame utilization
• Test with netperf (IE)
• netperf -H 192.168.1.51 -t TCP_RR -v 2 -l 60 -- -b 2 -r 250K -R 1M -s 250K,10M -S 10K,256K
• Check the wsrep_evs_repl_latency value in SHOW GLOBAL STATUS like ‘wsrep%’;

21

What is the right limit? Depends by the usage Balance incoming write/s consistence reads

22

Last chance for (virtually) Synchronous

Wan settings:

22

Last chance for (virtually) Synchronous

Wan settings:

evs.inactive_check_period = PT30S; evs.inactive_timeout = PT1M; evs.suspect_timeout = PT40S; evs.stats_report_period = PT3M; evs.join_retrans_period=PT0.5S don’t use PING

22

Last chance for (virtually) Synchronous

Wan settings:

evs.inactive_check_period = PT30S; evs.inactive_timeout = PT1M; evs.suspect_timeout = PT40S; evs.stats_report_period = PT3M; evs.join_retrans_period=PT0.5S don’t use PING

Master_Slave not a very good option though

wsrep_provider_options = "gcs.fc_limit = 256; gcs.fc_factor = 0.99; gcs.fc_master_slave = YES"

23

Async replication kicks in

We can use almost the same models we used

23

Async replication kicks in

We can use almost the same models we used Challenge is to shift from one Master-Node to a new one Or from a slave to another

24

Async replication ways

Standard binlog position using XID and wsrep_last_committed

+----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | wsrep_last_committed | 3282552 | +----------------------+---------+

Binlog

# at 544 #170920 19:26:56 server id 3306 end_log_pos 575 CRC32 0x3ae1edcd Xid = 3282552

Simple to install/setup Nightmare to maintain

25

Async replication ways

Using GTID All nodes on a cluster will have the same GTID Move from a slave from one node to another can be automated. Existing: Yves Trudeau solution : https://github.com/y-trudeau/Mysql-tools/tree/master/PXC Single link DC1->DC2 Multiple Link DC1 -> DC2 ->DC3

26

Conclusions

• Plan carefully you network and DC-DC connectivity
• Keep the number of nodes inside a PXC cluster to minimum
• Test properly (not ping) the latency on the network
• Use PXC/Galera replication between geo-distributed only if it is safe
• Do not esitate to shift to Async replication
• Use existing solutions to help you manage async replication between

PXCs

27

28

Q&A

29

Contacts

To contact Me Marco.tusa@percona.com marcotusa@tusacentral.net To follow me http://www.tusacentral.net/ http://www.percona.com/blog/ https://www.facebook.com/marco.tusa.94 @marcotusa http://it.linkedin.com/in/marcotusa/

“Consulting = No mission refused!”