ProxySQL hand-on Ren Canna Ren Canna ProxySQL ProxySQL Frankfurt, - - PowerPoint PPT Presentation

ProxySQL hand-on

René Cannaò ProxySQL

Frankfurt, 5th Nov 2018

René Cannaò ProxySQL

Frankfurt, 5th Nov 2018

2

A bit about ProxySQL LLC

We provide services to help build, support as well as improve the performance & reliability of your Cloud-Based or On- Premise MySQL infrastructure:

• ProxySQL Development
• Remote Consulting
• ProxySQL Support Services
• ProxySQL Training

We provide services to help build, support as well as improve the performance & reliability of your Cloud-Based or On- Premise MySQL infrastructure:

• ProxySQL Development
• Remote Consulting
• ProxySQL Support Services
• ProxySQL Training

3

A bit about me…

Rene Cannao

• Founder of ProxySQL
• MySQL DBA

Rene Cannao

• Founder of ProxySQL
• MySQL DBA

4

Agenda

• What is ProxySQL?
• Features
• Fundamentals
• What is new?
• Internals
• Admin
• Clustering
• Rules
• Mirroring
• Failover
• New protocols
• GTID casual reads
• What is ProxySQL?
• Features
• Fundamentals
• What is new?
• Internals
• Admin
• Clustering
• Rules
• Mirroring
• Failover
• New protocols
• GTID casual reads

5

Other sessions

HA and clustering solution: ProxySQL as an intelligent router for Galera and Group Replication Wednesday 7th November , 2018 @11:20AM ProxySQL Adaptive query routing based on GTID tracking Wednesday 7th November, 2018 @4:30PM HA and clustering solution: ProxySQL as an intelligent router for Galera and Group Replication Wednesday 7th November , 2018 @11:20AM ProxySQL Adaptive query routing based on GTID tracking Wednesday 7th November, 2018 @4:30PM

Overview of ProxySQL

7

What is ProxySQL

The MySQL data stargate The MySQL data stargate

8

ProxySQL Architecture Overview

Data gateway Clients connect to ProxySQL Requests are evaluated Actions are performed

9

Main motivations

empower the DBAs improve operation understand and improve performance create a proxy layer to shield the database High performance and High Availability

1

Some of the most interesting features:

• n-the-fly rewrite of queries
• caching reads outside the database server
• connection pooling and multiplexing
• complex query routing and read/write split
• load balancing
• real time statistics
• monitoring
• data masking
• multiple instances on same ports

1 1

Some of the most interesting features:

• high availability and scalability
• seamless failover
• firewall
• query throttling
• query timeout
• query mirroring
• runtime reconfiguration
• scheduler
• support for Galera/PXC and Group Replication

1 2

Some of the most interesting features:

• support for millions of users
• support for tens of thousands of database servers
• native ProxySQL Clustering solution
• support for ClickHouse as a backend
• support for Aurora
• SSL support for frontend
• SSLv1.2
• native Support for Galera
• causal reads using GTID

1 3

1 4

Hostgroups and Query Routing

All backends are grouped into hostgroups Hostgroups have logical functionalities All backends are grouped into hostgroups Hostgroups have logical functionalities

1 5

Hostgroups example #1

HostGroup0 (HG0): Write masters HostGroup1( HG1): Read slaves Read/Write split

1 6

Hostgroups example #2

HG0: main write masters HG1: main read slaves HG2: reporting slaves HG3: ad-hoc queries slaves HG4: data warehouse write masters HG5: data warehouse read slaves HG6: remote site servers HG7: test servers HG8 : mirror for traffjc on HG0 HG9 : mirror for traffjc on HG1 HG0: main write masters HG1: main read slaves HG2: reporting slaves HG3: ad-hoc queries slaves HG4: data warehouse write masters HG5: data warehouse read slaves HG6: remote site servers HG7: test servers HG8 : mirror for traffjc on HG0 HG9 : mirror for traffjc on HG1

1 7

Basic design

HG0 HG2 HG1

ProxySQL APP1 APP2 APP3

1 8

Basic design

HG0 HG2 HG1

APP APP ProxySQL Very low latency using Unix Domain Socket (?)

1 9

One ProxySQL per host

2

One ProxySQL per host

