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

▶
how we managed to scale percona xtradb cluster pxc
SMART_READER_LITE
LIVE PREVIEW

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

Apr 04, 2023 15 likes •397 views

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

slide-1
SLIDE 1

Krunal Bauskar PXC Product Lead @ Percona

How we managed to scale Percona XtraDB Cluster (PXC)

slide-2
SLIDE 2

Agenda

▪ PXC Performance

▪ Performance fixes

slide-3
SLIDE 3

PXC Performance

slide-4
SLIDE 4

PXC Performance

3x-10X

Improvement

slide-5
SLIDE 5

PXC Performance

slide-6
SLIDE 6

DML execution in MySQL

OPTIMIZE REWRITE EXECUTE COMMIT PARSE

slide-7
SLIDE 7

DML execution in MySQL

PARSE OPTIMIZE REWRITE EXECUTE COMMIT PREPARE COMMIT

slide-8
SLIDE 8

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

  • f fsync and achieve better throughput.

1. FLUSH BINARY LOG (also flushed REDO log) 2. SYNC BINARY LOG 3. COMMIT TRANSACTION in Storage Engine

slide-9
SLIDE 9

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 seqno=3 seqno=4

Enforcement is done using commit

  • rdering
slide-10
SLIDE 10

Commit Protocol

  • Binary Log enabled

○ Old Commit Protocol ○ New Commit Protocol

  • Binary Log disabled

○ Old Commit Protocol ○ New Commit Protocol

slide-11
SLIDE 11

Old-Commit Protocol - Binary log enabled

slide-12
SLIDE 12

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor pxc action mysql action prepare phase commit phase

slide-13
SLIDE 13

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor MySQL GROUP COMMIT starts with enqueuing of transaction pxc action mysql action prepare phase commit phase

slide-14
SLIDE 14

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory Exit CommitMonitor pxc action mysql action prepare phase commit phase

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

slide-15
SLIDE 15

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

pxc action mysql action prepare phase commit phase REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory Exit CommitMonitor

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

slide-16
SLIDE 16

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor pxc action mysql action prepare phase commit phase

slide-17
SLIDE 17

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor pxc action mysql action prepare phase commit phase

slide-18
SLIDE 18

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor pxc action mysql action prepare phase commit phase

slide-19
SLIDE 19

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor pxc action mysql action prepare phase commit phase

slide-20
SLIDE 20

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor pxc action mysql action prepare phase commit phase

slide-21
SLIDE 21

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor Commit Monitor will allow

  • nly one transaction to enter

through it and there-by

SERIALIZING

all the following steps. Each transaction results in

  • fsync. No batch commit

advantage explored.

5 steps critical section

slide-22
SLIDE 22

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor

GOAL is to maintain

COMMIT ORDERING

5 steps critical section

slide-23
SLIDE 23

Old-Commit Protocol - Binary log enabled

  • Binary Logging is enabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

Since binary log is flushed, REDO log capturing commit

  • f transaction can be

flushed later.

Exit CommitMonitor

MySQL already enforces COMMIT ORDERING and also takes advantage of batch commit using

GROUP COMMIT PROTOCOL

5 steps critical section

slide-24
SLIDE 24

New-Commit Protocol - Binary log enabled

slide-25
SLIDE 25

New-Commit Protocol - Binary log enabled

  • Help accelerate both paths (binary log enabled/disabled)

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory Exit CommitMonitor

CHANGE-1

replicate and commit ordering doesn’t need to go together. commit

  • rdering

can be enforced after transaction has update UNDO state to prepare.

4 steps critical section

slide-26
SLIDE 26

New-Commit Protocol - Binary log enabled

  • Help accelerate binary log enabled path

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Interim Commit Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

CHANGE-2

Grab commit monitor immediately after PREPARE and release it once transaction is added to GROUP COMMIT QUEUE.

1 steps critical section

slide-27
SLIDE 27

New-Commit Protocol - Binary log enabled

  • Help accelerate binary log enabled path

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

1 steps critical section

slide-28
SLIDE 28

New-Commit Protocol - Binary log enabled

  • Help accelerate binary log enabled path

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Interim Commit Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory

We still need to enter and exit CommitMonitor to enforce ordering of transaction based on the global seqno assigned them during replicate.

1 steps critical section

slide-29
SLIDE 29

Old-Commit Protocol - Binary log disabled

slide-30
SLIDE 30

Old-Commit Protocol - Binary log disabled

  • Binary Logging is disabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory Exit CommitMonitor

REPLICATE to group-channel Enter CommitMonitor

Update UNDO log to reflect PREPARE state

Flush/Sync REDO Log

Commit InnoDB transaction in memory

Flush/Sync REDO Log Exit CommitMonitor

4 steps critical section

slide-31
SLIDE 31

Old-Commit Protocol - Binary log disabled

  • Binary Logging is disabled

REPLICATE to group-channel Enter CommitMonitor Update UNDO log to reflect PREPARE state Flush/Sync REDO Log Flush BINARY Log Sync BINARY Log Commit InnoDB transaction in memory Exit CommitMonitor

REPLICATE to group-channel Enter CommitMonitor

Update UNDO log to reflect PREPARE state

Flush/Sync REDO Log

Commit InnoDB transaction in memory

Flush/Sync REDO Log

  • Flushing REDO log twice
  • First time: PREPARE stage
  • Second time: COMMIT stage

4 steps critical section

Exit CommitMonitor

slide-32
SLIDE 32

New-Commit Protocol - Binary log disabled

slide-33
SLIDE 33

New-Commit Protocol - Binary log disabled

  • Binary Logging is disabled

REPLICATE to group-channel Enter CommitMonitor

Update UNDO log to reflect PREPARE state

Flush/Sync REDO Log

Commit InnoDB transaction in memory

Flush/Sync REDO Log

CHANGE-1

replicate and commit ordering doesn’t need to go together. commit

  • rdering

can be enforced after transaction has update UNDO state to prepare. This allow first REDO flush to go in parallel.

2 steps critical section

Exit CommitMonitor

slide-34
SLIDE 34

New-Commit Protocol - Binary log disabled

  • Binary Logging is disabled

REPLICATE to group-channel Enter CommitMonitor

Update UNDO log to reflect PREPARE state

Flush/Sync REDO Log

Commit InnoDB transaction in memory

Flush/Sync REDO Log Interim Commit

CHANGE-2

Final FLUSH of REDO log doesn’t need Commit Ordering enforced as the transaction is already committed and REDO log advances sequentially.

1 steps critical section

slide-35
SLIDE 35

Summarize

  • Change-1: Delay entering into Commit Monitor (enter only when needed)
  • Change-2: Release Commit Monitor once transaction is queue in Group Commit

Protocol Queue

  • Change-3: Commit ordering is not needed for final REDO flush (log-bin=off).

All this and more in PERCONA-XTRADB-CLUSTER-5.7.17+ (Latest Version: 5.7.21). Give it a try

slide-36
SLIDE 36

…

▪ mail me: krunal.bauskar@percona.com ▪ PXC forum https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster ▪ PXC @ JIRA https://jira.percona.com/projects/PXC/issues

Q&A RATE THE TALK

slide-37
SLIDE 37

37

Thank You Sponsors!!

slide-38
SLIDE 38

DATABASE PERFORMANCE MATTERS