MySQL InnoDB Cluster - Group Replication

MySQL Group Replication in a nutshell the core of MySQL InnoDB Cluster Oracle Open World September 19th 2016 Frédéric Descamps MySQL Community Manager Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 2 / 126

Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purpose 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 up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle's product remains at the sole discretion of Oracle. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 3 / 126

Join us! MySQL Community Reception @ Oracle OpenWorld Celebrate, Have Fun and Mingle with Oracle’s MySQL Engineers & Your Peers Tuesday, September 20, 7.00 pm Jillian’s at Metreon: 175 Fourth Street, San Francisco At the corner of Howard and 4th st.; only 2-min walk fromMoscone Center (same place as last year) Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 4 / 126

Other sessions CON2907 - MySQL High Availability MattLord CON4669 - MySQL High Availability with Group Replication NunoGomes CON3373 - The State of the Art of MySQL Replication LuisSoares HOL2912 - Building a Highly Available MySQL Database Service with Group Replication MattLord Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 8 / 126

Group replication is a plugin ! GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory MySQL Group Communication System(GCS) is based on Paxos Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 20 / 126

Group replication is a plugin ! GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory MySQL Group Communication System(GCS) is based on Paxos Provides virtually synchronous replication for MySQL 5.7+ Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 21 / 126

Group replication is a plugin ! GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory MySQL Group Communication System(GCS) is based on Paxos Provides virtually synchronous replication for MySQL 5.7+ Supported on all MySQL platforms !! Linux, Windows, Solaris, OSX, FreeBSD Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 22 / 126

“Multi-masterupdateanywherereplicationpluginforMySQLwithbuilt-inconflict detectionandresolution,automaticdistributedrecovery,andgroupmembership.” Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 23 / 126

Group Replication : how does it work ? A node (member of the group) send the changes (binlog events) made by a transaction to the group through the GCS. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 25 / 126

Group Replication : how does it work ? A node (member of the group) send the changes (binlog events) made by a transaction to the group through the GCS. All members consume the writeset and certify it Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 26 / 126

Group Replication : how does it work ? A node (member of the group) send the changes (binlog events) made by a transaction to the group through the GCS. All members consume the writeset and certify it If passed it is applied, if failed, transaction is rolled back on originating server and discarded at other servers. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 27 / 126

OK... but how does it work ?! It's just magic ! ... no, in fact the writesets replication is synchronous and then certification and apply of the changes are local to each nodes and asynchronous. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 30 / 126

OK... but how does it work ?! It's just magic ! ... no, in fact the writesets replication is synchronous and then certification and apply of the changes are local to each nodes and asynchronous. not that easy to understand... right ? Let's illustrate this... Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 31 / 126

Group Replication : Optimistic Locking With GR we use what we call optimistic locking, it means we consider a resource free and this will be confirmed or not during certification. Let's first have a look at the traditional locking to compare. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 48 / 126

Group Replication : requirements exclusively use of InnoDB tables only every tables must have a PK defined only IPV4 is supported Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 64 / 126

Group Replication : requirements exclusively use of InnoDB tables only every tables must have a PK defined only IPV4 is supported a good network with lowlatency is important Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 65 / 126

Group Replication : requirements exclusively use of InnoDB tables only every tables must have a PK defined only IPV4 is supported a good network with lowlatency is important maximumof 9 members per group Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 66 / 126

Group Replication : requirements exclusively use of InnoDB tables only every tables must have a PK defined only IPV4 is supported a good network with lowlatency is important maximumof 9 members per group log-binmust be enabled and only ROWformat is supported Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 67 / 126

Group Replication : requirements (2) enable GTIDs replication meta-data must be stored on systemtables --master-info-repository=TABLE --relay-log-info-repository=TABLE Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 69 / 126

Group Replication : requirements (2) enable GTIDs replication meta-data must be stored on systemtables --master-info-repository=TABLE --relay-log-info-repository=TABLE writesets extraction must be enabled --transaction-write-set-extraction=XXHASH64 Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 70 / 126