The most common setup It scales to hundreds or thousands of node Try this solution before anything else!

2 1

Silos approach

2 2

Clustered ProxySQL Architecture

DB DB DB

APP

DB PROXYSQL DB DB

APP

PROXYSQL

APP

PROXYSQL

APP

PROXYSQL PROXYSQL PROXYSQL DBMANAGER PROXYSQL

Clustered Proxy Layer

2 3

Clustered ProxySQL at scale

Tested with:

• 8 app servers with 3k clients’ connections each (24k total)
• 4 middle layer proxysqls processing 4k connections each from local

proxysqls (16k total)

• 256 backends/shard (meaning 256 routing rules) processing 600

connections each (150k total) Single ProxySQL was tested with up to 950k connections ProxySQL 1.4 is able to process up to 750k QPS

Tested with:

• 8 app servers with 3k clients’ connections each (24k total)
• 4 middle layer proxysqls processing 4k connections each from local

proxysqls (16k total)

• 256 backends/shard (meaning 256 routing rules) processing 600

connections each (150k total) Single ProxySQL was tested with up to 950k connections ProxySQL 1.4 is able to process up to 750k QPS

2 4

ProxySQL at scale

Real setups: Over 1000 proxysql instances in a single network Over 100 mysql servers configured on a single proxysql instances Over 2 millions unique users Routing with over 100 thousands shards Real setups: Over 1000 proxysql instances in a single network Over 100 mysql servers configured on a single proxysql instances Over 2 millions unique users Routing with over 100 thousands shards

ProxySQL Internals

2 6

ProxySQL Modules in 1.3.x

HG0 HG1

APP1 ProxySQL

Queries cache Query Processor

APP2 APP3

Thread #1 Thread #2 Thread #3 Users Auth

Connection Pool Hostgroup Manager Admin Monitoring

2 7

ProxySQL Modules in 1.4.x

HG0 HG1

APP1 ProxySQL

Queries cache Query Processor

APP2 APP3

Thread #1 Thread #2 Thread #3 Users Auth

Connection Pool Hostgroup Manager Admin Monitoring ClickHouse Server

ClickHouse Auth

Cluster SQLite3 Server

Thread #3 idles Thread #2 idles Thread #1 idles

Purge Thread

2 8

Thread pool in MySQL

Threads in ProxySQL are known as "MySQL Threads" Fixed number of worker threads (confjgurable) All threads listen on the same port(s) Client connections are not shared between threads All threads perform their own network I/O Uses "poll()"... (does it scale?)

2 9

Threads never share client connections

Pros: Thread contention is reduced No need for synchronization Each thread calls "poll()" Cons: Possibly imbalanced load Thread 1 Thread 2 Thread 3

3

poll() vs. epoll()

"poll()" is O(N) "epoll()" is O(1) "epoll()" scales better than "poll()" Why does ProxySQL use "poll()"? It is faster than "epoll()" for fewer connections (~1000) Performance degrades when there are a lot of connections

3 1

ProxySQL Auxiliary Threads

Each worker thread has an auxiliary thread Worker thread uses "poll()" Auxiliary thread uses "epoll()" Worker thread passes idle connections to auxiliary thread When a connections becomes active auxiliary thread passes connection to the worker thread Solution scales to almost 1 million connections Thread 1 Aux 1 Thread 2 Aux 2 Thread 3 Aux 3

3 2

MySQL Thread Overview

MySQL Session MySQL Session MySQL Session MySQL Session

MySQL Thread

MySQL Session MySQL Session

All other Modules:

• Query Processor
• Query Cache
• Hostgroups Manager
• Authentication
• Others

For low contention, threads independently: Track internal metrics Store values for mysql-XXX variables Store a copy of the defjned query rules

3 3

Queries Processor

Based on Queries Rules Defjnes what to cache Defjnes the hostgroup target Timeout/delay Firewall Mirroring Rewrite queries Based on Queries Rules Defjnes what to cache Defjnes the hostgroup target Timeout/delay Firewall Mirroring Rewrite queries

3 4

Queries rules

Complex rules to match incoming traffjc:

• regex on query
• regex on digest text
• username
• schemaname
• Source IP address
• Bind IP address/port
• digest

Rules can be chained Complex rules to match incoming traffjc:

