Common Challenges

Context

This is why you need to learn about common challenges when using MySQL:

This chapter is a short but important laundry list of common MySQL challenges and how to mitigate them. These challenges don’t fit into other chapters because most are not directly related to performance. But don’t underestimate them: the first two challenges, for example, can ruin a database. More importantly, these challenges are not special cases that only happen when the stars align and The Fates conspire to ruin your day. These are common challenges. Take them seriously, and expect to face them.

Excerpt from Efficient MySQL Performance, Chapter 9
Copyright 2021 Daniel Nichter
No reproduction of this excerpt without permission

Key Points

Split-brain occurs when two or more MySQL instances in the same replication topology are written to
Split-brain is a detriment to data integrity—the data can no longer be trusted
Data drift occurs when a replica becomes out of sync with the source
Data drift is real but the origin of the drift is virtually impossible to pinpoint
Data drift can be detected with pt-table-checksum
ORMs can generate very poor queries and overall performance
Schemas always change, so an online schema change (OSC) tool is must-have
There are three popular OSC tools: pt-online-schema-change, gh-ost, and Spirit
MySQL has non-standard SQL statements, options, and clauses
Applications do not fail gracefully by default; it takes effort to fail gracefully
High performance MySQL is difficult

Pitfalls

Not taking into account the key points above

Hack MySQL Articles

Additional Resources

Resource	Type	About
InnoDB and Online DDL	MySQL manual	Since schema changes are routine for developers, it’s important to understand how MySQL (and InnoDB) handle them because there are many edge cases depending on the specific type of change.
Spirit	Open source tool	The fastest, most advanced online schema change (OSC) change tool available.
gh-ost	Open source tool	The predecessor of Spirit. Both are solid tools, but use Spirit if possible.
pt-table-checksum	Open source tool	Still the only tool that can detection data drift on replicas

pt-online-schema-change
Before gh-ost and Spirit, pt-online-schema-change (pt-osc) was the de facto standard tool. It's still widely used and referenced today, but as the author of pt-osc I strongly advise that you use Spirit instead. The only reason to use pt-osc is for rare (and risky) edge cases that Spirit (and usually gh-ost) do not support for good reason.

Source: https://hackmysql.com/learn/common-challenges/
Copyright 2025 Daniel Nichter