MySQL at Scale at Square Daniel Nichter Percona Live 2018 1 April - - PowerPoint PPT Presentation

▶
mysql at scale at square
SMART_READER_LITE
LIVE PREVIEW

MySQL at Scale at Square Daniel Nichter Percona Live 2018 1 April - - PowerPoint PPT Presentation

Apr 30, 2023 356 likes •843 views

MySQL at Scale at Square Daniel Nichter Percona Live 2018 1 April 23, 2018 An honest financial network for everyone Square Global: USA, Canada, UK, Japan, Australia Payment transaction data stored in MySQL We are hiring

slide-1
SLIDE 1

Daniel Nichter Percona Live 2018

MySQL at Scale at Square

April 23, 2018 1

slide-2
SLIDE 2
  • An honest financial network for everyone
  • Global: USA, Canada, UK, Japan, Australia
  • Payment transaction data stored in MySQL

Square

About Square 2

  • We are hiring (square.com/careers)
slide-3
SLIDE 3
  • >4,000 unique MySQL instances

○ Percona Server 5.6, RBR, GTID

  • 99.95% SLA

○ 43s day, 5m week, 21m month, 4h 23m year

  • ~1 PB of data
  • ~800 physical servers
  • ~300 microservices (apps)

The Numbers

MySQL at Square by the Numbers 3

slide-4
SLIDE 4

Theme

Theme: Pebbles, Not Boulders 4

Pebbles, Not Boulders

slide-5
SLIDE 5

Theme

Theme: Pebbles, Not Boulders 5

Pebbles, Not Boulders

slide-6
SLIDE 6

Topics

  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice

Topics of Discussion 6

slide-7
SLIDE 7
  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice
slide-8
SLIDE 8

Production Database Cluster 8

01 02 03 04 B-side A-side US West US East

slide-9
SLIDE 9

Benefits of this Topology 9

01 02 03 04 B-side A-side US West US East

  • It works
  • Simple, fewest moving parts
  • Balance cost, redundancy, complexity
  • Can add more read replicas
  • Inactive side for:

○ Backups ○ Maintenance ○ Upgrade/patch ○ Ad hoc queries by humans ○ No SLA

  • Shard and scale out (app)
slide-10
SLIDE 10

Theme

Theme: Pebbles, Not Boulders 10

Pebbles, Not Boulders

01 02 03 04

B-side A-side US West US East

slide-11
SLIDE 11
  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice
slide-12
SLIDE 12

RW CNAME and SIP 12

01 02 03 04 B-side A-side RW CNAME RW SIP

slide-13
SLIDE 13

RW CNAME and SIP flipped 13

01 02 03 04 B-side A-side RW CNAME RW SIP

slide-14
SLIDE 14

Node 02 Failure 14

01 03 04 B-side A-side RW CNAME RW SIP 02 Clone from 03 Clone from 02

slide-15
SLIDE 15

B-side Rebuilt 15

01 02 03 04 B-side A-side RW CNAME RW SIP

slide-16
SLIDE 16

Primary DC Failure 16

01 03 04 B-side A-side RW CNAME RW SIP 02 Clone from 04 Clone from 02

GTID

slide-17
SLIDE 17

Cluster Running In DR DC 17

01 03 04 B-side A-side RW CNAME RW SIP 02

slide-18
SLIDE 18

DR Successover Back To Primary DC 18

01 03 04 B-side A-side RW CNAME RW SIP 02

slide-19
SLIDE 19

Scaling Out

9 Production MySQL Clusters 19

01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02

slide-20
SLIDE 20

Theme

Theme: Pebbles, Not Boulders 20

Pebbles, Not Boulders

01 03 04 02 01 03 04 02 01 03 04 02 01 03 04 02

slide-21
SLIDE 21
  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice
slide-22
SLIDE 22

Backups

Percona XtraBackup for Backups 22

Percona XtraBackup

slide-23
SLIDE 23
  • Percona XtraBackup
  • Full daily on all inactive nodes
  • Encrypted
  • Monitored (we know if backups do not run)
  • Most stored locally on separate RAID array; some uploaded to cloud
  • Ruby script to wrap XtraBackup, monitoring, etc.
  • Restore verified via rebuilding failed nodes

Backups

Backups at Square 23

slide-24
SLIDE 24

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 24

slide-25
SLIDE 25

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 25

slide-26
SLIDE 26

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 26