• regex on query
• regex on digest text
• username
• schemaname
• Source IP address
• Bind IP address/port
• digest

Rules can be chained

3 5

Queries Cache and Rewrite

Caching on the wire Internal key/value storage In memory only Pattern based Expired by timeout Rewrite on the wire Regex match/replace on query on digest text Optionally cached or mirrored Caching on the wire Internal key/value storage In memory only Pattern based Expired by timeout Rewrite on the wire Regex match/replace on query on digest text Optionally cached or mirrored

3 6

Users Authentication

Credentials stored in the proxy User login always possible (even without backends) Max connections Login credentials are encrypted Extremely scalable, up to millions of users: https://www.percona.com/live/e17/sessions/scaling-million- databases-000webhost-lt Credentials stored in the proxy User login always possible (even without backends) Max connections Login credentials are encrypted Extremely scalable, up to millions of users: https://www.percona.com/live/e17/sessions/scaling-million- databases-000webhost-lt

3 7

Hostgroups Manager

Management of servers T rack servers status Manages the connections pool Management of servers T rack servers status Manages the connections pool

3 8

Connections Pool

Reduced the overhead of creating new connections, and are recycled when not in use One to many connections Multiplexing & maximum connections Auto-reconnect and automatic re-execution of queries Failover management Reduced the overhead of creating new connections, and are recycled when not in use One to many connections Multiplexing & maximum connections Auto-reconnect and automatic re-execution of queries Failover management

3 9

Connections Pool

4

Connections Pool

4 1

Connection Pool

Replies to ping. From hundreds of millions of ping per day, to 0:

https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/

Replies to ping. From hundreds of millions of ping per day, to 0:

https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/

4 2

Purge Thread

Unsafe connections are reset instead of being dropped Drastically reduce the need to establish new connections In ProxySQL 2.0 : each worker thread performs most of the reset Unsafe connections are reset instead of being dropped Drastically reduce the need to establish new connections In ProxySQL 2.0 : each worker thread performs most of the reset

4 3

Auto-reconnect and re-execution

Automatic detection of failures Graceful handling Auto-reconnect when possible Pause until a backend becomes available Re-execution of queries Automatic detection of failures Graceful handling Auto-reconnect when possible Pause until a backend becomes available Re-execution of queries

4 4

Multiplexing

Reduce the number of connections against mysqld (confjgurable) Many clients connections (tens of thousands) can use few backend connections (few hundreds) T racks connection status (transactions, user variables, temporary tables, etc) Order by waiting time Reduce the number of connections against mysqld (confjgurable) Many clients connections (tens of thousands) can use few backend connections (few hundreds) T racks connection status (transactions, user variables, temporary tables, etc) Order by waiting time

4 5

No ProxySQL: ProxySQL:

4 6

No ProxySQL: ProxySQL:

4 7

ProxySQL: No ProxySQL:

4 8

4 9

Multiplexing

https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/ https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/

5

MySQL Threads and Connection Pool

MyHGM is a shared resource so it can cause contention when accessed by MySQL Threads

MySQL_Thread MySQL_Thread

MyHGM

5 1

Thread Connection Cache

MySQL_Thread MySQL_Thread

MyHGM

Connections Cache Connections Cache

ProxySQL Web UI

5 3

Multiplexing (see it in web UI)

5 4

Multiplexing (see it in web)

5 5

More metrics on web UI

5 6

More metrics on web UI

5 7

More metrics on web UI

5 8

Monitoring Module

It monitors backends and collects metrics Monitors replication lag and shun hosts Monitors read_only (and super_read_only and innodb_read_only in 2.0) variables (replication hostgroups) Ping and terminates unresponsive nodes It monitors backends and collects metrics Monitors replication lag and shun hosts Monitors read_only (and super_read_only and innodb_read_only in 2.0) variables (replication hostgroups) Ping and terminates unresponsive nodes

5 9

Monitoring Module

Built in support for asynchronous replication: second behind master and pt-heartbeat Support for any type of clustering solution via external scripts (Galera/PXC , and any heterogeneous replication setup) In ProxySQL 1.4, support for Group Replication In ProxySQL 2.0, support for Galera Built in support for asynchronous replication: second behind master and pt-heartbeat Support for any type of clustering solution via external scripts (Galera/PXC , and any heterogeneous replication setup) In ProxySQL 1.4, support for Group Replication In ProxySQL 2.0, support for Galera

