Percona XtraDB Cluster 8.0 (PXC-8.0)

1 .Percona XtraDB Cluster 8.0 Krunal Bauskar (PXC Product Lead, Percona) 

2 .Quick Note About Myself ● Database enthusiast. ● Working on MySQL for more than decade. ● Wide interest in data handling and management. ● During my tenure at Yahoo!, Teradata worked on some of the real big-data problems. ● Developed multiple features in InnoDB during 5.7 tenure while working for MySQL/Oracle. 

3 .Agenda ● Quick note on “What is Percona XtraDB Cluster (PXC)”? ● Current supported versions of PXC. ● MySQL/PS-8.0 features and how they affect the PXC-8.0. ● What’s new with the PXC-8.0? ● What more to expect with the PXC-8.0? ● Q&A 

4 .Percona XtraDB Cluster (PXC) Enhanced Multi-master Security Network Flexible protection topology (Geo-distributed) Scaling up Performance and down tuned 

5 .Current Supported Versions of PXC PXC-5.5 PXC-5.6 PXC-5.7 PXC-8.0 Under EOL GA GA development Latest GA version: ● PXC-5.6.43 ● PXC-5.7.25 Under-development: ● PXC-8.0.15 

6 .MySQL/PS-8.0 New Features 

7 .MySQL/PS-8.0 New Features ● Atomic DDL ● Data dictionary ● Introduction of ROLES, RESOURCE GROUP ● CATS scheduling algorithm ● Encryption support ● Cloud compatible features (SET PERSIST) …. 

8 .Resource Group and PXC-8.0 

9 .Resource Group and PXC-8.0 ● Resource Group helps grouping of the available resources like vCPU, etc. vCPU vCPU vCPU ● MySQL threads can be assigned to a resource group for execution. vCPU vCPU ● Administrator can even associate single statement to a given resource group. (Say an important high priority query needs to get executed can be assigned vCPU vCPU vCPU to a specialized resource group). 

10 .Resource Group and PXC-8.0 ● Given resources are local to a node Resource group RESOURCE GROUP statements are statement not replicated (create/alter/drop). ● Admin can now configure a RESOURCE GROUP for applier threads as actions of applier threads needs to run with HIGH PRIORITY. (Can help reduce Flow-Control). 

11 .Roles and PXC-8.0 

12 .Roles and PXC-8.0 ● MySQL-8.0 introduced ROLES to facilitate developer app-user user and privilege management. (CREATE/DROP/REVOKE/GRANT/….) backup auditor admin ● ROLES management statements are replicated across the PXC cluster. ● MySQL needs ROLES to be activated before use. PXC replicates ROLE ACTIVATION too (done through SET command) node-1 node-2 

13 .Roles and PXC-8.0 ● PXC is already working on pre-defining some of the role for general action. ● Like we tried to define mysql.pxc.sst.role that can be assigned to backup-user (needed for SST)*. 

14 .Atomic DDL and PXC-8.0 

15 .Atomic DDL and PXC-8.0 ● MySQL-8.0 introduced support for Atomic DDL. ● PXC-5.7 replicates non-atomic statement (like DDL) through TOI and atomic statement (like DML) through write-set based replication [allowing DML transaction to rollback and killed]. ● PXC-8.0 continue to replicate DDL through TOI too. 

16 .Atomic DDL and PXC-8.0 Important difference to note: ● 2 tables t1 (local to n1) and t2 (replicated on n1 and n2) drop table t1, t2; on-n1 on-n2 PXC-5.7 t1, t2 dropped t2 dropped PXC-8.0 t1, t2 dropped no-table-dropped 

17 .Atomic DDL and PXC-8.0 ● Eventually, PXC plans to make use DDL of atomic nature of DDL and execute the DDL through write-set DDL DDL replication too. write-set replication ● This will also take-care of blocking DDL issue that exist due to TOI based execution. TOI/RSU 

18 .Non-Atomic DDL and PXC-8.0 

19 .Non-Atomic DDL and PXC-8.0 ● MySQL-8.0 continue to support some non-atomic DDL operations ○ OPTIMIZE ○ REPAIR ○ ANALYZE TOI based replication ● PXC continue to support them and there OPTIMIZE is no change in semantics of these REPAIR non-atomic DDLs. ANALYZE CREATE/DROP 

20 .SRS (Spatial Reference System) and PXC-8.0 

21 .SRS (Spatial Reference System) and PXC-8.0 ● MySQL-8.0 introduced SRS support. SRS can be created and dropped using CREATE/DROP command. ● PXC cluster replicates these SRS commands. 

22 .XID Inconsistency and PXC-8.0 

23 .XID Inconsistency and PXC-8.0 ● Now that DDL are transactional, MySQL assigns xid to each DDL statement. ● MySQL has its own logic to assign XID and PXC (wsrep) has its own logic to assign XID. ● With 2 sub-systems in place it can cause duplicate XID generation that can eventually cause problem with recovery (especially with new improved recovery logic of MySQL-8.0 that checks for unique XID). 

24 .XID Inconsistency and PXC-8.0 | mysql-bin.000001 | 3027 | Gtid | 2 | 3092 | SET @@SESSION.GTID_NEXT= '1bc0f40c-8930-ee16-770b-15117ed32fd0:12' | .... | mysql-bin.000001 | 3248 | Xid | 2 | 3279 | COMMIT /* xid=12 */ | mysql-bin.000001 | 759 | Gtid | 2 | 824 | SET @@SESSION.GTID_NEXT= '9725d6d2-5ff7-11e9-a3b4-9cb6d0ba1a1d:1' | .... | mysql-bin.000001 | 980 | Xid | 2 | 1011 | COMMIT /* xid=12 */ 

25 .XID Inconsistency and PXC-8.0 ● While running in cluster-mode: ○ Only PXC sub-system will be used for assigning XID. ○ Statement that are not replicated (ALTER TABLE DISCARD/IMPORT TABLESPACE) are not logged to binlog. ○ If session turns off cluster-mode (wsrep_on=off) then it will automatically set sql_log_bin=off too. ● This ensures consistent XID generation from PXC sub-system avoiding XID inconsistency. 

26 .DD-tables Explicit Locks and PXC-8.0 

27 .DD-tables Explicit Locks and PXC-8.0 ● PXC force-abort the local running transaction in favor of high-priority replicated transaction Brute-Force-Abort (BFA). 

28 .DD-tables Explicit Locks and PXC-8.0 ● If the local session is holding an explicit lock (not-recommended in PXC setup) through FLUSH TABLE WITH READ LOCK, etc… then such local transactions are non-preemptable. Background high-priority thread has to wait. 

29 .DD-tables Explicit Locks and PXC-8.0 ● With MySQL-8.0, DD-tables modification too establishes EXPLICIT locks as part of statement execution. ● PXC tag these MDL locks as preemptable and transaction holding these locks can be force-aborted.