ProxySQL Use Case Scenarios

Rene Cannao (ProxySQL) and Alkin Tezuysal (Percona) disscussed ProxySQL use case scenarios at Percona Live Amsterdam 2016.

ProxySQL aims to be the most powerful proxy in the MySQL ecosystem. It is protocol aware and able to provide high availability (HA) and high performance with no changes in the application, using several built-in features and integration with clustering software.

During this session we will quickly introduce its main features, so to better understand how it works. We will then describe multiple use case scenarios in which ProxySQL empowers large MySQL installations to provide HA with zero downtime, read/write split, query rewrite, sharding, query caching, and multiplexing using SSL across data centers.

Transcript

1. ProxySQL Use Case Scenarios Percona Live Amsterdam Oct 3-5 2016 Alkin Tezuysal René Cannaò
2. 2 Who we are • Alkin Tezuysal Sr. Technical Manager, Percona • René Cannaò MySQL SRE, Dropbox / ProxySQL
3. 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. 4 ProxySQL Highlights Scalability High Availability Advanced Query Support Manageability ProxySQL
5. 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. 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. 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. 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. 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. 10 Scalability with ProxySQL - Connection Pooling and Multiplexing
11. 11 Scalability with ProxySQL - Read/Write Split
12. 12 Scalability with ProxySQL - Read / Write Sharding
13. Making a short title here. By Jim Doherty
14. 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
15. DATABASE PERFORMANCE MATTERS
16. Main motivations empower the DBAs improve operation understand and improve performance create a proxy layer to shield the database High performance and High Availability
17. 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
18. Hostgroups and Query Routing All backends are grouped into hostgroups Hostgroups have logical functionalities
19. Basic design HG0 HG2 HG1 ProxySQL APP1 APP2 APP3
20. Hostgroups example #1 HostGroup0 (HG0): Write masters HostGroup1( HG1): Read slaves Read/Write split
21. 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
22. Basic design HG0 HG2 HG1 APP APP ProxySQL Very low latency using Unix Domain Socket
23. Basic design HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL
24. Clustered ProxySQL Architecture DB DB DB APP DB PROXYSQL DB DB APP PROXYSQL APP PROXYSQL APP PROXYSQL PROXYSQL PROXYSQL DBMANAGER PROXYSQL Clustered Proxy Layer
25. 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