gotchasqlMinor
Why does MySQL require two or more redo log files for InnoDB?
Viewed 0 times
whyloginnodbmoremysqltwofilesforrequiredoes
Problem
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#303964, answer score: 3
Revisions (0)
No revisions yet.