Home arrow MySQL arrow Page 3 - How Logs Work On MySQL With InnoDB Tables

How Logs Work On MySQL With InnoDB Tables

Every wondered how MySQL logs transactions etc with the InnoDB table type? In this article Peter teaches us everything we need to know and more!

Author Info:
By: Peter Gulutzan
Rating: 5 stars5 stars5 stars5 stars5 stars / 34
August 06, 2002
  1. · How Logs Work On MySQL With InnoDB Tables
  2. · What Happens With Data-Change Statements?
  3. · What Happens With Commits?
  4. · Conclusion

print this article

How Logs Work On MySQL With InnoDB Tables - What Happens With Commits?
(Page 3 of 4 )

At COMMIT time, InnoDB will NOT write all dirty data pages to disk. I emphasize that because it's easy to think that committing changes means writing everything on a persistent medium. Well, the InnoDB folks are smarter than that. They realize that only the log records need writing. The writing of dirty data pages can happen at overflow or checkpoint time, because their contents are redundant. If the log survives a crash, remaking of data pages is possible using the information in the log records.

So InnoDB should only write the log. Or to be exact, InnoDB should write log records, until it has written all log records which apply to the transaction that's committing. Since all log writing is serial, that means InnoDB must write log records for other transactions too, but that's okay.

Here I must get critical, because that's not what InnoDB does, necessarily. If MySQL's my.cnf's innodb_flush_log_at_trx_commit switch is zero, then InnoDB will avoid log writing at commit time. This means that a successful COMMIT won't "guarantee" that all data changes have become persistent, which is what the ANSI/ISO standard demands. The persistence guarantee applies only for checkpoints.

Anyway, you can set innodb_flush_log_at_trx_commit to one. In that case all's well, InnoDB will write the log. Also InnoDB will flush.

I'd better explain what flushing is, eh? Usually it's enough to merely write, but all modern operating systems will cache writes for efficiency reasons. To get the "guarantee" InnoDB must insist to the operating system "I really mean write, I want that disk write head on a platter, don't return till the physical operation is complete." This means that on a Windows system InnoDB calls the Windows-API function FlushFilBuffers, a call meaning "flush the cache." Here InnoDB parts company with Microsoft: SQL Server 2000 would use a "write through" option during the write, rather than flushing after the write.

We come now to the point that makes all the logging trouble worthwhile: if a crash happens, you can recover your data.

For a crash that didn't melt the disk drive, recovery is automatic. InnoDB reads the last checkpoint log record, sees if the "dirty pages" were written before the crash, and (if they weren't) reads the log records which affected the page and applies them. This is called "rolling forward" and it's easy for two reasons: (1) because the LSN is there so InnoDB just has to compare numbers to get into synch, (2) because I left out a few details.

Fine. Now, what about a crash that did melt the disk drive? Then the recovery scenario depends on your preparation.

Scenario one: the log is gone. Well, you should have prepared by having a log copy on a separate drive. InnoDB has no explicit option but there are operating-system-specific ways.

Scenario two: the database is gone and the log is overwritten. Well, you should have anticipated that with circular logging, log record #5 will overwrite log record #1. Remember? Therefore if you didn't take a backup after the writing of log record #1, you've lost data.

Scenario three: the database is gone and the log is okay. In that case, congratulations. You just have to restore your last backup copy, and roll the whole log forward. There would be complications if you had to back up the log itself several times since the last full backup ("archive logging"), but I've assumed that option is currently inoperative. By the way, I am not discussing the role of MySQL's binlog. It's essential to the process but isn't part of InnoDB, so out of scope.
blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials