ProxySQL Use Case Scenarios Percona Live Amsterdam Oct 3-5 2016 Alkin Tezuysal René Cannaò

2 Who we are • Alkin Tezuysal Sr. Technical Manager, Percona • René Cannaò MySQL SRE, Dropbox / ProxySQL

3 Top 5 reasons to use ProxySQL • Improve database operations. • Understand and solve performance issues. • Create a proxy layer to shield the database. • Add High-Availability to database topology. • Empower the DBAs with great tool.

4 ProxySQL Highlights Scalability High Availability Advanced Query Support Manageability ProxySQL

5 Use case overview - Scalability Connection Pooling and Multiplexing Add connection pooling layer Reduce connection thread overhead Read / Write Split More scalability for any MySQL topology Must for any heavy workloads Read / Write Sharding Effortless sharding implementation Without Dev. support and cost savings

6 Use case overview - High Availability Seamless Failover Graceful failover support Query rerouting Load Balancing Allows easy scaling For better utilization of servers Cluster Aware Supports PXC/Galera implementations Small footprint for big gain

7 Use case overview - Advanced Queries Query caching Cache frequently used data Adds another cache layer Query rewrite Add SQL flexibility Faster problem solving Query blocking Add database aware firewall Without App. support

8 Use case overview - Advanced Queries Query mirroring Query throttling Set prioritization for important queries Allows manual intervention to problem queries Query timeout Add another timeout layer before hitting the database Flexible query level timeout

9 Use case overview - Manageability Admin Utility Authentication support Limit user access to database pool Runtime reconfiguration On the fly modifications Configure database without restart or downtime Monitoring Stats collection and reporting Investigate database workload

10 Scalability with ProxySQL - Connection Pooling and Multiplexing

11 Scalability with ProxySQL - Read/Write Split

12 Scalability with ProxySQL - Read / Write Sharding

Making a short title here. By Jim Doherty

14 Break on through to the other slide… We put cover sheets on TPS reports Did you get the memo about the cover sheets? If you could go ahead and start using the cover sheet, that would be great. Mkay? Oh, and Friday is Hawaiian shirt day Focused 100% on Cover Sheets

DATABASE PERFORMANCE MATTERS

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: 1. on-the-fly rewrite of queries 2. caching reads outside the database server 3. connection pooling and multiplexing 4. complex query routing and read/write split 5. load balancing 6. real time statistics 7. monitoring 8. High Availability and Scalability 9. seamless failover 10. firewall 11. query throttling 12. query timeout 13. query mirroring 14. runtime reconfiguration

Hostgroups and Query Routing All backends are grouped into hostgroups Hostgroups have logical functionalities

Basic design HG0 HG2 HG1 ProxySQL APP1 APP2 APP3

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 APP APP ProxySQL Very low latency using Unix Domain Socket

Basic design HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL

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