Video and Slides: How InnoDB works

Posted in: Technical Track

This presentation was done by Sheeri Cabral of The Pythian Group and went into how to use SHOW ENGINE INNODB STATUS to get more information about your Innodb tables, foreign keys and transactions. This is a great presentation to learn how InnoDB works.

It also went through how to use SHOW ENGINE INNODB STATUS to tune several InnoDB variables:


The slides can be downloaded from:

(Note that the slides open up to the middle section, which has slides plus notes, but if you just want the slides without notes, that starts on page 1 of the PDF.)

The video can be watched below, or directly on YouTube at

Interested in working with Sheeri? Schedule a tech call.

11 Comments. Leave new

Hi Sheeri,

Thanks for sharing the slides!
A note: I’m taking a wild guess, that you created the presentation with OpenOffice, then exported to PDF. I’m guessing so because of the nasty “feature” of OpenOffice to first generate slides-only pages, then slides+notes pages.

You may find my following howto useful:



Shlomi — yep! Usually people just want the notes, so I use the trick of opening the first page to the first page of the notes. (mostly it bothered me when I printed, b/c when I print I want *only* the notes. So for a 10-slide deck, I print pages 11-20).

But I appreciate your trick!


Shlomi — did you like the video too?



Haven’t watched it yet. Will find the time after the weekend :)


Can you really say :
“modified db pages are pages not flushed to disk yet (>0 even when flush_logs_at_trx_commit=0).”

Only innodb_flush_log_at_trx_commit=1 (default/Acid compliant) mode guarantees flushes with every commit. So anything other than default value 1, will flush logs to disk. But still this isn’t directly related to dirty pages?


Kayra —

Yes, I can say that.

innodb_flush_log_at_trx_commit flushes logs to disk, not dirty pages. If the database crashes, InnoDB’s crash recovery will redo the writes on the dirty pages using what’s in the logs.

The flushes are for logs only, not the actual data pages. That tripped me up when I first thought about it too.


Sorry I should have been more clear with my question above.
My question was more like to understand relationship you came up with between dirty page count and innodb_flush_log_at_trx_commit variable.
AFAIK innodb_flush_log_at_trx_commit=1 guarantees 1 commit – 1 flush. Any other variation will (almost) provide flush every second. As you said, flushes are for logs only, so when you flush, you actually flush into log file, not to InnoDB table space. I believe flushing into log, whether its with every commit or every second should not reduce dirty pages in memory as they’re still not identical to the pages on InnoDB table space.


Kayra —

I’m not sure what the question is…you commented on me saying that dirty pages are not flushed to disk, even when logs are…you quoted me saying:

“modified db pages are pages not flushed to disk yet (>0 even when flush_logs_at_trx_commit=0).”

so I think we’re agreeing here….flushing logs has nothing to do with flushing dirty pages…..

Unless I missed something in your original question?


Thank you, I believe we agree on that.
I just wanted to confirm there is no direct relationship between dirty pages & log flushes as that sentence I quoted sounded as if there is one.

Thank you again.

Chris Huntley
June 5, 2010 2:52 am

I can’t tell from your comments if you’re just reviewing how innodb works impartially or if you’re a fan. That being said, do you think there’s a better storage engine for mysql?

Sheeri Cabral
June 5, 2010 9:57 am

Chris — I’m not sure what you mean….As the first paragraph implies, I went through SHOW ENGINE INNODB STATUS in this presentation, and in doing so ended up teaching a lot about how InnoDB works.

Is there a “better” storage engine for MySQL? Sure, if you’re doing lots of INSERTs, few or no UPDATEs and few or no DELETEs, and some reads, it’s best to use MyISAM with concurrent_insert=2. Similarly, if you want to do blob streaming PBXT is better than InnoDB.

I could go on, but “better” is relative for what you want to do with a particular table.


Leave a Reply

Your email address will not be published. Required fields are marked *