ProxySQL - High Performance & High Availability Proxy for MySQL

This presentation was given by Rene Cannao at Percona Live Amsterdam 2015 as ProxySQL introduction.

Talk abstract is as follows:

"ProxySQL is a new proxy (currently under development) that aims to become the first open source proxy in the MySQL ecosystem able to provide HA and high performance with no changes in the application, using several built-in features and integration with clustering software. Features under development are: - read/write split ; - queries caching ; - queries filtering and rewrite ; - logging ; - transparent reconnect ; - connection pooling ; - built-in key/value storage ; - integration with clustering solution like MHA for planned and/or automatic failover; - support for master-slave setups (MySQL replication) or multi-masters (NDB or Galera)".

Transcript

1. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com ProxySQL High Performance & High Availability Proxy for MySQL René Cannaò rene@sysown.com
2. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com GA Stable and production ready version 0.2
3. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Genesis: main motivations rewrite queries cache frequent reads solving/finding the answer for DEVs vs DBAs
4. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Open Source proxies for MySQL MySQL Proxy HAProxy http://tinyurl.com/ptjmzde Now also MaxScale
5. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com MySQL Proxy Pros: • customizable • expandable • acceptable performance Cons: • CPU intensive • Not really reliable • Not maintained
6. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com HAProxy Pros: • very stable • high performance • mature software Cons: • Layer 7 proxy only for HTTP • Doesn’t understand the MySQL Protocol • Layer 4 for other services
7. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Proxy for rewrite and caching? MySQL Proxy with: Lua script External caching
8. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com ProxySQL Network database proxy that sits transparently between the application and the database to: • empower the DBAs • improve operation • understand and improve performance
9. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com The right way! Designed by a DBA for DBAs Experience in a variety of setups Experience in very large production environments
10. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com 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. runtime reconfiguration
11. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Hostgroups and Query Routing All backends are grouped into hostgroups Hostgroups have logical functionalities
12. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Hostgroups example #1 HostGroup0 (HG0): Write masters HostGroup1( HG1): Read slaves Read/Write split
13. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com 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
14. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Support for heterogeneous setups Hostgroups have logical functionalities No replication relationship between hostgroups ProxySQL is not replication aware by design, yet it monitors replication A query is routed to a single hostgroup
15. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Basic design HG0 HG2 HG1 ProxySQL APP
16. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Basic design , example #2 HG0 HG2 HG1 ProxySQL APP1 APP2 APP3
17. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Internals diagram HG0 HG1 APP1 ProxySQL Queries cache Queries Processor APP2 APP3 Thread #1 Thread #2 Thread #3
18. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries Processor Rewrite queries Defines what to cache Defines the hostgroup target
19. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries rules Complex rules to match incoming traffic. Regex on query username schemaname (a lot more in the roadmap) Rules can be chained
20. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries cache Caching on the wire Internal key/value storage In memory only Pattern based Expired by timeout
21. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries Cache Benchmark Benchmark on a 4 cores server
22. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries Rewrite Rewrite on the wire Regex match/replace Optionally cached
23. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries Rewrite Benchmark Ref: http://tinyurl.com/oan4528
24. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Users Authentication HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread #1 Thread #2 Thread #3 Users Auth
25. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Users Authentication Credentials stored in the proxy User login always possible (even without backends) Max connections Security enhancement in roadmap: • different credentials on backends and frontends • internal mapping
26. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Hostgroup Manager and Connections Pool HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread #1 Thread #2 Thread #3 Users Auth Connection Pool Hostgroup Manager
27. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Hostgroups Manager Management of servers Track servers status Tightly integrated with the connections pool
28. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com 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
29. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Multiplexing • Reduce the number of connections against mysqld (configurable) • Many clients connections (tens of thousands) can use few backend connections (few hundreds) • Order by waiting time Few edge cases not handled correctly: open a feature request if you hit any
30. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com 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. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Failover management • Seamless switchover: http://www.proxysql.com/2015/09/proxysql-tutorial-seamles s-replication.html • Managed by external process • Switchover in less than 1 second
32. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Monitoring Module HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread #1 Thread #2 Thread #3 Users Auth Connection Pool Hostgroup Manager Monitoring
33. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Monitoring Module It monitors backends and collects metrics Monitors replication lag and shun hosts
34. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Admin Interface HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread #1 Thread #2 Thread #3 Users Auth Connection Pool Hostgroup Manager Admin Monitoring
35. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Admin Interface Allows runtime configuration Exports internal statuses It uses MySQL protocol Configuration possible from any client/tool using MySQL API
36. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Admin Interface mysql> show databases; +-----+---------+------------------+ | seq | name | file | +-----+---------+------------------+ | 0 | main | | | 2 | disk | /tmp/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | myhgm | | +-----+---------+------------------+ 5 rows in set (0.00 sec)
37. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Admin Interface mysql> SHOW TABLES FROM main; +-------------------+ | tables | +-------------------+ | mysql_servers | | mysql_users | | mysql_query_rules | | global_variables | | mysql_collations | | debug_levels | +-------------------+ 6 rows in set (0.01 sec)
38. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Admin Interface mysql> SHOW TABLES FROM stats; +--------------------------------+ | tables | +--------------------------------+ | 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 | +--------------------------------+ 7 rows in set (0.00 sec)
39. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Admin Interface mysql> SHOW TABLES FROM monitor; +----------------------------------+ | tables | +----------------------------------+ | mysql_server_connect | | mysql_server_connect_log | | mysql_server_ping | | mysql_server_ping_log | | mysql_server_replication_lag_log | +----------------------------------+ 5 rows in set (0.00 sec)
40. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries cache rules proxysql-admin> SELECT match_pattern , negate_match_pattern neg , destination_hostgroup hs_id , cache_ttl ttl FROM mysql_query_rules WHERE replace_pattern IS NULL ORDER BY rule_id ; +---------------------+------+-------+------+ | match_pattern | neg | hs_id | ttl | +---------------------+------+-------+------+ | ^SELECT | 1 | 0 | -1 | | \s+FOR\s+UPDATE\s*$ | 0 | 0 | -1 | | .* | 0 | 1 | 30 | +---------------------+------+-------+------+
41. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries rewrite rules proxysql-admin> SELECT match_pattern m, replace_pattern r , destination_hostgroup hs_id , cache_ttl ttl FROM mysql_query_rules WHERE replace_pattern IS NOT NULL\G m: ^SELECT(| DISTINCT) c FROM sbtest WHERE id BETWEEN (.*) AND (.*) ORDER BY c r: SELECT c FROM sbtest WHERE id BETWEEN \2 AND \3 hs_id: 1 ttl: 30
42. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Deploy ProxySQL
43. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Location, location, location ProxySQL sits between the application and the database systems Where is exactly the best location?
44. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Basic design HG0 HG2 HG1 ProxySQL APP APP ProxySQL Very low latency using Unix Domain Socket
45. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Basic design HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL
46. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com High Availability HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL HG0 ProxySQL ProxySQL
47. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com HG0 HG2 HG1 APP1 ProxySQL APP3 ProxySQL ProxySQL ProxySQL Complex Potential Setups APP2 ProxySQL ProxySQL HG0 HG0 HG0 HG1 ProxySQL ProxySQL
48. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Hostgroup reconfiguration HG0 HG1 1 4 3 ProxySQL APP 2 Remove host : all the connections to server2 are terminated 1 Add host : New connections to HG1 can use also server1
49. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Failover 2 phases process: • remove host • add host Fully supported Seamless failover in under 1 second
50. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Failover : 2 phases task HG0 HG1 1 4 3 ProxySQL APP 2 Connections to an empty hostgroup are put on hold 2 Connections to HG0 are resumed Connections to HG1 were never stopped 1) remove host 2) add host
51. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Distributed failover Multiple ProxySQL are available in a network Failover managed by an external process that: • remove host from each ProxySQL instance • add new host into each ProxySQL instance Manager is not part of ProxySQL. Ex: MHA or MySQL Utilities
52. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com APP1 ProxySQL APP2 ProxySQL Distributed failover : remove host HG0 1 HG1 3 2 4 MHA 2
53. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com APP1 ProxySQL APP2 ProxySQL Distributed failover : add host HG0 1 HG1 3 4 MHA 2
54. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com New features in roadmap • Improved security (SSL support, SQL injection detection, frontend/backend users, etc) • GUI • Prepared statements • Support for multiple backends (postgres, redis, mongo) • Enhanced monitoring and statistics
55. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Try it! Source code on GitHub: https://github.com/sysown/proxysql-0.2/ Binaries on GitHub: https://github.com/sysown/proxysql-binaries Forum: https://groups.google.com/forum/#!forum/proxysql Tutorial on: http://www.proxysql.com
56. @proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Please contact me Thanks! Feedback, feature requests, contribution: rene.cannao@gmail.com rene@sysown.com @rene_cannao @proxysql