Did you know that your database has a transaction log file? Do you know what’s it is used for? More importantly, do you know how to size it properly?
Stick with me, you will have answers to all these questions in a moment.
What is InnoDB Transaction Log?
Have you ever used an Undo or Redo function in a word processor, image editor or virtually any editor for that matter? I’m sure you have! Well guess what, transactional databases have exactly the same thing! Well not exactly, but the principles are the same.
And just like it’s important for you to always have the ability to go back a few steps in your editing process, so are the undo and redo functions important for a transactional database system. Why? Two reasons primarily:
- Rolling back a transaction (that’s the Undo)
- Replaying committed transactions in case of a database crash (and that’s Redo)
Here’s how it works.
When you are using a transactional storage engine (let’s stick to InnoDB for now), and you modify a record, the changes are not written to data file directly.
First, they are written to a special file on disk called transaction log. And at the same time, they are also modified in memory – the InnoDB’s buffer pool. This new InnoDB page that containts your modified record is now called dirty – remember?
The original unmodified page is copied to a special area on disk called rollback segment.
So far so good?
Now, if someone (or something) interrupts a transaction with a ROLLBACK before it’s committed, Undo operation needs to occur – your record has to be restored to it’s original state.
As the changes weren’t written to data file yet, this is pretty trivial – InnoDB just takes the old copy of the page from the rollback segment, wipes the dirty page from memory and marks in a transaction log that the transaction was rolled back.
So there you go. Data file was never modified and it’s good that it wasn’t, because you have cancelled any changes you made before even issuing a random write operation to flush that dirty page to disk.
When you COMMIT the transaction, however, and InnoDB approves your commit (i.e. it returns from the COMMIT call), changes are ready to be written to actual data files.
You’d think they are written to disk immediately at this point, but that’s not what happens. Why? Because doing so would be very inefficient. Instead, the changes are only written to the transaction log (this is very efficient sequential activity called Redo logging), while the modified record still lives in memory – in the InnoDB buffer pool as a dirty page, for as long as time comes to flush it.
CRASH!!!MySQL just crashed…
Guess what happens now?
Well, if MySQL (InnoDB, actually) had no redo log and it only kept dirty pages in memory – all the committed transactions that were not flushed to disk yet would be gone forever. Quite a disaster if you consider that one of these transactions may have been your $20,000 salary transfer from company account to yours.
Luckily, the changes ARE always written to the transaction (aka Redo) log before transactions return, so all InnoDB needs to do is find the last checkpoint in the Redo log (position that’s been synchronised to disk) and Redo all of the modifications by re-reading the “to-be-modified” data from disk and re-running the same changes.
Easy peasy, right?
Well, right. But only on the surface. Underneath, there’s a lot of really complex stuff happening that you probably don’t want to know about right now. We can talk about it sometime later.
One thing you may want to know about though is how to size the
innodb_log_file_size properly. The rules are actually pretty simple:
- Small log files make writes slower and crash recovery faster
- Large log files make writes faster and crash recovery slower
Writes become slow with small Redo log because the transaction logs act as a buffer for writes. And if you have a lot of writes, MySQL may be unable to flush the data fast enough, so write performance degrades.
Large log files, on the other hand, give you a lot of room that can be used before flushing needs to happen. That in turn allows InnoDB to fill in more pages (for example, modify few records that are on the same page, or in fact, modify same record several times) and also, in case of Magetic drives, flush the dirty pages in a more sequential order.
As for the crash recovery – larger Redo log files means more data to be read and more changes to be redone before the server can start, which is why it makes crash recovery slower.
Sizing the Redo log
Finally, let’s talk how you can figure out the right size for the Redo logs.
Luckily, you don’t have to come up with a size that’s exactly right. Here’s a rule of thumb that we found to work like magick:
Rule of ThumbCheck that total size of your Redo logs fits in 1-2h worth of writes during your busy period
How do you know how much InnoDB is writing? Here’s one way to do it:
mysql> pager grep seq
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 1777308180429
Log sequence number 1777354541591
mysql> select (1777354541591-1777308180429)*60/1024/1024;
| (1777354541591-1777308180429)*60/1024/1024 |
| 2652.80696869 |
1 row in set (0.00 sec)
In this case, based on 60s sample, InnoDB is writing 2.6GB per hour. So, if
innodb_log_files_in_group was not modified (by default it is 2, which is the minimum number of Redo log files that InnoDB needs), then by setting
innodb_log_file_size to, say, 2560M, you will have exactly 5GB of Redo log storage across the two Redo log files.
Changing the Redo log size
How hard it will be to change the
innodb_log_file_size AND how large you can set it to, depends greatly on the version of MySQL (or Percona, or MariaDB) server that you are using.
Specifically, if you are using version prior to 5.6, you can’t simply change the variable and expect that the server will restart. In fact it will, stop, but won’t start. Funny, I know.
So here’s how you have to do it – I have described the process on Percona blog few years ago. Basically, it’s:
- change innodb_log_file_size in my.cnf
- stop MySQL server
- ensure MySQL had a clean shutdown (mysql log is your friend)
- remove old log files, usually by running the following command: rm -f /var/lib/mysql/ib_logfile*
- start MySQL server – it should take a bit longer to start because it is going to be creating new transaction log files
Final thing you should be aware of is that until quite recently (i.e. until MySQL version 5.6.2), the total Redo log size (across all Redo log files) was limited to 4GB, which was quite a significant performance bottle-neck for write-intensive SSD backed MySQL servers. (Percona Server, on the other hand, supports 512GB since like Percona Server five ou something)
In other words, before you set
innodb_log_file_size to 2G or more, check if the version of MySQL you are running actually supports it.
P.S. If you have stumbled on this post by accident and you’re not sure what other posts I am talking about, start here.
Share this Post