Orchestrating Proxysql with OCP Avi Apelbaum, Wix aviap@wix.com - PowerPoint PPT Presentation

Orchestrating Proxysql with OCP Avi Apelbaum, Wix aviap@wix.com linkedin/aviapelbaum github.com/wix

Hi. I am Eyal DBA Team Leader @WIx.com

Wix in a Minute

Our main production DBs Version 5.6.x & 5.7.x 3.2.12 3.0.9 DB instances ~574/30TB ~174/1.7TB ~43/Few GB Most of them are multiple instances on a single physical server All DB’s are replicated with at least 2 DC’s replicas

Our DB Clusters Austin Google Amsterdam Amazon Main masters DC Used now for read-only dev access Tampa In 2 availability zones Secondary masters DC Austin & Tampa DCs have two instances for HA Most instances have dedicated BI slaves in Austin

Hi. I am Avi DBA & DevOPS @WIx.com

Standard Solutions for Standard Problems AGENDA Network Split Architecture Case OCP

01 Standard Solutions for Standard Problems

How we manage ~600 mysql nodes

How we manage ~600 mysql nodes

Orchestrator github.com/github/orchestrator

How we manage failover of ~600 machines

How we manage failover of ~600 machines

Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com

Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com Knows your MySQL topology Handles failover

Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com Knows your MySQL topology Handles failover Master-Slave What happens changes here?

Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com Knows your MySQL topology Handles failover Master-Slave Manual changes configuration Someone wakes up in the middle of the night

~1.5 min failover time Service malfunctioning Master-Slave Manual changes configuration Someone wakes up in the middle of the night

02 Network Split Architecture Case

Replication of data between 2 DCs DC 1 DC 2 ProxySQL Orchestrator Orchestrator ProxySQL Master Master Slave Slave Slave Slave

In case of a network split we have to stop reads to one of the DCs DC 1 DC 2 ProxySQL Orchestrator Orchestrator ProxySQL RW RO Master Master Slave Slave Slave Slave

In case of architecture change we have to notify/update all proxysql servers DC 1 DC 1 ProxySQL Orchestrator Orchestrator Master Master Slave Slave Slave Slave Slave

Consul Template github.com/hashicorp/consul-template Populates values from Consul into the file system. Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com Knows your MySQL topology Handles failover

Consul Template Gets Orchestrator to update a change github.com/hashicorp/consul-template in topology Populates values from Consul OCP script into the file system. Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com Knows your MySQL topology Handles failover

Consul Template Gets ProxySQL to sample for a github.com/hashicorp/consul-template change... Populates values from Consul OCP l u s script n o C into the file system. l p m e T Orchestrator ProxySQL ...and in case of change - download new github.com/github/orchestrator www.proxysql.com topology Knows your MySQL topology Handles failover

Orchestrator-Consule-ProxySQL (OCP) Demo

03 OCP Benefits

Before After Application Connected Connected to an directly to DB abstraction layer Servers clusters

Before After Application Connected Connected to an directly to DB abstraction layer Servers clusters Upon failure, app Upon failure, app Local Health servers wait for servers can still Check resume work (bcp)

Before After Application Connected Connected to an directly to DB abstraction layer Servers clusters Upon failure, app Upon failure, app Local Health servers wait for servers can still Check resume work (bcp) Configuration Manually Automatically Change

Before After Application Connected Connected to an directly to DB abstraction layer Servers clusters Upon failure, app Upon failure, app Local Health servers wait for servers can still Check resume work (bcp) Configuration Manually Automatically Change DB Failover ~1.5 minutes ~0.5 second

https://github.com/wix/ocp Getting Started

Consul Template github.com/hashicorp/consul-template Populates values from Consul OCP OCP script into the file system. script NEW ProxySQL Cluster Orchestrator ProxySQL github.com/github/orchestrator www.proxysql.com Knows your MySQL topology Handles failover

2 options to get auto update on DB failures and network split OCP OCP + Orchestrator NEW OCP script ProxySQL Connects 3 existing solutions DB recovery time? script Cluster (Orchestrator, Consul template & Less moving parts ProxySQL)

2 options to get auto update on DB failures and network split OCP OCP + Orchestrator NEW OCP script ProxySQL Connects 3 existing solutions DB recovery time? script Cluster (Orchestrator, Consul template & Less moving parts ProxySQL) What’s your How can you How much DB topology? make it update recovery time will automatically? be saved?

Lithuania Vilnius Wix Engineering Locations Ukraine THANK Kyiv Dnipro YOU! Any Questions? Israel aviap@wix.com Tel-Aviv Be’er Sheva