Download - Plmce2k15 15 tips galera cluster
![Page 1: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/1.jpg)
15 Tips to improve your 15 Tips to improve your Galera Cluster Galera Cluster
ExperienceExperience
Frédéric Descamps14 April 2015
![Page 2: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/2.jpg)
4/14/15
Who am I ?
Frédéric Descamps “lefred”
@lefred
http://about.me/lefred
Percona Consultant since 2011
Managing MySQL since 3.23
devops believer
I installed my first galera cluster in feb 2010
![Page 3: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/3.jpg)
4/14/15
Who am I ?
Frédéric Descamps “lefred”
@lefred
http://about.me/lefred
Percona Consultant since 2011
Managing MySQL since 3.23
devops believer
I installed my first galera cluster in feb 2010
back in 2010 where I heard Seppo for thefirst time talking about Galera at FOSDEM
![Page 4: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/4.jpg)
11
![Page 5: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/5.jpg)
4/14/15
How to perform point in time recovery ?
Binary log must be enabled
logslaveupdates should be enabled
![Page 6: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/6.jpg)
4/14/15
writeswrites
writes
The environment
![Page 7: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/7.jpg)
4/14/15
Suddenly !
Oups ! Dim0 truncated a production table... :-S
We can have 2 scenarios :– The application can keep running even without that table
– The application musts be stopped !
![Page 8: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/8.jpg)
4/14/15
Scenario 1: application must be stopped !
We have Xtrabackup (and it creates daily backups!)
We have binary logs
These are the steps :– Stop the each node of the cluster
/etc/init.d/mysql stopor
service mysql stop
![Page 9: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/9.jpg)
4/14/15
Scenario 1: application must be stopped !
We have Xtrabackup (and it creates daily backups!)
We have binary logs
These are the steps :– Stop the each node of the cluster
– Find the binlog file and position before “the event” happened# mysqlbinlog binlog.000001 | grep truncate B 2
#140123 23:37:03 server id 1 end_log_pos 1224 Query thread_id=4 exec_time=0 error_code=0SET TIMESTAMP=1390516623/*!*/;truncate table speakers
![Page 10: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/10.jpg)
4/14/15
Scenario 1: application must be stopped !
We have Xtrabackup (and it creates daily backups!)
We have binary logs
These are the steps :– Stop the each node of the cluster
– Find the binlog file and position before “the event” happened
– Restore the backup on one node
# cp binlog.00001 ~# innobackupex applylog . etc..
![Page 11: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/11.jpg)
4/14/15
Scenario 1: application must be stopped !
We have Xtrabackup (and it creates daily backups!)
We have binary logs
These are the steps :– Stop the each node of the cluster
– Find the binlog file and position before “the event” happened
– Restore the backup on one node
– Restart that node (being sure the application doesn't connect to it)
# /etc/init.d/mysql bootstrappxc
![Page 12: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/12.jpg)
4/14/15
Scenario 1: application must be stopped ! (2)
– Replay all the binary logs since the backup BUT the position of the event
# cat xtrabackup_binlog_infoBinlog.000001 565# mysqlbinlog binlog.000001 | grep end_log_pos | \grep 1224 B 1#140123 23:36:53 server id 1 end_log_pos 1136 #140123 23:37:03 server id 1 end_log_pos 1224 # mysqlbinlog binlog.000001 j 565 \ stopposition 1136 | mysql
![Page 13: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/13.jpg)
4/14/15
Scenario 1: application must be stopped ! (2)
– Replay all the binary logs since the backup BUT the position of the event
– Start other nodes 1 by 1 and let them perform SST
– Enable connections from the application
![Page 14: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/14.jpg)
4/14/15
Scenario 2: application can keep running
We have Xtrabackup (and it creates daily backups!)
We have binary logs
These are the steps :– Take care of quorum (add garbd, change pc.weight,
pc.ignore_quorum)
– Find the binlog file and position before “the event” happened (thank you dim0!)
– Remove one node from the cluster (and be sure the app doesn't connect to it, load-balancer...)
![Page 15: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/15.jpg)
4/14/15
Scenario 2: application can keep running (2)
– Restore the backup on the node we stopped
– Start mysql without joining the cluster (--wsrep-cluster-address=dummy://)
– Replay the binary log until the position of “the event”
– Export the table we need (mysqldump)
– Import it on the cluster
– Restart mysql on the off-line node and let it perform SST
![Page 16: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/16.jpg)
22
![Page 17: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/17.jpg)
4/14/15
Reduce “donation” time during XtraBackup SST
When performing SST with Xtrabackup the donor can still be active
by default this is disabled in clustercheck (AVAILABLE_WHEN_DONOR=0)
Running Xtrabackup can increase the load (CPU / IO) on the server
![Page 18: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/18.jpg)
4/14/15
Reduce “donation” time during XtraBackup SST (2)
Using Xtrabackup 2.1 features helps to reduce the time of backup on the donor
[mysqld]
wsrep_sst_method=xtrabackupv2
wsrep_sst_auth=root:dim0DidItAgain
[sst]
streamfmt=xbstream
[xtrabackup]
compress
compact
parallel=8
compressthreads=8
rebuildthreads=8
compress & compact can reduce thesize of payload transferred among nodesbut in general it slows down the process
![Page 19: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/19.jpg)
33
![Page 20: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/20.jpg)
4/14/15
in 5.5
Move asynchronous slave to a new master
![Page 21: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/21.jpg)
4/14/15
Move asynchronous slave to a new master in 5.5
A C
writeswrites
writesB
asyn
c
binlog.000002102
binlog.000001402
binlog.000039102
master_host=Amaster_log_file=binlog.000002master_pos=102
![Page 22: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/22.jpg)
4/14/15
Move asynchronous slave to a new master in 5.5 (2)
C
writeswrites
writesB
binlog.000001402
binlog.000039102
master_host=Bmaster_log_file=binlog.000002master_pos=102
asyn
c
![Page 23: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/23.jpg)
4/14/15
Move asynchronous slave to a new master in 5.5 (2)
C
writeswrites
writesB
binlog.000001402
binlog.000039102
master_host=Bmaster_log_file=binlog.000002master_pos=102
asyn
c
![Page 24: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/24.jpg)
4/14/15
Move asynchronous slave to a new master in 5.5 (3)
How can we know which file and position need to be used by the async slave ?
Find the last received Xid in the relay log on the async slave (using mysqlbinlog)# mysqlbinlog percona4relaybin.000002 | tailMjM5NDMxMDMxOTEtNTI4NzYxMTUxMDctMTM3NTAyNTI2NjUtNTc1ODY3MTc0MTg='/*!*/;# at 14611057#140131 12:48:12 server id 1 end_log_pos 29105924 Xid = 30097COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;\
![Page 25: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/25.jpg)
4/14/15
Move asynchronous slave to a new master in 5.5 (3)
How can we know which file and position need to be used by the async slave ?
Find the last received Xid in the relay log on the async slave (using mysqlbinlog)
Find in the new master which binary position matches that same Xid
Use the binary log file and the position for your CHANGE MASTER statement
# mysqlbinlog percona3bin.000004 | grep 'Xid = 30097'#140131 12:48:12 server id 1 end_log_pos 28911093 Xid = 30097
Async mysql> slave stop;
Async mysql> change master to master_host='percona3', > master_log_file='percona3bin.000004', > master_log_pos=28911093;
Async mysql> start slave;
![Page 26: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/26.jpg)
4/14/15
in 5.6
Move asynchronous slave to a new master
![Page 27: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/27.jpg)
4/14/15
Move asynchronous slave to a new master in 5.6
With 5.6 and GTID it's easier !
... but ...
It requires rsync SST (binlogs are needed)
Or wsrep_sst_xtrabackupv2 & XB>= 2.1.7
Just change master ;-)
![Page 28: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/28.jpg)
44
![Page 29: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/29.jpg)
4/14/15
Archive data from a Galera Cluster
Usually people archive data in two different ways:– delete from the production and move it to another server
– delete from the production and skip the delete on the other server that behaves as slave (much better solution)
Implementation of the second option using single production node:mysql master> SET SQL_LOG_BIN=0;
mysql master> DELETE FROM t WHERE d < (now() interval 15 day);mysql master> SET SQL_LOG_BIN=1;
# ptarchiver source h=localhost,D=db,t=t –purge b \where “d < (now() interval 15 day)” limit 1000 commiteach
![Page 30: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/30.jpg)
4/14/15
Archive data from a Galera Cluster (2)
But if you do so on a Galera Cluster it will lead to data inconsistency !!
A C
writeswrites
writes Bas
ync
ABC
ABC
ABC
ABC
SET SQL_LOG_BIN=0;DELETE FROM t where id = 'B';SET SQL_LOG_BIN=1;
![Page 31: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/31.jpg)
4/14/15
Archive data from a Galera Cluster (2)
But if you do so on a Galera Cluster it will lead to data inconsistency !!
A C
writeswrites
writes Bas
ync
ABC
ABC
A
C
ABC
SET SQL_LOG_BIN=0;DELETE FROM t where id = 'B';SET SQL_LOG_BIN=1;
![Page 32: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/32.jpg)
4/14/15
Archive data from a Galera Cluster (2)
But if you do so on a Galera Cluster it will lead to data inconsistency !!
A C
writeswrites
writes Bas
ync
ABC
ABC
A
C
ABC
SET SQL_LOG_BIN=0;DELETE FROM t where id = 'B';SET SQL_LOG_BIN=1;
![Page 33: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/33.jpg)
4/14/15
Archive data from a Galera Cluster (3)
On MariaDB Galera Cluster you can use selective binary logs events !
A C
writeswrites
writes Bas
ync
ABC
ABC
ABC
ABC
SET SKIP_REPLICATION=1;DELETE FROM t where id = 'B';SET SKIP_REPLICATION=0;
in my.cnf of the async slave:
[mysqld]replicate_events_marked_for_skip='FILTER_ON_MASTER'
![Page 34: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/34.jpg)
55
![Page 35: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/35.jpg)
4/14/15
Allow longer downtime for a node
When a node goes off-line, when it joins again the cluster, it sends its last replicated event to the donor
If the donor can send all next events, IST will be performed (very fast)
If not... SST is mandatory
![Page 36: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/36.jpg)
4/14/15
Allow longer downtime for a node (2)
Those events are stored on a cache on disk: galera.cache
The size of the cache is 128Mb by default
It can be increased using gcache.size provider option:In /etc/my.cnf:
wsrep_provider_options = “gcache.size=1G”
![Page 37: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/37.jpg)
4/14/15
Allow longer downtime for a node (3)
How can we calculate the needed size ?
Like we do to estimate the InnoDB log file size, we need to find how many bytes are written for a defined period of time
We need to check – wsrep_replicated_bytes (ws sent to other nodes)
– wsrep_received_bytes(ws received from other nodes)
![Page 38: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/38.jpg)
4/14/15
Allow longer downtime for a node (4)
mysql> pager grep wsrepmysql> show global status like 'wsrep_received_bytes';show global status like 'wsrep_replicated_bytes';select sleep(60);show global status like 'wsrep_received_bytes';show global status like 'wsrep_replicated_bytes';
| wsrep_received_bytes | 649893 || wsrep_replicated_bytes | 22821002249 |
| wsrep_received_bytes | 745871 || wsrep_replicated_bytes | 22825698741 |
run this on one line
![Page 39: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/39.jpg)
4/14/15
Allow longer downtime for a node (4)
mysql> pager grep wsrepmysql> show global status like 'wsrep_received_bytes';show global status like 'wsrep_replicated_bytes';select sleep(60);show global status like 'wsrep_received_bytes';show global status like 'wsrep_replicated_bytes';
| wsrep_received_bytes | 649893 || wsrep_replicated_bytes | 22821002249 |
| wsrep_received_bytes | 745871 || wsrep_replicated_bytes | 22825698741 |
( 22825698741 – 22821002249 ) + ( 745871 – 649893 ) = 4792470 bytes4,57 MB per minute so if we want to keep 1 hour write sets in galera cache
we should set its size to at least 280M
![Page 40: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/40.jpg)
4/14/15
Galera Cache Warnings
Galera Cache is “mmaped” (IO buffered to memory)
This increases the use of Buffer Cache
So the OS might swap
Don't set swappiness to 0 or to 1
I would recommend to use 10
Use fincore-linux to see how much of your galera cache is in memory
![Page 41: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/41.jpg)
4/14/15
How much of your Galera Cache is in memory ?
use linuxfincore
# linuxfincore L galera.cache...size: 134,219,048total_pages: 32,769min_cached_page: 0cached: 4,672cached_size: 19,136,512cached_perc: 14.26total cached size: 19,136,512
![Page 42: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/42.jpg)
66
![Page 43: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/43.jpg)
4/14/15
Choose the right donor !
Let's imagine this:
A C
writes
B
Event 1
Event 1
Event 1
![Page 44: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/44.jpg)
4/14/15
Choose the right donor ! (2)
Let's imagine this:
A
writes
B
Event 1Event 2
Event 1Event 2
CEvent 1
![Page 45: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/45.jpg)
4/14/15
Choose the right donor ! (3)
Let's imagine this:
A
writes
B
Event 1Event 2
Event 1Event 2
C
Join:last event = 1
Event 1
![Page 46: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/46.jpg)
4/14/15
Choose the right donor ! (4)
Let's imagine this:
A
writes
B
Event 1Event 2
Event 1Event 2
C
IST
![Page 47: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/47.jpg)
4/14/15
Choose the right donor ! (5)
Let's imagine this:
A C
writes
B
Event 1Event 2
Event 2
Event 1Event 2
![Page 48: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/48.jpg)
4/14/15
Choose the right donor ! (6)
Let's imagine this:
A
writes
B
Event 1Event 2
Event 1Event 2
C
Let's formatthe disk
![Page 49: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/49.jpg)
4/14/15
Choose the right donor ! (7)
Let's imagine this:
A
writes
B
Event 1Event 2
Event 1Event 2
C
Join:no cluster info
![Page 50: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/50.jpg)
4/14/15
Choose the right donor ! (8)
Full SST needed
A
writes
B
Event 1Event 2
Event 1Event 2
C
SST
![Page 51: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/51.jpg)
4/14/15
Choose the right donor ! (9)
This is what we have now:
A C
writes
B
Event 1Event 2Event 3
Event 3
Event 1Event 2Event 3
![Page 52: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/52.jpg)
4/14/15
Choose the right donor ! (10)
Let's remove node B for maintenance
A C
writes
B
Event 1Event 2Event 3Event 4
Event 3Event 4
Event 1Event 2Event 3
![Page 53: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/53.jpg)
4/14/15
Choose the right donor ! (11)
Now let's remove node C to replace a disk :-(
A C
writes
B
Event 1Event 2Event 3Event 4Event 5
Event 1Event 2Event 3
![Page 54: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/54.jpg)
4/14/15
Choose the right donor ! (12)
Node C joins again and performs SST
A C
writes
B
Event 1Event 2Event 3Event 4Event 5
Event 1Event 2Event 3
![Page 55: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/55.jpg)
4/14/15
Choose the right donor ! (12)
Node C joins again and performs SST
A C
writes
B
Event 1Event 2Event 3Event 4Event 5Event 6
Event 6
Event 1Event 2Event 3
![Page 56: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/56.jpg)
4/14/15
Choose the right donor ! (13)
Node B joins again but donor selection is not clever yet...
A C
writes
B
Event 1Event 2Event 3Event 4Event 5Event 6
Event 6
Event 1Event 2Event 3
Join:last event = 3
![Page 57: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/57.jpg)
4/14/15
Choose the right donor ! (13)
Node B joins again but donor selection is not clever yet...
A C
writes
B
Event 1Event 2Event 3Event 4Event 5Event 6
Event 6
Event 1Event 2Event 3
Join:last event = 3
SST will be needed !
![Page 58: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/58.jpg)
4/14/15
Choose the right donor ! (14)
So how to tell node B that it needs to use node A?
A C
writes
B
Event 1Event 2Event 3Event 4Event 5Event 6
Event 6
Event 1Event 2Event 3Join:
last event = 3
# /etc/init.d/mysql start wsrepsst_donor=nodeA
![Page 59: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/59.jpg)
4/14/15
Choose the right donor ! (15)
With 5.6 you have now the possibility to know the lowest sequence number in gcache using wsrep_local_cached_downto
To know the latest event's sequence number on the node that joins the cluster, you have two possibilities:# cat grasdate.dat# GALERA saved stateversion: 2.1uuid: 419201747ec611e3a05a6a2ab4033f05seqno: 11cert_index:
![Page 60: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/60.jpg)
4/14/15
Choose the right donor ! (15)
With 5.6 you have now the possibility to know the lowest sequence number in gcache using wsrep_local_cached_downto
To know the latest event's sequence number on the node that joins the cluster, you have two possibilities:
# mysqld_safe wsreprecover140124 10:46:32 mysqld_safe Logging to '/var/lib/mysql/percona1_error.log'.140124 10:46:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql140124 10:46:32 mysqld_safe Skipping wsreprecover for 419201747ec611e3a05a6a2ab4033f05:11 pair140124 10:46:32 mysqld_safe Assigning 419201747ec611e3a05a6a2ab4033f05:11 to wsrep_start_position140124 10:46:34 mysqld_safe mysqld from pid file /var/lib/mysql/percona1.pid ended
since PXC 5.6.19-25.6, joining the group, state message exchange, provides us with
gcache seqno limits.
![Page 61: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/61.jpg)
77
![Page 62: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/62.jpg)
4/14/15
Measuring Max Replication Throughput
Since (5.5.33) wsrep_desync can be used to find out how fast a node can replicate
The process is to collect the amount of transactions (events) during peak time for a define time range (let's take 1 min)mysql> pager grep wsrepmysql> show global status like 'wsrep_last_committed'; > select sleep(60); > show global status like 'wsrep_last_committed';
| wsrep_last_committed | 61472 |
| wsrep_last_committed | 69774 |
![Page 63: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/63.jpg)
4/14/15
Measuring Max Replication Throughput
Since (5.5.33) wsrep_desync can be used to find out how fast a node can replicate
The process is to collect the amount of transactions (events) during peak time for a define time range (let's take 1 min)mysql> pager grep wsrepmysql> show global status like 'wsrep_last_committed'; > select sleep(60); > show global status like 'wsrep_last_committed';
| wsrep_last_committed | 61472 |
| wsrep_last_committed | 69774 |
69774 – 61472 = 83028302 / 60 = 138.36 tps
![Page 64: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/64.jpg)
4/14/15
Measuring Max Replication Throughput
Since (5.5.33) wsrep_desync can be used to find out how fast a node can replicate
The process is to collect the amount of transactions (events) during peak time for a define time range (let's take 1 min)
Then collect the amount of transactions and the duration to process them after the node was in desync mode and not allowing writes
In desync mode, the node doesn't send flow control messages to the cluster
![Page 65: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/65.jpg)
4/14/15
Measuring Max Replication Throughput
Since (5.5.33) wsrep_desync can be used to find out how fast a node can replicate
The process is to collect the amount of transactions (events) during peak time for a define time range (let's take 1 min)
Then collect the amount of transactions and the duration to process them after the node was in desync mode and not allowing writes
In desync mode, the node doesn't sent flow control messages to the cluster
set global wsrep_desync=ON; flush tables with read lock; show global status like 'wsrep_last_committed'; select sleep( 60 ); unlock tables;
+++| Variable_name | Value |+++| wsrep_last_committed | 145987 |+++
![Page 66: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/66.jpg)
4/14/15
Measuring Max Replication Throughput
In another terminal you run myq_gadget and when wsrep_local_recv_queue (Queue Dn) is back to 0 check again the value of wsrep_last_committed.
LefredPXC / percona3 / Galera 2.8(r165)Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind13:25:24 P 7 3 Dono T/T 0 8k 0 0 0 0 0.0 0 0 0 125 0 0 013:25:25 P 7 3 Dono T/T 0 8k 0 197 0 300K 0.0 0 0 0 145 90 0 2...13:26:46 P 7 3 Dono T/T 0 7 0 209 0 318K 0.0 0 0 0 139 62 0 113:26:47 P 7 3 Dono T/T 0 0 0 148 0 222K 0.0 0 0 0 140 40 0 1
This is when FTWRL is released
This is when galeracatch up.
wsrep_last_committed = 165871165871 145987 = 1988419884 / 82 = 242.48 tps
We're currently at 57%of our capacity
![Page 67: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/67.jpg)
88
![Page 68: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/68.jpg)
4/14/15
Taking backups without stalls
When you want to perform a consistent backup, you need to take a FLUSH TABLES WITH READ LOCK (FTWRL)
By default even with Xtrabackup
This causes a Flow Control in galera
So how can we deal with that ?
![Page 69: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/69.jpg)
4/14/15
Taking backups without stalls
Choose the node from which you want to take the backup
Change the state to 'Donor/Desynced' (see tip 9)set global wsrep_desync=ON
Take the backup
Wait that wsrep_local_recv_queue is back down to 0
Change back the state to 'Joined' set global wsrep_desync=OFF
![Page 70: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/70.jpg)
4/14/15
Lock for Backup
Since Percona XtraDB Cluster 5.6.21-25.8 (Nov 25th 2014)
When using xtrabackup-v2 as SST method, backup locks are used instead of FLUSH TABLES WITH READ LOCK (FTWRL) on the donor
Requires Percona XtraBackup >= 2.2.5
No mix of nodes < 5.6.21 and nodes >= 5.6.21 allowed for this SST method
![Page 71: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/71.jpg)
99
![Page 72: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/72.jpg)
4/14/15
SSL everywhere !
It's possible to have the Galera replication encrypted via SSL
But now it's also possible to have SST over SSL, with xtrabackup_v2 and with rsync
https://github.com/tobz/galera-secure-rsync
![Page 73: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/73.jpg)
4/14/15
SSL everywhere : certs creation
openssl req new x500 days 365000 nodes keyout key.pem out cert.pem
Same cert and key must be copied on all nodes
Copy them in /etc/mysql for example and let only mysql read them
![Page 74: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/74.jpg)
4/14/15
SSL everywhere : galera configuration
wsrep_provider_options = “socket.ssl.cert=/etc/mysql/cert.pem; socket.ssl_key=/etc/mysql/key.pem”
It's possible to set a remote Certificate Authority for validation (use socket.ssl_ca)
All nodes must have SSL enabled
![Page 75: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/75.jpg)
4/14/15
SSL everywhere : SST configuration
As Xtrabackup 2.1 supports encryption, it's now also possible to use SSL for SST
Use wsrep_sst_method=xtrabackupv2[sst]
tkey=/etc/mysql/key.pem
tcert=/etc/mysql/cert.pem
encrypt=3
![Page 76: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/76.jpg)
4/14/15
SSL everywhere : SST configuration
And for those using rsync ?
galerasecurersync acts like wsrep_sst_rsync but secures the communication with SSL using socat.
Uses also the same cert and key filewsrep_sst_method=secure_rsync
https://github.com/tobz/galera-secure-rsync
![Page 77: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/77.jpg)
1010
![Page 78: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/78.jpg)
4/14/15
Decode GRA* files
When a replication failure occurs, a GRA_*.log file is created into the datadir
For each of those files, a corresponding message is present in the mysql error log file
Can be a false positive (bad DDL statement)... or not !
This is how you can decode the content of that file
![Page 79: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/79.jpg)
4/14/15
Decode GRA* files (2)
Download a binlog header file (http://goo.gl/kYTkY2 for 5.5 and goo.gl/ohCL8M for 5.6)
Join the header and one GRA_*.log file:– cat GRAheader GRA_3_3.log >> GRA_3_3bin.log
Now you can just use mysqlbinlog vvv and find out what the problem was !wsrep_log_conflicts = 1wsrep_debug = 1wsrep_provider_options = “cert.log_conflicts=1”
![Page 80: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/80.jpg)
1111
![Page 81: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/81.jpg)
4/14/15
Avoiding SST when adding a new node
It's possible to use a backup to prepare a new node.
Those are the 3 prerequisites:– use XtraBackup >= 2.0.1
– the backup needs to be performed with galerainfo
– the gcache must be large enough
![Page 82: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/82.jpg)
4/14/15
Avoiding SST when adding a new node (2)
Restore the backup on the new node
Display the content of xtrabackup_galera_info:5f22b204dc6b11e108007a9c9624dd66:23
Create the file called grastate.dat like this:#GALERA saved state
version: 2.1
uuid:5f22b204dc6b11e108007a9c9624dd66
seqno: 23
cert_index:
mysql> show global status like 'wsrep_provider_version';+++| Variable_name | Value |+++| wsrep_provider_version | 2.1(r113) |+++
![Page 83: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/83.jpg)
1212
![Page 84: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/84.jpg)
4/14/15
Play with quorum and weight
Galera manages Quorum
If a node does not see more than 50% of the total amount of nodes, reads/writes are not accepted
Split brain is prevented
This requires at least 3 nodes to work properly
Can be disabled (but be warned!)
You can cheat ;-)
![Page 85: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/85.jpg)
4/14/15
Quorum: lost of connectivity
![Page 86: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/86.jpg)
4/14/15
Quorum: lost of connectivity
Network ProblemNetwork Problem
![Page 87: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/87.jpg)
4/14/15
Quorum: lost of connectivity
Network ProblemNetwork Problem
Does not accept Reads & WritesDoes not accept Reads & Writes
![Page 88: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/88.jpg)
4/14/15
Quorum: lost of connectivity
![Page 89: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/89.jpg)
4/14/15
Quorum: lost of connectivity
Network ProblemNetwork Problem
![Page 90: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/90.jpg)
4/14/15
Quorum: lost of connectivity
Network ProblemNetwork Problem
![Page 91: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/91.jpg)
4/14/15
Quorum: lost of connectivity
Network ProblemNetwork Problem
![Page 92: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/92.jpg)
4/14/15
Quorum: arbitrator
It's possible to use an arbitrator (garbd) to play an extra node. All traffic will pass through it but it won't have any MySQL running.
Useful in case of storage available only for 2 nodes or if you have an even amount of nodes.
Odd number of nodes is always advised
![Page 93: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/93.jpg)
4/14/15
Quorum: cheat !
You can disable quorum but watch out ! (you have been warned):
wsrep_provider_options = “pc.ignore_quorum=true”
You can define the weigth of a node to affect the quorum calculation (default is 1):
wsrep_provider_options = “pc.weight=1”
![Page 94: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/94.jpg)
1313
![Page 95: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/95.jpg)
4/14/15
How to optimize WAN replication?
Galera 2 requires all point-to-point connections for replication
datacenter A datacenter B
![Page 96: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/96.jpg)
4/14/15
How to optimize WAN replication? (2)
Galera 3 brings the notion of “cluster segments”
datacenter A datacenter B
![Page 97: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/97.jpg)
4/14/15
How to optimize WAN replication? (3)
Segments gateways can change per transaction
datacenter A datacenter B
![Page 98: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/98.jpg)
4/14/15
How to optimize WAN replication? (3)
Replication traffic between segments is mimized. Writesets are relayed to the other segment through one node
datacenter A datacenter B
commit
WS
![Page 99: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/99.jpg)
4/14/15
How to optimize WAN replication? (3)
Replication traffic between segments is mimized. Writesets are relayed to the other segment through one node
datacenter A datacenter B
commit
WS WS
WS
![Page 100: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/100.jpg)
4/14/15
How to optimize WAN replication? (4)
From those local relays replication is propagated to every nodes in the segment
datacenter A datacenter B
commit
WS WS
![Page 101: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/101.jpg)
4/14/15
How to optimize WAN replication? (4)
From those local relays replication is propagated to every nodes in the segment
datacenter A datacenter B
commit
WS WSgmcasts.segment = 1...255
![Page 102: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/102.jpg)
1414
![Page 103: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/103.jpg)
4/14/15
Load balancers
Galera is generally used in combination with a load balancer
The most used is HA Proxy
Codership provides one with Galera: glbdapp 1 app 2 app 3
node 1 node 2 node 3
HA PROXY
![Page 104: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/104.jpg)
4/14/15
Load balancers: myths, legends and reality
TIME_WAIT– On heavy load, you may have an issue with a large amount of TCP connections in TIME_WAIT state
– This can leas to a TCP port exhaustion !
How to fix ?– Use nolinger option in HA Proxy, but this lead to an increase
of Aborted_clients is the client is connecting and disconnecting to MySQL too fast
– Modify the value of tcp_max_tw_buckets
![Page 105: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/105.jpg)
4/14/15
Load balancers: common issues
Persitent Connections– Many people expects the following scenario:
app 1 app 2 app 3
node 1
node 2 node 3
HA PROXY
![Page 106: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/106.jpg)
4/14/15
Load balancers: common issues
Persitent Connections– When the node that was specified to receive the persistent
write fails for exampleapp 1 app 2 app 3
node 1
node 2 node 3
HA PROXY
![Page 107: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/107.jpg)
4/14/15
Load balancers: common issues
Persitent Connections– When the node is back on-line...
app 1 app 2 app 3
node 1
node 2 node 3
HA PROXY
![Page 108: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/108.jpg)
4/14/15
Load balancers: common issues
Persistent Connections– Only the new connections will use again the preferred node
app 1 app 2 app 3
node 1
node 2 node 3
HA PROXY
![Page 109: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/109.jpg)
4/14/15
Load balancers: common issues
Persistent Connections– HA Proxy decides where the connection will go at TCP handshake
– Once the TCP session is established, the sessions will stay where they are !
Solution ?– With HA Proxy 1.5 you can now specify the following option :
onmarkedup shutdownbackupsessions
onmarkeddown shutdownsessions backup
![Page 110: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/110.jpg)
4/14/15
Better Proxy Alternative ?
ScaleArc is a close source alternative that provides many features– R/W split
– Caching
– GUI
MaxScale (GA since 2015)– Open Source
– Maybe the best R/W splitting
![Page 111: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/111.jpg)
1515
![Page 112: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/112.jpg)
4/14/15
Multicast replication
By default, galera uses unicast TCP
1 copy of the replication message sent to all other nodes in the cluster
![Page 113: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/113.jpg)
4/14/15
Multicast replication (2)
By default, galera uses unicast TCP
1 copy of the replication message sent to all other nodes in the cluster
More nodes, more bandwidth
![Page 114: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/114.jpg)
4/14/15
Multicast replication (3)
If your network supports it you can use Multicast UDP for replicationwsrep_provider_options = “gmacast.mcast_addr = 239.192.0.11”
wsrep_cluster_cluster_address = gcomm://239.192.0.11
![Page 115: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/115.jpg)
15+115+1for freefor free
![Page 116: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/116.jpg)
4/14/15
Reduce replication traffic
Galera uses ROW Base Replication events (RBR)
Since 5.6 (MySQL & Percona Server) you can send only the updated data
Set binlog_row_image = minimal
You can gain up to 80% traffic
![Page 117: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/117.jpg)
15+215+2for freefor free
![Page 118: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/118.jpg)
4/14/15
Avoid MyISAM ! You have to !
To avoid surprises or if you don't have full control on schema creation
For PXC: – set enforce_storage_engine = “innodb”
For MariaDB / MySQL:– use a plugin https://github.com/xiezhenye/mysql-plugin-
disable-myisam
![Page 119: Plmce2k15 15 tips galera cluster](https://reader036.vdocuments.us/reader036/viewer/2022062304/55a92a101a28abbd768b48b6/html5/thumbnails/119.jpg)
Thank you
Questions ?
Percona Live London 2014