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 … now uses Perl’s DBD::MySQL package for server communication instead of spawning the MySQL command line client. Support …

Filed Under: MySQL, Percona XtraBackup Tagged With:

Post: Feature preview: Compact backups in Percona XtraBackup

January 29, 2013 By 11 Comments

… 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: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index…file_per_table, then you can rebuild the tablespace for your table by simply doing: mysql> alter table mytable ENGINE=Innodb; However…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name… run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation it can make…

Filed Under: Insight for DBAs, MySQL

Post: Innodb vs MySQL index counts

November 29, 2011 By 6 Comments

…/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This customer was… 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 might…

Filed Under: MySQL

Post: MySQL EXPLAIN limits and errors.

July 24, 2006 By 6 Comments

… executed – will in add index live as it can now do on Cluster ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In MySQL 5.0… some non-index based filtering applied. The other reason for difference is join_buffer which reduces number of passes MySQL had to…

Filed Under: Insight for Developers Tagged With: ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER… table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast index creation. So we have 36 seconds in total which is about 1.7 times faster than updating indexes…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

June 19, 2012 By 2 Comments

… I was thinking this small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date… 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…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

…) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +———+——+————————————————–+ | Level | Code | Message | +———+——+————————————————–+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC… to actually rebuild the table. -rw-rw—-. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_ADDED.ibd -rw-rw—-. 1 mysql mysql 98304… subject 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

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

Filed Under: Insight for DBAs, MySQL
« Older Posts