ProxySQL Use Case Scenarios

1 . ProxySQL Use Case Scenarios Percona Webinar Nov 23 2016 Alkin Tezuysal René Cannaò 

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

3 .Top 5 reasons to use ProxySQL • Improve database opera:ons. • 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. 3 

4 .ProxySQL Highlights Scalability High Availability Advanced Query Manageability Support 4 

5 .Use case overview - Scalability Connec:on Pooling Reduce Add connec:on connec:on and Mul:plexing pooling layer thread overhead More scalability Must for any Read / Write Split for any MySQL heavy workloads topology Read / Write Effortless Without Dev. sharding support and cost Sharding implementa:on savings 5 

6 .Use case overview - High Availability Seamless Failover Graceful failover Query rerou:ng support For bePer Allows easy u:liza:on of Load Balancing scaling servers Supports PXC/ Small footprint Cluster Aware Galera for big gain implementa:ons 6 

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

8 .Use case overview - Advanced Queries Audit, Analyze, Allow Query mirroring Review, Cache benchmarking on Warming live data Set priori:za:on Allows manual Query throPling for important interven:on to queries problem queries Add another Flexible query Query :meout :meout layer level :meout 8 

9 .Use case overview - Manageability Admin U:lity Authen:ca:on Limit user access support to database pool Configure Run:me On the fly database without reconfigura:on modifica:ons restart or down:me Inves:gate Stats collec:on Monitoring and repor:ng database workload 9 

10 .Scalability with ProxySQL - Connection Pooling • Any application without persistent connection to database • PHP applications to be specific without built in connection pool Application ProxySQL MySQL • Reduces number of new connections to the database 10 

11 .Scalability with ProxySQL – Connection Multiplexing • Any application with persistent connection to database • Java applications to be specific with built in connection pools Application Pool Application Pool ProxySQL MySQL Application Pool • Reduce connections similar to Aurora • Testing being performed for 300K database connections 11 

12 .Scalability with ProxySQL - Read/Write Split • On the fly Read / Write implementation • Use read_only flag to switch traffic MySQL Master Write hostgroups Application Read hostgroups MySQL MySQL Slave Slave • Load balancing made easy 12 

13 .Scalability with ProxySQL – User and schema level sharding • Granular sharding per username and schema • Backend pooling based on user activity to specific schema Application MySQL MySQL User A Master Slave ProxySQL MySQL MySQL Master Slave Application User B MySQL MySQL Master Slave • Use advanced sharding to parallelize queries • Scale beyond the sharding per host limitations. 13 

14 .High Availability with ProxySQL – Seamless failover • Neither VIP setup nor service discovery needed • Use read_only flag to switch traffic Application Pool MySQL Master Application Pool ProxySQL Application Pool MySQL MySQLMaster Slave • Integration with other HA Managers 14 

15 .High Availability with ProxySQL – Improve Multi DC implementation • No connection pools latency on SSL connections. • Costly delays across the water. DC1 ProxySQL MySQL Master Application DC2 ProxySQL MySQL Master 15 

16 .High Availability with ProxySQL – Adoption to Clustering Node 1 • PXC / Galera / Group Replication MySQL Node 2 Application ProxySQL MySQL Application Node 3 ProxySQL MySQL Application • 16 

17 .Advanced Queries with ProxySQL – Caching • Improve performance on read intensive workloads • Reduce slave provisioning MySQL Master MySQL Slave Application A MySQL Slave ProxySQL Cache MySQL Application Slave B MySQL • Improved query cache over default implementation Slave • Query aware caching over general caching MySQL Slave 17 

18 .Advanced Queries with ProxySQL – Query Timeout • Built in query killer a.k.a query sniper • Adjustable threshold based on query rule MySQL Master MySQL Slave Application A MySQL Query Slave ProxySQL Timeouts MySQL Application Slave B MySQL • No idle or long running queries Slave MySQL Slave 18 

19 .Advanced Queries with ProxySQL – Firewall • Protect database from unwanted traffic • Stop unwanted user, account , application (new code) Application MySQL MySQL A Master Slave Query ProxySQL Blocking MySQL MySQL Master Slave Application B MySQL MySQL Master Slave • Fast modification to database behavior • Added protection for DDOS and other attacks. 19 

20 .Advanced Queries with ProxySQL – Query rewrite engine • Most wanted feature by DBAs • Rewrite queries overloading the database on the fly. MySQL Master MySQL Slave Application A MySQL Query Slave ProxySQL Rewriting MySQL Application Slave B MySQL • Simply buy time until application can be modified Slave MySQL Slave 20 

21 .Advanced Queries with ProxySQL – Query retry • Server failures or maintenance do not loose a query. • Fails over and resubmits the query to another host. MySQL Master MySQL Slave Application A MySQL Query Slave ProxySQL Retry MySQL Application Slave B MySQL • Improved operation and new way of handling slaves. Slave MySQL Slave 21 

22 .Advanced Queries with ProxySQL – Query routing • Selective routing based on importance. • Use metrics based on stats_mysql_query_digest and decide MySQL Master MySQL Slave Application A MySQL Query Slave ProxySQL Routing MySQL Application Slave B MySQL • Go beyond read/write split. Slave MySQL Slave 22 

23 .Advanced Queries with ProxySQL – Query mirroring • Mirror incoming queries to different back ends Host A • Use it for audit, analytics, cache warming, benchmarking. MySQL MySQL Master Slave Application A Query ProxySQL Mirroring Host B Application B MySQL MySQL Master Slave 23 

24 .Advanced Queries with ProxySQL Query caching Query Timeout Query Rou:ng and Mirroring Query Retry Query Blocking 24 

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) • Up to 1M client connections • 1600 backend servers • 2000 query rules • 100k distinct users • up to 750k QPS 25 

26 .DATABASE PERFORMANCE MATTERS