Admin Module

6 1

Admin Interface

Allows runtime confjguration Exports internal statuses It uses MySQL protocol Confjguration possible from any client/tool using MySQL API Covered during the tutorial Allows runtime confjguration Exports internal statuses It uses MySQL protocol Confjguration possible from any client/tool using MySQL API Covered during the tutorial

6 2

Admin Interface

Disk Memory Runtime Disk Memory Runtime

Runtime Memory Disk Config

6 3

Try it!

Source code on GitHub: https://github.com/sysown/proxysql/ Forum: https://groups.google.com/forum/#!forum/proxysql T utorials on: http://www.proxysql.com Source code on GitHub: https://github.com/sysown/proxysql/ Forum: https://groups.google.com/forum/#!forum/proxysql T utorials on: http://www.proxysql.com

Demo Environment

6 5

Demo

Code on GitHub:

https://github.com/renecannao/ProxySQL-Tutorial-PLSC2018

Some hosts provided: Username: plsc Passwords: proxysql Code on GitHub:

https://github.com/renecannao/ProxySQL-Tutorial-PLSC2018

Some hosts provided: Username: plsc Passwords: proxysql

ProxySQL Cluster

6 7

ProxySQL Cluster

Introduced in 1.4.2 Support for MySQL Query Rules, MySQL Users, MySQL Servers and ProxySQL Servers (no Global Variables, no Scheduler) A group of instances constantly check each other for changes in configuration A configuration change applied in a proxy is pulled by the other proxies Allow to keep multiple ProxySQL instances with the same configuration(s) Introduced in 1.4.2 Support for MySQL Query Rules, MySQL Users, MySQL Servers and ProxySQL Servers (no Global Variables, no Scheduler) A group of instances constantly check each other for changes in configuration A configuration change applied in a proxy is pulled by the other proxies Allow to keep multiple ProxySQL instances with the same configuration(s)

6 8

ProxySQL Cluster

• Core Nodes
• Satellite Nodes

Largest setup we know: 1) 3 core nodes 2) 900 satellite nodes

6 9

ProxySQL Cluster

Conflict resolution is timestamp based

• No order
• Last win

No quorum (in the roadmap) Highly scalable: Core nodes vs satellite nodes Conflict resolution is timestamp based

• No order
• Last win

No quorum (in the roadmap) Highly scalable: Core nodes vs satellite nodes

7

ProxySQL Cluster - Confjg

mysql> SET admin-cluster_username='radmin'; mysql> SET admin-cluster_password='radmin'; mysql> LOAD ADMIN VARIABLES TO RUNTIME; mysql> SAVE ADMIN VARIABLES TO DISK; mysql> INSERT INTO proxysql_servers (hostname) VALUES ('proxysql1'),('proxysql2'), ('proxysql3'); mysql> LOAD PROXYSQL SERVERS TO RUNTIME; mysql> SAVE PROXYSQL SERVERS TO DISK; mysql> SET admin-cluster_username='radmin'; mysql> SET admin-cluster_password='radmin'; mysql> LOAD ADMIN VARIABLES TO RUNTIME; mysql> SAVE ADMIN VARIABLES TO DISK; mysql> INSERT INTO proxysql_servers (hostname) VALUES ('proxysql1'),('proxysql2'), ('proxysql3'); mysql> LOAD PROXYSQL SERVERS TO RUNTIME; mysql> SAVE PROXYSQL SERVERS TO DISK;

