MySQL 로드 밸런서 살펴보기

8 개월 ago fermat39 0

[원문 : https://dzone.com/articles/a-look-at-mysql-load-balancers]

이 글의 소유권은 원저자에게 있습니다. 잘못된 번역이나 오역들은 모두 번역자의 능력문제입니다. ㅠㅠ오역 및 추가사항에 대해서 댓글 달아주시면 반영하겠습니다.

 

Percona recently interviewed Krzysztof Książek, senior support engineer at Severalnines AB. See what Książek has to say about which MySQL load balancers work best where.페르코나는 최근에 Krzysztof Książek와 인터뷰하였습니다. 그는 Severalnines AB의 수석 서포트 엔지니어입니다. MySQL 로드 밸런서가 잘 운영되는 곳에 대해 Książek가 이야기하는 것을 보기 바랍니다.

 

Welcome to the first Percona Live Europe featured talk with Percona Live Europe 2016: Amsterdam speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference. We’ll also discuss the technologies and outlooks of the speakers themselves.

In this Percona Live Europe featured talk, we’ll meet Krzysztof Książek, senior support engineer at Severalnines AB. His talk will be on MySQL Load Balancers – MaxScale, ProxySQL, HAProxy, MySQL Router, and Nginx:  A close up look. Load balancing MySQL connections and queries using HAProxy has been popular in the past years. However, the recent arrival of MaxScale, MySQL Router, ProxySQL, and now also Nginx as a reverse proxy have changed the game. Which use cases are best for which solution, and how well do they integrate into your environment?

 

저는 Krzysztof와 이야기할 기회가 있었고, 앞선 질문들에 대해서 배우게 되었습니다:

페르코나: 간단한 자기 소개 부탁드립니다: 어떻게 데이터베이스 개발을 시작하게 되었는지, 어디에서  일하고, 사랑하는 일과 관련된 것에 대해서요.

Krzysztof: I was working as a system administrator in a hosting company in Poland. They had a need for a dedicated MySQL DBA. So I volunteered for the job. Later, I decided it was time to move on and joined Laine Campbell’s PalominoDB. I had a great time there, working with large MySQL deployments. At the beginning of 2015, I joined Severalnines as Senior Support Engineer. It was a no-brainer for me as I was always interested in building and managing scalable clusters based on MySQL — this is exactly what Severalnines helps its customers with.저는 폴란드에서의 호스팅 업체에서 시스템 관린자로 일하고 있었습니다. 회사에서 MySQL DBA를 전담으로 할 필요가 있었습니다. 저는 그 일을 지원하였고,

Percona: Your talk is called “MySQL Load Balancers: MaxScale, ProxySQL, HAProxy, MySQL Router, and Nginx: A close up look.” Why are more load balancing solutions becoming available? What problems does load balancing solve for database environments?

Krzysztof: Load balancers are a must in highly scalable environments that are usually distributed across multiple servers or data centers. Large MySQL setups can quickly become very complex — many clusters, each containing numerous nodes and using different and interconnected technologies: MySQL replication, Galera Cluster. Load balancers not only help to maintain availability of the database tier by routing traffic to available nodes, but they also hide the complexity of the database tier from the application.

Percona: You call out three general groups of load balancers: application connectors, TCP reverse proxies, and SQL-aware load balancers. What workloads do these three groups generally address best?

Krzysztof: I wouldn’t say “workloads” — I’d say more like “use cases.” Each of those groups will handle all types of workloads but they do it differently. TCP reverse proxies like HAProxy or Nginx will just route packets: fast and robust. They won’t understand the state of MySQL backends, though. For that, you need to use external scripts like Percona’s clustercheck or Severalnines’ clustercheck-iptables.

On the other hand, should you want to build your application to be more database-aware, you can use mysqlnd and manage complex HA topologies from your application. Finally, SQL-aware load balancers like ProxySQL or MaxScale can be used to move complexity away from the application and, for example, perform read-write split in the proxy layer. They detect the MySQL state and can make necessary changes in routing — such as moving writes to a newly promoted master. They can also empower the DBA by allowing him to (for example) rewrite queries as they pass the proxy.

Percona: Where do you see load balancing technologies heading in order to deal with some of the database trends that keep you awake at night?

Krzysztof: Personally, I love to see the “empowerment” of DBAs. For example, ProxySQL not only routes packets and helps to maintain high availability (although this is still the main role of a proxy), it is also a flexible tool that can help a DBA tackle many day-to-day problems. An offending query? You can cache it in the proxy or you can rewrite it on the fly. Do you need to test your system before an upgrade, using real-world queries? You can configure ProxySQL to mirror the production traffic on a test system. You can use it to build a sharded environment. These things, in the past, typically weren’t possible for a DBA to do — the application had to be modified and new code had to be deployed. Activities like those take time, time that is very precious when the ops staff is dealing with databases on fire from a high load. Now I can do all that just through reconfiguring a proxy. Isn’t it great?

Percona: What are looking forward to the most at Percona Live Europe this year?

Krzysztof: The Percona Live Europe agenda looks great and, as always, it’s a hard choice to decide which talks to attend. I’d love to learn more about the upcoming MySQL 8.0: there are quite a few talks covering both performance improvements and different features of 8.0. There’s also a new Galera version in the works with great features like non-blocking DDL’s, so it would be great to see what’s happening there. We’re also excited to run the “Become a MySQL DBA” tutorial again (our blog series on the same topic has been very popular).

Additionally, I’ve been working within the MySQL community for a while and I have many friends who, unfortunately, I don’t see very often. Percona Live Europe is an event that brings us together and where we can catch up. I’m definitely looking forward to this.