ProxySQL Tutorial High Performance & High Availability Proxy - - PowerPoint PPT Presentation

Santa Clara, California | April 23th – 25th, 2018

ProxySQL Tutorial

High Performance & High Availability Proxy for MySQL

With a GPL license!

2

Who we are

René Cannaò

ProxySQL Founder

Derek Downey

Director of OSDB, Pythian

3

Agenda

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

4

Other sessions

Consistent Reads Using ProxySQL and GTID Tuesday April 24, 2018 @3:50PM Automatic Failovers with Kubernetes using Orchestrator, ProxySQL and Zookeeper Tuesday April 24, 2018 @3:50PM

Overview of ProxySQL

6

What is ProxySQL

The MySQL data stargate

7

Main motivations

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

ProxySQL Features

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

ProxySQL Features (2)

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

What’s new in ProxySQL 1.4.0

Native Support for Group Replication Multiple regex engines Better performance Support for millions of users Support for tens of thousands of database servers Better queries control http://www.proxysql.com/blog/releasing-proxysql-140

What’s new in ProxySQL 1.4.7

Native ProxySQL Clustering Solution Support for ClickHouse as a backend SQLite network Support for millions of shards

What’s new in ProxySQL 2.0 (dev)

… all in 1.4.x plus: Support for Aurora SSL support for frontend SSLv1.2 Native Support for Galera Causal reads using GTID Multi-threaded connections reset

Hostgroups and Query Routing

All backends are grouped into hostgroups Hostgroups have logical functionalities

Hostgroups example #1

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

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 traffic on HG0 HG9 : mirror for traffic on HG1

Basic design

HG0 HG2 HG1

ProxySQL APP1 APP2 APP3

Basic design

HG0 HG2 HG1

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

One ProxySQL per host

Silos approach

Clustered ProxySQL Architecture

DB DB DB

APP

DB PROXYSQL DB DB

APP

PROXYSQL

APP

PROXYSQL

APP

PROXYSQL PROXYSQL PROXYSQL DBMANAGER PROXYSQL

Clustered Proxy Layer

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 At today, ProxySQL is able to process up to 750k QPS

ProxySQL Internals

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

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

Queries Processor

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

Queries rules

Complex rules to match incoming traffic:

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

Rules can be chained

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

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

Hostgroups Manager

Management of servers Track servers status Tightly integrated with the connections pool

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

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 perform most of the reset

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

Multiplexing

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

Multiplexing (see it in web UI)

Multiplexing (see it in web)

More metrics on web UI

More metrics on web UI

More metrics on web UI

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

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.0, support for Group Replication In ProxySQL 2.0, support for Galera

Admin Module

Admin Interface

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

Admin Interface

Disk Memory Runtime

Runtime Memory Disk Config

Try it!

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

Demo Environment

47

Demo

Code on GitHub:

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

Some hosts provided: Username: plsc Passwords: proxysql

ProxySQL Cluster

ProxySQL Cluster

New in 1.4.2 – experimental (not fully completed, subject to change) Support for MySQL Query Rules, MySQL Users, MySQL Servers and ProxySQL Servers 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)

ProxySQL Cluster

ProxySQL Cluster

Conflict resolution is timestamp based

• No order
• Last win

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

52

ProxySQL Cluster - Config

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_diffs_before_sync | 3 | | admin-cluster_mysql_servers_diffs_before_sync | 3 | | admin-cluster_mysql_users_diffs_before_sync | 3 | | admin-cluster_proxysql_servers_diffs_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)

53

ProxySQL Cluster - Monitoring

Stats_proxysql_servers_checksums Stats_proxysql_servers_metrics

ProxySQL Cluster DEMO

MySQL Query rules

56

Query rewriting overview

Sql-aware Regex-based flexible

57

Query rewriting examples

read/write splitting Data masking Sharding

58

Query rewriting flow

Match Related Tables

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

Action related fields

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

active

3 layers for ProxySQL tables

Disk Memory Runtime

Runtime Memory Disk Config

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.

63

Example - Simple R/W

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)

Query Rewriting with ProxySQL DEMO

Click to add text

Mirroring with ProxySQL

Click to add text

66

What is mirroring?

67

What is mirroring?

68

Why mirror queries?

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

69

How to mirror

mysql>SHOW CREATE TABLE mysql_query_rules\G *********************** 1. row ************************ table: mysql_query_rules Create Table: 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, flagIN INT NOT NULL DEFAULT 0, *snip* mirror_flagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, *snip*)

70

Mirroring flow

71

Mirroring - example #1

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

72

Mirroring - example #2

Mirroring Demo

Click to add text

74

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

Failover with ProxySQL

76

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

77

Failover scenario - without ProxySQL

78

Failover scenario - without ProxySQL

79

Failover scenario - with ProxySQL

80

Failover scenario - with ProxySQL

81

Failover scenario - with ProxySQL

82

Failover scenario - with ProxySQL

83

Failover without ProxySQL (10 minute outage)

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

84

Failover with ProxySQL (10 second outage)

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

85

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)

86

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)

87

ProxySQL Failover ProTIP

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

Failover Demo

Click to add text

New Features

Click to add text

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)

ClickHouse Server

ClickHouse Server

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

You-Name-It Server

What other service should become part of ProxySQL ?

You-Name-It Server

What other service should become part of ProxySQL ?

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+

97

ProxSQL Read / Write Split

98

ProxySQL Binlog Reader

99

ProxySQL Binlog Reader

10

We offer support

Subscriptions Consulting Professional services

10 2

Rate My Session

Thank You!