道森mysql性能资料pluc_2013_gcbp

1、Galera Cluster BestPracticesSeppo Jaakola CodershipAgenda1) Galera Cluster Short Introduction2) Consistent reads3) wsrep_cluster_address 4)Multi-master Conflicts 5)State Transfers (SST IST)6) Backups7) Schema Upgrades8) MySQL Replication Topologies9) Migrating to Galera Cluster10) Detecting Slow Nod

2、es11)Parallel R2Galera ClusterMulti-MasterReplicationRead & write access to any node Client can connect to any nodeThere can be several nodes read & write read & writeread & writeMySQLMySQLMySQLReplication is synchronousGalera R5Multi-MasterRepli

3、cationread & write read & writeread & writeMulti-master cluster looks like one big database with multiple entry pointsMySQL6Galera ClusterSynchronous multi-master cluster For MySQL/InnoDB3 or more nodes needed for HAAutomatic node provisioningWorks in LAN / WAN / Cloudwww.codership.

4、com7SynchronousReplicationTransaction is processed locally up to commit timeRead & writeMySQLMySQLMySQLGalera R10SynchronousReplicationTransaction is replicated to whole clustercommitMySQLMySQLMySQLGalera R11SynchronousReplicationClient gets OK s

5、tatusOKMySQLMySQLMySQLGalera R12SynchronousReplicationTransaction is applied in slavesMySQLMySQLMySQLGalera R13Consistent ReadsConsistent readsReplication is virtually synchronous.Transaction is replicated to whole clustercommitMySQLMySQLMySQLGal

6、era R15Consistent reads1. Insert into t1 values (1,. . )2. Select from t1 where i=1Will the select see the inserted row?MySQLMySQLGalera R16Consistent ReadsAka read causalityThere is causal dependency between operations on two database connection

7、s:One thread does a databaseoperationAnd some other thread is expecting to see the values of earlier 17Consistent ReadsUse:wsrep_causal_reads=ONEvery read (select, show) will wait until slave queue has been fully appliedThere is timeout for max causal read wait:replicator.causa

8、l_read_18Consistent ReadsUse:wsrep_causal_reads=ONEvery read (select, show) will wait until slave queue has been fully appliedThere is timeout for max causal read wait:replicator.causal_read_19Consistent reads1. Insert into t1 values (1, .)2. Selec

9、t from t1 where i=1Wait for last WS to apply.Run the selectMySQLMySQLGalera R20Consistent Readswsrep_causal_reads makes the cluster to look fully synchronous for applicationConsistent reads makes SELECTs slowerUse wsrep_causal_reads Only when there is real need for itwww.c

10、21The Thing with wsrep_cluster_addressThe Thing with wsrep_cluster_addressWrite all nodes planned for the cluster, in the address string:wsrep_cluster_address=gcomm:/,.To bootstrap a new clusterService mysql start wsrep_cluster_address=gcomm:/Or rewrite f: wsrep_clus

11、ter_address=gcomm:/Make sure not to leave wsrep_cluster_address=gcomm:/ in f !Note: wsrep_urls parameter for mysqld_safe is 23The Thing with wsrep_cluster_addressXtrabackup and rsync SST cannot be used for a running serverWhy?It is not possible to copy datafiles under

12、 running InnoDBHaving wsrep_address set to a list of node addresses and starting replication on a running node can lead to node 24wsrep_cluster_addresswsrep_cluster_address=gcomm:/,10. 0.0.2,JoinerGalera R25wsre

13、p_cluster_addressGalera R26wsrep_cluster_addressIt “may” also happen that will operate as bridge between and the rest of the clusterReplication relayGalera R27wsrep_

14、cluster_addresswsrep_cluster_address=gcomm:/JoinerCluster 128wsrep_cluster_addressCluster 1Cluster 229Dealing with Multi-Master ConflictsMulti-masterConflictswritewriteMySQLMySQLMySQLGalera Replicationwww.coder

15、31Multi-masterConflictswritewriteConflict detectedMySQLMySQLMySQLGalera R32Multi-masterConflictsDeadlock errorOKwriteMySQLMySQLMySQLGalera R33Multi-MasterConflictsGalera uses optimistic concurrency controlIf two transactions modify same r

16、ow on different nodes at the same time, one of the transactions must abortVictim transaction will get deadlock errorApplication should retry deadlocked transactions, however not all applications have retrying logic 34Database Hot-SpotsSome rows where many transactions want to

17、 write to simultaneouslyPatterns like queue or ID allocation can be hot- 35Hot-Spotswrite write 36Hot rowDiagnosing Multi-Master Conflictswsrep_log_conflicts will print info of each cluster conflict in mysql error logCert.log_conflicts to print out informa

