Dev PHP Mới Thường Bỏ Qua Tầm Quan Trọng Của Chỉ Mục Trong MySQL

Truy vấn chạy chậm dần khi dữ liệu lớn lên là tình huống rất dễ gặp với dev mới. Trong giai đoạn phát triển, dữ liệu thử nghiệm thường chỉ có vài chục tới vài trăm bản ghi. Số lượng này quá ít để bộc lộ vấn đề hiệu năng. Một câu truy vấn không có chỉ mục vẫn chạy nhanh bình thường khi bảng chỉ có vài trăm dòng. Nhưng khi bảng phình lên tới hàng chục nghìn hay hàng trăm nghìn dòng dữ liệu thật, tốc độ truy vấn có thể chậm đi rất nhiều lần. Đây đúng là trường hợp bạn dev chúng tôi kể ở đầu bài đã gặp phải ngay khi ứng dụng lên môi trường thực tế.

Thiếu chỉ mục hợp lý khiến hiệu năng ứng dụng giảm sút dần theo thời gian. Đây là hệ quả âm thầm mà nhiều dev mới không nhận ra ngay từ đầu, vì vấn đề tích lũy dần chứ không bộc phát ngay lập tức. Ứng dụng vẫn chạy được, chỉ chậm hơn một chút mỗi tuần khi dữ liệu tăng thêm. Tới một ngưỡng nào đó, người dùng mới thực sự cảm nhận rõ độ trễ và bắt đầu phàn nàn. Lúc đó, việc tìm nguyên nhân gốc và sửa lại thường tốn công sức hơn nhiều so với việc thiết kế đúng chỉ mục ngay từ khi viết truy vấn lần đầu.

Chỉ Mục Trong MySQL Hoạt Động Như Thế Nào

Bản chất cốt lõi của chỉ mục là giúp cơ sở dữ liệu tìm kiếm nhanh hơn, thay vì quét toàn bộ bảng. Nói dễ hiểu, chỉ mục hoạt động giống như mục lục ở đầu một cuốn sách dày. Không có mục lục, muốn tìm một chương cụ thể bạn phải lật qua từng trang. Có mục lục, bạn tra ngay số trang cần tìm rồi mở thẳng tới đó. Trong MySQL, khi một cột được đánh chỉ mục, cơ sở dữ liệu sẽ tạo ra một cấu trúc dữ liệu riêng, thường gọi là cấu trúc B-Tree. Cấu trúc này giúp tìm kiếm giá trị trong cột đó nhanh hơn rất nhiều so với quét lần lượt từng dòng trong bảng. Thao tác quét toàn bộ bảng như vậy gọi là full table scan, chính là nguyên nhân khiến truy vấn của bạn dev chúng tôi kể ở đầu bài chạy chậm khi dữ liệu tăng lên.

Nhiều dev mới chưa để ý rằng cần cân nhắc giữa tốc độ truy vấn và chi phí khi ghi dữ liệu mới. Chỉ mục không phải “càng nhiều càng tốt”. Mỗi khi thêm mới, cập nhật hoặc xóa một dòng dữ liệu, MySQL không chỉ ghi vào bảng chính mà còn phải cập nhật lại toàn bộ các chỉ mục liên quan. Nghĩa là bảng có càng nhiều chỉ mục, thao tác ghi dữ liệu sẽ càng chậm đi một chút. Với những bảng có tần suất ghi rất cao như bảng log truy cập, việc thêm quá nhiều chỉ mục không cần thiết có thể khiến hiệu năng ghi bị ảnh hưởng đáng kể, dù tốc độ đọc có nhanh hơn.

Cách Áp Dụng Chỉ Mục Hiệu Quả Cho Dev Mới

Xác Định Đúng Cột Cần Đánh Chỉ Mục

Xác định đúng cột thường xuyên xuất hiện trong điều kiện truy vấn là bước đầu tiên và quan trọng nhất. Nguyên tắc cơ bản chúng tôi luôn khuyên dev mới áp dụng là ưu tiên đánh chỉ mục cho các nhóm cột sau:

• Cột thường xuất hiện sau mệnh đề WHERE để lọc dữ liệu.
• Cột dùng để sắp xếp kết quả bằng ORDER BY.
• Cột dùng để nối bảng bằng JOIN, ví dụ cột mã khách hàng trong bảng đơn hàng của trường hợp bạn dev chúng tôi kể ở đầu bài.

Một công cụ hữu ích để kiểm tra truy vấn có đang dùng chỉ mục hay không chính là lệnh EXPLAIN đặt trước câu truy vấn. Kết quả trả về sẽ cho biết MySQL đang quét toàn bộ bảng hay đang dùng đúng chỉ mục để tìm kiếm. Nhờ vậy, dev có thể tự kiểm tra và tối ưu ngay trong quá trình phát triển, thay vì đợi tới khi có sự cố thực tế.

Tránh Tạo Quá Nhiều Chỉ Mục Không Cần Thiết

Tránh tạo quá nhiều chỉ mục không cần thiết là lưu ý thứ hai không kém phần quan trọng. Một sai lầm khá phổ biến chúng tôi từng gặp ở dev mới, sau khi hiểu lợi ích của chỉ mục, là đi ngược thái cực. Họ đánh chỉ mục cho hầu hết mọi cột trong bảng vì nghĩ “có chỉ mục là nhanh hơn”. Trong khi đó, nhiều cột gần như không bao giờ dùng để tìm kiếm hay lọc dữ liệu. Việc đánh chỉ mục cho chúng chỉ tốn thêm dung lượng lưu trữ và làm chậm thao tác ghi, mà không mang lại lợi ích tương xứng.

Mẹo thực tế chúng tôi thường áp dụng là chỉ đánh chỉ mục cho những cột có tính phân biệt cao, tức là giá trị trong cột đó khá đa dạng giữa các dòng, ví dụ cột mã đơn hàng hay email khách hàng. Ngược lại, cột chỉ có vài giá trị lặp lại như cột trạng thái đơn hàng (thường chỉ ba bốn giá trị cố định) không nên ưu tiên đánh chỉ mục, vì hiệu quả tăng tốc mang lại không đáng kể.

Với những dự án website thương mại điện tử có lượng dữ liệu đơn hàng và sản phẩm lớn, tối ưu tốc độ truy vấn cần được tính ngay từ đầu. Việc chọn đúng đơn vị phát triển am hiểu cả thiết kế lẫn tối ưu cơ sở dữ liệu là điều quan trọng. Bạn có thể tham khảo thêm dịch vụ làm website ecommerce để có giải pháp phù hợp cho việc xây dựng nền tảng bán hàng vận hành ổn định.

Tài Liệu Tham Khảo Thêm Cho Dev Mới

Nếu bạn là dev mới còn đang làm quen với công cụ trực quan để quản lý MySQL, có thể tham khảo thêm MySQL Workbench là gì và tại sao nên cài đặt công cụ này. Công cụ này giúp bạn thao tác với chỉ mục và bảng dữ liệu dễ dàng hơn, thay vì chỉ gõ lệnh dòng lệnh.

Nếu muốn nắm chắc kiến thức nền tảng trước khi đi sâu vào tối ưu chỉ mục, bạn có thể xem thêm tổng quan về hệ quản trị cơ sở dữ liệu MySQL để hiểu rõ cách MySQL tổ chức và lưu trữ dữ liệu.

Nếu công việc của bạn có liên quan tới cả SQL Server bên cạnh MySQL, hãy tham khảo thêm schema là gì và vai trò của schema trong SQL Server để có thêm góc nhìn so sánh giữa hai hệ quản trị cơ sở dữ liệu phổ biến này.

Kết Luận

Nắm vững kiến thức cơ bản về chỉ mục giúp dev PHP mới tối ưu hiệu năng ứng dụng ngay từ giai đoạn phát triển đầu tiên. Điều này giúp bạn tránh phải xử lý gấp gáp khi ứng dụng đã lên môi trường thật, như trường hợp bạn dev chúng tôi kể ở đầu bài. Nếu bạn đang viết một tính năng có truy vấn tìm kiếm hoặc lọc dữ liệu, hãy dành vài phút chạy thử lệnh EXPLAIN để kiểm tra xem truy vấn đó có đang dùng chỉ mục hay không. Đây là thói quen nhỏ nhưng có thể giúp bạn tránh được không ít sự cố hiệu năng đáng tiếc về sau.

The post Tối Ưu Chỉ Mục (Index) Trong MySQL Cho Dev PHP Mới appeared first on DBAhire.

The post 13 best practices for database development in Visual Studio 2026  appeared first on Devart Blog.

