Vettabase Webinar - Mastering MySQL High Availability with Galera

Join us for a deep dive into Galera Cluster for MySQL and its role in achieving high availability. We’ll explore load balancing configurations with HAProxy and ProxySQL, complete with real-world examples. The session also includes a demonstration of deadlocks in multi-master setups, explaining why they occur and how to prevent them. Perfect for DBAs and architects aiming to build resilient, scalable database infrastructures.

Transcript

1. Mastering MySQL High Availability with Galera Mike Rykmas mykhaylo.rykmas@vettabase.com 19 February 2025, 5:00 CET
2. WHO AM I? 01> MySQL consultant / DevOps engineer having 10+ years hands-on experience. 02> MySQL blogger: https://vettabase.com/author/mykhaylo-rykmas/ 03> Vettabase Ltd., a young company offering automation and expert services for MariaDB, MySQL, PostgreSQL and Cassandra.
3. Agenda • Introduction ◦ Quick welcome and purpose of today’s webinar • Understanding Load Balancing for Galera ◦ Importance and challenges. • Deep Dive into Load Balancing Tools ◦ Configurations and advanced features of HAProxy and ProxySQL. • Real-World Scenarios ◦ Demonstrations of problems and solutions related to load balancing. • Live Demo • Q&A
4. GIT repo • All materials and commands in repo: https://github.com/Vettabase/webinar-2025-02-19-mastering-mysql-high-availabili ty-with-galera
5. Understanding Load Balancing for Galera
6. Recap of Galera Basics • What is Galera Cluster? ◦ Definition: Galera Cluster is an open-source database clustering solution for MySQL, providing synchronous multi-master replication. ◦ Certification-Based Replication: Galera uses a certification process to determine if a transaction conflicts with any other transaction. If not, it is applied, ensuring no write-write conflicts and maintaining data integrity. ◦ Node Equality: Each node in the cluster is a full read and write member, which simplifies the architecture and management.
7. Recap of Galera Basics • Key Features of Galera Cluster ◦ Automatic Node Provisioning: New nodes automatically sync data with the cluster, reducing manual configuration and downtime. ◦ Multi-Master: Allows writes and reads from any node ◦ Deadlock Detection: Automatically detects and resolves deadlocks, enhancing database reliability. *
8. Recap of Galera Basics • Benefits of Using Galera Cluster ◦ No Single Point of Failure: With multiple masters, the failure of one node does not affect the availability of the database. ◦ High Scalability: horizontal scaling. ◦ Scalability: Distributes read and write loads across the nodes. *
9. Importance of Load Balancing in Galera • Distributes Client Requests ◦ Balanced Workload: Evenly distributes client requests across all nodes, preventing overload on any single node. ◦ Enhanced Response Times: Routes queries to the least loaded nodes, improving user experience with faster response times. • Prevents Node Overloads ◦ Avoids Bottlenecks: Ensures no single node becomes a performance bottleneck, which can lead to system slowdowns. ◦ Maintains System Health: Regular and balanced load distribution keeps the system performing optimally and extends the lifespan of each node.
10. Importance of Load Balancing in Galera • Increases Fault Tolerance ◦ Automatic Failover: Automatically reroutes traffic to healthy nodes in case of a node failure, ensuring no interruption in service. ◦ Redundancy: Complements Galera’s redundancy by enhancing system robustness against hardware or software failures. • Improves Scalability and Consistency ◦ Easy Scaling: Facilitates the integration of new nodes by automatically adjusting the load balancing configuration. ◦ Flexible Resource Management: Efficiently manages resources during peak loads, crucial for maintaining performance. ◦ Enhances Consistency and Reduces Deadlocks: Intelligent query routing helps maintain data consistency and reduces the likelihood of deadlocks by managing transaction flows across nodes.
11. Challenges Without Load Balancing Impact on Performance and System Health • Increased Risk of Node Overload ◦ Description: Without load balancing, certain nodes may receive disproportionately high amounts of traffic, leading to performance degradation. ◦ Consequence: Overloaded nodes can slow down, impacting response times and overall user experience. • Heightened Risk of Bottlenecks ◦ Description: Uneven distribution of queries and transactions might create bottlenecks in specific nodes. ◦ Consequence: This can lead to increased latency and reduced throughput, especially during peak usage times.
12. Challenges Without Load Balancing Risks to Fault Tolerance and Scalability • Decreased Fault Tolerance ◦ Description: In the absence of load balancing, a failure in a heavily loaded node can lead to significant portions of the database becoming unavailable. ◦ Consequence: This decreases the cluster’s overall resilience and ability to handle node failures smoothly. • Compromised Scalability ◦ Description: Without effective load distribution, adding more nodes to the cluster may not result in expected performance gains. ◦ Consequence: It becomes challenging to scale the system horizontally as new nodes might not alleviate the load on existing overloaded nodes.
13. Challenges Without Load Balancing Risks to Fault Tolerance and Scalability • Increased Likelihood of Deadlocks ◦ Description: Concentrated loads on certain nodes can lead to more frequent transaction conflicts and deadlocks. ◦ Consequence: This not only affects performance but also increases the complexity of database management and operational overhead.
14. Deep Dive into Load Balancing Tools
15. Load Balancing Tools Overview - HAProxy • Overview: HAProxy is a reliable, high-performance load balancer for TCP and HTTP-based applications that is frequently used to improve the availability and scalability of websites. • Strengths: ◦ High Performance and Reliability: Known for its stability, load balancing capabilities, and low latency handling. ◦ Flexibility: Offers various load-balancing algorithms (round-robin, least connections, source) to suit different needs. ◦ Health Checks: Provides active and passive monitoring to ensure traffic is only directed to healthy nodes. • Typical Use: Often used when simple, effective load balancing is needed without the necessity for understanding MySQL-specific queries.
16. Load Balancing Tools Overview - ProxySQL • Overview: ProxySQL is an SQL-aware proxy server that can be used to implement advanced load balancing features, query caching, and routing based on query type or content. • Strengths: ◦ SQL-aware Load Balancing: Understands MySQL queries which allows it to route reads and writes intelligently, optimizing traffic flow to the cluster. ◦ Query Caching: Can cache query results, reducing read load on the database servers and improving response times for frequently accessed data. ◦ Connection Pooling: Reduces the connection overhead on databases by maintaining a pool of connections that can be reused for multiple queries. • Typical Use: Best suited for environments where detailed query routing and performance optimization are required, particularly when differentiating between read and write queries.
17. Load Balancing Tools Overview - ProxySQL When to Use Each Tool HAProxy is best when: • High availability and simple load balancing are the primary concerns. • The application requires straightforward load distribution without the need for understanding SQL semantics. ProxySQL excels when: • Fine-grained control over SQL traffic is necessary. • There is a need to split read and write operations effectively based on content. • Advanced features like query caching and connection pooling can significantly enhance performance.
18. Load Balancing Algorithms for Galera
19. Load Balancing Tools Overview - ProxySQL Round Robin • Description: Distributes client requests sequentially across all available nodes in the list, then starts over from the beginning. • Pros: Simple and fair distribution; ensures all nodes are used evenly. • Cons: Does not account for node performance or current load, which can lead to inefficiencies if nodes vary in capacity or are temporarily overloaded. • Supported by: Both HAProxy and ProxySQL.
20. Load Balancing Tools Overview - ProxySQL Weight-Based • Description: Allocates requests to nodes based on assigned weights which reflect their capacity or performance metrics. • Pros: More sophisticated distribution that can handle nodes of differing capacities more effectively. • Cons: Requires initial setup to determine appropriate weights and periodic adjustments as node performance changes. • Supported by: Both HAProxy and ProxySQL (ProxySQL allows for dynamic weighting based on performance).
21. Load Balancing Tools Overview - ProxySQL Least Connections • Description: Routes new connections to the node with the fewest active connections. • Pros: Can more evenly distribute load in real-time, ideal for situations where sessions vary significantly in length. • Cons: More complex to manage; performance improvements depend heavily on an accurate understanding of session load. • Supported by: HAProxy.
22. Load Balancing Tools Overview - ProxySQL Source Hashing • Description: Uses a hash of some part of the request (e.g., the source IP) to determine which node will handle the request, ensuring a client consistently reaches the same node. • Pros: Useful for session persistence in applications where this is crucial. • Cons: Can lead to uneven load distribution if not enough unique hash keys are present. • Supported by: HAProxy.
23. Load Balancing Tools Overview - ProxySQL Health-Aware Routing • Description: Beyond load considerations, this algorithm also accounts for the health of nodes, avoiding nodes that are down or performing poorly. • Pros: Enhances fault tolerance by dynamically adapting to node status. • Cons: Requires more sophisticated monitoring and integration to update health statuses in real-time. • Supported by: Both HAProxy and ProxySQL (via its comprehensive health check mechanisms).
24. Real-World Scenarios
25. Scenario: Without Load Balancing What Could Go Wrong? Overload and Performance Degradation • Description: Without load balancing, one of your Galera nodes (say galera1) might receive a disproportionate amount of traffic, leading to slower query response times and potentially overloading the node. • Impact: This overload can result in increased latency, decreased throughput, and a higher likelihood of node failure under load, impacting overall system performance and reliability.
26. Scenario: Without Load Balancing What Could Go Wrong? Node Failure and Cascading Effects • Example: If galera1, handling most of the write operations, goes down, the remaining nodes (galera2 and galera3) might not be able to handle the sudden increase in load. • Consequence: This can lead to a cascading failure where the additional nodes also become overloaded and potentially fail, resulting in significant downtime.
27. Demonstrating a Deadlock • Live Simulation
28. Demonstrating a Deadlock • Live Simulation: Transaction #1 SHOW ENGINE INNODB STATUS\G
29. Demonstrating a Deadlock • Live Simulation: Transaction #2 SHOW ENGINE INNODB STATUS\G
30. Demonstrating a Deadlock Key Observations Deadlocks • Galera CASE 1: 121 deadlocks • HAProxy CASE 1: 97 deadlocks • Galera CASE 2: 42 deadlocks • HAProxy CASE 2: 29 deadlocks • HAProxy significantly reduces deadlocks across both cases. • Galera CASE 1 suffered from deadlock hotspotting, likely due to transactions being routed to a single node. • HAProxy distributes load better, minimizing write conflicts.
31. Demonstrating a Deadlock Key Observations Queries Per Second (QPS) • Galera CASE 1: 824.50 QPS • HAProxy CASE 1: 1224.64 QPS • Galera CASE 2: 24.48 QPS • HAProxy CASE 2: 64.00 QPS • HAProxy handles more queries in both cases, benefiting from better load distribution. • In CASE 2, Galera’s standalone performance collapses, while HAProxy keeps some efficiency.
32. Demonstrating a Deadlock Key Observations 95th Percentile Latency (ms) • Galera CASE 1: 1803.47 ms • HAProxy CASE 1: 3095.38 ms • Galera CASE 2: 88157.45 ms • HAProxy CASE 2: 28867.59 ms • HAProxy introduced slightly higher latency in CASE 1, likely due to additional routing overhead. • Galera CASE 2 became unusable, with 95th percentile latencies exceeding 88 seconds! • HAProxy kept response times lower, proving its efficiency under load.
33. Does a Load Balancer Eliminate Deadlocks? No, load balancers don’t eliminate deadlocks completely. But they reduce their frequency by: • Lowering contention per node • Spreading transactions intelligently • Preventing overload on a single writer A load balancer doesn’t fix bad transaction design but ensures that queries don’t all fight over the same locks at the same time. For best results, use proper indexing, short transactions, and deadlock handling logic along with a well-configured load balancer.
34. Live Demo
35. Q&A
36. Wrap-Up and Closing Remarks
37. Upcoming Webinars: MariaDB Data Protection: Backup Strategies for the Real World Federico Razzoli March 12, 2025 https://meeting.zoho.eu/meeting/register?sessionId=1235532372
38. Thank you! Mike Rykmas Consultant, Vettabase Ltd. mykhaylo.rykmas@vettabase.com