slide-27
SLIDE 27

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 27

slide-28
SLIDE 28

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 28

slide-29
SLIDE 29

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 29

slide-30
SLIDE 30

ulimit -n 262144 && umask 0227 && \ innobackupex \

  • -defaults-extra-file=#{backup_my.cnf} \
  • -slave-info \
  • -safe-slave-backup \
  • -databases=<db list file> \
  • -stream=xbstream \
  • -parallel=4 \

#{tmpdir} \ | pigz --fast -p 1 -c \ | gpg2 --batch --no-options -o #{dst} \ | tee >(md5sum > #{file}.md5) \ | sudo -u mysql split -a 2 --bytes=4096m - #{file}-

innobackupex command 30

slide-31
SLIDE 31
  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice
slide-32
SLIDE 32
  • App-specific MySQL clusters

Access and Security

Access and Security 32

slide-33
SLIDE 33
  • App-specific MySQL clusters

Access and Security

Access and Security 33

01 02 03 04

App: foo foo-001

01 02 03 04

foo-002

01 02 03 04

bar-001

slide-34
SLIDE 34
  • App-specific MySQL clusters
  • TLS for all connections and auth (no passwords)

Access and Security

Access and Security 34

slide-35
SLIDE 35
  • App-specific MySQL clusters
  • TLS for all connections and auth (no passwords)

Access and Security

Access and Security 35

GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'%' REQUIRE ISSUER '/CN=Square/O=Square Inc./ST=California/L=San Francisco' SUBJECT '/CN=daniel/serialNumber=1'

slide-36
SLIDE 36
  • App-specific MySQL clusters
  • TLS for all connections and auth (no passwords)
  • Rotating accounts: human, app, system

Access and Security

Access and Security 36

slide-37
SLIDE 37
  • App-specific MySQL clusters
  • TLS for all connections and auth (no passwords)
  • Rotating accounts: human, app, system
  • Isolated security zones: directional, no cross-talk, no hardware reuse

Access and Security

Access and Security 37

slide-38
SLIDE 38
  • App-specific MySQL clusters
  • TLS for all connections and auth (no passwords)
  • Rotating accounts: human, app, system
  • Isolated security zones: directional, no cross-talk, no hardware reuse

Access and Security

Access and Security 38

01 02 03 04 01 02 03 04

Blue Zone Red Zone

INBOUND TCP 3306 OUTBOUND

slide-39
SLIDE 39
  • App-specific MySQL clusters
  • TLS for all connections and auth (no passwords)
  • Rotating accounts: human, app, system
  • Isolated security zones: directional, no cross-talk, no hardware reuse
  • Auditing, "paper" trails, etc.

Access and Security

Access and Security 39

slide-40
SLIDE 40
  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice
slide-41
SLIDE 41

Monitoring and Alerting

SignalFx and PagerDuty 41

slide-42
SLIDE 42
  • SignalFx and PagerDuty
  • Local and remote monitoring agents (custom)
  • Query metrics (custom; use PMM or SaaS solution if possible)
  • MySQL instances, cluster alive and correct, backups → DBAs
  • Replication lag, et al. → app owners
  • Monitoring database vs. app

Monitoring and Alerting

Monitoring and Alerting 42

slide-43
SLIDE 43
  • 1. Setup and Replication
  • 2. High Availability
  • 3. Backups
  • 4. Access and Security
  • 5. Monitoring and Alerting
  • 6. Advice
slide-44
SLIDE 44

✗ Everything will fail ✗ Manual processes ✗ Fancy replication ✗ Fleet-wide ops (boulders) ✗ Go it alone ✗ Alert DBAs on app-specific metrics ✗ Blindly trust new versions ✗ MySQL as queue or cache

Advice

Advice for Running MySQL at Scale 44

slide-45
SLIDE 45

✓ Test HA situations in production ✓ Shard from the beginning (apps) ✓ Limit shard sizes (apps) (1 TB @ 1 Gbps @ 90% efficiency = 2.5 hour transfer) ✓ Small clusters ✓ Automate everything ✓ Monitoring "MySQL up and correct" ✓ Query metrics (PMM, VividCortex, …) ✓ pt-table-checksum

Advice

Advice for Running MySQL at Scale 45

slide-46
SLIDE 46

Theme

Theme: Pebbles, Not Boulders 46

Pebbles, Not Boulders

slide-47
SLIDE 47

square.com

Thank You