How we managed to scale Percona XtraDB Cluster (PXC) Krunal Bauskar - PowerPoint PPT Presentation

How we managed to scale Percona XtraDB Cluster (PXC) Krunal Bauskar PXC Product Lead @ Percona

Agenda ▪ PXC Performance ▪ Performance fixes

PXC Performance

PXC Performance 3x-10X Improvement

PXC Performance

DML execution in MySQL PARSE OPTIMIZE REWRITE EXECUTE COMMIT

DML execution in MySQL PARSE OPTIMIZE REWRITE PREPARE EXECUTE COMMIT COMMIT

MySQL Group Commit Protocol ● MySQL Group Commit protocol Batch transactions in group and let leader perform the action while followers wait. ○ ○ Batching actions like flush, sync, commit (including redo flush) help reduce number of fsync and achieve better throughput. 1. FLUSH BINARY LOG (also flushed REDO log) 2. SYNC BINARY LOG 3. COMMIT TRANSACTION in Storage Engine

PXC Commit Monitor ● PXC follow strict commit ordering Transaction that commits first to the group channel should be first to get ○ committed too. ○ Each Transaction is assigned a unique identifier when it replicates on group channel seqno=1 seqno=2 Enforcement is done using commit seqno=3 ordering seqno=4

Commit Protocol ● Binary Log enabled ○ Old Commit Protocol ○ New Commit Protocol ● Binary Log disabled ○ Old Commit Protocol ○ New Commit Protocol

Old-Commit Protocol - Binary log enabled

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state MySQL GROUP COMMIT starts with enqueuing of Commit InnoDB transaction Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit InnoDB Exit Sync BINARY transaction in CommitMonitor Log memory pxc action Since binary log is flushed, mysql action REDO log capturing commit of transaction can be prepare phase flushed later. commit phase

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled 5 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state Commit Monitor will allow only one transaction to enter Commit InnoDB through it and there-by Exit Sync BINARY transaction in SERIALIZING CommitMonitor Log memory all the following steps. Since binary log is flushed, Each transaction results in REDO log capturing commit fsync. No batch commit of transaction can be advantage explored. flushed later.

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled 5 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state GOAL is to maintain COMMIT Commit InnoDB Exit Sync BINARY transaction in ORDERING CommitMonitor Log memory Since binary log is flushed, REDO log capturing commit of transaction can be flushed later.

Old-Commit Protocol - Binary log enabled ● Binary Logging is enabled 5 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state MySQL already enforces COMMIT ORDERING and also takes advantage of batch Commit InnoDB Exit Sync BINARY transaction in commit using CommitMonitor Log memory GROUP COMMIT PROTOCOL Since binary log is flushed, REDO log capturing commit of transaction can be flushed later.

New-Commit Protocol - Binary log enabled

New-Commit Protocol - Binary log enabled ● Help accelerate both paths (binary log enabled/disabled) 4 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync Flush BINARY to reflect PREPARE group-channel CommitMonitor REDO Log Log state CHANGE-1 replicate and commit ordering Commit InnoDB Exit Sync BINARY doesn’t need to go together. transaction in CommitMonitor Log commit ordering can be memory enforced after transaction has update UNDO state to prepare.

New-Commit Protocol - Binary log enabled ● Help accelerate binary log enabled path 1 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync to reflect PREPARE Interim Commit group-channel CommitMonitor REDO Log state CHANGE-2 Grab commit monitor Commit InnoDB Sync BINARY Flush BINARY immediately after PREPARE transaction in Log Log memory and release it once transaction is added to GROUP COMMIT QUEUE.

New-Commit Protocol - Binary log enabled ● Help accelerate binary log enabled path 1 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync to reflect PREPARE Interim Commit group-channel CommitMonitor REDO Log state Each transaction is added to Commit InnoDB commit queue maintained by Sync BINARY Flush BINARY transaction in MySQL Group Commit Protocol. Log Log memory Group Commit enforces ordering so Commit Monitor is no more needed to enforce the same post this point.

New-Commit Protocol - Binary log enabled ● Help accelerate binary log enabled path 1 steps critical section Update UNDO log REPLICATE to Enter Flush/Sync to reflect PREPARE Interim Commit group-channel CommitMonitor REDO Log state We still need to enter and Commit InnoDB exit CommitMonitor to Sync BINARY Flush BINARY transaction in Log Log enforce ordering of memory transaction based on the global seqno assigned them during replicate.

Old-Commit Protocol - Binary log disabled

Old-Commit Protocol - Binary log disabled REPLICATE to Enter Update UNDO log to Flush/Sync REDO Flush BINARY Log group-channel CommitMonitor reflect PREPARE state Log ● Binary Logging is disabled Commit InnoDB Exit CommitMonitor transaction in Sync BINARY Log memory Commit InnoDB REPLICATE to Enter Flush/Sync REDO Update UNDO log to transaction in group-channel CommitMonitor reflect PREPARE state Log memory Exit Flush/Sync REDO CommitMonitor Log 4 steps critical section