18、tion of the conflicting transactionStatus variables to monitor:wsrep_local_bf_abortswsrep_local_cert_failuresby using wsrep_debug configuration, all conflicts (.and plenty of other information) will be 37wsrep_retry_autocommitGalera can retry autocommit transaction on behalf o

19、f the client application, inside of the MySQL serverMySQL will not return deadlock error, but will silently retry the transactionwsrep_retry_autocommit=n will retry the transaction n times before giving up and returning deadlock errorRetrying applies only to autocommit transactions, as retrying is n

20、ot safe for multi- statement 38Retry Autocommit1. conflict detectedWritewrite2. retryingMySQLMySQLMySQLGalera R39Multi-MasterConflicts1) Analyze the hot-spot2) Check if application logic can be changed to catch deadlock exception and apply retr

21、ying logic in application3) Try if wsrep_retry_autocommit configuration helps4) Limit the number of master nodes or change completely to master-slave modelif you can filter out the access to the hot- spot table, it is enough to treat writesonly to hot-spot table as 40Sta

22、te TransfersState TransferJoining node needs to get the current database stateTwo choices:IST: incremental statetransfer SST: full state transferIf joining node had some previous state and gcache spans to that, then IST can be 42State Snapshot TransferTo send full database state

23、 wsrep_sst_method to choose the method:mysqldump rsync 43SST RequestMySQLjoinerMySQLGalera R44SST Request wsrep_sst_methodSST MethodMySQLjoinerdonorGalera R45wsrep_sst_xtrabackupwsrep_sst_rsyncwsrep_sst_mysqldumpSST API

24、SST is open API for shell scripts Anyone can write custom SST SST API can be used e.g. for:BackupsFiltering out part of 46wsrep_sst_mysqldumpLogical backup Slowest methodConfigure authenticationwsrep_sst_auth=”root:rootpass”Super privilege neededMake sure SST user in donor n

25、ode can take mysqldump from donor and load it over the network to joinernodeYou can try this manually 47wsrep_sst_rsyncPhysical backup Fast methodCan only be used when node is startingRsyncing datadirectory under running InnoDB is not 48wsrep_sst_x

26、trabackupContributed by Percona Probably the fastest method Uses xtrabackupLeast blocking on Donor side (short readlock is still used when backup starts)49SST DonorAll SST methods cause some disturbance for donor nodeBy default donor accepts client connections, although committing w

27、ill be prohibited for a whileIf wsrep_sst_donor_rejects_queries is set, donor gives unknown command error to clientsBest practice is to dedicate a reference node for donor and backup 50Incremental StateTransferRequest to joinGTID: seqno-nJoinerD

28、51gcachegcacheIncremental StateTransferJoinerDonorSend IST 52gcacheapplyIncremental StateTransferVery effectivegcache.size parameter defines how big cache will be maintainedGcache is mmap, available disk space is upper limit for size 53I

29、ncremental StateTransferUse database size and write rate to optimize gcache:gcache databaseWrite rate tells how long tail will be stored in 54Incremental StateTransferYou can think that IST IsA short asynchronous replication sessionIf communication is bad quality, node can drop

30、 and join back fast with IST55Backups BackupsBackupsBackupsAll Galera nodes are constantly up to dateBest practices:Dedicate a reference node for backupsAssign global trx ID with the backupPossible methods:1. Disconnecting a node for backup2. Using SST script interface 3.xtrabackupw

31、57Backups with global Trx IDGlobaltransactionID(GTID)marksaposition in the cluster transaction streamBackup with known GTID make it possible to utilize IST when joining new nodes, eg, when:Recovering the nodeProvisioning new 58Backup by Disconnecting a NodeIsola

32、te the backup nodeLoad BalancingMySQLMySQLMySQLGalera R59Backup by Disconnecting a NodeLoad BalancingDisconnect from groupe.g. clear wsrep_providerMySQLMySQLMySQLGalera R60Backup by Disconnecting a NodeLoad BalancingDisconnect from groupe.g. clea

33、r wsrep_providerMySQLMySQLMySQLGalera R61Backup by Disconnecting a NodeLoad BalancingWork your backup magicMySQLMySQLMySQLGalera R62Backup by Disconnecting a NodeLoad BalancingRead global transaction ID from status and assign to backup wsr

34、ep_cluster_uuid wsrep_last_committedMySQLMySQLMySQLGalera R63Backup by SSTDonor mode provides isolated processing environmentA special SST script can be written just to prepare backup in donor node: wsrep_sst_backupGarbd can be used to trigger donor node to run the

35、wsrep_sst_64Backup by SST APILaunch garbdLoad BalancingSST requestGarbdnode1node3node2wsrep_sst_donor=node3 wsrep_sst_method=backupGalera R65Backup by SST APIDonor launches wsrep_sst_backupLoad Balancingwsrep_sst_backup.node1node2node3Galera Replicat

