What are your go-to strategies for handling a MySQL or MariaDB database that has become too large or too busy for one server to manage? I'm looking for ideas beyond just scaling up CPU and RAM or using standard replication. Has anyone used ProxySQL to balance traffic across multiple hosts? Also, if you've transitioned to TiDB, what steps did you take to ensure that your app's SQL is compatible with it?
5 Answers
We recently started moving to Planetscale. We set up our application to mirror queries to it while monitoring for errors. Plus, during imports, Planetscale acts as a replica and proxies back to the original source, helping keep data consistent as we make the transition.
A good starting point is to separate your reads and writes. Adding replicas specifically for read operations can help offload some of the traffic from the main host.
There's no one-size-fits-all solution. It really hinges on your data volume and patterns. If you don't share specific issues you're facing, you likely won’t get the most helpful advice.
There are plenty of resources on scaling MariaDB that emphasize understanding your read/write mix. Options like sharding are popular, but remember, softening ACID constraints can lead to issues down the line. One interesting concept I’ve seen is using many temporary SQLite databases instead of one big RDBMS, though I haven’t tried it myself.
I’ve had success with vitess.io for large MySQL setups with huge user bases. It can handle a lot of data effectively and scales pretty well without too much hassle.
Just remember, if your replication isn’t synchronous, like with a Galera cluster, those replicas might serve outdated data, especially in a write-heavy environment.