Page 11 - MySQL

A database snapshot taken while writes are actively in flight can be internally inconsistent, even when the backup completes without any error message. Only during the actual restore, often months later, does it become clear that tables and indexes no longer match. This article shows how MariaDB, MySQL, and PostgreSQL on Linux can produce truly consistent snapshots, using locking, Mariabackup, LVM, and WAL archiving.

mysqldump and XtraBackup solve the same task in fundamentally different ways: one exports data as SQL text, the other copies the data files directly. Which backup strategy fits your database size, allowed downtime and required restore speed decides between hours and minutes of downtime when it matters.

The Percona Toolkit is a collection of command-line tools that fills exactly the gaps MySQL leaves open: schema changes without locks, systematic slow query analysis, and reliable replication consistency. Teams that use these tools deliberately against a Magento database gain control over areas that would otherwise only be manageable through risky manual intervention.

Huge pages reduce the number of page table entries the CPU has to manage for a large InnoDB buffer pool, lowering the rate of expensive translation lookaside buffer misses. Anyone who confuses Transparent Huge Pages with classic huge pages risks exactly the latency spikes they were trying to avoid.

JSON_TABLE is one of the lesser known but genuinely useful functions in MySQL 8: it turns a JSON document, whether stored as a column value or written as a literal directly in the query, into a regular result set that can be joined with other tables. Instead of awkwardly pulling JSON values out through several JSON_EXTRACT calls, JSON_TABLE directly produces rows and columns that behave like any other table. This article covers the basic mechanics, concrete use cases around Magento attribute data, and where the performance limits sit compared to a genuinely normalized table.

The MySQL query cache promised faster reads by simply reusing results, but it foundered on a single global lock that stalled the whole instance on every write. This article explains the architecture, the concrete bottlenecks, the removal in MySQL 8.0, and how Redis and ORM based caching replace the query cache more cleanly today.

Gap locks are among the least understood mechanisms of InnoDB and regularly trigger surprising lock waits in practice, without any specific row seeming to be affected. This article explains what gap locks actually are, why InnoDB needs them under REPEATABLE READ to prevent phantom reads, and how next-key locks work as a combination of record lock and gap lock.

Semi-synchronous replication guarantees that a replica has received a transaction before the source acknowledges the commit. What it explicitly does not guarantee is that this transaction has already been applied on the replica. Anyone who conflates these two figures in monitoring ends up trusting a replica that is actually falling noticeably behind.

N+1 queries almost always emerge unnoticed from ORM generated code and add up under load to hundreds of extra database round trips per page view. This methodology shows how to systematically spot N+1 queries in the query log, fix them with eager loading and batch loading, and measure the actual performance gain with EXPLAIN ANALYZE.

Every plugin increases memory usage, extends startup time, and can slow the IDE down. The question is not which plugins exist, but which ones genuinely add value to your own workflow, and which ones can be disabled without any loss.