… table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast index creation. So we… a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… stored already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB: Database page… do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode Innodb becomes read only…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… requires table rebuild. Yes you get it right even though Innodb is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data change needs a table rebuild…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… that the extra index in Innodb could be removed by doing: ALTER TABLE table_name ENGINE=Innodb; This, of course, rebuilds the whole table based on the .frm table definition and removes the existing index in Innodb, which…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…: 11569733 # InnoDB_IO_r_ops: 38530 InnoDB_IO_r_bytes: 631275520 InnoDB_IO_r_wait: 0.204893 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages… control it (tough it is possible in XtraDB / InnoDB-plugin with FAST INDEX creation) so be careful with queries scan many records…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
…’d like to see Packed indexes. In many cases then difference in Performace with MyISAM and Innodb is huge for read only workload it is caused by the fact Innodb does not have prefix compression for its indexes… as ALTER TABLE (expecially together with ability to create index without table rebuilding) Buffer pool improvements. I would like to see couple…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1 mysql> select * from a… table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure… no indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes; doing…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… run to show any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may… that adaptive hash indexes are not used for primary key lookups or not showed up in “show innodb statusâ€. select * from table where id=5 show innodb status: Hash table size 10624987, used…
Post: Magic Innodb Recovery self healing
May 22, 2007 By 2 Comments
… with Innodb, as it is limited to only one particular index on one particular table type it is likely to be Innodb bug… today. And Indeed Restarting Innodb with innodb_force_recovery=0 allowed it to start normally and I could rebuild affected tables my “normal way”. So when dealing with Innodb recovery for bad corruptions you may want to: – Start with lower innodb…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 5 Comments
… the tablespace. If Innodb stops (read: crashes) with dirty buffer pool pages, Innodb recovery must be done to rebuild the last consistent picture… percentage of the total buffer pool. A page in Innodb contains rows, indexes, etc., while a transaction may modify 1 or millions…/or use the global tablespaces) *cough ext3 cough*. innodb_adaptive_flushing: An Innodb plugin/5.5 setting that tries to be smarter…
