I will add here my impressions about both services, with pros and cons, so we can take an informed decision. In particular, I would like to know labs group opinion, as it could add maintenance overhead in some cases:
- It is a pure mysql proxy, which means it understands natively the protocol, allowing to have integrated support for things such as lag monitoring, connection failure, master-slave failover, etc. BUT
- We do not need many of those for labs- we need 2 things- if things completely break, move to another server; and handle maintenance windows in the most gracefully way possible
- I thought proxysql would have a way to customize check- however, the models is to create checks and those pool or depool servers by changing the configuration (aside from the basic health checks). BUT
- This is something haproxy can already do, by writing our own http wrapper based on bash scripts that do arbitrary checks
- proxysql has nice features you cannot find on a regular proxy- it has mysql connection pooling/persistent connections, so it can avoid the overhead of creating new connections BUT
- the proxy will live outside of the tools by definition, so there will still be overhead on connecting to the proxy itself, and for labs the connection overhead is not that large, not that an interesting feature
- proxysql allows to have global status per user instead of having to aggregate them. For example, we can limit the number of total connections, instead of doing it per server
- proxysql, at this time, requires authentication to be setup in the proxy itself. According to documentation, it is planned to have different frontend and backend users, which would be great to maintain users in a single place, but as of now, they have to be duplicated- which means double the places where things can fail and maintain.
- proxysql allows query rewriting or rerouting, that is not possible for haproxy. For example, certain users or queries can be sent to a specific set of servers. While in theory we do not need this for now, maybe we could redirect certain database usage or writes to a single master automatically.
- Chase mentioned the possibility of incompatibilties as proxysql reimplements the full protocol rather than being a L4 proxy. This could in theory introduce incompatibilities. I believe those should be minimal, but I cannot discard it 100%.
- Seeing the latest releases, https://github.com/sysown/proxysql/releases proxysql seems to still have some important bugs fixed. Which is good, because it means it is maintained, but I am not sure how well it would fit with the difficult labs environement. L4 routing, by definition, will be easier to handle by simpler pieces of software.
More coming up.