Group Replication : requirements (2) enable GTIDs replication meta-data must be stored on systemtables --master-info-repository=TABLE --relay-log-info-repository=TABLE writesets extraction must be enabled --transaction-write-set-extraction=XXHASH64 log-slave-updatesmust be enabled Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 71 / 126

Group Replication : limitations There are also some technical limitations: binlog checksumis not supported --binlog-checksum=NONE Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 73 / 126

Group Replication : limitations There are also some technical limitations: binlog checksumis not supported --binlog-checksum=NONE Savepoints are not supported Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 74 / 126

Group Replication : limitations There are also some technical limitations: binlog checksumis not supported --binlog-checksum=NONE Savepoints are not supported SERIALIZABLEis not supported as transaction isolation level Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 75 / 126

Group Replication : limitations There are also some technical limitations: binlog checksumis not supported --binlog-checksum=NONE Savepoints are not supported SERIALIZABLEis not supported as transaction isolation level an object cannot be changed concurrently at different servers by two operations, where one of themis a DDL and the other is either a DML or DDL. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 76 / 126

Is my workload ready for Group Replication ? Large transactions are more vulnerable because: Large write set may increase the likelihood of certification conflicts Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 79 / 126

Is my workload ready for Group Replication ? Large transactions are more vulnerable because: Large write set may increase the likelihood of certification conflicts Transaction taking longer to execute may be more vulnerable to be aborted by a certified transaction. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 80 / 126

Is my workload ready for Group Replication ? Large transactions are more vulnerable because: Large write set may increase the likelihood of certification conflicts Transaction taking longer to execute may be more vulnerable to be aborted by a certified transaction. Small transactions changing a very small set of data (hotspots): Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 81 / 126

Is my workload ready for Group Replication ? Large transactions are more vulnerable because: Large write set may increase the likelihood of certification conflicts Transaction taking longer to execute may be more vulnerable to be aborted by a certified transaction. Small transactions changing a very small set of data (hotspots): Concurrently executing themat different sites will trade-off contention with roll backs due to the optimistic execution Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 82 / 126

Therefore, when using Group Replication, we should pay attention to these points: PK is mandatory (and a good one is better) avoid large transactions avoid hotspot Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 83 / 126

Scheme verification We will see nowa list a queries that allows us to analyze our scheme. We will look for: non InnoDB tables tables without PK tables with a bad PK Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 84 / 126

Non InnoDB tables SELECT table_schema, table_name, engine, table_rows, (index_length+data_length)/1024/1024 AS sizeMB FROM information_schema.tables WHERE engine != 'innodb' AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema'); Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 85 / 126

