ProxySQL : High Availability and High Performance Proxy for MySQL

This presentation was given by Rene Cannao, ProxySQL author and CEO, at FOSDEM 2014.

There are excellent Enterprise software which are able to scale out and boost performances of a cluster, but none open source. 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) .

The project is currently under active development, its source code and a mini how-to is available at https://github.com/renecannao/proxysql , and some initial benchmark are available at http://www.proxysql.com

Transcript

1. @proxysql http://proxysql.com https://github.com/renecannao/proxysql ProxySQL High Performance & High Availability Proxy for MySQL René Cannaò @rene_cannao
2. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Genesis: main motivations rewrite queries cache frequent reads
3. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Rewrite queries Some applications generate bloated queries Unnecessary joins Wrong use of indexes Not optimized for all storage engines Can cause: excessive load, high latency, site outage
4. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Cache frequent reads Possibly caching solutions: Caching in application or external caching Yet many applications don’t cache reads: Load and unnecessary latency
5. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Open Source proxies for MySQL MySQL Proxy HAProxy http://tinyurl.com/ptjmzde
6. @proxysql http://proxysql.com https://github.com/renecannao/proxysql MySQL Proxy Pros: • customizable • expandable • acceptable performance Cons: • CPU intensive • Not really reliable • Not maintained
7. @proxysql http://proxysql.com https://github.com/renecannao/proxysql HAProxy Pros: • very stable • high performance • mature software Cons: • Layer 7 proxy for only HTTP • Doesn’t understand the MySQL Protocol • Layer 4 for other services
8. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Proxy for rewrite and caching? MySQL Proxy with: Lua script External caching
9. @proxysql http://proxysql.com https://github.com/renecannao/proxysql ProxySQL Network proxy that sits transparently between the application and the database to solve: - on-the-fly rewrite of queries - caching reads outside the database server - load balancing - query routing and read/write split - High Availability
10. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Hostgroups and Query Routing All backends are grouped into hostgroups Hostgroups have logical functionalities
11. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Hostgroups examples HG0: Write masters HG1: Read slaves Read/Write split
12. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Hostgroups examples 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
13. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Support for heterogeneous setups Hostgroups have logical functionalities No replication relationship between hostgroups ProxySQL is not replication aware by design A query is routed to a single hostgroup
14. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Basic design HG0 HG2 HG1 ProxySQL APP
15. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Basic design , example #2 HG0 HG2 HG1 ProxySQL APP1 APP2 APP3
16. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Internals diagram HG0 HG1 APP1 ProxySQL Queries cache Queries Processor APP2 APP3 Thread #1 Thread #2 Thread #3
17. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Processor Rewrite queries Defines what to cache Defines the hostgroup target
18. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries cache Caching on the wire Internal key/value storage In memory only Pattern based Expired by timeout Roadmap : other criteria to expire cache entries distributed caching
19. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Cache Benchmark Benchmark on a 4 cores server
20. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Rewrite Rewrite on the wire Regex match/replace Optionally cached
21. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Rewrite Benchmark Ref: http://tinyurl.com/oan4528
22. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Connections Pooling & Users Auth HG0 HG1 APP1 ProxySQL Queries cache Query Processor APP2 APP3 Thread #1 Thread #2 Thread #3 Users Auth Connections Pooling
23. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Connections Pooling & Users Auth Reduced the overhead of creating new connections, and are recycled when not in use Users login always possible One to many connections Auto-reconnect (unstable) Failover management (unstable)
24. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Admin Interface HG0 HG1 APP ProxySQL Queries cache Query Processor APP APP Thread #1 Thread #2 Thread #3 Users Auth Connections Pooling Admin
25. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Admin Interface Allows runtime configuration Exports internal statuses It uses MySQL protocol Configuration possible from any client/tool using MySQL API
26. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries cache rules proxysql-admin> SELECT match_pattern , negate_match_pattern neg , destination_hostgroup hs_id , cache_ttl ttl FROM 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 | +---------------------+------+-------+------+
27. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries rewrite rules proxysql-admin> SELECT match_pattern m, replace_pattern r , destination_hostgroup hs_id , cache_ttl ttl FROM 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
28. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Location, location, location ProxySQL sits between the application and the database systems Where is exactly the best location?
29. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Basic design : 80/20 rule HG0 HG2 HG1 ProxySQL APP APP ProxySQL Very low latency using Unix Domain Socket
30. @proxysql http://proxysql.com https://github.com/renecannao/proxysql 80/20 rule HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL
31. @proxysql http://proxysql.com https://github.com/renecannao/proxysql High Availability HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL HG0 ProxySQL ProxySQL
32. @proxysql http://proxysql.com https://github.com/renecannao/proxysql HG0 HG2 HG1 APP1 ProxySQL APP3 ProxySQL ProxySQL ProxySQL Complex Potential Setups APP2 ProxySQL ProxySQL HG0 HG0 HG0 HG1 ProxySQL ProxySQL
33. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Location and multi-layer 80/20 rule Close to the application SQL caching like caching for HTTP, DNS, FTP High Availability easy to achieve Multi-layer caching Sharding
34. @proxysql http://proxysql.com https://github.com/renecannao/proxysql 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
35. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Failover 2 phases process: • remove host • add host Still experimental
36. @proxysql http://proxysql.com https://github.com/renecannao/proxysql 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
37. @proxysql http://proxysql.com https://github.com/renecannao/proxysql 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 https://code.google.com/p/mysql-master-ha/
38. @proxysql http://proxysql.com https://github.com/renecannao/proxysql APP1 ProxySQL APP2 ProxySQL Distributed failover : remove host HG0 1 HG1 3 2 4 MHA 2
39. @proxysql http://proxysql.com https://github.com/renecannao/proxysql APP1 ProxySQL APP2 ProxySQL Distributed failover : add host HG0 1 HG1 3 4 MHA 2
40. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Try it Source code on GitHub: https://github.com/renecannao/proxysql Tutorial on: http://www.proxysql.com
41. @proxysql http://proxysql.com https://github.com/renecannao/proxysql Contact me Thanks! Feedbacks are welcome: rene.cannao@gmail.com @rene_cannao @proxysql