mysql> SHOW VARIABLES LIKE 'admin-cluster%'; +---------------------------------------------------+--------+ | Variable_name | Value | +---------------------------------------------------+--------+ | admin-cluster_username | radmin | | admin-cluster_password | radmin | | admin-cluster_check_interval_ms | 1000 | | admin-cluster_check_status_frequency | 10 | | admin-cluster_mysql_query_rules_difgs_before_sync | 3 | | admin-cluster_mysql_servers_difgs_before_sync | 3 | | admin-cluster_mysql_users_difgs_before_sync | 3 | | admin-cluster_proxysql_servers_difgs_before_sync | 3 | | admin-cluster_mysql_query_rules_save_to_disk | true | | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | +---------------------------------------------------+--------+ 12 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'admin-cluster%'; +---------------------------------------------------+--------+ | Variable_name | Value | +---------------------------------------------------+--------+ | admin-cluster_username | radmin | | admin-cluster_password | radmin | | admin-cluster_check_interval_ms | 1000 | | admin-cluster_check_status_frequency | 10 | | admin-cluster_mysql_query_rules_difgs_before_sync | 3 | | admin-cluster_mysql_servers_difgs_before_sync | 3 | | admin-cluster_mysql_users_difgs_before_sync | 3 | | admin-cluster_proxysql_servers_difgs_before_sync | 3 | | admin-cluster_mysql_query_rules_save_to_disk | true | | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | +---------------------------------------------------+--------+ 12 rows in set (0.00 sec)

7 1

ProxySQL Cluster - Monitoring

stats_proxysql_servers_checksums stats_proxysql_servers_metrics stats_proxysql_servers_checksums stats_proxysql_servers_metrics

ProxySQL Cluster DEMO

MySQL Query rules

7 4

Query rewriting overview

SQL-aware Regex-based fmexible SQL-aware Regex-based fmexible

7 5

Query rewriting examples

read/write splitting Data masking Sharding read/write splitting Data masking Sharding

7 6

Query rewriting fmow

7 7

Match Related T ables

mysql_users

• active
• fast_forward(bypass)

mysql_users

• active
• fast_forward(bypass)

mysql_query_rules

• username
• schemaname
• flagIN/OUT
• client_addr
• proxy_addr
• proxy_port
• digest
• match_digest
• match_pattern
• negate_match_pattern

mysql_query_rules

• username
• schemaname
• flagIN/OUT
• client_addr
• proxy_addr
• proxy_port
• digest
• match_digest
• match_pattern
• negate_match_pattern

7 8

Action related fjelds

flagOUT replace_pattern destination_hostgroup cache_ttl timeout retries delay mirror_flagOUT mirror_hostgroup OK_msg error_msg log multiplex apply flagOUT replace_pattern destination_hostgroup cache_ttl timeout retries delay mirror_flagOUT mirror_hostgroup OK_msg error_msg log multiplex apply

active New in 2.0 : gtid_from_hostgroup cache_empty_result cache_timeout active New in 2.0 : gtid_from_hostgroup cache_empty_result cache_timeout

7 9

3 layers for ProxySQL tables

Disk Memory Runtime Disk Memory Runtime

Runtime Memory Disk Config

8

Precautions

Confirm the query hits with stats_mysql_query_rules double check rules and rewrite results hold off on writing rules to disk until you're sure they are working as expected have a rollback plan

• load from disk,
• then load from memory to runtime

test select query rules and rewrites on a slaves first test mutable rules on a mirror or other throw away db. Confirm the query hits with stats_mysql_query_rules double check rules and rewrite results hold off on writing rules to disk until you're sure they are working as expected have a rollback plan

• load from disk,
• then load from memory to runtime

test select query rules and rewrites on a slaves first test mutable rules on a mirror or other throw away db.

8 1 mysql> SELECT username, default_hostgroup FROM mysql_users\G *************************** 1. row *************************** username: root default_hostgroup: 0 1 row in set (0.00 sec) mysql> SELECT match_digest, destination_hostgroup FROM mysql_query_rules\G *************************** 1. row *************************** match_digest: ^SELECT.*FOR UPDATE destination_hostgroup: 0 *************************** 2. row *************************** match_digest: ^SELECT destination_hostgroup: 1 2 rows in set (0.00 sec) mysql> SELECT username, default_hostgroup FROM mysql_users\G *************************** 1. row *************************** username: root default_hostgroup: 0 1 row in set (0.00 sec) mysql> SELECT match_digest, destination_hostgroup FROM mysql_query_rules\G *************************** 1. row *************************** match_digest: ^SELECT.*FOR UPDATE destination_hostgroup: 0 *************************** 2. row *************************** match_digest: ^SELECT destination_hostgroup: 1 2 rows in set (0.00 sec)

Example - Simple R/W

This example is a simple one, NOT for production use

For production configuration guide, see: https://proxysql.com/blog/configure-read-write-split

Query Rewriting with ProxySQL DEMO

Click to add text Click to add text

