… below). Let’s compare the restore time for a regular dump with a dump created with –innodb-optimize-keys (the test database… 0m3.204s sys 0m0.062s Caveats: As I mentioned previously, InnoDB fast index creation uses temporary files in tmpdir for all indexes… a separate ALTER TABLE after restoring the data from a dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
…. First, you must meet certain prerequisites to be able to restore a ibd tablespace: The ibd file must be from a… of the shared tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid the server… –export option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers. # innobackupex…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… differences between MyISAM and Innodb though well it is typically easily spotted by converting tables to Innodb on restored backup. Full text search… very fast for MyISAM but does table/index scan for Innodb. Defaults You need to know two things about defaults for Innodb. First… most important values to check are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
…, I see real needs in this: – when we need to restore only single table from backup (sometimes developers kill only single… in error.log like: InnoDB: import: extended import of test/img_out59 InnoDB: import: 3 indexes are detected. InnoDB: Progress in %: 1 2… shutdown instance with innodb_fast_shutdown=1 (done in xtrabackup –prepare –export call). The more complex problem was that InnoDB stores pointers on…
Post: XtraDB feature: save / restore buffer pool
January 20, 2010 By 20 Comments
… announcement, the release-making feature is ability to save and restore InnoDB buffer pool. The idea is not new and was originally….percona.com/docs/wiki/percona-xtradb:patch:innodb_lru_dump_restore (Yasufumi names it LRU dump/restore, because he thinks about buffer pool… the same as in my runs on fast storages, and I used RAID10 to store InnoDB files). First run (xtradb cold) I…
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
… data The important tables were all InnoDB Having a backup, customer has first attempted to restore from backup on the production master… a raw device. I was surprised how amazingly simple and fast it was (did you know that latest version of page… and see what you get. If InnoDB Data Dictionary was not overwritten by an attempt to restore from the backup, actually second…
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
New features of InnoDB – compression format and fast index creation sound so promising so I spent some time … ‘bodyuc’ as compressed field, doing compress()/uncompress() to store/restore. To test InnoDB compression I use only uncompressed text, though I tested… size 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create…
Post: My "hot" list for next InnoDB features
March 30, 2009 By 18 Comments
Many InnoDB scalability problems seem fixed in InnoDB-plugin-1.0.3 and I expect InnoDB-plugin will run fine on 16-24… wish-list of features I would like to see soon: Fast recovery. Both recovery after crash and recovery from backup can… 500GB backup while you need to restore only single 20GB table is very non-productive Open InnoDB tables in parallel. Currently opening…
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 change innodb_log_file_size safely
March 18, 2013 By 17 Comments
…ini innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_fast…26 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring …
