Download as PDF, PPTX
Uploaded byRené Cannaò
ProxySQL - High Performance and HA Proxy for MySQL
ProxySQL is a high-performance, high-availability proxy designed for MySQL, focusing on query rewriting, caching, and connection pooling to optimize database operations. It offers features like load balancing, real-time statistics, seamless failover, and a robust admin interface, enabling efficient management of database traffic. The software supports complex setups and is aimed at improving the experience for database administrators, with future enhancements planned for security and multi-database support.
In this document
Powered by AI
Introduction to ProxySQL, a high-performance and stable MySQL proxy. Version 0.2 is production-ready.
Explains motivations behind ProxySQL such as rewriting queries and caching. Mentions other MySQL proxies.
Comparison of MySQL Proxy and HAProxy including pros and cons. Highlights issues with reliability and protocol understanding.
Describes ProxySQL's functions including rewrite, caching, query routing, load balancing, and monitoring with emphasis on DBA empowerment.
Details on hostgroups in ProxySQL, examples of configurations, logical functionalities, and routing without replication awareness.
Explanation of ProxySQL's internal design, query rewriting capabilities, caching strategies, and benchmarks for performance.
User authentication methods, credential storage, and future security enhancements in ProxySQL.
Management of hostgroups, connection pooling, multiplexing, and failover management for optimized connections.
Monitoring capabilities of ProxySQL, admin interface features, and database querying options for performance tracking.
Best practices for deploying ProxySQL, including location, setup configurations for optimal database connectivity.
Complex setups for high availability, failover processes, and distributed failover implementations for ProxySQL.
Upcoming features for ProxySQL including improved security, GUI, and support for multiple backend systems.
ProxySQL - High Performance and HA Proxy for MySQL
- 1. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com ProxySQL High Performance &High Availability Proxy for MySQL René Cannaò rene@sysown.com
- 2.
- 3. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Genesis: main motivations rewritequeries cache frequent reads solving/finding the answer for DEVs vs DBAs
- 4. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Open Source proxiesfor MySQL MySQL Proxy HAProxy http://tinyurl.com/ptjmzde Now also MaxScale
- 5. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com MySQL Proxy Pros: customizable expandable acceptable performance Cons: CPUintensive Not really reliable Not maintained
- 6. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com HAProxy Pros: very stable high performance maturesoftware 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/sysown.com Proxy for rewriteand caching? MySQL Proxy with: Lua script External caching
- 8. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com ProxySQL Network database proxythat 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/sysown.com The right way! Designedby 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/sysown.com Features Some of themost 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 High Availability and Scalability Seamless failover Firewall Query throttling query timeout runtime reconfiguration
- 11. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Hostgroups and QueryRouting All backends are grouped into hostgroups Hostgroups have logical functionalities
- 12. @proxysql http://proxysql.com https://github.com/sysown/proxysql/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/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/sysown.com Support for heterogeneoussetups 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.
- 16. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Basic design ,example #2 HG0 HG2 HG1 ProxySQL APP1 APP2 APP3
- 17.
- 18. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Queries Processor Rewrite queries Defineswhat to cache Defines the hostgroup target
- 19. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Queries rules Complex rulesto 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/sysown.com Queries cache Caching onthe wire Internal key/value storage In memory only Pattern based Expired by timeout
- 21.
- 22. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Queries Rewrite Rewrite onthe wire Regex match/replace Optionally cached
- 23.
- 24.
- 25. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Users Authentication Credentials storedin 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/sysown.com Hostgroup Manager and ConnectionsPool 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/sysown.com Hostgroups Manager Management ofservers Track servers status Tightly integrated with the connections pool
- 28. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Connections Pool Reduced theoverhead 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/sysown.com Multiplexing Reduce the numberof 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/sysown.com Auto-reconnect and re-execution Automaticdetection 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/sysown.com Failover management Seamless switchover: http://www.proxysql.com/2015/09/proxysql-tutorial-seamles s-replication.html Managedby external process Switchover in less than 1 second
- 32. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Monitoring Module HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread#1 Thread#2 Thread#3 UsersAuth Connection Pool Hostgroup Manager Monitoring
- 33. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Monitoring Module It monitorsbackends and collects metrics Monitors replication lag and shun hosts
- 34. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Admin Interface HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread#1 Thread#2 Thread#3 UsersAuth Connection Pool Hostgroup Manager Admin Monitoring
- 35. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Admin Interface Allows runtimeconfiguration 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/sysown.com Admin Interface mysql> showdatabases; +-----+---------+------------------+ | 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/sysown.com Admin Interface mysql> SHOWTABLES 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/sysown.com Admin Interface mysql> SHOWTABLES 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/sysown.com Admin Interface mysql> SHOWTABLES 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/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+FORs+UPDATEs*$ | 0 | 0 | -1 | | .* | 0 | 1 | 30 | +---------------------+------+-------+------+
- 41. @proxysql http://proxysql.com https://github.com/sysown/proxysql/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 NULLG 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.
- 43. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Location, location, location ProxySQLsits between the application and the database systems Where is exactly the best location?
- 44. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Basic design HG0 HG2 HG1 APP APP ProxySQL Very lowlatency using Unix Domain Socket
- 45.
- 46.
- 47.
- 48. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Hostgroup reconfiguration HG0 HG1 1 43 ProxySQLAPP 2 Removehost : 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/sysown.com Failover 2 phases process: removehost add host Fully supported Seamless failover in under 1 second
- 50. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com Failover : 2phases task HG0 HG1 1 43ProxySQLAPP 2 1.Connections to an empty hostgroup are put on hold 2 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/sysown.com Distributed failover Multiple ProxySQLare 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.
- 53.
- 54. @proxysql http://proxysql.com https://github.com/sysown/proxysql/sysown.com New features inroadmap 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/sysown.com Try it! Source codeon GitHub: https://github.com/sysown/proxysql 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/sysown.com Please contact me Thanks! Feedback,feature requests, contribution: rene.cannao@gmail.com rene@sysown.com @rene_cannao @proxysql