Tag: MySQL

1. Keeping the cron_schedule Table Clean and Tidy

   May 10, 2026

   cron_schedule logs every scheduled and executed cron job in Magento and can accumulate millions of rows within a few weeks if misconfigured. Once you understand the table's status transitions, set history_cleanup_every correctly, and step in manually when needed, you prevent slow cron runs and locking problems in production.

   Read more

2. Redo Log and Crash Recovery: How InnoDB Prevents Data Loss

   Apr 4, 2026

   When a MySQL server crashes in the middle of a write, the redo log decides whether data is lost or whether the database automatically repairs itself on the next start. Understanding write-ahead logging, checkpoint mechanics and log sizing is therefore not an academic exercise but the foundation for resilient production databases. This article explains the full mechanism with real configuration examples.

   Read more

3. The Doublewrite Buffer: Protection Against Torn Pages Explained

   Mar 14, 2026

   An InnoDB page is larger than the atomic write block of most storage systems, meaning a power failure in the middle of a write can leave a page half old and half new. The doublewrite buffer is the mechanism that reliably prevents exactly this scenario, the torn page. This article explains how it works, what it costs in performance, and when disabling it is actually worthwhile.

   Read more

4. Designing Composite Indexes Correctly: Column Order Matters

   Mar 14, 2026

   A composite index with the right columns in the wrong order is often worthless to the optimizer. The leftmost prefix rule determines which part of a composite index can be used at all, and putting equality columns before range columns squeezes noticeably faster queries out of the same index without any extra storage.

   Read more

5. Avoiding Long-Running Transactions in MySQL

   Feb 9, 2026

   A single transaction left open can make the InnoDB undo log grow without bound, hold locks for minutes, and delay replication, without a single error message appearing anywhere. This article explains how long-running transactions arise in MySQL, how to reliably track them down with information_schema.innodb_trx, and which timeout and batch strategies prevent them in application code from the start.

   Read more

6. Choosing the Right Data Types: Save Storage and Improve Performance

   Jan 26, 2026

   An oversized INT, a VARCHAR field that should really be TEXT, or FLOAT instead of DECIMAL for money add up in large tables to significant, often overlooked storage cost and performance loss. Choosing the right data type is one of the cheapest optimizations in the entire database design. This article explains the most important data type decisions with concrete sizing rules.

   Read more

7. Savepoints for Partial Rollbacks in MySQL

   Jan 25, 2026

   Anyone who rolls back an entire transaction just because a single step inside a larger batch operation failed is throwing away work already done. Savepoints let you return to a marked point inside a transaction with precision, without losing the changes that came before it. This article shows the complete SAVEPOINT syntax in MySQL, explains the behavior inside InnoDB in detail, and provides practical examples for batch imports and a PHP implementation with PDO.

   Read more

8. MySQL Deadlocks: Detecting, Analysing and Avoiding

   Jan 6, 2026

   A deadlock in InnoDB is not a bug, it is an unavoidable consequence of parallel transactions blocking each other. This article shows how to read the deadlock report from SHOW ENGINE INNODB STATUS, which patterns keep recurring in practice, and how retry logic together with consistent lock order systematically reduces deadlocks.

   Read more

9. Understanding and Tuning Magento Indexer Tables at the DB Level

   Jan 1, 2026

   Indexer tables are the backbone of Magento performance on category and search pages because they replace EAV joins with precomputed, flat structures. Once you know the structure of catalog_category_product_index and catalog_product_index_price and how to place the Update on Save and Update on Schedule modes correctly, you can tune reindex runs deliberately instead of treating them as a black box.

   Read more

10. MySQL Monitoring: the Metrics That Actually Matter

    Dec 25, 2025

    CPU usage and free memory alone say almost nothing about the actual health of a MySQL database. Only buffer pool hit ratio, connection saturation, replication lag and lock waits together show whether a system is running stable or is about to hit an incident that classic server monitoring never sees.

    Read more