…: 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: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
…_fast_index_creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs Fixed InnoDB requires a full table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 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: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row… run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation it can make…
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 10 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…
Post: What to do with MySQL Full Text Search while migrating to Innodb ?
September 10, 2009 By 12 Comments
… run into during migration is Full Text Search indexes which are not supported for Innodb tables. So what can you do ? Leave…. Use “Shadow” MyISAM Table You can keep main data in Innodb but build a “shadow” MyISAM table which is used for… on the writes. In this case you can just rebuild the table from Innodb source on regular basics. In case you can…
Post: Feature preview: Compact backups in Percona XtraBackup
January 29, 2013 By 11 Comments
… does. As you may know InnoDB PK (Primary Key) contains all data, and all secondary indexes are only subset of columns of… to rebuild indexes, and it is done on prepare stage, and it takes time. The command to prepare is: innobackupex –apply-log –rebuild-indexes /data/backup As a bonus, secondary indexes are created by sorting, that in general gives much less fragmented indexes, so it…
