Tag
MySQL
32 articles tagged with "MySQL"
MySQL
Marcelo Altmann
Introducing Automatic Failover and Multi Server Support
Starting in Readyset 1.33, you can hand Readyset a list of servers. It fails over automatically, resumes CDC on another server from the last GTID with no rows lost or applied twice, and spreads proxied queries across your replicas. MySQL with GTIDs; PostgreSQL covered with limits.
Marcelo Altmann2026-10-296 min read
Tutorials
Vinicius Grippa
Readyset HA with ProxySQL and MySQL
A MySQL source, a replica and a ProxySQL in front of both. Adding a highly available Readyset cache behind one slow report query took two docker run commands and two INSERT statements, with no change to the application, the schema or MySQL. Here is the measurement, the routing, and how to check the cache is really serving.
2026-10-2213 min read
Database CachingVinicius Grippa
Cache Invalidation for SQL Queries: How To Stop Writing It Yourself
Cache invalidation is the hardest part of caching SQL queries. Here's why TTLs and manual keys break, and how incremental view maintenance avoids both.
2026-09-2125 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 11: GTID_TAGGED_LOG_EVENT — Tagged GTIDs and MySQL's New Serialization Framework
In this eleventh and final post of our series, we decode the GTID_TAGGED_LOG_EVENT — the event MySQL 8.4 introduced to carry user-defined tags alongside the classic UUID and GNO, and along the way meet the new mysql::serialization framework that encodes it. Introduction Back in Part 5 we deferred one event: the GTID_TAGGED_LOG_EVENT (event type 42, 0x2a). It was introduced in MySQL 8.4 to support tagged GTIDs, which extend the classic UUID:GNO form with an optional user-defined label: 557789
Marcelo Altmann2026-05-1321 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 10: ROTATE_EVENT — Closing the File and Pointing at the Next One
In this tenth post of our series, we decode the ROTATE_EVENT — the event that closes a binary log file and tells every reader, replica or local, where to look next. Introduction The ROTATE_EVENT (event type 4, 0x04) is the bookmark MySQL leaves at the end of a binary log file. It carries two pieces of information: The name of the binary log file to read next. The starting position within that next file (always 4, the byte right after the 4-byte magic number from Part 2). Together thos
Marcelo Altmann2026-05-0610 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 9: XID_EVENT — Transaction Commit
In this ninth post of our series, we decode the XID_EVENT — the smallest event in the binary log, and the one that marks every transactional commit. Introduction Every DML transaction we've decoded so far ends the same way: with an XID_EVENT (event type 16, 0x10). At only 31 bytes on the wire, it carries a single piece of information — an 8-byte transaction identifier — but it does some of the heaviest lifting in MySQL replication. The XID_EVENT is what allows replication to be crash-safe wit
Marcelo Altmann2026-04-2912 min read
MySQLMarcelo Altmann
Introducing MySQL GTID Support and Zero-Downtime Failover in Readyset
One of the most requested features since Readyset first supported MySQL has been GTID-based replication. With the latest release, Readyset now fully supports MySQL Global Transaction Identifiers (GTIDs), and with it, a capability that changes how you think about cache durability: zero-downtime failover. This post covers what GTID support means for Readyset users, how failover works in practice, and why this matters for anyone running Readyset in production. The Problem with Binlog File and Po
Marcelo Altmann2026-04-166 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log - Part 8: Row Events — WRITE_ROWS, UPDATE_ROWS, and DELETE_ROWS
In this eighth post of our series, we decode the three row events — WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT — that carry the actual row data for INSERT, UPDATE, and DELETE operations in row-based replication. Introduction In the previous posts, we decoded the events that set the stage for row-based replication: the GTID_LOG_EVENT identifies the transaction, the QUERY_EVENT opens it with BEGIN, and the TABLE_MAP_EVENT describes the table's schema. Now we finally arrive at th
Marcelo Altmann2026-04-1525 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 7: TABLE_MAP_EVENT – Table Metadata for Row-Based Replication
In this seventh post of our series, we decode the TABLE_MAP_EVENT — the event that maps a numeric table ID to a database/table name and the column layout that the row events immediately following it will reference. Introduction The TABLE_MAP_EVENT (event type 19, 0x13) is essential for row-based replication. It appears before any row event (INSERT, UPDATE, DELETE) and provides: Table identification: Database name, table name, and a numeric table ID Column metadata: Number of columns an
Marcelo Altmann2026-04-0813 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log - Part 6: QUERY_EVENT — DDL Statements and Transaction Boundaries
In this sixth post of our series, we decode the QUERY_EVENT — the workhorse event that records DDL statements and transaction boundaries. Introduction The QUERY_EVENT (event type 2, 0x02) is one of the most versatile events in a binary log. It records: DDL statements: CREATE, ALTER, DROP, TRUNCATE, etc. Transaction boundaries: BEGIN statements that start a transaction * Statement-based DML: In statement-based replication mode, INSERT/UPDATE/DELETE Even in row-based replication (the d
Marcelo Altmann2026-04-0116 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 5: GTID_LOG_EVENT — The Global Transaction Identifier
In this fifth post of our series, we decode the GTID_LOG_EVENT — the event that marks every transaction with a globally unique identifier. Introduction Every transaction in a GTID-enabled MySQL server begins with a GTID_LOG_EVENT (event type 33, 0x21). This event assigns a globally unique identifier to the transaction, consisting of: SID (Source ID): The UUID of the server that originally committed the transaction GNO (Group Number): A monotonically increasing sequence number Together
Marcelo Altmann2026-03-2513 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 4: PREVIOUS_GTIDS_LOG_EVENT — Tracking Transaction History
In this fourth post of our series, we decode the PREVIOUS_GTIDS_LOG_EVENT — the event that tracks which GTIDs were recorded in prior binary log files, enabling replicas to determine their starting point. Introduction When MySQL rotates to a new binary log file, it needs to record which transactions have already been committed in previous files. This is the job of PREVIOUS_GTIDS_LOG_EVENT—it appears near the beginning of every binlog file (right after the FORMAT_DESCRIPTION_EVENT) and contains
Marcelo Altmann2026-03-199 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 3: FORMAT_DESCRIPTION_EVENT — The Self-Describing Event
In this third post of our series, we decode the FORMAT_DESCRIPTION_EVENT — the critical first event in every binary log that tells us how to interpret everything that follows. Introduction The FORMAT_DESCRIPTION_EVENT (FDE) is arguably the most important event in a binary log. It's always the first event after the magic number, and it serves as a self-describing header for the entire file. Without it, we wouldn't know: Which version of the binary log format we're dealing with What MySQ
Marcelo Altmann2026-03-118 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log Part 2: File Header and Common Event Header
In this second post of our series, we examine the binary log file header and the 19-byte common event header that every event shares. The Binary Log File Header (Magic Number) Every MySQL binary log file begins with a 4-byte magic number that identifies it as a binary log: Bytes Hex ASCII Meaning 4 FE 62 69 6E .bin Unencrypted binary log 4 FD 62 69 6E .bin Encrypted binary log The first byte distinguishes encrypted (0xFD) from unencrypted (0xFE) logs. For encrypted logs, an
Marcelo Altmann2026-03-045 min read
MySQLMarcelo Altmann
Replication Internals: Decoding the MySQL Binary Log - Part 1: Introduction and Data Types
This is the first post in a series where we dive deep into the MySQL binary log format. We'll manually read binary log files byte by byte to understand exactly what goes under the hood of MySQL replication. Introduction Have you ever wondered what's actually inside a MySQL binary log? Sure, you can use mysqlbinlog to decode events, but what if you wanted to parse them yourself? Whether you're building a Change Data Capture (CDC) system, debugging replication issues, or just curious about MySQ
Marcelo Altmann2026-02-184 min read
MySQLMarcelo Altmann
MySQL 9.6: Foreign Key Cascade Operations Finally Hit the Binary Log
For years, MySQL users working with Change Data Capture (CDC), and replication environments have dealt with a fundamental architectural limitation: foreign keys were handled by the Storage Engine (InnoDB) and cascading operations were invisible to the binary log. MySQL 9.6, released January 20, 2026, addresses this long-standing issue by moving foreign key enforcement from the InnoDB storage engine to the SQL layer. This post examines the technical details of this change, its implications for r
Marcelo Altmann2026-02-095 min read
Database Caching
Gautam Gopinadhan
Readyset QueryPilot Automates Query Caching for MySQL
QueryPilot is Readyset’s automatic query caching engine for MySQL. It automatically identifies and caches your most expensive queries at the SQL layer, with no code changes or manual tuning. Today, we're announcing the Public Beta of Readyset QueryPilot, a significant step toward making OLTP workloads, especially AI-generated applications, scale seamlessly. Think of this as the “Look Ma, no hands” moment for database performance. You route traffic once, QueryPilot takes the handlebars, caches s
Gautam Gopinadhan2025-07-314 min read
Database CachingVinicius Grippa
When Query Optimization Isn’t Enough: Solving MySQL Overload with Caching
Consider the perfect scenario: the database is running on reliable hardware, MySQL has been finely tuned, and queries have been reviewed and optimized. Despite this, sustained business growth inevitably leads to an increase in workload over time. When this happens, even optimized queries can begin to overwhelm the system, not because they are slow, but because they run frequently and consume cumulative resources over time. At this stage, optimizing performance goes beyond analyzing execution pl
2025-07-0711 min read
Query OptimizationVinicius Grippa
Identifying Cacheable Queries: Using tools like pt-query-digest or the MySQL sys schema to pinpoint queries that would benefit from caching
Introduction It’s common for DBAs to optimize queries to address bottlenecks and reduce resource contention. However, in production workloads, the most resource-intensive queries aren’t always the longest-running ones. High-frequency queries that execute quickly but are called repeatedly can collectively consume significant resources, increasing overall latency and system load. Identifying these queries requires a more comprehensive analysis than simply inspecting the slow query log or process
2025-05-2910 min read
Tutorials
Michael Virga
Readyset Private on Azure VM: A Hands-On Guide to Caching Performance (MySQL Edition)
"What if scaling reads didn’t require replicas, rewrites, or added infrastructure complexity? This post walks you through running Readyset—a caching layer purpose-built for real-time SQL acceleration—on Azure VM with MySQL. In just a few commands, you'll launch a MySQL database, deploy Readyset, and witness query speeds drop from milliseconds to microseconds." Prerequisites Before we dive in, ensure you're authenticated with Azure and have a role that includes permission to: * Create Azure
Michael Virga2025-04-286 min read
Database ScalingVinicius Grippa
Vertical Scaling of MySQL vs. Horizontal Scaling with Readyset: A Cost and Performance Analysis in the Cloud
Scaling relational databases in the cloud often creates a tension between cost and performance. As workloads grow—especially read-heavy ones powering analytics, dashboards, or reports—latency increases and infrastructure bills spike. The default response is usually vertical scaling: increase instance size, add more CPU and memory. While effective up to a point, this approach quickly hits diminishing returns and inflates costs. An alternative is horizontal scaling, traditionally achieved through
2025-04-148 min read
Query OptimizationVinicius Grippa
Optimizing SQL Pagination in MySQL
In our blog post on Optimizing SQL Pagination in Postgres, we explained how to optimize SQL pagination for Postgres. As a counterpart, we’ve decided to write one for MySQL pagination. Like PostgreSQL, MySQL offers several ways to handle pagination, but some are far more efficient than others. Let's break down the SQL Pagination options, talk about why LIMIT and OFFSET can be a problem, and look at smarter ways to paginate large datasets efficiently. The Classic SQL Pagination Approach: LIMIT
2025-03-176 min read
Tutorials
Wagner Bianchi
Getting Started with Readyset on MySQL
Optimizing MySQL and PostgreSQL performance is crucial for modern applications, especially those with read-heavy workloads. Readyset is a drop-in SQL caching engine that sits between your application and the database. It automatically caches query results to improve response times without requiring application changes. In this post, we'll cover: Installing Readyset on Ubuntu 24.04 using a .deb package Configuring Readyset to connect to an upstream MySQL database Running queries and caching r
Wagner Bianchi2025-03-054 min read
MySQLMarcelo Altmann
Migrating From RDS MySQL 5.7 to RDS MySQL 8.0 Using Blue/Green Deployment and Readyset
Overview MySQL 5.7 has reached EOL in October 2023 forcing users to migrate to MySQL 8.0. Migrating from MySQL 5.7 to MySQL 8.0 offers significant performance improvements and new features, but the removal of the Query Cache in MySQL 8.0 can pose challenges and performance hits. Readyset provides a solution to maintain and improve query performance by acting as an external query cache with automatic cache updates. This white paper outlines a migration strategy that integrates Readyset into the
Marcelo Altmann2024-10-316 min read
MySQL
Tanmay Sinha
Readyset Cloud Adds Support for MySQL - Unleash Unmatched Database Performance
Readyset is thrilled to announce the availability of MySQL database support in Readyset Cloud! This marks a significant milestone for MySQL users who have long sought an efficient way to speed up their database queries while reducing operational costs. Boost MySQL Performance with Readyset Cloud Readyset Cloud provides a future-proof solution for MySQL users looking to boost performance without being constrained by the lack of native caching and expensive alternatives. By offloading read quer
Tanmay Sinha2024-09-233 min read
MySQLMarcelo Altmann
Introducing Hash Join Algorithm
Joins are a concept in relational databases, allowing the combination of data from two or more tables. Joins establish a relationship between two tables using a common key, which is used to lookup data from one table to another. There are different types of joins and different algorithms that can be used to retrieve the data. In this article we will be focusing on discussing the differences between each algorithm. Nested Loop Joins A Nested Loop Join algorithm is a straightforward method for
Marcelo Altmann2024-08-217 min read
MySQLMarcelo Altmann
MySQL: Binary Log Transaction Compression
Introduction MySQL 8.0.20 introduced a binary log transaction compression feature designed to save storage space and reduce network bandwidth usage by applying compression to each transaction. This feature can be enabled dynamically without restarting the server by setting the binlog_transaction_compression system variable to ON, as it is OFF by default. Additionally, the compression level of the zstd algorithm can be adjusted using the binlog_transaction_compression_level_zstd variable. By def
Marcelo Altmann2024-06-034 min read
MySQLMarcelo Altmann
MySQL 8.4 - What is new?
MySQL 8.0 has been known for its innovative pace, introducing new breaking changes in minor releases within the 8.0 series, which has been a great debate among the MySQL community and a long ask to have a more stable release cadence within the same minor series. Oracle has listened to MySQL users and has announced a new release model, where it introduces the concept of LTS releases and Innovate releases. On the 30th of April, the first Long Term Support release was announced. This release rece
Marcelo Altmann2024-05-092 min read
MySQLMarcelo Altmann
MySQL 5.7 EOL: The End of MySQL Query Cache
Back in October 2023, MySQL 5.7 reached end-of-life. With it went support for some of the earlier functionality of MySQL. No more updates, no more security patches, and, in some cases, no more customer support (or, rather, extended support continues, but at a premium). One of the features on the chopping block was MySQL Query Cache, which has now been completely removed in the current stable major release, MySQL 8.0. Many engineers and DBAs are happy to see the back Query Cache. But not everyo
Marcelo Altmann2024-05-078 min read
MySQLMarcelo Altmann
Automatic MySQL Query Cache with Readyset and ProxySQL
In our previous blog post we announced Readyset version stable-240328 comes with the ability to integrate with ProxySQL. This version inspired the creation of a dedicated scheduler for integrating Readyset with ProxySQL, bringing the ability to automatic inspect your workload and cache problematic queries: 0:00 /3:32 1× Use Scheduler to Integrate Readyset and ProxySQL To easily integrate Readyset and ProxySQL, we have created a scheduler to automatic
Marcelo Altmann2024-04-044 min read
MySQLMarcelo Altmann
Integrating Readyset with ProxySQL for MySQL workloads
ProxySQL is a high-performance, high-availability proxy for MySQL, serving as an intermediary between MySQL clients and servers to optimize and manage database traffic. It provides advanced query routing, directing queries to the most appropriate database server based on predefined rules, such as the query type or server load, thus enhancing performance and spreading the load evenly across servers. Open and closing connections in MySQL means creating and deleting new OS threads, which under the
Marcelo Altmann2024-04-024 min read
Engineering
Readyset
How are MySQL and Postgres ENUMs different?
The Hidden Complexities of ENUM Data Types Halloween’s approach means it's the spooky month, and there’s nothing more spooky than the horrors lurking behind everyday database features. The humble enum may seem friendly and innocent enough, but let’s peel back the wallpaper and take a look at what’s hiding underneath... The concept of an enum is simple enough: database tables frequently contain string columns with a limited number of valid values, so storing that column as one of the standard t
2022-10-137 min read