@proxysql http://proxysql.com https://github.com/renecannao/proxysql ProxySQL High Performance & High Availability Proxy for MySQL René Cannaò @rene_cannao

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Genesis: main motivations rewrite queries cache frequent reads

@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

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Open Source proxies for MySQL MySQL Proxy HAProxy http://tinyurl.com/ptjmzde

@proxysql http://proxysql.com https://github.com/renecannao/proxysql MySQL Proxy Pros: ● customizable ● expandable ● acceptable performance Cons: ● CPU intensive ● Not really reliable ● Not maintained

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Proxy for rewrite and caching? MySQL Proxy with: Lua script External caching

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Hostgroups and Query Routing All backends are grouped into hostgroups Hostgroups have logical functionalities

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Hostgroups examples HG0: Write masters HG1: Read slaves Read/Write split

@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

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Basic design HG0 HG2 HG1 ProxySQL APP

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Basic design , example #2 HG0 HG2 HG1 ProxySQL APP1 APP2 APP3

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Processor Rewrite queries Defines what to cache Defines the hostgroup target

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Cache Benchmark Benchmark on a 4 cores server

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Rewrite Rewrite on the wire Regex match/replace Optionally cached

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Queries Rewrite Benchmark Ref: http://tinyurl.com/oan4528

@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

@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)

@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

@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

@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 | +---------------------+------+-------+------+

@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

@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?

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql 80/20 rule HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL

@proxysql http://proxysql.com https://github.com/renecannao/proxysql High Availability HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL HG0 ProxySQL ProxySQL

@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

@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

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Failover 2 phases process: ● remove host ● add host Still experimental

@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

@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/

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql APP1 ProxySQL APP2 ProxySQL Distributed failover : add host HG0 1 HG1 3 4 MHA 2

@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

@proxysql http://proxysql.com https://github.com/renecannao/proxysql Contact me Thanks! Feedbacks are welcome: rene.cannao@gmail.com @rene_cannao @proxysql