Tables Without Primary Key select tables.table_schema, tables.table_name, tables.engine, tables.table_rows from information_schema.tables left join ( select table_schema, table_name from information_schema.statistics group by table_schema, table_name, index_name having sum( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks on tables.table_schema = puks.table_schema and tables.table_name = puks.table_name where puks.table_name is null and tables.table_type = 'BASE TABLE' and engine='InnoDB'; Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 86 / 126

Tables with bad primarky keys set SQL_MODE=""; select a.TABLE_SCHEMA,a.TABLE_NAME, b.ENGINE, a.COLUMN_NAME, a.DATA_TYPE, a.COLUMN_TYPE, a.COLUMN_KEY, b.TABLE_ROWS from information_schema.COLUMNS as a join information_schema.TABLES as b on b.TABLE_NAME=a.TABLE_NAME and b.TABLE_SCHEMA=a.TABLE_SCHEMA where COLUMN_KEY='PRI' and ENGINE="InnoDB" and DATA_TYPE not like '%int' and DATA_TYPE not like 'enum%' and DATA_TYPE not like 'date%' and DATA_TYPE not like 'time%' and a.TABLE_SCHEMA not in ('mysql','sys') group by table_schema, table_name; Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 87 / 126

Workload analysis Nowit's time to play with Performance_Schemato analyze our workload. The goal is to identify: transactions with most statements (and most writes in particular) transactions with most rows affected largest statements by rowaffected queries updating most the same PK and therefore having to wait more) Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 88 / 126

Setup Performance_Schema We need to enable some consumers and instruments: update performance_schema.setup_consumers set enabled = 'yes' where name like 'events_statement%' or name like 'events_transaction%'; update performance_schema.setup_instruments set enabled = 'yes', timed = 'yes' where name = 'transaction'; Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 89 / 126

Find the transactions with most statements select t.thread_id, t.event_id, count(*) statement_count, sum(s.rows_affected) rows_affected, length(replace(group_concat( case when s.event_name = "statement/sql/update" then 1 when s.event_name = "statement/sql/insert" then 1 when s.event_name = "statement/sql/delete" then 1 else null end),',','')) as "# write statements" from performance_schema.events_transactions_history_long t join performance_schema.events_statements_history_long s on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id group by t.thread_id, t.event_id order by rows_affected desc limit 10; Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 90 / 126

Find the transactions with most statements It's possible to see what are all these statements in one transaction: set group_concat_max_len = 1000000; select t.thread_id, t.event_id, count(*) statement_count, sum(s.rows_affected) rows_affected, group_concat(sql_text order by s.event_id separator 'n') statements from performance_schema.events_transactions_history_long t join performance_schema.events_statements_history_long s on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id group by t.thread_id, t.event_id order by statement_count desc limit 1G Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 91 / 126

Find the transactions with most rows affected select t.thread_id, t.event_id, count(*) statement_count, sum(s.rows_affected) rows_affected, length(replace(group_concat( case when s.event_name = "statement/sql/update" then 1 when s.event_name = "statement/sql/insert" then 1 when s.event_name = "statement/sql/delete" then 1 else null end),',','')) as "# write statements" from performance_schema.events_transactions_history_long t join performance_schema.events_statements_history_long s on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id group by t.thread_id, t.event_id order by rows_affected desc limit 10; Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 92 / 126

Find the transactions with most rows affected It's again possible the see what are the statements: select t.thread_id, t.event_id, count(*) statement_count, sum(s.rows_affected) rows_affected, group_concat(sql_text order by s.event_id separator 'n') statements from performance_schema.events_transactions_history_long t join performance_schema.events_statements_history_long s on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id group by t.thread_id, t.event_id order by rows_affected desc limit 1G Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 93 / 126

Find the transactions with most rows affected It's again possible the see what are the statements: select t.thread_id, t.event_id, count(*) statement_count, sum(s.rows_affected) rows_affected, group_concat(sql_text order by s.event_id separator 'n') statements from performance_schema.events_transactions_history_long t join performance_schema.events_statements_history_long s on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id group by t.thread_id, t.event_id order by rows_affected desc limit 1G Don't forget to verify the auto_commit ones are they are not returned with the query above. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 94 / 126

Find largest statements by row affected select query, db, rows_affected, rows_affected_avg from sys.statement_analysis order by rows_affected_avg desc limit 10; Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 95 / 126

Find queries that write the most. Looking for lots of updates/timer_wait to the same PKs. select * from performance_schema.events_statements_history_long where rows_affected > 1 order by timer_wait desc limit 20G Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 96 / 126

MySQL Shell The MySQL Teamextended MySQL Shell to use the newAdmin API. NowMySQL Shell is a single unified client for all adminstrative and operatons tasks. Multi-Language: Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 98 / 126

MySQL Shell The MySQL Teamextended MySQL Shell to use the newAdmin API. NowMySQL Shell is a single unified client for all adminstrative and operatons tasks. Multi-Language: JavaScript Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 99 / 126

MySQL Shell The MySQL Teamextended MySQL Shell to use the newAdmin API. NowMySQL Shell is a single unified client for all adminstrative and operatons tasks. Multi-Language: JavaScript Python Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 100 / 126

MySQL Shell The MySQL Teamextended MySQL Shell to use the newAdmin API. NowMySQL Shell is a single unified client for all adminstrative and operatons tasks. Multi-Language: JavaScript Python SQL Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 101 / 126

MySQL Shell The MySQL Teamextended MySQL Shell to use the newAdmin API. NowMySQL Shell is a single unified client for all adminstrative and operatons tasks. Multi-Language: JavaScript Python SQL Supports both Document and Relational models Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 102 / 126

MySQL Shell The MySQL Teamextended MySQL Shell to use the newAdmin API. NowMySQL Shell is a single unified client for all adminstrative and operatons tasks. Multi-Language: JavaScript Python SQL Supports both Document and Relational models Exposes full Development and Admin API Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 103 / 126

MySQL Shell: Admin API mysql-js>dba.help() the global variable dbais used to access the MySQL API performDBA operations to manage MySQL InnoDB Cluster: Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 106 / 126

MySQL Shell: Admin API mysql-js>dba.help() the global variable dbais used to access the MySQL API performDBA operations to manage MySQL InnoDB Cluster: create cluster Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 107 / 126

MySQL Shell: Admin API mysql-js>dba.help() the global variable dbais used to access the MySQL API performDBA operations to manage MySQL InnoDB Cluster: create cluster deploy MySQL instances (in sandbox only for the moment) Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 108 / 126

MySQL Shell: Admin API mysql-js>dba.help() the global variable dbais used to access the MySQL API performDBA operations to manage MySQL InnoDB Cluster: create cluster deploy MySQL instances (in sandbox only for the moment) get cluster info Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 109 / 126

MySQL Shell: Admin API mysql-js>dba.help() the global variable dbais used to access the MySQL API performDBA operations to manage MySQL InnoDB Cluster: create cluster deploy MySQL instances (in sandbox only for the moment) get cluster info start/stop MySQL instances Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 110 / 126

MySQL Shell: Admin API mysql-js>dba.help() the global variable dbais used to access the MySQL API performDBA operations to manage MySQL InnoDB Cluster: create cluster deploy MySQL instances (in sandbox only for the moment) get cluster info start/stop MySQL instances validate MySQL instances Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 111 / 126

mysqlprovision The mysqlprovision utility is designed to facilitate the management of MySQL Group Replication, allowing users to start a replica set (a newreplication group), to add/remove members to/froman existing group. It can also check if an instance meets all the requirements to successfully create a newgroup or to be added to an existing group. This utility is also capable of modifying MySQL option files of existing instances in order for themto meet the requirements of Group Replication. Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 112 / 126

MySQL Router MySQL Shell MySQL Group Replication MySQL Server MySQL InnoDB Cluster So MySQL InnoDB Cluster is composed by: CON2907 - MySQL High Availability - MattLord- Wed 12:15 Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 117 / 126

MySQL Router Transparent client connection routing (TCP level 4) load balancing connection failover Native support for InnoDB Clusters understands Group Replication topology utilizes metadata schema stored on each members bootstrap and auto-config supports multi-master and single primary modes Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 118 / 126

Any other alternatives ? It's possible to use MySQL Group Replication with other proxies/load-balancers HAProxy : http://lefred.be/content/mysql-group-replication-as-ha-solution/ Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 123 / 126

Join us! Register: http://tinyurl.com/mysqloow16 MySQL Community Reception @ Oracle OpenWorld Tuesday, September 20, 7.00 pm Jillian’s at Metreon: 175 Fourth Street, San Francisco At the corner of Howard and 4th st.; only 2-min walk fromMoscone Center (same place as last year) Copyright @ 2016 Oracle and/or its affiliates. All rights reserved. 126 / 126

MySQL InnoDB Cluster - Group Replication

More Related Content

What's hot

Viewers also liked

Similar to MySQL InnoDB Cluster - Group Replication

More from Frederic Descamps

Recently uploaded

MySQL InnoDB Cluster - Group Replication