What is the industry/production grade solutions or if you have already any experience please share it. Thanks
Yeah, Postgres might be better, like the people in the other answers say.
For the people though, maybe OP as well, who haven't got free rein on what DB system they use because they might be doing this for work, or they might already have a lot built on MySQL, or they might be hosting software that only works with MySQL:
https://dev.mysql.com/doc/refman/8.0/en/replication.html
Create read replicas, route read traffic to them. You can scale reads out on MySQL or any other relational DB really by using read replicas, you usually can't scale writes horizontally though. The one thing you should check in addition to your traffic profile vis-à-vis reads vs. writes is whether read replicas still provide consistency, ergo if a write on the master immediately appears on the replicas.
Databases usually have two choices there; they either have replicas lag behind the master, meaning that something you wrote to the master will not always appear immediately on the read replicas, or they lock the whole system up on each write to guarantee you are reading the latest info. I guess MySQL would tend to the saner former option.
If you have any reads that are really important to be consistent with writes done just before them, just route them to the master if you still have capacity there.
To build on this (and I also use Postgres, so I'm assuming MySQL/MariaDB are similar), there is almost certainly a metric emitted by the DBs that can tell you how long that lag is between initial write and replica updates. That would be the thing to monitor to detect the specific problem where replication lag creates application lag.
Also worth mentioning that horizontal scaling can solve some problems, but there are a few major configuration items to check that will improve performance across all the replicas. Off the cuff:
- Properly index your tables
- Build on hardware big enough to keep indexed data in memory
- Don't use the MyISAM engine with MySQL since it has a bunch of performance and locking problems. Upgrade to InnoDB.
- Optimize your queries. Horizontal scaling won't give you much improvement if you're doing full table scans or something like that. ORMs can produce some pretty ugly SQL sometimes. Consider writing your own queries that are better optimized to make use of your indices.
