tag:blogger.com,1999:blog-5412150273415696914.post1505402796965893211..comments2009-02-13T18:42:57.575-05:00Comments on Jay Janssen -- MySQL Guy: What is Innodb doing?Jay Janssennoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-5412150273415696914.post-36205140016333613622007-11-09T15:33:00.000-05:002007-11-09T15:33:00.000-05:00This raises some questions, with respect to runnin...This raises some questions, with respect to running backups.<BR/><BR/>In a replicated environment, you either want to:<BR/><BR/>a) take a backup off a slave by stopping the slave i/o, waiting for it to catch up, waiting for the buffers to flush, tarring up the data/log directories, and then restarting the slave.<BR/><BR/>or<BR/><BR/>b) take a backup off of the master with ibbackup/innobackup.<BR/><BR/>I think the following is likely:<BR/><BR/>1) In case a, the insert merge buffer is contained within the regular buffer pool, so 'Modified db pages' won't read 0 until that insert buffer is cleared.<BR/><BR/>2) In case b, the secondary index merge buffer is irrelevant, because transactions won't be marked as completed/flushed (not sure of the innodb terminology for this) until that buffer is fully merged, so in your backup, an unmerged transaction will simply be replayed from the beginning from the innodb transaction log.<BR/><BR/>Is #1 correct? If not, how do you tell if the insert merge buffer still has data in it, and where is that stored when the server is restarted?<BR/><BR/>Is #2 correct? If not, how does this work to maintain database integrity with respect to the innodb transaction log, where you can have transactions that are marked as completed in the transaction log but have not been fully flushed to disk?Adam Fieldshttps://www.blogger.com/profile/01905580169818425599noreply@blogger.comtag:blogger.com,1999:blog-5412150273415696914.post-37198735663627130482007-11-06T23:58:00.000-05:002007-11-06T23:58:00.000-05:00Innodb also defers secondary index writes using th...Innodb also defers secondary index writes using the 'insert merge buffer'. This process can even continue after MySQL restarts. More details here: http://dev.mysql.com/doc/refman/5.0/en/innodb-insert-buffering.html<BR/><BR/>This is one of the reasons why doing a flush tables with read lock isn't enough to make innodb 'hold still' for a backup like myisam does.Eric Bergenhttps://www.blogger.com/profile/12080548425880371958noreply@blogger.com