ProxySQL Upgrade with zero downtime

8 4

Multiple instances running on the same port

Requires Linux Kernel 3.9 Use different datadir (optionally different config file) Close the listener for a process Drain connections from one node Wait all the connections to pass over Requires Linux Kernel 3.9 Use different datadir (optionally different config file) Close the listener for a process Drain connections from one node Wait all the connections to pass over

Mirroring with ProxySQL

Click to add text Click to add text

8 6

What is mirroring?

8 7

What is mirroring?

8 8

Why mirror queries?

Validate performance on a difgerent server using difgerent hostgroups. Validate performance of query rewrite or schema change Pre-fetch slave replication (Replication Booster) Validate performance on a difgerent server using difgerent hostgroups. Validate performance of query rewrite or schema change Pre-fetch slave replication (Replication Booster)

8 9

How to mirror

mysql>SHOW CREATE TABLE mysql_query_rules\G *********************** 1. row ************************ table: mysql_query_rules Create T able: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, username VARCHAR, schemaname VARCHAR, fmagIN INT NOT NULL DEFAULT 0, *snip* mirror_fmagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, *snip*) mysql>SHOW CREATE TABLE mysql_query_rules\G *********************** 1. row ************************ table: mysql_query_rules Create T able: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, username VARCHAR, schemaname VARCHAR, fmagIN INT NOT NULL DEFAULT 0, *snip* mirror_fmagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, *snip*)

9

Mirroring fmow

9 1

Mirroring - example #1

mysql> SELECT username, destination_hostgroup, mirror_hostgroup, mirror_fmagOUT FROM mysql_query_rules WHERE username='plam_mirror'\G ****************** 1. row ********************** username: pl_mirror destination_hostgroup: 3 mirror_hostgroup: 4 mirror_fmagOUT: NULL mysql> SELECT username, destination_hostgroup, mirror_hostgroup, mirror_fmagOUT FROM mysql_query_rules WHERE username='plam_mirror'\G ****************** 1. row ********************** username: pl_mirror destination_hostgroup: 3 mirror_hostgroup: 4 mirror_fmagOUT: NULL

9 2

Mirroring - example #2

Mirroring Demo

Click to add text Click to add text

9 4

Mirroring caveats

Can be dropped: mysql-mirror_max_concurrency, mysql-mirror_max_queue_length Can be executed out of order No error handling of failed queries Can be dropped: mysql-mirror_max_concurrency, mysql-mirror_max_queue_length Can be executed out of order No error handling of failed queries

Failover with ProxySQL

9 6

Failover highlights

improve failover time as perceived by the application prevent errors sent to the application perform transparent database failovers: gracefully redirecting traffic without the application knowing existing applications do not have to be rewritten to autoreconnect since connections are not lost from failovers improve failover time as perceived by the application prevent errors sent to the application perform transparent database failovers: gracefully redirecting traffic without the application knowing existing applications do not have to be rewritten to autoreconnect since connections are not lost from failovers

9 7

Failover scenario - without ProxySQL

9 8

Failover scenario - without ProxySQL

9 9

Failover scenario - with ProxySQL

1

Failover scenario - with ProxySQL

1 1

Failover scenario - with ProxySQL

1 2

Failover scenario - with ProxySQL

1 3

Failover without ProxySQL (10 minute outage)

https://www.nylas.com/blog/growing-up-with-mysql/

1 4

Failover with ProxySQL (10 second outage)

https://www.nylas.com/blog/growing-up-with-mysql/

1 5

Failover - MySQL Servers

mysql> SELECT hostgroup_id, hostname, status FROM mysql_servers WHERE hostname IN ('mysql1, 'mysql2’)\G *************************** 1. row *************************** hostgroup_id: 0 hostname: mysql1 status: ONLINE *************************** 2. row *************************** hostgroup_id: 1 hostname: mysql2 status: ONLINE 2 rows in set (0.00 sec) mysql> SELECT hostgroup_id, hostname, status FROM mysql_servers WHERE hostname IN ('mysql1, 'mysql2’)\G *************************** 1. row *************************** hostgroup_id: 0 hostname: mysql1 status: ONLINE *************************** 2. row *************************** hostgroup_id: 1 hostname: mysql2 status: ONLINE 2 rows in set (0.00 sec)

