Observing the Effects of MySQL Group Commit through Experiments
Through a series of experiments using MySQL 8.0, this article demonstrates how group commit reduces I/O operations by consolidating multiple transactions into a single commit group, showing that doubling load increases runtime modestly while transaction count and commit groups rise significantly, highlighting performance benefits.
In the illustration series we previously introduced the concept of group commit (see MySQL group commit article); this time we observe its effect through experiments.
First, we create a database as usual.
We adjust the flush rhythm to make the phenomenon more noticeable; the dual‑write flush configuration is also the recommended setting for production environments to ensure data reliability.
We conduct an initial stress test.
While the stress test runs, we monitor I/O using pt-ioprofile .
We then run a second stress test, this time doubling the concurrency.
During this test we continue to monitor I/O counts with pt-ioprofile .
We observe that when the load is doubled, the total execution time rises from 55 s to 67 s (about a 20 % increase), but the number of I/O operations for each file, exemplified by the binlog flush count, increases by only about 1 %.
To see group commit more directly, we examine the last_committed field in the binlog, which indicates the commit group to which each transaction belongs.
Analyzing the two tests, the number of commit groups increased by 25 % while the total number of transactions doubled, meaning most transactions were merged into commit groups for submission.
This demonstrates the purpose of group commit: multiple transactions are placed into a single I/O operation for submission, thereby saving I/O counts.
Tip: If you are using MySQL 8.0 for the experiment and have binlog_transaction_dependency_tracking set to WRITESET or WRITESET_SESSION , identifying commit groups via the last_commit field may be inaccurate.
Do you have other MySQL technical topics you’d like to learn about? Leave a comment for the editor!
#GroupCommit #Performance
Want to see more technical articles? Click “Read More”!
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.