Similarly, while dealing with various incidents occurring in Percona XtraDB Cluster or MariaDB Galera Cluster environments, how many times did you stumble upon the fact that the GCache file (galera.cache) is a black box and you can’t inspect it in a meaningful way? 

In some scenarios, having the opportunity to see what exactly ended up in the cache file(s) could help us understand the write workload impact or what happened with transactions.

Why would one need to dig into galera.cache files, though? Let’s think about possible scenarios:

• Debugging replication issues or conflicts (BF aborts, etc).
• Understanding recent workload patterns per table (especially when binary log is not enabled or lost).
• Understanding the IST capacity and why node joining falls back to SST.
• Forensic analysis after incidents.
• Why on-demand gcache.page.X files are created and what transactions are inside.
• What committed writesets are still in “assigned / live” vs “released / reclaimable” state.
• Observe / confirm the impact of binlog_row_image setting on the writesets size.

To address those, I decided to experiment with a tool that would decode the Galera cache files. As a result of these experiments, I recently published gcache-inspector – an open source project available on GitHub. 

Before I introduce how the tool works, let’s quickly review the write set caching process.

What is Galera Cache? 

In short, it is a RingBuffer file storing Write-set Cache, which is also memory-mapped. Every replicated transaction is appended to it. Due to the fixed size, the oldest entries are overwritten to allow new writes. In special circumstances when the cache file is too small to fit a big transaction or old entries are not ready to be removed, additional on-demand cache files are created.

From the operational perspective, the most important role of the Galera cache is to provide quick incremental synchronization (IST) of (re-)joining cluster nodes. Having the cache of enough size, so that it can store enough time’s worth of writes, determines the joining process – whether a restarted node will be able to join quickly via IST or whether it will have to pull a full backup (SST) from the donor.

The diagram below shows the typical transaction lifecycle, role, and structure of the Galera cache.

The IST determination is a bit more complex than you’d expect. The joiner estimates the donor’s capabilities with some safety margin.

It is possible to verify the current potential donor Galera cache coverage from its wsrep_local_cached_downto status variable. Moreover, the cache rotation can be put on hold to extend the donor’s time window coverage via the gcache.freeze_purge_at_seqno provider option.

If the above diagram is difficult to digest, the following blog post should shed light on the process: https://www.percona.com/blog/understanding-ist-donor-selected/

Given all this complexity, you may sometimes just want to check and verify for yourself what on earth is in the Galera cache files, instead of guessing. 

And historically, the cache files were just a mystery – no tools available to actually properly inspect them. This is why I decided to experiment with a utility that would fill that gap.

The gcache-inspector

The tool I ended up with can fully decode the Galera cache files. It makes quick general write patterns statistics, write set nature information, and can decode the actual Row-based events (binary log style).

Gcache-inspector works offline (the examined PXC node can be running or not). You may point it to a galera.cache or gcache.page.X file. Below is an example of the default report without additional options used.

$ gcache-inspector --file node2/data/galera.cache 
=== gcache-inspector 0.2.5 — GCache Summary ===
File:    /data/sandboxes/pxc_msb_pxc8_4_10/node2/data/galera.cache
Size:    128.00 MB
Version: 2   UUID: 62f2ad43-8de5-11f1-9fb8-8bae2a687fc9
Seqno (retained):  2 – 4533  (4532 in cache)
Synced:  yes   Offset: 1704
Encrypted: no
Flavor:  PXC / MySQL 8.x

Write-sets found:  4498  4498 retained, 0 older/overwritten
Decodable seqnos:  3 – 4533  (4498 write-sets; pick one with --seqno)
Time range:        2026-08-01 22:12:58 – 2026-08-05 23:04:02 CEST  (span 96h51m4s, newest 12d ago)
DDL statements:    216
GTID events seen:  0
Rows changed:      513758  (95.20 MB)  [all write-sets]

Top 10 tables by row activity:
  table                                      insert   update   delete    ddl       size
  ───────────────────────────────────────────────────────────────────
  sbtest.sbtest1                               5039     5574       15      2        2.9M
  sbtest.sbtest32                              5053       37       18      2        0.9M
  sbtest.sbtest8                               5050       30       23      2        0.9M
  sbtest.sbtest60                              5049       31       21      2        0.9M
  sbtest.sbtest100                             5045       36       18      2        0.9M
  sbtest.sbtest10                              5054       26       19      2        0.9M
  sbtest.sbtest85                              5055       25       17      2        0.9M
  sbtest.sbtest35                              5042       40       14      2        0.9M
  sbtest.sbtest87                              5045       37       14      2        0.9M
  sbtest.sbtest33                              5048       30       18      2        0.9M

By using the --detail parameter, the tool will show per-individual sequence number details, i.e.:

$ gcache-inspector --file node2/data/galera.cache --detail --no-summary --seqno 100-105