1 6

Failover - Replication Hostgroups

mysql> SELECT * FROM mysql_replication_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 0 reader_hostgroup: 1 comment: 1 row in set (0.00 sec) mysql> SELECT * FROM mysql_replication_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 0 reader_hostgroup: 1 comment: 1 row in set (0.00 sec)

1 7

ProxySQL Failover ProTIP

ProxySQL does not handle promotion or re-slaving External process needed, such as MHA or Orchestrator ProxySQL does not handle promotion or re-slaving External process needed, such as MHA or Orchestrator

Failover Demo

Click to add text Click to add text

Some New Features

Click to add text Click to add text

1 1

ClickHouse Server

New in 1.4.3 – experimental (not fully completed, subject to change) Support for ClickHouse: https://clickhouse.yandex/ Fast, really fast data analytics using MySQL client Users management Supports only one backend Supports only DDL and SELECT (no INSERT) New in 1.4.3 – experimental (not fully completed, subject to change) Support for ClickHouse: https://clickhouse.yandex/ Fast, really fast data analytics using MySQL client Users management Supports only one backend Supports only DDL and SELECT (no INSERT)

1 1 1

ClickHouse Server

1 1 2

ClickHouse Server

1 1 3

SQLite3 Server

New in 1.4.3 – experimental (not fully completed, subject to change) Support for SQLite3 : https://www.sqlite.org/ Fully transactional, in-memory Same users as MySQL backends A lot of features in the roadmap New in 1.4.3 – experimental (not fully completed, subject to change) Support for SQLite3 : https://www.sqlite.org/ Fully transactional, in-memory Same users as MySQL backends A lot of features in the roadmap

1 1 4

You-Name-It Server

What other service should become part of ProxySQL ? What other service should become part of ProxySQL ?

1 1 5

Causal read with GTID

Feature introduced in 2.0 Ensures that reads sent to slave returns what the client has written on master Requires GTID and MySQL 5.7+ Feature introduced in 2.0 Ensures that reads sent to slave returns what the client has written on master Requires GTID and MySQL 5.7+

1 1 6

11 6

ProxSQL Read / Write Split

1 1 7

11 7

ProxySQL Binlog Reader

1 1 8

11 8

ProxySQL Binlog Reader

1 1 9

Hundreds of thousands of shards

Feature introduced in 1.4.7 Simpler version of mysql_query_rules Used as a hash table

mysql> SELECT username,schemaname,flagIN, destination_hostgroup dest FROM mysql_query_rules_fast_routing; +----------+------------+--------+------+ | username | schemaname | flagIN | dest | +----------+------------+--------+------+ | userA | shard1 | 0 | 1 | | userA | shard2 | 0 | 1 | | userA | shard3 | 0 | 1 | | userA | shard4 | 0 | 1 | | userA | shard5 | 0 | 2 | | userA | shard6 | 0 | 2 | | userA | shard7 | 0 | 2 | +----------+------------+--------+------+

Feature introduced in 1.4.7 Simpler version of mysql_query_rules Used as a hash table

mysql> SELECT username,schemaname,flagIN, destination_hostgroup dest FROM mysql_query_rules_fast_routing; +----------+------------+--------+------+ | username | schemaname | flagIN | dest | +----------+------------+--------+------+ | userA | shard1 | 0 | 1 | | userA | shard2 | 0 | 1 | | userA | shard3 | 0 | 1 | | userA | shard4 | 0 | 1 | | userA | shard5 | 0 | 2 | | userA | shard6 | 0 | 2 | | userA | shard7 | 0 | 2 | +----------+------------+--------+------+

1 2

Rate My Session

1 2 1

Thank you!

• Please remember to report feature requests and bug reports:

https://github.com/sysown/proxysql/

• Community support can be found on our forum:

https://groups.google.com/forum/#!forum/proxysql

• Useful blog articles are available at our site: http://proxysql.com/blog
• Visit us at http://proxysql.com/support for subscription and support options
• Please remember to report feature requests and bug reports:

https://github.com/sysown/proxysql/

• Community support can be found on our forum:

https://groups.google.com/forum/#!forum/proxysql

• Useful blog articles are available at our site: http://proxysql.com/blog
• Visit us at http://proxysql.com/support for subscription and support options