-
The DuckDB MySQL engine at 500 GB
We ran DuckDB MySQL storage engine at scale factor 500. It is around 500 GB of raw TPC-H, three billion lineitem rows on an 80-core server with 187 GB of RAM. Three engines on the same box: InnoDB, our MySQL+DuckDB engine, and plain DuckDB as the reference.
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
Engine
Load time
ENGINE=DuckDB (COPY fast path)
36m 05s
InnoDB (bulk LOAD DATA)
15h 21m
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
Component
Size
vs raw CSV
raw TPC-H CSV
500.0 GB
100%
ENGINE=DuckDB (tpch.duckdb)
132.4 GB
26% (3.78x smaller)
InnoDB (tpch/*.ibd)
673.2 GB
135%
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.
Query
InnoDB
MySQL+DuckDB (ours)
native DuckDB
Q1
11864.5
11.1
5.2
Q6
3539.4
1.3
4.1
Q9
DNF
17.1
18.1
Q13
DNF
17.1
10.4
Q18
3846.1
27.0
11.9
Q19
6672.3
2.4
8.6
Q21
14211.7
26.0
15.1
All 22
18/22 finished, ~28 h
185.6 s
152.7 s
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.
-
MySQL 8.0.17 GTID Crash Safety Improvement
I have known for some times that there is an interesting improvement in MySQL 8.0.17 regarding GTID Crash Safety, but I have not had the time nor the need to look into it before. When writing my last post (Understanding MySQL Replication "fatal error 1236": [...]), I saw something interesting related to this, and it is now time to cover this on my blog. From my point of view, this change is
-
Recovery Optimization for Large MySQL Transactions
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:
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.
The transaction produces an Xid_event, which is written to the binlog file together with the DML binlog events and persisted.
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:
The main thread sets the transaction state to Active and persists that state.
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.
-
Understanding MySQL Replication "fatal error 1236": "Replica has more GTIDs than the source has, using the source's SERVER_UUID"
This MySQL replication error — fatal error 1236 / Replica has more GTIDs than the source has, using the source's SERVER_UUID — shows the importance of thinking before acting. I am glad a non-DBA Colleague asked me about it, because if he had restarted replication, it would have caused a much bigger mess.
Often, we are tempted — or pushed — to just restart things
-
MySQL Development as it Happens – Innovating Together
Henrik IngoMySQL Community Architect In May we hosted the first MySQL Contributor Summit 2026. We arrange such Summits every quarter, and it’s a forum where contributors come together and make proposals, and then discuss them, on what they wish to work on, or see someone else work on, in future MySQL versions. All of the presentations are now […]
|
