Video and Slides: How InnoDB works

Oct 16, 2009 / By Sheeri Cabral

Tags: , ,

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:

innodb_adaptive_hash_index
innodb_commit_concurrency
innodb_concurrency_tickets
innodb_file_io_threads
innodb_log_buffer_size
innodb_max_purge_lag
innodb_sync_spin_loops
innodb_thread_concurrency
innodb_thread_sleep_delay

The slides can be downloaded from:

http://technocation.org/files/doc/ShowEngineInnoDBStatus.pdf

(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 http://www.youtube.com/watch?v=ocdjspoLM58

11 Responses to “Video and Slides: How InnoDB works”

  • Shlomi Noach says:

    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: http://code.openark.org/blog/openoffice/how-to-export-openoffice-presentations-to-pdf

    Regards

  • Sheeri Cabral says:

    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!

  • Sheeri Cabral says:

    Shlomi — did you like the video too?

  • Shlomi Noach says:

    Sheeri,

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

  • Kayra Otaner says:

    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?

  • Sheeri says:

    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.

  • Kayra Otaner says:

    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.

  • Sheeri says:

    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?

  • Kayra Otaner says:

    Sheeri,
    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.

  • 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?

  • 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

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>