PhxSQL: A High-Availability & Strong-Consistency MySQL Cluster - PowerPoint PPT Presentation

PhxSQL: A High-Availability & Strong-Consistency MySQL Cluster Ming CHEN@WeChat

Why PhxSQL Highly expected features for MySql cluster

Availability and consistency in MySQL cluster SVR A • Master-slaves replication MySQL (master) client Binlog • Replication solutions … 3: z 2: y 1: x • Fully synchronous pull pull Innodb SVR C SVR B -|--|- -|--|- • Semi-synchronous MySQL MySQL (slave) (slave) • Asynchronous Binlog Binlog … … 3: z 3: z 2: y 2: y • Third-party aided failover 1: x 1: x Innodb Innodb -|--|- -|--|- • Zookeeper/Admin/… -|--|- -|--|- 3

Call for both high-availability and strong- consistency • Critical Applications demand both high-availability and strong-consistency • accounts/financial transactions/… • Support for both high-availability and strong-consistency • greatly simplifies system design • makes correctness-reasoning easier 4

A new MySQL cluster supports the same high- availability and strong-consistency as Zookeeper does PhxSQL 5

What is PhxSQL High-availability & strong-consisteny MySQL cluster

Key PhxSQL features • Support full compatibility with MySQL • Support high availability and linearizable consistency • Support deployment in wide-area network • Support online reconfiguration of cluster membership 7

Support full compatibility with MySQL • Transparent to MySQL clients • Support full features of MySQL • Even serializable level of transaction isolation • Minimum intrusive change to MySQL source code modify after_flush add before_recoverty 8

Support high availability • Fully automatic failover within configurable seconds • A cluster works well when more than half of cluster servers still function 9

Support linearizable consistency • A cluster of PhxSQL seems to be a single MySQL server to MySQL clients concurrently accessing it • PhxSQL supports the same consistency level as strong as Zookeeper does! 10

Support online reconfiguration of cluster membership • Add a new server, remove an old server, or replace an old server with a new one in an atomic fashion while still serving read/write requests • Be easier for maintenance and more friendly to clients 11

How PhxSQL Works MySQL cluster powered by Paxos

Paxos maintains consistent states across servers by enforcing same operations on all servers when more than half of servers work SVR A SVR B SVR C int X=0; int X=0; int X=0; int Y=0; int Y=0; int Y=0; 1: R1: Foo(1, 2) 2: T1: Foo(3, 4) 3: S1: Bar(1, 10) void Foo(int a, int b); void Foo(int a, int b); void Foo(int a, int b); 4: R2: Bar(3, 30) bool Bar(int a, int b); bool Bar(int a, int b); bool Bar(int a, int b); … God invoke: R1, T1, S1, R2, … invoke: R1, T1, S1, R2, … invoke: R1, T1, S1, R2, … Paxos Paxos Paxos Foo(1, 2); Bar(3, 30) Bar(1, 10) Foo(3, 4) 13 Client R Client S Client T

PhxSQL enforces a simple but effective constraint by Paxos • Constraint: two MySQL servers have SAME states if and only if they have SAME binlog • Enforcement • PhxSQL maintains a GLOBAL consistent binlog by Paxos • Every MySQL server aligns its LOCAL binlog to the GLOBAL one. 14

SVR A MySQL cluster MySQL (master) client Binlog … 3: z 2: y 1: x pull SVR B SVR C Innodb -|--|- -|--|- MySQL MySQL (slave) (slave) pull Binlog Binlog … … 3: z 3: z 2: y 2: y 1: x 1: x Innodb Innodb -|--|- -|--|- -|--|- -|--|- 15

SVR A Phx PhxSQL 1. Paxos detects failure and elects new master by leasing Proxy and periodic heartbeat 2. Clients access master MySQL transparently via PhxProxy Phx MySql who forwards requests to current master BinlogSvr (master) 3. MySql master appends local binlog to global consistent PhxBinlog maintained by Paxos client Binlog 4. MySql slaves pull binlog from global consistent … PhxBinlog 3: z Paxos 2: y forward 1: x Phx SVR B SVR C Plugin Phx Phx Phx Binlog Proxy Proxy … Innodb 3: z -|--|- 2: y Phx Phx -|--|- MySql MySql 1: x BinlogSvr BinlogSvr (slave) (slave) Binlog Binlog … … 3: z 3: z Paxos Paxos 2: y 2: y 1: x 1: x pull pull Phx Phx Plugin Plugin Phx Phx Binlog Binlog … … Innodb Innodb 3: z 3: z -|--|- -|--|- 2: y 2: y -|--|- -|--|- 16 1: x 1: x

