proxysql tutorial
play

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

ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license! Santa Clara, California | April 24th 27th, 2017 Who we are David Turner DBA, Uber Derek Downey OSDB Practice Advocate, Pythian Ren Canna


  1. ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license! Santa Clara, California | April 24th – 27th, 2017

  2. Who we are David Turner DBA, Uber Derek Downey OSDB Practice Advocate, Pythian René Cannaò MySQL SRE, Dropbox / ProxySQL

  3. Agenda ● ProxySQL: Fundamentals (René Cannaò) ● The 3 Rs of ProxySQL (David Turner) ● Failover and Mirroing with ProxySQL (Derek Downey)

  4. Other Sessions Use ProxySQL to Improve Your MySQL High Availability Solution Marco "The Grinch" Tusa Tuesday April 25th , 11:30AM - 12:20PM - Ballroom D Percona XtraDB Cluster 5.7 with ProxySQL for your high availability and clustering needs Ramesh Sivaraman, Krunal Bauskar Tuesday April 25th , 4:20PM - 4:45PM - Room 209

  5. Other Sessions (2) Advance Sharding Solution with ProxySQL Marco "The Grinch" Tusa Tuesday April 25th, 4:50PM - 5:15PM - Ballroom A MySQL Load Balancers - MaxScale, ProxySQL, HAProxy, MySQL Router & nginx - a close up look Krzysztof Ksi ąż ek Wednesday April 26th, 11.10AM - 12.00PM - Ballroom D

  6. Other Sessions (3) ProxySQL Use Case Scenarios Alkin Tezuysal, René Cannaò Wednesday April 26th, 11.10AM - 12.00PM - Room 210 Inexpensive Datamasking for MySQL with ProxySQL - data anonymization for developers Frédéric Descamps, René Cannaò Wednesday April 27th, 11.00AM - 11.50AM - Ballroom F

  7. Overview of ProxySQL

  8. What is ProxySQL? The MySQL data stargate

  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

  10. ProxySQL Features 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

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

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

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

  14. 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

  15. Basic design HG0 APP1 HG1 ProxySQL APP2 HG2 APP3

  16. Basic design HG0 APP APP HG1 ProxySQL HG2 Very low latency using Unix Domain Socket

  17. One ProxySQL per host

  18. Silos approach

  19. Clustered ProxySQL Architecture APP APP APP APP PROXYSQL PROXYSQL PROXYSQL PROXYSQL Clustered Proxy Layer PROXYSQL PROXYSQL PROXYSQL DBMANAGER DB DB DB DB DB DB

  20. 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

  21. What’s new in ProxySQL 1.4.0 Native Support for Group Replication Multiple regex engines Better performance Support for hundreds of thousands of users Support for tens of thousands of database servers Better queries control http://www.proxysql.com/blog/releasing-proxysql-140

  22. ProxySQL Internals

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

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

  25. 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

  26. 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

  27. Users Authentication Credentials stored in the proxy User login always possible (even without backends) Max connections Login credentials are encrypted

  28. Hostgroups Manager Management of servers Track servers status Tightly integrated with the connections pool

  29. 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

  30. 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

  31. 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

  32. Monitoring Module It monitors backends and collects metrics Monitors replication lag and shun hosts Monitors read_only variables (replication hostgroups) Ping and terminates unresponsive nodes

  33. Monitoring Module Built in support for asynchronous replication 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

  34. Admin Module

  35. 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

  36. 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 Join us at booth #102

  37. Demo Environment

  38. Demo Code on GitHub: https://github.com/dtest/proxysql-tutorial Run locally: Docker, Ansible Some hosts provided: Username: plsc Passwords: proxysql

  39. The 3 Rs of ProxySQL and related statistics

  40. ProxySQL ● The 3 Rs ○ Rules ○ Rewrite ○ Routing ● Related Statistics ○ Identify offensive queries and resources consumed ○ View results of Remediation

  41. ProxySQL Troubleshooting Methodology ● Identify problem via stats tables ● Arrive at a solution ○ Match columns ○ Action columns

  42. Identify the problem ● stats_mysql_query_rules ● stats_mysql_commands_counters ● stats_mysql_processlist ● stats_mysql_connection_pool ● stats_mysql_query_digest ● stats_mysql_query_digest_reset ● stats_mysql_global

  43. Match Related Tables ● mysql_rules ● mysql_users ○ ○ username active ○ ○ schemaname fast_forward(bypass) ○ flagIN/OUT ● global_variables ○ client_addr ● mysql_collations ○ proxy_addr ○ Default(utf8) ○ proxy_port ○ No stats ○ digest ○ match_digest ○ match_pattern ○ Negate_match_pattern

  44. Action related fields ● flagOUT ● active ● replace_pattern ● destination_hostgroup ● cache_ttl ● reconnect ● timeout ● retries ● delay ● mirror_flagOUT ● mirror_hostgroup ● error_msg ● log ● apply

  45. 3 Levels for ProxySQL Tables ● Disk ● Memory ● Runtime

  46. 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 memory to runtime ○ load from repo, then memory to runtime and later load to disk ● test select query rules and rewrites on a slaves first ● test mutable rules on a mirror or other throw away db.

  47. Stats and Query Rewrite Tutorial ● http://tiny.cc/proxysql

  48. Thank you ● Derek Downey ● Krzysztof Ksiazek ● Marco Tusa ● Rene Cannao Links ● https://github.com/sysown/proxysql/tree/master/doc ● https://www.percona.com/blog/2016/08/30/mysql-sharding-with-proxysql/ ● https://tusacentral.net/joomla/index.php/mysql-blogs/183-proxysql-percona- cluster-galera-integration.html ● http://severalnines.com/blog/how-proxysql-adds-failover-and-query-control-y our-mysql-replication-setup

  49. Uber is hiring ● Growing and talented team ● Embracing Open Source Technologies ● Awesome projects awaiting you ● Cool campuses ● Build the future ● Uber Eats / Monthly credits (weekend coverage) ● Automated vehicles for all employees

  50. Failover with ProxySQL

Recommend


More recommend