=== Write-sets ===
  seqno 100           453632 B 2026-08-01 22:14:39  RELEASED  sbtest.sbtest16[i:2281 u:0 d:0]
  seqno 101              256 B 2026-08-01 22:14:39  RELEASED  1 DDL: CREATE INDEX k_16 ON sbtest16(k); sbtest.sbtest16[i:0 u:0 d:0]
  seqno 102              432 B 2026-08-01 22:14:39  RELEASED  1 DDL: CREATE TABLE sbtest17(; sbtest.sbtest17[i:0 u:0 d:0]
  seqno 103           540664 B 2026-08-01 22:14:39  RELEASED  sbtest.sbtest17[i:2719 u:0 d:0]
  seqno 104           453632 B 2026-08-01 22:14:39  RELEASED  sbtest.sbtest17[i:2281 u:0 d:0]
  seqno 105              256 B 2026-08-01 22:14:39  RELEASED  1 DDL: CREATE INDEX k_17 ON sbtest17(k); sbtest.sbtest17[i:0 u:0 d:0]

The above example shows that a transaction committed with the sequence number 100 has inserted 2281 rows into the table sbtest16 and did not update or delete any rows.

To see exactly what a given transaction was about, the --decode-rows option prints the whole event details. For example, it’s possible to see what rows were changed under seqno 4252:

$ gcache-inspector --file node2/data/galera.cache --decode-rows --no-summary --seqno 4252

-- seqno 4252 at 2026-08-01 22:15:32 (816 bytes) RELEASED
### DELETE FROM `sbtest`.`sbtest41`
### WHERE
###   @1= 1843
###   @2= 3562
###   @3= '55824051154-00248428540-43829027453-18090470997-77687189613-13487855838-34568671126-01577127301-81564593132-49010886470'
###   @4= '09475435259-72703365718-14065084029-80972334150-38881617733'
### INSERT INTO `sbtest`.`sbtest41`
### SET
###   @1= 1843
###   @2= 4879
###   @3= '37041074202-54426174421-76052854404-43175485519-62755971707-75981734496-81616509419-51624022546-52075561216-00090498892'
###   @4= '46023326729-33104312594-23620888475-28615232417-62781559343'

A DDL investigation example

Handling DDLs in Galera replication may be quite confusing. Even if, for instance, an ALTER query fails on the writer, it still gets replicated, causing surprising errors on the peer members, similar to this:

2026-08-05T21:04:02.137267Z 11 [ERROR] [MY-010584] [Repl] Replica SQL: Error 'Table 'sbtest.foo' doesn't exist' on query. Default database: 'sbtest'. Query: 'alter table foo engine=innodb', Error_code: MY-001146
2026-08-05T21:04:02.137334Z 11 [Warning] [MY-000000] [WSREP] Event 1 Query apply failed: 1, seqno 4533
2026-08-05T21:04:02.138503Z 0 [Note] [MY-000000] [Galera] Member 0(przemek-d1) initiates vote on 62f2ad43-8de5-11f1-9fb8-8bae2a687fc9:4533,aebcd4f61a8a51aa:  Table 'sbtest.foo' doesn't exist, Error_code: 1146;

Although such an event normally produces a GRA file to let us investigate, like in this case: GRA_11_4533_v2.log, now we can also look into the cache file for the same (here the SKIPPED flag confirms it was not applied):

$ gcache-inspector --file node2/data/galera.cache --detail --no-summary --seqno 4533

=== Write-sets ===
  seqno 4533             256 B 2026-08-05 23:04:02  RELEASED|SKIPPED  1 DDL: alter table foo engine=innodb; sbtest.foo[i:0 u:0 d:0]

Encrypted Galera Cache

For strict security compliance cases, Percona XtraDB Cluster allows encrypting the Gcache files. The tool allows inspection of encrypted files as well, if the encryption key or vault credentials are provided. But there is one caveat here. A regular, non-encrypted cache file will contain all replicated transactions immediately. Whilst the encrypted one will not show anything new until the encryption in-memory cache is filled or synced during shutdown. Therefore, new transactions are expected to appear in the encrypted cache file with a delay.

Note: the tool does not support encryption available in MariaDB Galera Cluster Enterprise Edition (no source code access).

An example output against an encrypted file:

$ gcache-inspector --file node1/data/galera.cache --keyring-file /opt/mysql/pxc8.4.10/keyring/component_keyring_file
=== gcache-inspector 0.2.5 — GCache Summary ===
File:   node1/data/galera.cache
Size:    128.00 MB
Version: 2   UUID: 62f2ad43-8de5-11f1-9fb8-8bae2a687fc9
Seqno (retained):  4395 – 4533  (139 in cache)
Synced:  yes   Offset: 1776
Encrypted: yes — decrypted   (enc version 1)
Master key: GaleraKey-d6945297-8f7a-11f1-9533-7a5bf82f508c@62eff734-8de5-11f1-b956-7f3a785ad5e2-1
Key source:/opt/mysql/pxc8.4.10/keyring/component_keyring_file (GaleraKey-d6945297-8f7a-11f1-9533-7a5bf82f508c@62eff734-8de5-11f1-b956-7f3a785ad5e2-1)
Cipher:    AES-256-ctr-file, clear below 0x400, counter from 0x0 [CTR unwrap (zero IV), keyring bytes]
Freshness: on a live node the encrypted file lags the in-memory cache (write-back page cache; flushed on eviction/shutdown)
Flavor:  PXC / MySQL 8.x

Write-sets found:  127  127 retained, 0 older/overwritten
Decodable seqnos:  4395 – 4533  (127 write-sets; pick one with --seqno)
Time range:        2026-08-01 22:15:32 – 2026-08-05 23:04:02 CEST  (span 96h48m30s, newest 12d ago)
DDL statements:    1
GTID events seen:  0
Rows changed:      5782  (2.08 MB)  [all write-sets]

Top 10 tables by row activity:
  table                                      insert   update   delete    ddl       size
  ───────────────────────────────────────────────────────────────────
  sbtest.sbtest1                                  2     5545        1      0        2.0M
  sbtest.sbtest58                                 5        1        2      0        0.0M
  sbtest.sbtest64                                 3        2        2      0        0.0M
  sbtest.sbtest52                                 4        1        2      0        0.0M
  sbtest.sbtest14                                 2        3        1      0        0.0M
  sbtest.sbtest26                                 2        2        2      0        0.0M
  sbtest.sbtest6                                  2        2        2      0        0.0M
  sbtest.sbtest98                                 3        1        2      0        0.0M
  sbtest.sbtest97                                 4        0        2      0        0.0M
  sbtest.sbtest68                                 1        4        0      0        0.0M

Summary

Although in most cases, problems with PXC/Galera replication can be successfully investigated based on error logs, binary logs, and GRA files, there may be more complex cases where you may want to look inside the Galera cache files. Or simply for experimenting or to allow better understanding of how it works. I hope gcache-inspector will help you do this. The tool is available as GPLv3, with Go source code and binary packages ready to play with on GitHub: https://github.com/PrzemekMalkowski/gcache-inspector. Demo recording: https://asciinema.org/a/1263342

If, despite acquiring details, you face undersized gcache or other reasons causing nodes to keep falling back to SST, Percona’s engineers can help you tackle those problems. Talk to us about a cluster health review https://www.percona.com/contact-us/

Additional references about Galera Cache can be found in the following blog posts by other Percona engineers:
https://www.percona.com/blog/all-you-need-to-know-about-gcache-galera-cache/
https://www.percona.com/blog/no-sst-node-rejoins/
https://www.percona.com/blog/understanding-ist-donor-selected/
https://www.percona.com/blog/gcache-and-record-set-cache-encryption-in-percona-xtradb-cluster-part-one/

The article was written by a human

The post Stop guessing at gcache: inspect Galera/PXC write sets with gcache-inspector appeared first on Percona.

So we tried it. The first run failed, and it failed in a way that is easy to miss: the replica took every transaction, reported success, and stored nothing. We tracked down why, fixed it, and the whole test suite passes now. This post is what we tested, how we checked it, the bug we found, and where it stands.

It’s still an experiment, not production software. The code and the test harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

Why replicate into DuckDB

A DuckDB table on one server is already useful. The analytical queries get fast and the application does not change. But almost nobody runs their reports on the primary – they run them on a replica, so the big scans stay out of the way of the OLTP traffic.

So the shape of it is simple. The primary stays InnoDB and takes the writes. The replica has the same tables, only marked ENGINE=DuckDB. Row-based replication ships the changes across, the replica writes them into the column store, and the reports run there. You get an analytics replica out of the replication you already run.

Row events are engine-agnostic on purpose. The primary logs the row changes, not the SQL, and the replica applies them through the storage-engine API. On paper, then, the replica should not care that one side is InnoDB and the other DuckDB. We wanted to see the paper version hold up on a running server.

The setup

Two containers from the same image, one primary and one replica. It’s all in Docker, so it repeats cleanly.

• Primary: InnoDB, binlog_format=ROW, GTID on.
• Replica: same server, GTID on, tables made with ENGINE=DuckDB.
• Replication uses SOURCE_AUTO_POSITION=1.

One thing you have to get right before any data moves. Create the replica tables as ENGINE=DuckDB yourself. A CREATE TABLE … ENGINE=InnoDB on the primary goes into the binlog with the ENGINE word still in it, and the replica runs it exactly as written, so you would end up with an InnoDB table there, not a DuckDB one. There is no automatic mapping. Pre-create the DuckDB tables on the replica, and let the row changes flow into them.

-- primary (InnoDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=InnoDB;

-- replica (same columns, DuckDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=DuckDB;

The other rule is a primary key on the replica table. UPDATE and DELETE row events find the row by its old image, and the engine needs the key for that. INSERT works without one, but put a key on it anyway.

One script drives all of this: bench/tb/07-replication-spike.sh. It starts both containers, wires up replication, runs every scenario below, and prints PASS or FAIL for each.

What we tested, and how

The part that matters is the checking. Row counts are not enough – the replica can hold the right number of rows and still have the wrong data in them. So after each step the script dumps the whole table on both sides, ordered by primary key, and compares an md5 of the two dumps. One byte off is a FAIL. And rather than sleep between steps, it waits on WAIT_FOR_EXECUTED_GTID_SET(), so the checks do not race the replica.

Here is what went through it.

Basic DML. Insert, update a row, delete a row, compared after each one.

All the column types, in a single wide table: signed and unsigned integers, DECIMAL, DOUBLE, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TEXT, BLOB, a few NULLs, and a unicode string. Insert it, update it, compare byte for byte. Blobs get their own note below.

DDL. ALTER TABLE ADD COLUMN, ALTER TABLE ADD INDEX, and DROP TABLE against a DuckDB replica table. These arrive as statements. We check that the column shows up, the index shows up, the old rows survive, and the drop removes the table.

Transactions. A transaction with two inserts and an update has to land on the replica as one unit. A transaction the primary rolls back has to leave nothing behind. We also open a transaction straight on the replica and both roll it back and commit it, to check the engine’s own commit and rollback.

Bulk load. 5000 rows through LOAD DATA on the primary, has to arrive and match.

Durability. Two cases, and the second is the hard one.

• Clean restart. Stop the replica properly, write on the primary while it is down, start it again, and see it pick up from its GTID position.

• Crash. Apply some rows, then SIGKILL the replica. No clean shutdown, no checkpoint. Bring it back, write more on the primary, and check one exact thing: every row present once. Nothing lost – DuckDB has to replay its write-ahead log when it opens the file – and nothing applied twice, which means the saved position has to line up with the data that actually reached disk.

The bug: multi-engine transactions lost data

The first full run fell down on the wide-table test. Zero rows on the replica, and then everything after it failed too. The applier had stopped with HA_ERR_KEY_NOT_FOUND. It went to UPDATE a row that was not there, because the INSERT before it had returned success and written nothing.

When a scenario fails, the harness saves the applier error, both server logs, and both schemas. The replica log had the line that mattered:

[Warning] Combining the storage engines InnoDB and DuckDB is deprecated, but the
statement or transaction updates both the InnoDB table mysql.slave_worker_info and the
DuckDB table rpl.wide.

That line is the whole thing. A replica does not only write your data. In the same transaction it also writes its own position into InnoDB system tables – mysql.slave_worker_info, the relay-log info, gtid_executed. So every applied transaction touches two engines at once: InnoDB for the position, DuckDB for the data. Two engines means MySQL runs a real two-phase commit: prepare, then commit.

Our prepare was wrong. It took the open DuckDB transaction, moved it into a registry meant for external XA COMMIT, and cleared the per-connection state. Then commit looked at that state, found it empty, and committed nothing. The position went into InnoDB, the GTID advanced, the binlog moved on, and the DuckDB rows were thrown away. No error anywhere. The replica looked healthy while it dropped every write.

We cut it down to the smallest case, with no replication at all. One server, one transaction into a DuckDB table and an InnoDB table:

BEGIN;
INSERT INTO duck VALUES (1,10),(2,20),(3,30);   -- DuckDB
INSERT INTO inno VALUES (1,10),(2,20),(3,30);   -- InnoDB
COMMIT;
-- duck: 0 rows   inno: 3 rows

InnoDB kept its three rows, DuckDB kept none, and COMMIT said it was fine. A DuckDB-only transaction was fine as well, because with one engine MySQL skips the prepare step. It only broke with a second engine in the transaction. And on a replica, that is every transaction.

The fix

Small change, in the engine’s transaction code. prepare now remembers which prepared transaction belongs to the connection, and commit finishes that one instead of an empty state. External XA is untouched. It went out as v0.2.3.

With that in place the reproducer keeps three rows in both tables, and the full run comes back clean, crash test included:

[8]  data integrity: all column types, NULL / unicode / negatives ....... PASS
[9]  DDL replication (ALTER ADD COLUMN / ADD INDEX / DROP) .............. PASS
[10] transactions (atomic commit, rollback, engine commit/rollback) ..... PASS
[11] bulk LOAD DATA on master -> replica ................................ PASS
[12] durability: graceful restart, then SIGKILL crash recovery .......... PASS

VERDICT: PASS=24  FAIL=0

The crash case is the important one. After a SIGKILL in the middle of applying, the replica came back with every committed row exactly once, matching the primary. Committed transactions survive the kill, and the position stays in step with them.

We left two tests behind so this cannot slip back in quietly: an MTR test, txn_mixed_engine, that runs a mixed DuckDB+InnoDB transaction on every build, and scripts/repro-2pc-dataloss.sh, which you can point at any published image to check it.

What works, and what doesn’t yet

Where it stands on v0.2.3, for an InnoDB primary feeding a DuckDB replica:

The things to keep in mind:

• Create the replica tables as ENGINE=DuckDB yourself. A replicated CREATE TABLE keeps the primary’s engine, so it will not turn into DuckDB on its own.

• Replica tables need a primary key for UPDATE and DELETE.

• The applier goes row by row. That is fine for a normal OLTP change stream. It is not fine for keeping up with a primary that bulk-loads at full speed – the replica will fall behind.

• Committed transactions are crash-safe, with one small gap. The engine holds a prepared-but-not-committed transaction in memory only, so a crash in the short window between prepare and commit can lose that single transaction. The applier commits right away, so the window is small, but it is not zero.

• Blobs behave differently over replication than through a direct statement. A plain UPDATE of a BLOB or TEXT column has a known limit in the engine and does not apply. Over replication it does apply, because the row event carries a full before-and-after image instead of the shared buffer the direct path uses.

And the obvious one. This is an experiment. It is a functional result from a test harness on small data, not an HA or failover benchmark. We did not test multi-source replication, filters, or a real write rate.

Where it stands

An InnoDB primary feeding a DuckDB replica works on v0.2.3. Inserts, updates, deletes, every common type, schema changes, transactions, bulk load – they all replicate and match, and it comes back clean from both a graceful restart and a hard kill. The one real bug, silent data loss on every replicated transaction, is found, understood, fixed, and covered by tests.

It is not production-ready, and we do not treat it as such. But the idea holds up. Point normal MySQL replication at a DuckDB replica, and you get an analytics copy that keeps itself in sync.

The post Replicating from InnoDB into a DuckDB storage engine appeared first on Percona.

Here is what came out. InnoDB finished 18 of the 22 queries and spent more than 28 hours of query time on them. Four never finished. Our engine ran all 22 in about three minutes. It loaded the data 25 times faster than InnoDB, and it used 5 times less disk. On the queries it stays close to plain DuckDB, and on a few it is ahead.

It’s still an experiment, not production software. Code and the benchmark harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

The machine, and how we ran it

• One server, 80 cores, 187.5 GB RAM.
• SF500: about 500 GB of raw CSV, 3,000,028,242 lineitem rows.
• Three engines, one at a time: InnoDB, our engine, native DuckDB.
• All of it through the harness in the repo (bench/tb), in Docker.

Two details about how we ran it change how the numbers read.

The load streams. We generate a chunk of CSV, load it, delete it, then generate the next one. So the disk never holds more than one 20 GB chunk, which is the only reason 500 GB fits on the box at all.

And “native DuckDB” is not a second copy of the data. It opens the engine’s own DuckDB file read-only and queries that. Same bytes on both sides. That keeps the comparison honest, and it means there is no separate native load time to report.

Loading the data

InnoDB took 25.5 times longer. The engine hands LOAD DATA straight to a DuckDB COPY instead of going row by row through the handler, so the three billion lineitem rows go in in about nineteen minutes, and the whole set in thirty-six. InnoDB inserts row by row and builds the primary key as it goes. That is where the rest of the fifteen hours goes.

Storage on disk

DuckDB stores columns and compresses them, so 500 GB of CSV comes down to 132 GB. InnoDB stores rows and carries the index with them, and it ends up bigger than the CSV it came from: 673 GB, five times the DuckDB file. The InnoDB lineitem.ibd on its own is 446 GB. That is more than three times our entire database.

Storage, lower is better. The DuckDB engine holds all of SF500 in 132 GB.

Query time

All 22 queries. Warm runs, minimum of a few, in seconds. InnoDB had a two-hour cap per query; the ones that hit it are marked DNF.

SF500, all 22 queries, log scale, lower is better. Hatched InnoDB bars did not finish inside the cap.

Two things to take from this.

InnoDB is far behind, which is no surprise. Scanning three billion rows for a wide GROUP BY or a six-way join is the wrong job for a row store. Four queries (Q9, Q13, Q17, Q20) did not finish at all, and the eighteen that did add up to more than 28 hours. This is the exact problem the engine is for. It is not a mark against InnoDB, which is doing the transactional job it was built for.

The comparison worth reading is our engine against plain DuckDB, since both are the same DuckDB reading the same file. Over all 22 they are close: 186 seconds for ours, 153 for native. Query by query it goes both ways. On the selective ones ours is often faster — Q6 (1.3 vs 4.1), Q19 (2.4 vs 8.6), Q17, Q20. On the biggest joins native wins — Q18 (27 vs 12), Q21, Q1. That gap comes from settings, not data: the memory limit, the thread count, and running inside mysqld versus a bare CLI. Either way, both are around a thousand times faster than the row store.

Correctness

We checked the answers, not only the clock. For every query we compared our engine’s output to native DuckDB’s, numbers rounded to four decimals and the order ignored. 21 of 22 matched exactly. None mismatched. One was skipped because a result file came back empty on one side. So the engine gives the same answers as plain DuckDB.

What this means, and where it stops

At 500 GB the small-scale picture holds and gets sharper. Analytical queries that took hours on InnoDB, or never finished, come back in seconds on the DuckDB engine. The load is far quicker, and the footprint is far smaller. All of it inside one MySQL server, with the tables queried the normal way.

The limits are the same as before:

• It is for analytics, not OLTP. Point lookups and single-row work stay on the row path, where an index seek is the right tool.
• DuckDB runs inside mysqld, so a heavy query under a tight memory limit can go over budget. DUCKSDB_MEMORY_LIMIT and DUCKSDB_TEMP_DIR let it spill to disk instead of failing. We set a limit here so the big CTEs spill rather than get OOM-killed.
• Some queries still fall back to normal MySQL and run on the row path.
• It is one workload on one machine. The result is strong, but the engine is still an experiment, not something for production traffic.

Try it

Pull the image and run your own queries:

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secret \
  perconalab/ducksdb-mysql-engine:latest

The engine, the patches, and the harness that produced these numbers are on GitHub: https://github.com/Percona-Lab/ducksdb-mysql-engine. The per-query numbers and the method are in the repo. If it breaks, or your hardware gives different numbers, open an issue.

The post The DuckDB MySQL engine at 500 GB appeared first on Percona.

This article is also available in Chinese: 中文版. Browse all English articles.

Have you ever run into a mysqld process that has been starting for a long time and still won’t come up? When that happens, you can use perf top to check what the MySQL process is mainly doing. If what you see looks like the figure below — the MySQL main thread (the one starting from mysqld_main) spending the vast majority of its time rolling back transactions — then you are very likely hitting a large-transaction rollback.

The most common way to get here is a large transaction that fills up the disk while writing its binlog, crashing the instance. The largest binlog file I have run into was over 114GB. Since the Binlog Cache’s temporary file is only cleaned up after the binlog is written, that transaction occupied 228GB in total. The MySQL parameter binlog_error_action controls the behavior when writing to the binlog file fails. The default is ABORT_SERVER, which shuts the process down. You can also set it to IGNORE_ERROR, which closes the binlog file on a write failure so that later transactions produce no binlog at all. That obviously leaves the primary and the replica inconsistent, so don’t use it unless you have no other choice.

Root Cause

Why does the main thread have to roll transactions back when the MySQL process starts? It comes from the binlog crash-safe mechanism; here is only a brief overview. DML in a transaction produces binlog events, and when the transaction commits, those events are written to the binlog file and persisted. To keep the data and the binlog consistent after a crash and restart, MySQL designed a crash-safe mechanism that applies two-phase commit (2PC) to ordinary transactions, also known as internal XA.

As the figure shows, under internal XA a transaction commits in three steps:

1. The storage engine prepares the transaction. The transaction state changes from ACTIVE to PREPARED, and both the state and the XID are persisted to the redo log.
2. The transaction produces an Xid_event, which is written to the binlog file together with the DML binlog events and persisted.
3. The transaction commits.

When the server goes down unexpectedly, a transaction may be in one of the following states:

• Active: under two-phase commit, this kind of transaction was never written to the binlog.
• Prepared but not written to the binlog (or only partially written): the transaction is already in the Prepared state, but its XID does not appear in the binlog file.
• Prepared and written to the binlog: the transaction is already in the Prepared state, and its XID appears in the binlog file.
• Committed: the transaction has been written to the binlog and committed.

For a Committed transaction, the design already guarantees that its binlog events made it into the binlog file, so the binlog and the data are consistent and nothing needs to be done at startup. For an Active transaction, the binlog events certainly never reached the binlog file, and InnoDB has a background rollback thread that rolls it back automatically. A Prepared transaction has to be handled according to the XID information in the last binlog file: if its XID appears in the binlog file, the transaction must be committed to keep the binlog and the data consistent; otherwise it must be rolled back.

Handling Prepared transactions is called Binlog Recovery, and it must be completed before MySQL starts serving users. Committing a transaction is usually fast, but rolling one back generally takes about as long as executing it did. If a transaction took an hour to execute, the rollback will very likely take another hour, and MySQL is unavailable throughout.

Why must all these transactions be resolved before the server starts serving? It has to do with how the XID is implemented. An XID is made up of the MySQL prefix plus a query_id, and query_id is a global counter that starts over from 1 after a restart. If the earlier Prepared transactions are neither committed nor rolled back after startup, two Prepared transactions may end up with the same XID, and recovery has no way to tell which one to commit and which one to roll back.

Rolling Back Prepared Transactions Asynchronously

In AliSQL, we designed an asynchronous rollback mechanism to solve this problem.

As the figure shows, this design splits the rollback of a Prepared transaction into two parts:

1. The main thread sets the transaction state to Active and persists that state.
2. InnoDB’s background rollback thread asynchronously rolls back all of the transaction’s changes.

Binlog Recovery can start serving traffic as soon as the first part is done. Since that step executes very quickly, Binlog Recovery finishes in a very short time.

After a crash and restart, Active transactions are rolled back directly by InnoDB’s background thread, without needing the XID to drive the decision. So during recovery, simply changing the state of the transactions to be rolled back from Prepared to Active avoids the problem of two Prepared transactions sharing an XID. The key here is to persist the Active state, so that the transaction is still Active after a crash and restart and InnoDB will roll it back automatically.

Community InnoDB already rolls a Prepared transaction back by first setting it to Active and then undoing it from the undo records. The Active state is written to the redo log; it is simply not persisted at that moment. However, InnoDB persists the redo log once per second by default, so the state gets persisted very soon after the change. This means that when a large-transaction rollback keeps an instance from starting, even on community MySQL, we only need to force a restart of the mysqld process and the large transaction turns into a background rollback that no longer blocks startup.

The source code for this feature was contributed to MariaDB and has been merged into MariaDB 11.7; see MDEV-33853 for details.

Conclusion

With the asynchronous rollback design, the Binlog Recovery phase only has to set Prepared transactions to Active, while the genuinely time-consuming rollback is carried out asynchronously by InnoDB’s background rollback thread. This optimization shortens a startup that used to take tens of minutes, or even hours, to one that completes in seconds.

This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful.

Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, the CPU architecture, the number of cores, the amount of RAM, the storage capacity and speed. On paper the hardware looks like it can handle the workload. But in reality, things rarely go exactly as planned. So, conducting a thorough stress test is the next thing to do.

2. Realities of stress testing

You configure your MySQL server setting the innodb_buffer_pool_size to 70-80% of your available RAM. This creates a large fast buffer for your data and indexes, reducing the need for slower disk input/output.

After a warmup period and a few hours of testing, everything looks great. The server is working at a steady pace, performance is stable. You tick the box – the server has passed the basic stress test. Thinking everything is fine, you consider leaving the test running over the weekend, expecting only minor fluctuations in performance.

However, when you check the status the next morning, you find that the CPU is idle and the mysqld process has vanished. Did it crash? You check the server error logs, but there is no record of a crash or a shutdown—not even a core dump. Then, you look at the system logs and find something unexpected:

journalctl -k -g mysqld

Jun 09 07:29:14 beast-node7.tp.int.percona.com kernel: Out of memory:
Killed process 3936620 (mysqld) total-vm:194627592kB, anon-rss:183047480kB, file-rss:640kB, shmem-rss:0kB,
UID:955676158 pgtables:355860kB oom_score_adj:0

It appears that mysqld ran out of memory and was terminated by the OOM (Out of Memory) killer after running for about 16 hours.

We will focus on Resident Set Size (RSS), which is the subset of Virtual Memory Size (VSZ). RSS is the most significant part of VSZ and other parts like swap (only 8Gb) do not make notable contributions.

The RSS reached 183GiB, significantly higher than the initial 145GiB (with the innodb_buffer_pool_size set to 135G). The mysqld process had grabbed nearly 40GiB of extra memory, which at first looked like a memory leak. I ran my stress tests on different versions of MySQL and Percona Server and found a recurring pattern: memory usage climbed steadily until the system killed the process.

I won’t dive into the leak diagnosis here, but the result was clear: mysqld wasn’t leaking memory in the traditional sense. However, we still had to explain that 40GiB growth.

3. Configuration and methodology

The configuration was as follows:

The testing was done as follows:

What we wanted to achieve:

• Create conditions when memory allocations and deallocations inside the database server are frequent.
• Utilize as much of physical memory as possible (at least 80%) by giving it to InnoDB Buffer Pool.
• Use all available CPU resources in the most efficient way to prevent threads contesting for execution time (the number of connections should match the number of logical CPU cores).
• Eliminate any layers that add overhead and get in the way of direct measuring of allocators frequency and efficiency. The connections will be established using a socket file.

Servers configuration file:

# Make sure data dir is on NVMe
datadir=/nvme/data

# Thread Pool is enabled only for Percona Server
plugin-load-add=thread_pool.so
thread_pool_size=16
thread_pool_max_threads=5000
thread_pool_stall_limit=500

# Disable binary logging
skip-log-bin

# Connection settings
max_connections = 200

# Logging
log-error = /home/bogdan.degtyariov/servers/data/mysql-error.log
pid-file = /home/bogdan.degtyariov/servers/data/mysql.pid

# Socket
socket = /tmp/mysql-alloc-test.sock

# Disable SSL requirement
require_secure_transport = OFF

# Other settings
sql_mode = ""
wait_timeout = 288000        # 80 hours
interactive_timeout = 288000 # 80 hours

# Table settings
default-storage-engine = InnoDB

# InnoDB redo log configuration
innodb_redo_log_capacity = 32G

# Minimize flush overhead (not crash-safe, but optimal for testing)
innodb_flush_log_at_trx_commit = 0

# Memory configuration
innodb_buffer_pool_size = 150G # Configurable down to 80G
innodb_buffer_pool_instances = 16
innodb_io_capacity = 20000

# Performance optimizations
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M
innodb_doublewrite = OFF

# Transparent Huge Pages can be turned ON or OFF for the testing
large-pages = ON

4. Where did the memory go?

Memory management is complex, so let’s simplify. Applications rarely talk directly to the Linux kernel because the kernel typically works in 4KB pages, which is inefficient for developers. Instead, applications use allocators like glibc malloc, jemalloc, or tcmalloc. These tools handle memory operations by minimizing overhead, managing bookkeeping, and preventing fragmentation. Most importantly, they use caching.

When a program frees memory, the allocator rarely returns it to the OS immediately. Instead, it moves that memory into an internal “free-list” cache. Reusing memory from this cache is much faster than requesting new memory from the kernel.

Also, the Percona Server for MySQL and upstream MySQL Server use their own implementation of the memory arena allocator called MEM_ROOT. Historically MEM_ROT was architected decades ago when the standard Linux implementation of glibc memory allocator was slow and prone to lock contention in multithreaded programs.

Enabling memory profiling revealed that MEM_ROOT allocations for cursor metadata in stored routines was responsible for most of the additional memory acquired by the server process:

sp_head::execute_procedure           (TPC-C stored procedure)
   └─ sp_instr_copen::execute        (OPEN <cursor> statement)
       └─ sp_cursor::open
           └─ mysql_open_cursor
               ├─ Materialized_cursor::send_result_set_metadata  87831 MB (94.3%)
               └─ Query_result_materialize::start_execution      5311 MB  (5.7%)
                   └─ MEM_ROOT::Alloc / AllocBlock / ForceNewBlock

NOTE: 87G is a significant growth of memory allocation considering that in that run the server initially allocated ~85G with Innodb_buffer_pool_size=80G.

The problem happens regardless of the data size because the actual issue is in stored routines cursor metadata. When the stored procedure is called the memory allocated for cursor metadata is not freed. Over the course of many repeated calls to the same stored procedure the cumulative amount of memory for the cursor can reach any value.

The following graph demonstrates the memory growth in Percona Server 8.4.8-8 from ~80G to over ~180G in RSS and over 200G VSZ over the period of 24 hours.

Thus, a bug was reported for Percona Server: https://perconadev.atlassian.net/browse/PS-11472

With Percona Server for MySQL 9.7.0-1 the RSS/VSZ growth was at a slower rate, but still noticeable and it was not flattening towards a stable horizontal line (the server was configured with a small amount of memory for innodb_buffer_pool_size=4G and run for 5 hours instead of 20).

Memory profiling showed the new allocations in version 9.7.0-1 were in the same place where cursor metadata is handled:

sp_head::execute_procedure           (TPC-C stored procedure)
   └─ sp_instr_copen::execute        (OPEN <cursor> statement)
       └─ sp_cursor::open
           └─ mysql_open_cursor
               ├─ Materialized_cursor::send_result_set_metadata
               └─ Query_result_materialize::start_execution      
                   └─ MEM_ROOT::Alloc / AllocBlock / ForceNewBlock 4,025.8 MB (99.6%)

5. Possible workarounds

My tests showed that OOM crashes happened consistently under two specific conditions:,

1. Connections are never closed and stay open permanently
2. Connections ran queries at maximum speed without any pauses

Also, when the connection lifetime was limited and users were made to close connection and reconnect after 1M transactions, the memory exhaustion stopped, and memory was freed correctly – all with only a minor impact on performance. To minimize the delays associated with creating a new connection thread on the server I used the connection pool functionality in HammerDB. When the connection lifetime is ended, the actual connection is not closed, but “reset” and reused. This frees the context accumulated during the connection activity and stimulates returning memory to the OS. This connection pool mechanism is more efficient than the open/close cycle for maintaining the connection lifetime. 

I had two runs with reconnecting users: with and without connection pool. The graph demonstrates that using the pool improves the performance in this test.

Also, during another experiment with unlimited connection lifetime, adding a 0.5ms pause after a few transactions prevented the crashes, though performance dropped slightly more.

The memory graphs have consistent periodic oscillations that never reach into the dangerous zone.

6. Summary

To sum it up: the observed MySQL’s memory bloating is caused by a problem in the server cursor implementation not freeing metadata memory. 

Under heavy, constant load, that memory accumulates to the amount which eventually causes an OOM crash. Capping how long connections stay active or adding a short pause between transactions, gives the server time to clean itself up. Normally the client side processing adds such pauses without need to do it on purpose.

Finally, it is important to remember that the best benchmark results do not always guarantee the best real-life performance.

The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.

Over my career as a database reliability engineer, I've come to a conclusion which I don't see repeated often:

All practical implementations have to balance the opposing concerns of correctness vs. performance & availability (this is kind of similar to CAP theorem , but not exactly the same).   Perfect correctness with no data loss across geographic distances would result in a database which is too slow or too costly to be useful for most applications. And these constraints cannot be overcome because it's the physical bounds of reality which imposes these limits.

Why geographic distances? I'll explain this step-by-step below.

Step 1: A Single Isolated Database Instance

You start with installing a copy of PostgreSQL or MySQL on a single instance, or using a managed database product from a cloud provider such as AWS's RDS.  At this scale the database performs everything you need.  Full ACID compliance, fast reads and writes, transactions all work beautifully.  We're done, right?

A happy little database on its own.  You'll never see one this happy again.

Well only if you ignore that hardware and VM instances are not flawless.  Despite the database software being more than adequate, sometimes that underlying hardware or VM infrastructure will fail.  The naive response is to just wait until the original instance can be restored and you continue on your merry way.  However this could take minutes, or hours, or days.  If you have customers paying to use your service, they're not going to be so patient.  So now you need High Availability! 

Step 2: High Availability

High Availability means that you want your system to recover to a usable state as quickly as possible.  How quick? It depends on the specific implementation.  For me, <10 seconds is common.  <1 second is the goal.  For systems like RDS their default gives you ~2 minutes average recovery time, with options that will get that down to ~30 seconds.  Not really HA-enough for my taste, but for most people it's a vast improvement over hours or days of outage!

BUT THERE IS A TRADEOFF -- A COST!

Do you see it yet? It's that improving reliability means having another copy of your database ready to take over when the primary fails. Maintaining that copy takes time. Time is the tradeoff.

Every time you write data to your primary/live instance, that data must be recorded somewhere else where it will be available when another database instance takes over to maintain availability. Well, you could certainly build a system where this isn't true, but imagine your customer's surprise when you flip from one database instance to another and data they thought they had persisted suddenly disappears.  Even worse is the problems you would invite when you flip back to the original copy of the database and that data suddenly appears again.  This is our "correctness" problem.

 The correctness problem is this: when we have to maintain multiple copies of our "source of truth" data, how can we make sure they all stay in-sync?

I have some good news and bad news on that front: the good news is that we absolutely can keep all our copies perfectly in-sync and ensure perfect correctness.  The bad news is our database system will now be so slow it's probably unusable on a practical level.

 It works like this:

 When a request comes to our primary/active database to update/insert/delete data, we can pause the transaction at the time of commit and go transfer that transaction data to our other copies.  Only once we have confirmed the data has been durably persisted to our other copies do we finish the commit and return a success to the original transaction's client.  This adds time to the client's request.  They have to wait for data to be transferred over the network between our databases.  For two database instances in the same datacenter, this could be microseconds -- not terrible, maybe not even noticeable.

 However, that's not the end of the problems we've added.  Now what happens if our backup database fails and can no longer accept updates, even when it's not being actively used? To maintain correctness we would have to stop accepting writes to the primary database as well!  It's the only way to ensure they always remain perfectly in sync is to treat a failure of one node as a whole-system failure.

 Wait, we were supposed to be increasing availability.  Did we just actually decrease it instead? Also when the primary database fails and we flip to the secondary we now no longer have a backup copy and we lose HA properties until the other instance is restored.

 We could just run more backup copies, but now we have more data transfers to keep everything in-sync.  We could just say we only need 2 out of N nodes to be in-sync at all times and mark the others as unusable temporarily until they can re-sync.  Or is that 3 out of N, so that we have a backup-for-the backup.  And our cost to serve a single copy of our data set has gone up to what? 3x? 5x?  It's starting to get more expensive now too.

Now I hope you're starting to see the complexity of the problem here.  We could go into permutations of redundant architectures until the cows come home, but I'll spare you.  Suffice it to say that every single architecture we could examine or invent is going to run into the same fundamental limit -- it takes time to keep copies of our data up-to-date perfectly. And the only way to mitigate the time constraint is to relax the correctness constraint. There is no way around this.

 And we're not even done yet, because our highly available system still only operates in a single physical datacenter. A single disaster which takes out the whole datacenter still means we're hosed.  Many companies just call this good enough and accept the risk (after all us-east-1 never goes down, right?)  But for others, their customers won't be happy with an extended outage even if you can claim it's not your fault.  For true fault-tolerance you need yet another copy of your data in some other physical location, usually far enough so that the same hurricane, or earthquake or power grid outage doesn't affect both locations.  This is how we arrive at geographic distribution.

Step 3: Disaster Recovery & Geographic Databases

 The astute will note that this is just an extension of the same problem we have with transit times in our HA setup, now with larger distances involved.  This should be easy! How much more time could we possibly have to manage?

Let's take New York to Los Angeles as an example.  If you were able to send data at the speed of light, it would take 16 milliseconds! And that's a one-way trip.  To let our primary database receive a confirmation that the data was received we need a minimum of 32 ms.  And keep in mind this is the theoretical maximum the laws of physics allow for a straight-line path.  In practice even if our network was fiber from end-to-end, we still have stops at various routers along the way for processing.  A real network request therefore takes more like 66ms per trip, and a 130ms round-trip time.

I have yet to experience any commercial enterprise willing to accept database write latency of 130ms.  Using cloud services you might end up paying thousands, or even tens of thousands of dollars per year for a system that can process <100 write transactions per second.

Sad databases, so far apart.

 So what do you do?  How do you surmount the laws of physics? You don't. You MUST compromise something.

And that's the entire point of this article -- you cannot escape the fundamental laws of physics.  You only choose what properties are desirable and know that you will be giving up other things.  If you absolutely cannot tolerate data loss, then your system will be incredibly slow (or costly).  If you want great performance and efficiency, there will be ways you can lose data.

 I talk about the laws of physics because someone might see the 130ms round-trip-time and think that we just need to do some fancy computing to optimize that.  Or change how we build networks. But even if we did that we'd get at most ~4x improvement. There is not even a single order of magnitude left between our current performance and the maximum allowed by the laws of physics.  We cannot optimize time much more than we already have!  No matter how advanced technology of the future becomes, this same problem will still exist until the end of the universe. Even at the speed of light, it takes a significant amount of time to move data.

 Most companies use a strategy of creating an HA cluster with strong consistency guarantees within a single datacenter only, and then using an "eventual consistency" approach to shipping data to another geographic location. If the need arises to run their application from a different geographic location, they call it "disaster recovery" and let clients know that recovery could take hours and some data loss is expected. This applies equally if you're using async replication to a warm-standby database or if you're taking backups or snapshots and filling in the gaps between full backups with transaction logs.  If your primary datacenter fails while processing user requests, there will always be some window of time where data written to your database at that location won't make it to your backup. It could be seconds, or minutes. No matter what you cannot guarantee consistency with an async update model.

 This is why databases are hard -- there's never a perfect solution which will work all the time for all use-cases.  And this also ignores the entire other class of database problems which relate to availability which is what happens when someone writes a bad query that DOSes your database.  Just scale up your database, or just partition it, right? But as we see in this article, scaling a database is hard because everything takes time.  Partitioning is hard because you create new consistency problems.  Availability is hard because keeping things in-sync is hard.

A Postscript 

Communicating this is honestly the most challenging part of my job.  Initial development of most software projects starts with the single isolated database either from a cloud provider or just on someone's laptop.  They develop against a non-distributed database system and a tiny database size and everything works! It's blazing fast, it's perfectly consistent. No durability issues, etc.  When engineers take this to production they are soon frustrated by the production database which seems slower and less reliable.  They underestimate that the production database has so much more demands and constraints against it.

Yet I will talk with an engineering team one week that stresses how important consistency guarantees are for them.  Sure, I can do that.  Then the next week I'll talk to another team that demands the fastest performance possible.  Now we have a challenge.  Then after an incident I'll be yelled at by a manager who says we need to make availability our highest priority because our largest customer is threatening to churn.  Then as we approach the end of our fiscal year I'll have other people breathing down my neck saying we need to cut costs. Then this whole cycle repeats.

 You can try to fix this by running multiple database systems.  The slow-yet-scalable system; the ultra-fast-but-lossy system; the perfectly-consistent-but-tiny metadata store.  This is why so many companies run several different types of databases.  Redis for ephemeral data, MySQL/PostgreSQL for transactional guarantees, key-value stores for easy scaling of simple data.  But now the job is to make sure engineers are choosing the right location for their data.  Inevitably not all data will find the right home on the first try and migrating data from one system to another is always a big task which isn't fun.  It all feels a bit like Sisyphus some days, but at least it's job security!

“Through transparent roadmaps, community-driven collaboration, contributor programs, and the MySQL Governance model, we aim to create an environment where innovation can accelerate while preserving the reliability, compatibility, security, and operational excellence that organizations around the world depend on.”

Q1. Oracle has announced a “new era” of MySQL community engagement at MySQL’s 30th anniversary. Can you walk us through what specifically prompted this strategic shift, and what concrete changes can the community expect to see in how Oracle approaches MySQL development and governance?

HVC: Throughout 2025 we celebrated 30 years of MySQL and reflected on the past and present, but more importantly, the future. The MySQL Community team sought feedback from around the globe on how to lead the next generation of MySQL innovation and open source collaboration. We came to Jason in November and shared that feedback and proposed a plan to rebuild community trust. By December we agreed on a plan, calling it a new era of Community Engagement.

We have entered a deeper collaboration with the MySQL Community, focused on faster innovation, greater transparency, deeper community collaboration, and expanding the ecosystem. Starting with the April 2026 release, we’re delivering more features directly into the MySQL Community Edition core while preserving the stability customers rely on.

As part of this effort, we introduced the MySQL Governance model, which provides clear pathways for participation, community leadership, and long-term collaboration with the broader MySQL ecosystem. Together, these initiatives are designed to build deeper trust, accelerate innovation, and grow the MySQL ecosystem.

Q2. One of the most significant announcements is moving previously commercial-only features into the MySQL Community Edition. What drove this decision, and what other enterprise features are you planning to bring to the community edition in the coming months?

JW: Both our Community and our Customers are asking for stability and faster innovation. At the same time, they want more visibility into our roadmap and a stronger voice in shaping it. Driving some previously Enterprise-only features into the Community Edition addresses both needs, while we not only deliver those features, but we also build, prioritize, and deliver new features and innovations into MySQL.

With the GA of MySQL 9.7.0 LTS, MySQL moves from the 9.x innovation series to a new Long-Term Support release line. This begins the 9.7.x LTS series, giving users a stable branch to standardize on while continuing to build on the innovation delivered through the 9.x cycle.

This release matters not only because it establishes the next LTS baseline, but because it reflects a broader direction for MySQL. Over the last several releases, we have talked about giving users earlier visibility into what is coming, broadening access to important capabilities, and working more openly with the MySQL community. With MySQL 9.7.0 LTS, that direction is reflected in the product itself.

Several capabilities previously limited to MySQL Enterprise Edition are now available in MySQL Community Edition, while Dynamic Data Masking is now available in MySQL Enterprise Edition. Together, these changes make MySQL 9.7.0 LTS a meaningful release for DBAs, developers, and operators across both editions.

More capability in MySQL Community Edition

One of the biggest themes in MySQL 9.7.0 LTS is the continued expansion of MySQL Community Edition. Across 4 major technical areas, this release delivers 8 notable new Community Edition capabilities — a substantial broadening of what DBAs and developers can do with Community Edition.

The 4 major areas

• Replication observability and HA behavior
  • Flow-control monitoring
  • Multi-threaded applier extended statistics
  • Automatic Eviction & Rejoin
  • Up-to-date Aware Primary Election
• Telemetry and observability integration
  • Telemetry / OpenTelemetry support
• Modern application development
  • MySQL JSON Duality Views
• Query optimization and performance
  • Hypergraph Optimizer
  • Profile-Guided Optimization (PGO)

Q3. Some community members have expressed concerns about MySQL’s development velocity and commit rates. Jason, as SVP of Data Services, what specific steps are you taking to address these concerns, and how do you plan to balance cloud service development with core MySQL innovation?

JW: Oracle has invested heavily in MySQL since 2010, and we hear feedback from the community. People want to see that investment show up in a more visible way, especially through faster delivery in the open. We’re working on that in a few concrete ways: getting more features into MySQL Community Edition, sharing more of the roadmap and worklogs, using Early Access releases to get feedback earlier, and creating more public forums where contributors can talk directly with the MySQL engineering team.

We’re also putting more structure around how people can participate, through the MySQL Governance model, contributor summits, design discussions, and clearer contribution paths. The goal is straightforward: be more open about where MySQL is going and give the community more practical ways to influence priorities, test features earlier, report issues, and contribute improvements. Cloud and core MySQL are not separate priorities for us — the core database is the foundation for Community, Enterprise, and HeatWave, so continued innovation in MySQL itself remains central to everything we’re doing.

In addition to accelerating innovation, we are creating more opportunities for community participation through public roadmaps, Early Access releases, public discussions, contributor summits, and the MySQL Governance model. Together, these initiatives provide greater transparency into our priorities while creating structured mechanisms for contributors to participate, provide feedback, and help influence the future direction of MySQL.

Q4. Oracle has published the MySQL Community roadmap and promised to facilitate community contributions through worklogs and bug reports. How will this differ from past practices, and what mechanisms are you putting in place to ensure transparent, bidirectional communication between Oracle’s engineering team and external contributors?

HV: With our Community Engagement Plans, MySQL customers and users get the best of both worlds: enterprise-grade stability and faster access to innovation. They’ll also have greater visibility into what’s coming and more opportunities to provide input, which helps them align MySQL with their own technology roadmaps. In addition to publishing select worklogs and CVE information, we have continued Labs for new features and early access releases leading up to the 9.7 launch, which will continue in future releases, with our next Early Access planned for early July. These provide valuable insight and transparency to community members and invaluable feedback to the engineering team. 

We have organized a series of public discussions (four so far), with a fifth planned for July, as well as established a quarterly Contributor Summit and regular design meetings under the MySQL Governance model. The first Contributor Summit took place in May 2026, with a design meeting held the week prior. The next Contributor Summit is scheduled for August 2026 in Broomfield, Colorado.

The governance model provides structured pathways for participation through code contributions, testing, documentation, reviews, technical discussions, and community leadership. It introduces clearly defined roles—including Contributors, Committers, Project Leads, Core Project Leads, a Steering Committee, and a Vulnerability Group—to help ensure transparent collaboration while maintaining MySQL’s standards for quality, stability, compatibility, and security.

In the last quarter, we also published the MySQL Developer Guide, which describes how to effectively contribute and participate in the evolution of MySQL.

To catch up on previous discussions, see highlights from earlier sessions:

• Edition #4 highlights (contributions and feature requests)
• Edition #3 highlights (bugs and contributions)
• Edition #2 highlights (ecosystem and metrics)
• Edition #1 highlights (community roadmap)

Q5. How do you see the MySQL governance structure evolving to give the community a stronger voice while maintaining Oracle’s stewardship?

HV: The MySQL Governance model is a key part of how we are evolving community participation while maintaining Oracle’s long-term stewardship of the project. The model is built on principles of transparent processes, merit-based participation, shared stewardship, and a commitment to quality, stability, compatibility, and security.

Oracle remains the primary steward of MySQL while creating clearer pathways for the community to participate in shaping the project’s future. Community members can contribute through code, testing, documentation, bug reports, design discussions, and reviews. As contributors gain experience and demonstrate sustained engagement, they can take on greater responsibilities through defined governance roles.

The model also introduces a Steering Committee that brings together perspectives from Oracle, users, customers, hyperscalers, and the broader open source ecosystem to help guide long-term priorities, governance evolution, ecosystem growth, and community engagement.

Together with public roadmaps, Early Access releases, GitHub collaboration, contributor summits, and design meetings, the governance model creates a structured framework for community participation while preserving the engineering excellence and operational stability that organizations around the world depend on.

Q6. PostgreSQL has been gaining ground with features like pgvector for AI workloads, while MySQL faced criticism for lack of similar capabilities. How does Oracle plan to ensure MySQL remains competitive not just with PostgreSQL, but also with cloud-native databases and newer entrants in the database market?

JW: MySQL offers a uniquely predictable and stable operational model at global scale, combined with strong performance and ease of use. Backed by Oracle, it delivers enterprise-grade reliability while maintaining the flexibility and innovation of open source. We will continue to collaborate with the community to deliver innovations based on our published roadmap into MySQL Community Edition. 

Q7. The move of MySQL into Oracle’s cloud organization raised concerns about resource allocation. Can you address these concerns and explain how Oracle is ensuring MySQL has the engineering resources it needs to execute on this new community-focused vision?

JW: MySQL’s success has always come from the combination of strong stewardship and a vibrant community. Oracle continues to invest deeply in both. What’s new is increased transparency, stronger engagement with the community, and more structured ways for contributors, partners, customers, and ecosystem participants to help shape MySQL’s future through the MySQL Governance model and related community programs.

These investments complement our continued engineering investment in MySQL Community Edition, MySQL Enterprise Edition, and MySQL HeatWave.

Q8. You’ve mentioned expanding collaboration with Linux distributions, particularly Canonical and Ubuntu, as well as supporting major open source projects like WordPress and Drupal. What does this ecosystem support look like in practice, and how will Oracle work with companies that some might consider competitors in the MySQL space?

HV: We have built relationships and communication between the MySQL Community Team and open source maintainers to ensure the pathways are smooth for projects to build their projects and platforms using MySQL.  We continue to strengthen communications and remove barriers to collaboration. 

That spirit of collaboration is reflected in the MySQL Governance model and community engagement efforts. The recent Contributor Summit brought together Oracle engineers and contributors from organizations including Amazon, Google, Percona, ProxySQL, Readyset, VillageSQL, and participants from across the broader MySQL ecosystem, including MariaDB, to share ideas and help shape the future of MySQL.

We continue to focus on growing and expanding the MySQL ecosystem, referencing the analogy of a rising tide lifting all boats. Growing the community and bringing more collaboration and alignment makes us all stronger together and creates opportunities throughout the ecosystem.

Q9. For organizations currently running MySQL in production, what’s your message about long-term support and the roadmap? With MySQL 8.0 approaching end of life and MySQL 9.7 LTS published in April 2026, how should enterprises plan their migration strategies and what assurances can you provide about stability and backward compatibility?

JW: MySQL offers a uniquely predictable and stable operational model at global scale, combined with strong performance and ease of use. Backed by Oracle, it delivers enterprise-grade reliability while maintaining the flexibility and innovation of open source. 

The release introduces a new long-term support version of MySQL Community Edition, and MySQL Enterprise Edition, along with expanded feature delivery into the core, early access capabilities, and the first phase of our enhanced transparency and community engagement model.

Q10. Looking beyond the immediate announcements, what is Oracle’s five-year vision for MySQL? How do you see MySQL evolving to meet the demands of AI workloads, cloud-native architectures, and modern developer expectations while preserving the simplicity and reliability that made it the world’s most popular open source database?

JW: MySQL powers everything from startups to hyperscale platforms. It’s used by companies like Uber and Booking, and underpins major platforms like WordPress and Ubuntu. That breadth of adoption is a strong validation of its reliability and scalability.

 The vision is simple: build MySQL in the open with the community, accelerate innovation without sacrificing quality or stability, and continue to scale and grow the ecosystem around the world’s most widely used open source database platform.

A key part of that vision is establishing a sustainable governance framework that enables broader participation, develops future community leaders, and creates stronger connections between Oracle, contributors, customers, partners, hyperscalers, and the broader open source ecosystem.

Through transparent roadmaps, community-driven collaboration, contributor programs, and the MySQL Governance model, we aim to create an environment where innovation can accelerate while preserving the reliability, compatibility, security, and operational excellence that organizations around the world depend on.

Jason Wilcox
Senior Vice President, Data and AI Platform, Oracle Cloud Infrastructure (OCI)
Jason Wilcox leads the Data and AI Platform organization at Oracle Cloud Infrastructure (OCI), overseeing the design and development of OCI’s data platforms, AI infrastructure and platform services, and open source technologies. His portfolio spans cloud-scale data services, data processing and integration platforms, operational services for AI workloads, and widely adopted open source technologies that developers and enterprises rely on to build modern applications. These services help customers manage and use data, run AI workloads, and operate secure, reliable, and scalable systems on OCI.

Heather Vancura
Vice President, External Standards & Community Engagement, Oracle Cloud Infrastructure (OCI) Heather VanCura is Vice President of External Standards & Community Engagement at Oracle, where she leads Java Community programs and the MySQL Community Outreach team. With over 20 years of experience at Oracle and Sun Microsystems, she is a central figure in the global ecosystem, focusing on community growth, engagement, and standardization efforts.

………………….

Follow us on X

Follow us on LinkedIn