Download as PDF, PPTX
Uploaded bySeveralnines
MySQL Load Balancers - Maxscale, ProxySQL, HAProxy, MySQL Router & nginx - A Close Up Look
The document discusses various types of MySQL load balancers, including MaxScale, ProxySQL, HAProxy, MySQL Router, and NGINX, highlighting their features, advantages, and specific use cases. It emphasizes the importance of load balancing for high availability, performance, and minimizing application complexity, providing insights into query routing, health checks, and management tools. The document suggests selecting a suitable load balancer based on specific requirements, such as advanced features or integration with existing MySQL setups.
In this document
Powered by AI
Overview of the presenter and the focus on MySQL Load Balancers like MaxScale, ProxySQL, and others.
Discussion on the importance of load balancers, including application connectors and SQL-aware proxies, and their roles in high availability and performance.
Explanation of application connectors such as mysqlnd_ms and MySQL Connector/J, highlighting features like read/write splitting and failover handling.
Description of reverse proxies including HAProxy, focusing on functionalities and limitations regarding MySQL backends.
Insights into using Keepalived for high availability setups in MySQL clusters, including configuration examples.
Detailed comparison of NGINX’s functionality for MySQL load balancing, including health checks and operational constraints.
Introduction to MaxScale, its capabilities like read/write splitting, health monitoring, routing based on rules, and known limitations.
Details regarding MaxScale's connection management, performance concerns, and configurations.
Introduction to ProxySQL, its advanced features for query routing, health monitoring, and dynamic adjustments.
Overview of MySQL Router, its integration with MySQL Fabric, and features supporting high availability and scalability.
Discussion on health checks used with load balancers, including the use of scripts for monitoring MySQL node states.
Summary of options available for load balancing in MySQL databases and recommendations based on user needs.
Wrap-up of the presentation, thank you note, and invitation for questions from the audience.
MySQL Load Balancers - Maxscale, ProxySQL, HAProxy, MySQL Router & nginx - A Close Up Look
- 1. Copyright 2017 SeveralninesAB Krzysztof Książek, Senior Support Engineer @Severalnines krzysztof@severalnines.com Presenter MySQL Load Balancers - MaxScale, ProxySQL, HAProxy, MySQL Router & nginx - a close up look April, 26th 2017
- 2. Copyright 2017 SeveralninesAB Why load balancers? Types of load balancers •Application connectors •Reverse proxies •SQL-aware proxies Health checks used with proxies Agenda
- 3. Copyright 2017 SeveralninesAB High availability •Detect node health •Route traffic away from failed nodes Performance •Scale out by routing traffic to multiple nodes •Minimize number of connections to databases Why load balancers? Keep complexity away from the application •Monitor node state •Understand replication topology •Failover client connections Additional features •Collect performance data from cluster •Rewrite queries as they pass the proxy •Route queries depending on some rules (e.g. Read/write split)
- 4. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB Application connectors
- 5. Copyright 2017 SeveralninesAB mysqlnd_ms Native driver for MySQL in PHP •Default since PHP 5.4 mysqlnd_ms adds option to implement read/write split and load balancing to PHP application Supports failover handling Supports query routing modifications through SQL hints Application connectors MySQL Connector/J Adds great deal of flexibility to Java applications when it comes to database layer handling Failover handling Live reconfiguration of replication topology Advanced routing options
- 6. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB Reverse proxies
- 7. Copyright 2017 SeveralninesAB Industry standard - thousands of installations across the world 1.7 released in November, 2016 •1.7.5 released on April, 3rd, 2017 Reverse proxies - HAProxy Reverse proxy •Load balancing, scaling out, failover •Lack of advanced internal support for MySQL backends
- 8. Copyright 2017 SeveralninesAB Lack of ability to perform read/write split on a single port •Two backends on two different ports are required •Could be a blocker if an application can’t split reads from writes Reverse proxies - HAProxy No understanding of MySQL state •Only simple check on port 3306: handshake or authentication Workaround exists: clustercheck •HTTP check, xinetd, external script to monitor MySQL/Galera states
- 9. Copyright 2017 SeveralninesAB Reverse proxies - HAProxy
- 10. Copyright 2017 SeveralninesAB Designed to detect failed nodes and move VIP around One single VIP for connections, no load balancing is possible •Might be useful to build high availability if you connect only to a single node Potential use cases: •Galera cluster (for HA only) •Master - standby replication •pair of hosts connected through DRBD Reverse proxies - Keepalived
- 11. Copyright 2017 SeveralninesAB Example config for Galera cluster of three nodes •Keepalived should be installed on all hosts •All hosts should be using this configuration Leverage mysqlchk script deployed by ClusterControl •Percona’s clustercheck can also be used Can also be configured to use multicast Reverse proxies - Keepalived
- 12. Copyright 2017 SeveralninesAB A HTTP server and a HTTP reverse proxy Since 1.9 can be used as a TCP reverse proxy Limited features in free (NGINX) version •No configurable port for health checks •Not possible to integrate with clustercheck scripts More features in paid (NGINX Plus) version •Advanced health checks •Custom port for checking the health of the backend Reverse proxies - NGINX
- 13. Copyright 2017 SeveralninesAB Functionality similar to HAProxy (with regards to MySQL) •Read/write split could be done using two ports •Backup servers work differently though - all at once •No understanding of MySQL and Galera states Has to be used with clustercheck-iptables Reverse proxies - NGINX
- 14. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB SQL-aware proxies - MaxScale
- 15. Copyright 2017 SeveralninesAB Created by MariaDB Corporation, currently in version 2.0.5 (2.1 in beta) Tool designed to solve load balancing needs of DBA’s •Handles read/write split •Automatically tracks health of backend nodes •Understands MySQL and Galera states •Master or slave? Synced or donor? SQL-aware proxies - MaxScale
- 16. Copyright 2017 SeveralninesAB Support for SSL Support for replication, Galera and MySQL Cluster Statement hinting •Where to route •Maximum replication lag Nagios integration Query mirroring SQL-aware proxies - MaxScale Caching mechanism (in 2.1) Result set masking (in 2.1) Result set limiting (in 2.1) Consistent Critical Reads (in 2.1)
- 17. Copyright 2017 SeveralninesAB Several types of routers are available: •readwritesplit - performs read/write split •readconnroute - round-robin access •dbfwfilter - filters queries based on regex •schemarouter - implements schema- based sharding •binlogrouter - MaxScale as a binlog server •avrorouter - saves binlogs in Avro file SQL-aware proxies - MaxScale Design decisions •Read queries are routed to master if executed within a transaction •Stored procedures, UDF’s, prepared statements are sent to the master only •Session variables and settings are sent to all hosts •No connection multiplexing
- 18. Copyright 2017 SeveralninesAB Lack of real-time traffic statistics (slightly improved in 2.1) No remote admin interface (available in 1.4.x, removed in 2.0) Most of configuration settings require restart (improved in 2.1) Lack of more advanced routing configuration •Route to master or slave, using hints •No regex-based routing SQL-aware proxies - MaxScale Serious scalability issues with more than 100 concurrent threads running •Based on Vadim Tkachenko’s blog post from May 12, 2016 who tested it on MaxScale 1.4.1 Performance regression when using more than 8 threads for MaxScale (from the blog post as stated above) Non-GPL license starting from 2.0 •Up to two nodes free (more - paid) •GPL fork of 1.3 branch - AirBnB MaxScale
- 19. Copyright 2017 SeveralninesAB SQL-aware proxies - MaxScale
- 20. Copyright 2017 SeveralninesAB SQL-aware proxies - MaxScale
- 21. Copyright 2017 SeveralninesAB SQL-aware proxies - MaxScale
- 22. Copyright 2017 SeveralninesAB 320 connections: SQL-aware proxies - MaxScale 16 connections: 200 connections:
- 23. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB SQL-aware proxies - ProxySQL
- 24. Copyright 2017 SeveralninesAB Created by René Cannaò, currently in version 1.3.6 (1.4 announced as beta) Designed to handle complex topologies •Implements read write split •Support for multiple hostgroups •Tracks health of backends on per-query basis •Advanced routing based on query rules SQL-aware proxies - ProxySQL
- 25. Copyright 2017 SeveralninesAB Admin interface uses SQL •INSERT INTO mysql_servers … •SET mysql-query_retries_on_failure=…; Cisco-like configuration approach •LOAD MYSQL QUERY RULES TO RUNTIME; •SAVE MYSQL USERS TO DISK; Virtually every setting can be changed dynamically SQL-aware proxies - ProxySQL Query rules mechanism for great flexibility •Match queries using regex, digest hash, user, schema, IP, port •Route queries based on above conditions •Query rewrite and query mirroring •Query caching - TTL and memory limit
- 26. Copyright 2017 SeveralninesAB Connection multiplexing - minimizes number of connections to backends Support for MySQL replication and Group Replication (in 1.4) Support for Galera Cluster using external scripts •Example monitoring script is provided with the ProxySQL SQL-aware proxies - ProxySQL Non-standard approach to backend health monitoring •Health check happens when traffic is sent •Monitor module extends functionality Detailed statistics about traffic handled by the ProxySQL
- 27. Copyright 2017 SeveralninesAB No client side SSL support - in the roadmap Lack of out-of-the-box support for explicit transactions •You have to either use SET autocommit=0 •Or enable persistent transactions for app user Session variables are tricky to support •You have to make sure they are routed to a single, writable node (i.e. replication master) SQL-aware proxies - ProxySQL Lack of direct nagios integration Lack of internal support for Galera •Although support via external script gives you more options for customization No advanced logging options (JSON, Kafka)
- 28. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB SQL-aware proxies - MySQL Router
- 29. Copyright 2017 SeveralninesAB Created by Oracle, currently in version 2.1.3 Designed to provide high availability and scalability to your MySQL backends Supports external plugins using MySQL Harness Integrates well with MySQL Fabric for high availability setups Used as a part of MySQL InnoDB Cluster SQL-aware proxies - MySQL Router
- 30. Copyright 2017 SeveralninesAB Two modes for routing •read-only - read-only connections routed in round-robin fashion •read-write - read-write connections routed to the first working node from the list Support for MySQL Fabric: •Can route traffic to given HA group •Topology detection via Fabric SQL-aware proxies - MySQL Router
- 31. Copyright 2017 SeveralninesAB Support for MySQL InnoDB Cluster - a solution based on MySQL group replication •Deployable via MySQL Shell •Provides a support for InnoDB Cluster Metadata Schema •Caches metadata of the InnoDB Cluster and improves high availability SQL-aware proxies - MySQL Router
- 32. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB Health checks used with proxies
- 33. Copyright 2017 SeveralninesAB HTTP check for HAProxy •Will work with any other proxy which supports http checks on custom port Script itself is a bash script which checks MySQL status •Designed for Galera but can be easily modified to use with replication •Checks wsrep_local_state for Galera states •Configurable Donor/Desync state handling Health checks used with proxies - clustercheck Script returns HTTP codes •HTTP/1.1 200 OK - if node is available •HTTP/1.1 503 Service Unavailable - otherwise Clustercheck should be installed on all databases nodes •It’s executed by xinetd, listening on custom port (9200 by default) •Make sure to configure it in /etc/services
- 34. Copyright 2017 SeveralninesAB By default, script is executed directly from xinetd •Could become a problem if it’s executed too often Numerous forks are available out there •One of nice modifications was to execute script via cron and store result in shared memory •xinetd executes another script which reads the status - minimizing impact on MySQL Health checks used with proxies - clustercheck
- 35. Copyright 2017 SeveralninesAB Derived from Percona’s clustercheck Created by Severalnines’ Ashraf Sharif Designed to work with proxies which can do only simple check on port used for service •For example, NGINX Idea is simple - use iptables to open or close port, depending on node’s state Health checks used with proxies - clustercheck-iptables Run script in a background, as a daemon Setup redirection from port 3308 to port 3306 in PREROUTING chain Every second checks the state of a node •If node is deemed unhealthy, remove redirection rule from PREROUTING chain Similar approach to health detection as in Percona’s clustercheck •Checks wsrep_local_state for Galera states
- 36. Copyright 2017 SeveralninesAB Copyright 2017 Severalnines AB Summary
- 37. Copyright 2017 SeveralninesAB Great deal of options to pick from •Everyone can choose a suitable solution Do you use NGINX in your setup? Why not to leverage your knowledge of it and use it for databases too? •You’d need clustercheck-iptables or NGINX Plus, though Do you want to build all database handling logic in your application? •PHP and Java give you great opportunities for that Summary Or maybe you prefer to move complexity of database layer from the application? •MaxScale or ProxySQL is there for you Maybe you want to use advanced features like query rewriting or sharding? •Again, ProxySQL or MaxScale will work great for you Do you work with MySQL Fabric or MySQL InnoDB Cluster and want some integration? •MySQL Router could be a great choice
- 38. Copyright 2012 SeveralninesAB Thank You! Contact: krzysztof@severalnines.com Q&A