June 19, 2013

Post: Announcing Percona XtraBackup 2.1.1 GA

May 15, 2013 By Leave a Comment

… XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel processing of individual tables when rebuilding the index. Percona XtraBackup … the MySQL command line client. Support for InnoDB 5.0 and InnoDB 5.1 builtin has been removed from Percona …

Filed Under: MySQL, Percona XtraBackup Tagged With:

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… 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…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 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…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…’ve sorted this out, let’s drop and rebuild our FT index on the InnoDB table and try the above queries one more…. To test this hypothesis, I reset innodb_ft_min_token_size to 1, dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT… add your first FULLTEXT KEY to an InnoDB table, be prepared for a table rebuild. Calculation of match score is completely different…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

… it in the INFORMATION_SCHEMA.INNODB_INDEX_STATS table and the ‘index_total_pages’ column.  A page in Innodb is (usually) 16k, so some…_schema, table_name) join information_schema.innodb_index_stats s using (table_schema, table_name, index_name) where t.table_schema like ‘sakila… use innodb_file_per_table, then you can rebuild the tablespace for your table by simply doing: mysql> alter table mytable ENGINE=Innodb…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)

June 19, 2012 By 2 Comments

… 128MB 17 sec REBUILD 37 sec REBUILD in this table is using “fast_index_creation=0″ which allows to disable fast index creation in… case table rebuild is so close in performance to building index by sort with small buffer – this comes from building index on long character field with very short length, Innodb would…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…| InnoDB rebuilding table to add column FTS_DOC_ID | +———+——+————————————————–+ This doesn’t make a lot of sense to me. Why does InnoDB … of adding and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

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…

Filed Under: Insight for DBAs, MySQL

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…

Filed Under: MySQL

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…

Filed Under: Insight for DBAs, MySQL Tagged With:
« Older Posts