28
How to scale MySQL horizontally?
(programming.dev)
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 03 Jan 2024
28 points (93.8% liked)
Asklemmy
43853 readers
661 users here now
A loosely moderated place to ask open-ended questions
If your post meets the following criteria, it's welcome here!
- Open-ended question
- Not offensive: at this point, we do not have the bandwidth to moderate overtly political discussions. Assume best intent and be excellent to each other.
- Not regarding using or support for Lemmy: context, see the list of support communities and tools for finding communities below
- Not ad nauseam inducing: please make sure it is a question that would be new to most members
- An actual topic of discussion
Looking for support?
Looking for a community?
- Lemmyverse: community search
- sub.rehab: maps old subreddits to fediverse options, marks official as such
- !lemmy411@lemmy.ca: a community for finding communities
~Icon~ ~by~ ~@Double_A@discuss.tchncs.de~
founded 5 years ago
MODERATORS
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.
Thank you for answering the question like a helpful person instead of just instantly posting “you’re wrong and you should use this instead”
It’s so frustrating when someone doesn’t understand that there are constraints that OP hasn’t included because it’s not relevant to the question.
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:
It's also important to consider that horizontal scaling has limits, when you enable replication it does put additional load on the write instance and a naively configured replication setup will add additional load for every replica.