June 20, 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: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

… do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode Innodb becomes read only…) == dict_index_get_page(index)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If…

Filed Under: Insight for DBAs 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: 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: 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…

Filed Under: MySQL, Percona Software 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… indexes, but the tablespaces on disk are still the same old size.  If you 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: 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
« Older Posts