3

A single redo log file can also be written in a circular fashion.

In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was unnecessarily split into multiple files, logically treated as one big circular file" and "it turned out that a single fixed-size circular log file would perform best in typical scenarios" (See).

So why does MySQL choose to use multiple redo log files? Are there any advantages compared to using a single log file?

2 Answers 2

3

I've had conversations about this with InnoDB developers.

It turns out there was never a good reason for InnoDB to default to two redo log files. The reason was that it mimicked Oracle's default.

Oracle uses its redo log files like MySQL uses both InnoDB log files (for database recovery) and the binary log files (for replication). In MySQL, these are separate features because binary logs are independent of storage engine, and redo logs are used only for InnoDB. But in Oracle, there are no pluggable storage engines, so all the features work together.

In Oracle's implementation, it is worth having multiple redo log files to support log archiving. There needs to be one "active" redo log file while the "inactive" file is being archived (see Managing the Redo Log for details).

InnoDB doesn't do log archiving of redo logs, because the binary log can fulfill that purpose. So there's no distinction in InnoDB redo logs of the active vs. inactive files.

Why then was the default to have two InnoDB redo log files? Only because the founder of InnoDB, Heikki Tuuri, had experience with Oracle and he had a vision of implementing a database engine that did similar things, but with a better implementation. So some options and defaults in InnoDB are based on assumptions that it would work at least a bit similarly to Oracle.

There has in fact never been any advantage to InnoDB using two redo log files. But no significant advantage to changing that default either. Even if MariaDB has decided to change the default, I'm skeptical that it makes enough difference to justify it for the sake of performance.

What I mean is, if the extremely tiny difference of using one redo log file instead of two is an optimization that you feel the need to do, then you should probably think about other more significant improvements, like optimizing your queries or moving to a sharded architecture so you can split your database write traffic over multiple servers.

But it's certainly a reasonable choice to change the default for the sake of simplifying the operation of the software, or clearing up confusion about questions like this one.

1

(I do not have much to back up this Answer. But I know a lot about the history.)

The original implementer of Innobase (Heikki Tuuri in about 2000) decided that ping-ponging between two files was the right way to go, so that is what he implemented. MySQL built a mechanism to handle various "engines" and absorbed Innobase, renaming it InnoDB.

There is some evidence to indicate that more than 2 files is actually counter-productive.

(See Bill's Answer for a more authoritative discussion of InnoDB's history.)

Philosophy After that, "if it ain't broke, don't fix it". MySQL always seems to have an attitude of enhancing only what is really needed.

Some things get implemented (or fixed) because a single customer either submits code or pays to get it done.

I guess no one thought innodb_log_files_in_group was broken enough to fix.

The Query Cache is going away (gone in MySQL). It was implemented before 2000 to beat the pants off the competition in some benchmark. But it was implemented in a clunky way. Once multi-Master and Cluster topologies became popular, the QC had to go. The QC cannot easily be replicated. Aurora re-implemented it at least to scale better.

MyISAM is all-but-deprecated by MySQL (but not MariaDB). It, too, does not play nicely with clustering.

MariaDB.com has some of the MySQL developers from the 1990s.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.