Paxos maintains consistent states in the cluster PhxBinlogSvr A PhxBinlogSvr B PhxBinlogSvr C master_ver master_ver master_ver SM SM SM master_id master_id master_id 1: R1: C1(1, R) binlog_queue 2: T1: C1(1, T) binlog_queue binlog_queue 3: S1: C1(1, S) C1: bool change_master(ver, new_master); 4: R2: C2(…) C1: bool change_master(ver, new_master); C1: bool change_master(ver, new_master); C2: int app_binlog(binlog_id,binlog); C2: int app_binlog(binlog_id,binlog); C2: int app_binlog(binlog_id, binlog); … God Paxos Paxos Paxos C1(1, R); C2(“ R_xxx ”, “insert foo=2 into table_1”) C1(1, S) C1(1, T) 17 PhxPlugin:Client S PhxPlugin:Client R PhxPlugin:Client T

How to Use PhxSQL Easy to integrate into existing system

Case 1: non-intrusive change to existing system • Assume there some kinds of naming service that tells the current master in existing system • Zookeeper/DNS/configuration file/... • Develop a new daemon program to learn the change of master in PhxSQL cluster and update information in the naming service accordingly • Safety: PhxSQL ensures consistency even the master information in naming service is stale or MySQL clients connects to a slave daemon I. learn new master PhxSQL master II. update 10.1.1.1 client 2. request 1. get ip of master slave slave naming service 10.1.1.2 10.1.1.3 master ip: 10.1.1.1 19

Case 2: minimal intrusive change to existing system • Traditional invocation scenario 1. Read the IP of master from configuration file 2. Get MySQL handler by calling mysql_real_connect(MYSQL *mysql, const char * IP , …) with the IP 3. Invoke other MySQL client API with the handler • PhxSQL invocation scenario 1. Read the IP list of PhxSQL cluster servers from configuration file 2. Get MySQL handler by calling PhxSQLClientBase::Connect() with the IP list and then PhxSQLClientBase::GetMySQLFD() 3. Invoke other MySQL client API with the handler 20

Performance On par with MySQL semi-sync replication

Performance test settings: PhxSQL vs. MySQL Semi-sync • 3 servers: Intel(R) Xeon(R) CPU E5-2420 @ 1.90GHz * 2, 32G memory, SSD Raid10, 1000M NIC • Network ping latency: master->slave: 3ms; client->master: 4ms • Percona 5.6.31-77.0 • Master of MySQL semi-sync replication waits for only ONE ack • Test tool and parameters: sysbench --oltp-tables-count=10 --oltp-table- size=1000000 --num-threads=500 --max-requests=100000 --report-interval=1 -- max-time=200 22

QPS on par 200 co concu curre rent cl clien ent t thread ads PhxSQL MySQL 47528 46334 25657 20391 5076 4055 insert.lua select.lua OLTP.lua (100% write) (0% write) (20% write) 500 co concu curre rent cl clien ent t thread ads PhxSQL MySQL 121535 105928 33229 46543 8260 7072 insert.lua select.lua OLTP.lua 23 (100% write) (0% write) (20% write)

Response time (ms) on par 200 concurrent client threads PhxSQL MySQL 176.39 140.16 49.27 39.34 4.21 4.1 insert.lua select.lua OLTP.lua (100% write) (0% write) (20% write) 500 00 concurrent client threads PhxSQL MySQL 270.38 192.93 70.6 60.41 4.58 4.17 insert.lua select.lua OLTP.lua 24 (100% write) (0% write) (20% write)

Current progress • Deployed in WeChat account system • WeChat account: 889M monthly active users • Support for Percona 5.7 in progress • Open source: https://github.com/tencent-wechat/phxsql 25