…=InnoDB Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from…: 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
… be much faster as well as ALTER TABLE (expecially together with ability to create index without table rebuilding) Buffer pool improvements. I would like… and promoting real one to unique. Tablespaces. To call “innodb_file_per_table” option tablespaces is a joke. It is better than…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…. Q37: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are… run to show any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may last way… adaptive hash indexes are not used for primary key lookups or not showed up in “show innodb statusâ€. select * from table where…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
… corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of… help. Type ‘\c’ to clear the buffer. mysql> show create table test_innodb; +————-+—————————————————————————————————————————-+ | Table | Create Table… need to be exist. The intuitive way to rebuild MyISAM table would be REPAIR TABLE test_myisam USE_FRM, however it does not…
Post: What to do with MySQL Full Text Search while migrating to Innodb ?
September 10, 2009 By 12 Comments
… migration is Full Text Search indexes which are not supported for Innodb tables. So what can you do ? Leave Tables as MyISAM The beauty… evils. Use “Shadow” MyISAM Table You can keep main data in Innodb but build a “shadow” MyISAM table which is used for full… on the writes. In this case you can just rebuild the table from Innodb source on regular basics. In case you can afford…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… keys 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. In…
Post: Feature preview: Compact backups in Percona XtraBackup
January 29, 2013 By 11 Comments
… innobackupex –compact and it will create a backup where all InnoDB tables contain only Primary Keys and not secondary. It allows to…-log –rebuild-indexes /data/backup As a bonus, secondary indexes are created by sorting, that in general gives much less fragmented indexes, so it…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 …large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_…
Post: Small things are better
October 8, 2006 By 15 Comments
… kind be mild, so ALTER TABLE to rebuild the table fixes it. Sometimes table needs to be dropped and …column to 500GB Innodb table. It will probably take long hours or even days for ALTER TABLE to …as whole indexes will well fit in memory for such small tables. Not to mention splitting 500 tables …
Post: Magic Innodb Recovery self healing
May 22, 2007 By 2 Comments
… the table corrupting with Innodb, as it is limited to only one particular index on one particular table type it is likely to be Innodb… 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…
