… innobackupex –rebuild-threads option that can be used to specify the number of threads started by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel processing of individual tables when rebuilding the index. Percona XtraBackup has…
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 have 36 seconds in total which is about 1.7 times faster than updating indexes… tmpdir, but rebuilding the indexes will obviously take longer. Another important thing that is worth mentioning is fragmentation. Fast index creation results in…
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… in buffer pool completely in this case which means table rebuild could have done fast too. Results are from Percona Server…
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…
Comment: Feature preview: Compact backups in Percona XtraBackup
March 5, 2013 By 11 Comments
… i dont use –rebuild-index option, as i think, it only apply data to datafile , the space of secondary index won’t be… useless。 should it be as i think， if dont use index rebuild， the space only contain primary key could be used correctly？
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_name, all_indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where used_indexes.INDEX_NAME is…, all_indexes.index_name as index_name, ROUND(all_indexes.index_total_size / ( 1024 * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using…. If you use innodb_file_per_table, then you can rebuild the tablespace for your table by simply doing: mysql> alter…
Comment: Why MySQL could be slow with large tables ?
August 14, 2006 By 141 Comments
Hm. I’m actually quite surprised. ALTER TABLE normally rebuilds indexes by sort, so does LOAD DATA INFILE (Assuming we’re … unexpected. Might be for some reason ALTER TABLE was doing index rebuild by keycache in your tests, this would explain it. ALTER…
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… become very slow when index does not fit to memory any more. Creating Index by sort is great Creating index by sort can… run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation it can make…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL… 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…
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… passed to the next step – there could be some non-index based filtering applied. The other reason for difference is join…, while retrieval of large rows from uncached table by random index values (or even range) may be perfoming at some 100…