36、66Backup by SST APIwsrep_sst_backup prepares the backupLoad Balancingwsrep_sst_backup.GTIDnode1node2node3Galera R67Backup by SST APIBackup node returns to clusterLoad Balancingnode1node2node3Galera R68Backup byxtrabacku

37、pXtrabackup is hot backup method and can be used anytimeSimple, efficientUse galera-info option to get global transaction ID logged into separate galera info 69Schema UpgradesSchema UpgradesDDL is non-transactional, and therefore bad forreplicationGalera has two methods for DDLT

38、OI, Total Order IsolationRSU, Rolling Schema UpgradeUse wsrep_osu_method to choose either optionPt-online-schema-change can give lockless schema upgradeUse TOI modeBeware of foreign 71Total OrderIsolationDDL is replicated up-frontEach node will get the DDL statement and must pro

39、cess the DDL at same slot in transaction streamGalera will isolate the affected table/database for the duration of DDL 72Total OrderIsolationUser issues some DDL statement, like ATLTER TABLE.ALTER.73Total OrderIsolationnode1node3node2ALTER w

40、ill be replicated and waits in slave queue according to seqnoALTERALTERALTER74Total OrderIsolationnode1node3node2ALTER gets his turn to apply, Galera will forbid any concurrent access to the affected table(s)ALTERALTERALTER75Total OrderIsolationDDL is over and clust

41、er is back to normal 76Rolling Schema UpgradeDDL is not replicatedGalera will take the node out of replication for the duration of DDL processingWhen DDL is done with, node will catch up with missed transactions (like IST)DBA should roll RSU operation overall

42、 nodesRequires backwards compatible schema 77Rolling Schema UpgradeUser issues some DDL statement, like ALTER TABLE.ALTER.78Rolling Schema UpgradeNode is de-synced from replication,Replication stream is buffered in gcachenode3node1node2www.code

43、79Rolling Schema UpgradeALTER.ALTER processingnode3node1node2Slave 80Rolling Schema UpgradeNode re-syncs to replicationnode1node3node2Slave 81Rolling Schema UpgradeCatching up with missed transactionsnode1node3node2Slave 82Ro

44、lling Schema UpgradeNode is back to cluster againQ: who rolls the upgrade? A: DBA!83wsrep_on=OFFwsrep_on is a session variable telling if this session will be replicated or notI tried to hide this information to the best I can, but somebody has leaked this outAnd so,

45、yes, it is possible to run“poor mans RSU” with wsrep_on set to OFFsuch session may be aborted by replication Use only, if you are really sure that:planned SQL is not conflictingSQL will not generate 84Schema UpgradesBest practices:Plan your upgradesTry to be backwards c

46、ompatibleRehearse your upgradesFind out DDL execution timeGo for RSU ifpossibleALTER TABLE to create new autoinccolumn will cause issuesEvery node has different autoinc increment and offset 85Create autoinc ColumnALTER TABLE myTable ADD COLUMN (new INT AUTOINC.)node1node2www

47、.86myTableAaa Bbb CccmyTableAaa Bbb CccCreate autoinc ColumnALTER TABLE myTable ADD COLUMN (new INT AUTOINC.)87myTableAaa Bbb Ccc258myTableAaa Bbb Ccc147MySQL Replication TopologiesGalera as MySQL SlaveMySQLMySQL replicationSame server_id in all nodesMySQLslav

48、eNode 1 slaveNode 3Node 289Galera as MySQL MasterSame server_id in all nodesNode 1Node 2 masterNode 3MySQL replicationMySQL90MySQL ReplicationBottleneckMySQL replication is single threadedSynchronous replication adds latency for transactionsGalera Cluster will

49、not apply replication as fast as native MySQL Slavewsrep_mysql_replication_bundleGroups mysql replication events in large transactionsHelps with the latency, you can go up to 5K trx/sec Experimental, bugs aheadMySQL 5.6 “parallel replication” will help, if you have several 91

50、Migrating to Galera ClusterMigrating to Galera ClusteringMySQLmasterMySQL master slave hierarchyMySQL93Migrating to Galera ClusteringMySQLmasterInstall first Galera node to operate as MySQL slaveGalera Node 1MySQL94Migrating to Galera ClusteringMySQLmaster

51、promote Galera node to operate as one node clusterGalera Node 1MySQL95Migrating to Galera ClusteringMySQLmasterJoin more nodes in Galera slave clusterGalera Node 1MySQLslaveGalera Node 2Galera Node 396Migrating to Galera ClusteringMySQLmasterMove traffic to Galera clusterGalera Node 1MySQLslaveGalera Node 2Galera Node 397