MySQL proxy cluster - ProxySQL - Enhance Control Panel

MySQL proxy cluster - ProxySQL

AdamM

For admin and clients

Hi.
Everyone knows that databases can be a bottleneck on servers.
What if we thought about Enhance using proxy clusters for databases?
As an additional role for databases? This would increase performance and reduce the load.

I think the best solution would be to ProxySQL - https://proxysql.com/
ProxySQL is an open source high performance, high availability, database protocol aware proxy for MySQL

It is a proven solution by many companies. It exists in a free version and can also use Docker.

@Adam what do you think about it? Does it make sense?
What do other Enhance customers think about it?

Kindly regards

necrom

Complex subject at this point of development IMHO.

Worth noting this is just proxySQL and does no replication / expects traditional replication I think from a glance. I'd lean more towards Galera for replication and clustering if I had this need.

I've generally found I am better having many independent MySQL instances for the small customers (same as we're doing with PHP), when they or we do something silly to break the replication (which doesn't seem to scale well to many small databases using traditional binlogging) it is better to have 1 customer and a few GB to resync, than a few thousand and TB to resync. For the customers really needing distributed SQL, they're probably needing a bespoke solution without control panel for me.

My initial approach would be:

shared type customers = shared db role local to the app role
small/medium type business customers = containered db role local to the app role
heavy and PCI type customers = db role separate to the app role
enterprise customers = standalone galera instance

That is not to say long term I wouldn't like to see medium customers be treated more like shared customers with some sort of CloudLinux dbgovernor type of resource limiter, or a managed galera option that can be so easy to scale it is cheaper to just throw more IOPS at it than try manage customer limits.

ss88us

I'm not sure I understand what ProxySQL is...

What's wrong with master/slave (or read/write replicas) functionality already built into database servers? Why does ProxySQL need to go on-top?

If you need this type of set-up, you probably don't use ECP (sorry, just my two-cents). You'll end up building infrastructure from the ground-up and/or using for example DigitalOcean's Database product which includes scaling, clustering, replication, and automatic failover.

I tend to agree with necrom. There's a reason why GoDaddy has multiple database servers and it's not because of geographical location. If you have 1,000 clients and one database goes down, even with automatic failure, you end up having downtime for 1,000 clients. 10% (100) clients going offline is far better than 100%.

necrom

ss88us What's wrong with master/slave (or read/write replicas) functionality already built into database servers? Why does ProxySQL need to go on-top?

You have one location specified in your code for database, it sends writes to the master and spreads reads across the slaves, so it scales the reads beyond the capability of a single master.

ss88us

necrom Understood. So the example on their website (the diagram) it really needs a ProxySQL cluster to prevent a single point of failure too? So essentially to correctly use ProxySQL you would need a least 2 ProxySQL "servers", one write database, and two read databases? That's assuming ProxySQL cannot 'read' to the write database if the single 'read' database fails.

I'm not sure that made sense!

necrom

ss88us Yeah >=2x ProxySQL or haproxy, your master can be read+write and your slave just read in a traditional sense, or master<>master with auto_increment_offset, or something more complex with galera or otherwise. TBH I see a lot the customers needing such things going to cloud DBaaS as it removes a lot of the costs to scaling and maintaining/retaining talent for it.