Asklemmy

44205 readers

1352 users here now

A loosely moderated place to ask open-ended questions

Search asklemmy 🔍

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
[email protected]: a community for finding communities

~Icon~ ~by~ ~@Double_[email protected]~

founded 5 years ago

MODERATORS

[email protected]

How to scale MySQL horizontally? (self.asklemmy)

submitted 1 year ago by hypnotic_nerd to c/[email protected]

12 comments fedilink hide all child comments

What is the industry/production grade solutions or if you have already any experience please share it. Thanks

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 3 points 1 year ago

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.