Hands on ProxySQL

1 .ProxySQL hand-on 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 2 

3 .A bit about me… Rene Cannao • Founder of ProxySQL • MySQL DBA 3 

4 .Agenda ● What is ProxySQL? ● Features ● Fundamentals ● What is new? ● Internals ● Admin ● Clustering ● Rules ● Mirroring ● Failover ● New protocols ● GTID casual reads 4 

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 5 

6 .Overview of ProxySQL 

7 .What is ProxySQL The MySQL data stargate 7 

8 .ProxySQL Architecture Overview Data gateway Clients connect to ProxySQL Requests are evaluated Actions are performed 8 

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 9 

10 .Some of the most interesting features: ● on-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 0 

11 .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 1 

12 .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 2 

13 .1 3 

14 .Hostgroups and Query Routing All backends are grouped into hostgroups Hostgroups have logical functionalities 1 4 

15 .Hostgroups example #1 HostGroup0 (HG0): Write masters HostGroup1( HG1): Read slaves Read/Write split 1 5 

16 .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 1 6 

17 .Basic design HG0 APP1 HG1 APP2 ProxySQL HG2 APP3 1 7 

18 .Basic design HG0 APP APP HG1 ProxySQL HG2 Very low latency using Unix Domain Socket (?) 1 8 

19 .One ProxySQL per host 1 9 

20 . One ProxySQL per host The most common setup It scales to hundreds or thousands of node Try this solution before anything else! 2 0 

21 .Silos approach 2 1 

22 .Clustered ProxySQL Architecture APP APP APP APP PROXYSQL PROXYSQL PROXYSQL PROXYSQL Clustered Proxy Layer PROXYSQL PROXYSQL PROXYSQL DBMANAGER DB DB DB DB DB DB 2 2 

23 .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 2 3 

24 .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 2 4 

25 .ProxySQL Internals 

26 .ProxySQL Modules in 1.3.x ProxySQL HG0 APP1 Queries Monitoring cache HG1 APP2 Users Auth Admin Thread #3 Thread #1 Thread #2 Connection APP3 Pool Query Hostgroup Processor Manager 2 6 

27 .ProxySQL Modules in 1.4.x ProxySQL HG0 APP1 Monitoring ClickHouse Server Cluster Admin HG1 APP2 ClickHouse Auth Queries Purge Thread Thread #3 idles Thread #1 idles Thread #2 idles Thread #1 Thread #2 Thread #3 cache APP3 Connection Users Auth Pool SQLite3 Query Hostgroup Server Processor Manager 2 7 

28 . Thread pool in MySQL Threads in ProxySQL are known as "MySQL Threads" Fixed number of worker threads (configurable) 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 8 

29 .Threads never share client connections Thread 1 Pros: Thread contention is reduced No need for synchronization Thread 2 Each thread calls "poll()" Cons: Possibly imbalanced load Thread 3 2 9