… through the pages for each Innodb file and simply write all the new pages to the separate file. Because pages already have position information in them there is no need to have complex “diff” meta data. For recovery we can simply read this new pages file and put the pages back to…
Post: Recovery after DROP [ TABLE | DATABASE ]
July 19, 2009 By 4 Comments
… the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can’t help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm – files are deleted…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… works well for data storage and for…innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery…innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb…
Post: Friday challenge: ibd recovery
May 15, 2009 By 8 Comments
… InnoDB data recovery techniques. I have .ibd datafile which was created by query CREATE TABLE `tryme` ( `email` mediumblob, `content` mediumblob ) ENGINE=InnoDB … (…$ Gift Card as replacement. File tryme.ibd you can download here http://mysqlperformanceblog.com/files/contest/tryme.ibd. Size 65536 …
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… benchmark (100W ~ 10GB of data, 12G innodb_buffer_pool_size, 1G innodb_log_file_size), MySQL 5.5.10 with innodb_adaptive_flushing=ON (default… would be to increase innodb_log_file_size, but that: 1) only delays the problem until later; 2) increases recovery time (and that is an important factor with slow disks); and 3) MySQL does not support innodb_log_file_size > 4GB…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
… file time may have recovery time of 5-10 minutes. To understand things affecting recovery time you better to understand how Innodb recovery works. When Innodb… second. Database Size This comes back to the data locality but with same data access distribution the larger database you’ll have…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_data_writesblogpercona.commanual Innodb_data_writtenblogpercona.commanual Innodb_dblwr_pages_writtenblogpercona.commanual Innodb_dblwr_writesblogpercona.commanual innodb_doublewriteblogpercona.commanual innodb_fast_shutdownblogpercona.commanual innodb_file_formatblogpercona.commanual innodb…
Comment: How to estimate time it takes Innodb to Recover ?
June 25, 2008 By 7 Comments
…:16 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer… InnoDB: Doing recovery: scanned…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size=5600M innodb-file-format=barracuda innodb-file-per-table innodb-flush-log-at-trx-commit=1 innodb…
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
… to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time… explain what happens on recovery and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find log… data modifications (random updates will need random IO to check if pages are up to date), number of unflushed pages in innodb…
