Vettabase Webinar - Galera Best Practices for MySQL users

As per official website, Galera Cluster for MySQL is a true Multimaster Cluster based on synchronous replication. Galera Cluster is an easy-to-use, high-availability solution, which provides high system uptime, no data loss and scalability for future growth. How can you make the most out of your MySQL + Galera installation leveraging industry best practices? Learn at our webinar!

Transcript

1. Galera Best Practices for MySQL users Mike Rykmas mykhaylo.rykmas@vettabase.com 15 January 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 • Welcome & Introduction • Understanding Galera Cluster • Key Features and Benefits • Load Balancing Strategies • Best Practices for Galera Deployment • Optimizing Performance and Scalability • Interactive Q&A
4. Introduction to Galera Cluster
5. What is Galera Cluster? • A certification-based semi-synchronous replication solution for MySQL and MariaDB. • Built to ensure data consistency using transaction certification. • Provides high availability and scalability for mission-critical applications. • Minimizes the risk of data loss even in failover scenarios.
6. Key Features of Galera
7. Key Features of Galera Cluster • Certification-Based Semi-Synchronous Replication: Transactions are certified before commit to maintain consistency. • Multimaster Architecture: Any node can handle reads and writes. • Automatic Node Provisioning: Simplifies scaling and recovery. • Built-in Conflict Resolution: Conflicting transactions are rejected to prevent inconsistency. • High Availability: Automatic failover ensures minimal downtime.
8. Benefits of Using Galera
9. Why Use Galera Cluster? • Data Integrity: Guaranteed consistent data across all nodes. • Scalability: Add nodes to handle increased read workloads. • High Uptime: Redundant nodes minimize downtime. • Ease of Management: Simplifies maintenance and deployment.
10. What is wsrep?
11. What is wsrep? • Definition: ◦ Write Set Replication (wsrep) is the API and protocol that powers Galera Cluster • Key Features: ◦ Facilitates communication between nodes ◦ Manages transaction write-sets for certification ◦ Ensures global ordering of transactions across the cluster • Common wsrep Variables: ◦ wsrep_cluster_size: Number of nodes in the cluster ◦ wsrep_ready: Indicates if the node is operational ◦ wsrep_local_cert_failures: Tracks transaction certification failures
12. Certification in Galera
13. Certification-Based Semi-Synchronous Replication • What is Certification? ◦ Transactions are replicated to all nodes and validated (certified) before being committed ◦ Prevents conflicts by rejecting transactions that fail certification
14. Certification-Based Semi-Synchronous Replication • How It Works: ◦ A transaction is sent as a write-set to other nodes ◦ Other nodes check for conflicts with local transactions ◦ If no conflicts, the transaction is committed across all nodes • Benefits: ◦ Ensures consistency without full synchronous replication ◦ Reduces network overhead compared to true synchronous systems
15. Load Balancing in Galera
16. Why Load Balancing Matters in Galera • Writing to all nodes at once increases the risk of deadlocks due to concurrent writes and certification conflicts • Proper load balancing ensures: ◦ Even distribution of traffic across nodes ◦ Minimized write conflicts and deadlocks ◦ High availability during node failures
17. Load Balancing Options
18. Load Balancing Solutions for Galera • ProxySQL: ◦ A high-performance SQL-aware proxy designed for complex routing and traffic management in Galera setups ◦ Supports advanced query rules, real-time cluster monitoring, and automatic failovers • HAProxy: ◦ A lightweight and reliable TCP/HTTP load balancer ◦ Simple to configure for distributing traffic across Galera nodes with health checks
19. Load Balancing: ProxySQL • Pros: ◦ Advanced routing logic with query rules ◦ Handles automatic failover when nodes go down ◦ Native support for Galera cluster monitoring • Cons: ◦ Configuration complexity for beginners ◦ Higher resource usage than simpler load balancers • Managing Failovers: ◦ Use LOAD MYSQL SERVERS TO RUNTIME; to reload configurations ◦ Set up query rules to route traffic based on cluster status
20. Load Balancing: HAProxy • Pros: ◦ Lightweight and fast ◦ Well-established and simple to configure • Cons: ◦ Lacks advanced Galera-specific features like ProxySQL ◦ Requires external scripts or tools for failover management • Managing Failovers: ◦ Combine HAProxy with health-check scripts for node monitoring ◦ Use Galera health-check endpoints to dynamically route traffic
21. Cluster Performance Dependencies
22. Cluster Performance and Node Weakness • Performance Dependency: ◦ The cluster’s performance is limited by its weakest node ◦ Slow nodes introduce delays in transaction certification and replication • Best Practices: ◦ Ensure all nodes have similar hardware and network configurations ◦ Monitor node performance regularly using wsrep_flow_control_paused and wsrep_local_send_queue
23. Galera’s Data Center Features
24. Enhancements in New Galera Library • Data Center Awareness: ◦ Enables efficient clustering across geographically distributed data centers ◦ Reduces latency by routing transactions locally where possible • Improved Flow Control: ◦ Optimized mechanisms to prevent bottlenecks during high-traffic periods • Improved Parallel Applying: ◦ Faster transaction applying for workloads with high concurrency
25. Best Practices for Galera Deployment
26. Best Practices for Deployment • Deploy an odd number of nodes for better quorum handling • Place nodes in low-latency networks for optimal performance • Configure gcache.size to handle your workload and recovery needs • Monitor cluster health and performance regularly with tools like ClusterControl. • Use load balancers like ProxySQL for seamless traffic distribution
27. Advanced Best Practices • Regular Backups: ◦ Use Percona XtraBackup to ensure consistent backups without downtime • GCache Management: ◦ Adjust gcache.size to support your workload and avoid desync issues • Monitoring Tools: ◦ Use ClusterControl or Percona Monitoring and Management (PMM) for advanced metrics and alerting • Upgrade Planning: ◦ Always test upgrades in staging environments before applying to production
28. Optimizing Galera Performance
29. Performance Optimization • Transaction Size: Keep transactions small to reduce replication overhead. • Write Workload: Optimize write patterns to avoid conflicts. • Flow Control: Monitor and adjust flow control settings to maintain cluster stability. • Asynchronous Writes: Use asynchronous nodes for non-critical workloads.
30. Common Challenges and Solutions
31. Overcoming Challenges • Challenge: Network latency can slow down synchronous replication. ◦ Solution: Deploy nodes close to each other or in the same data center. • Challenge: Write conflicts in multimaster setups. ◦ Solution: Use application-level sharding or conflict detection. • Challenge: GCache insufficient for large transactions. ◦ Solution: Increase gcache.size or adjust workload.
32. Q&A
33. Upcoming: Mastering MySQL High Availability with Galera Mike Rykmas mykhaylo.rykmas@vettabase.com 19 February 2025, 5:00 CET https://meet.zoho.eu/JRo4gGH2yW
34. Thank you! Mike Rykmas Consultant, Vettabase Ltd. mykhaylo.rykmas@vettabase.com