After reading https://www.percona.com/blog/percona-kubernetes-operator-for-percona-xtradb-cluster-haproxy-or-proxysql/ , it pushes me towards haproxy because of easy to maintainability and write performance. I have to mention that we cant do write/read seperation on application level so if we go with haproxy we gonna have 1 haproxy instance which gonna handle both reads and writes, so we lose read scalability and we are okay with it. What is your thoughts? do we have any statistics about which preferred more?
Update: can anyone confirm that proxysql configuration and maintainance hard? following guide is not enought to have prod proxysql? Load balancing with ProxySQL - Percona XtraDB Cluster . While checking the docs setup of maintaining of proxysql looks easier.
My personal preference is ProxySQL. In order to do read scaling with HAProxy, you must make changes to your application in such a way that the code opens separate connections for all read queries. These separate connections are typically just a different port on which HAProxy is listening to balance the traffic between all the backend readers.
If you can’t, or don’t want to, change the code, but you still want the read scaling, ProxySQL works best. It requires no changes to application code. Send all queries to ProxySQL and write the regex rules to route SELECT queries.
(I’m going to mark this ‘solved’ but feel free for others to continue commenting their opinions as this question is very opinion based.)
great, would you agree that configuration guide from Load balancing with ProxySQL - Percona XtraDB Cluster is suitable for prod env? I wonder if it will round-robin write requests or stick to one server
You shouldn’t be using PXC 5.7 nor ProxySQL v1. Use PXC 8.0 and ProxySQL v2.
why, we are doing migration and our older cluster is till in 5.7. I think we have to stick to PXC 5.7 and ProxySQL v2. Maybe you mean we have to use Load balancing with ProxySQL - Percona XtraDB Cluster version 8 manual which based on proxysql2.
5.7 dies October 2023. There will be no support for 5.7 after this date. No matter what you decide, yes, use ProxySQL v2
****, I think it would be good idea first upgrade mysql and then setup cluster, do you know any good guide which describes what things can go wrong after upgrading to 8?
Our blog has many posts about upgrading to 8 and some of the “gotchas” to watch out for.