I’m a big fan of MySQL, but this is something you can do with ProxySQL [0] already. While connection pooling isn’t quite as drastic of a need as with Postgres, the other benefits ProxySQL gives you (aforementioned R/W split, dynamic query rewrites and blocking, query cache, HA failover and LB…) makes it a no-brainer.
Yeah, in the last 2 releases of PostgreSQL there is not even a single feature that was useful for me, so I'm kinda disappointed. and there are so many missing features in postgresql (stop using threads for connections, automatically precompile all parameterized queries, "real" materialized views, etc...)
ProxySQL is a complex jet engine requiring manual tuning. 99% of MySQL users will be satisfied with the simple configuration and automatic query parsing of MySQL Router.
You’re not wrong. Getting ProxySQL config correct to do exactly what you want and nothing else can be quite the undertaking. Even moreso if you want to have it be HA and capable of live ruleset reloads.
Any scalability simplification features like this are very welcome, and necessary to compete with other databases at this point such as CockroachDB and Vitess
Handling stale data needs to be considered when implementing it.
ORMs like Prisma execute an update query and follow it up with a select. This would result in stale data being returned most of the time. If you use this data expecting your update to be contained in it you are going to have a bad time (statemachines, etc.)
If you need sub-second live data, then yeah, you need to read from the primary. Most applications do not, and 1-2 seconds of potential lag are acceptable.
Not MySQL, but with the MariaDB and MaxScale combo this has been possible for quite some time. The only drawback in my opinion is that you'll need a duplicate set of grants, one for accessing the database for the maxscale process and one for accessing the database as the client user. It is free to use for small clusters (3 node).
I've been a happy MaxScale user for a couple of years now, it is simple to configure and very robust.
15 comments
[ 2.8 ms ] story [ 45.6 ms ] thread[0]: https://github.com/sysown/proxysql
You’re not wrong. Getting ProxySQL config correct to do exactly what you want and nothing else can be quite the undertaking. Even moreso if you want to have it be HA and capable of live ruleset reloads.
[0]: https://github.com/postgresml/pgcat
Any scalability simplification features like this are very welcome, and necessary to compete with other databases at this point such as CockroachDB and Vitess
Isn't Vitess built on top of MySQL?
https://vitess.io/docs/16.0/overview/architecture/
If you need sub-second live data, then yeah, you need to read from the primary. Most applications do not, and 1-2 seconds of potential lag are acceptable.
I've been a happy MaxScale user for a couple of years now, it is simple to configure and very robust.