ProxySQL - High Performance & High Availability Proxy for MySQL

Slide 1

Slide 1 text

@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

Slide 2

Slide 2 text

@proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com GA Stable and production ready version 0.2

Slide 3

Slide 3 text

@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

Slide 4

Slide 4 text

@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

Slide 5

Slide 5 text

@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

Slide 6

Slide 6 text

@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

Slide 7

Slide 7 text

@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

Slide 8

Slide 8 text

@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

Slide 9

Slide 9 text

@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

Slide 10

Slide 10 text

@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

Slide 11

Slide 11 text

@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

Slide 12

Slide 12 text

@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

Slide 13

Slide 13 text

@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

Slide 14

Slide 14 text

@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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

@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

Slide 18

Slide 18 text

@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

Slide 19

Slide 19 text

@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

Slide 20

Slide 20 text

@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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

@proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Queries Rewrite Benchmark Ref: http://tinyurl.com/oan4528

Slide 24

Slide 24 text

@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

Slide 25

Slide 25 text

@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

Slide 26

Slide 26 text

@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

Slide 27

Slide 27 text

@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

Slide 28

Slide 28 text

@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

Slide 29

Slide 29 text

@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

Slide 30

Slide 30 text

@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

Slide 31

Slide 31 text

@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

Slide 32

Slide 32 text

@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

Slide 33

Slide 33 text

@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

Slide 34

Slide 34 text

@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

Slide 35

Slide 35 text

@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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

@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

Slide 42

Slide 42 text

@proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Deploy ProxySQL

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

@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

Slide 45

Slide 45 text

@proxysql http://proxysql.com https://github.com/sysown/proxysql-0.2/ sysown.com Basic design HG0 HG2 HG1 APP1 ProxySQL APP2 ProxySQL APP3 ProxySQL

Slide 46

Slide 46 text

@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

Slide 47

Slide 47 text

@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

Slide 48

Slide 48 text

@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

Slide 49

Slide 49 text

@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

Slide 50

Slide 50 text

@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

Slide 51

Slide 51 text

@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

Slide 52

Slide 52 text

@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

Slide 53

Slide 53 text

@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

Slide 54

Slide 54 text

@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

Slide 55

Slide 55 text

@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

Slide 56

Slide 56 text

@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