…_id = innodb_sys_tables.id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb… they have on buffer pool which may take hours to recover. This tool can be also helpful for capacity planning/performance…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
… for most users – you do not get to deal with recovering tables on the crash or partially executed statements. Table locks… but instantly break upon upgrading to Innodb. Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and…) for logging. Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really…
Post: Innodb Double Write
August 4, 2006 By 24 Comments
One of very interesting techniques Innodb uses is technique called “doublewrite” It means Innodb will write data twice when it performs table… term log file allocated inside Innodb tablespace – it contains space for 100 pages. When Innodb flushes pages from Innodb buffer pool it does… discarded, if it is inconsistent in the tablespace it is recovered from double write buffer. How much does double write buffer…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 3 Comments
… it is easy to check: mysql> select * from information_schema.innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | space_id | zip_size | page_no | max… on-the-page part of the row needs to be recovered from undo space and it will contain correct pointer to…
Post: Innodb Recovery - Is large buffer pool always better ?
July 17, 2007 By 3 Comments
How does Buffer Pool size affects Innodb Performance ? I always expected the effect to be positive, Innodb with large buffer pool to…) with Innodb Buffer pool configured to be just a bit over 20GB. Innodb recovery was taking long hours with reasonably sized Innodb log… with much smaller buffer pool size and we got it recovered much faster. Interesting enough the problem does not seems to…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… instead of a table lock on AUTO-INC. We have recovered the concurrency and the performance but with a small cost…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No gaps at all…
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
… you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance… on recovery and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find… application, crash MySQL in the middle and watch it to recover. Doing this several times you should be able to estimate…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 26, 2012 By 20 Comments
…, we bring up the new mysqld, which finds a crashed innodb, recovers this, and then it is being configured to become a… machine. Every once in a while the recovery of the innodb in the target location fails, though, even though it has…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
Post: Recovery beyond data restore
August 2, 2008 By 11 Comments
… careful such as using sync-binlog innodb_flush_logs_at_trx_commit=1 and only using Innodb tables to be OK in… it also can be things like Innodb corruption or soft crash which you can’t recover promptly enough. In this case most… keep replication positions in mind. Such if you recover master you need to recover slaves to matching snapshot – either it has to…
