… fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX….062s Caveats: As I mentioned previously, InnoDB fast index creation uses temporary files in tmpdir for all indexes being created. So make sure you…
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to … size 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create table only with primary key, without additional indexes. Numbers…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are not… space. Fast Index Creation This is another feature which I was asking Heikki about probably since my first days as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build indexes by…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… feature to address this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should be quite good. To check that, there is xtrabackup –stats for index domain…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… physically sorted indexes with Innodb (with index built by sort) this is when this should be helpful the most. Q3: Does Innodb secondary Indexes trees… where the next index page in the ascending order is. InnoDB has a similar heuristic for descending index scans. The InnoDB sequential read-ahead…). The index is required for fast lookups: it can be used if the columns are the first columns in an index. I think…
Post: Percona Server 5.1.49-rel12.0
September 15, 2010 By 6 Comments
… global system variable innodb_flush_log_at_trx_commit. (Yasufumi Kinoshita) innodb_fast_index_creation – Allows disabling of fast index creation. (Yasufumi Kinoshita) innodb_use_sys_stats… Bug #576041 – Fixes long stalls while accessing the innodb_buffer_pool_pages_index table on systems with a large number of tables…
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 This… were adding indexes to the same tables. We had a suspicion that it had something to do with Fast index creation in Innodb, and… Innodb thinks is in the Innodb Data dictionary. The first recommendation I got was to simply use the INFORMATION_SCHEMA.INNODB_SYS_INDEXES table…
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… really control it (tough it is possible in XtraDB / InnoDB-plugin with FAST INDEX creation) so be careful with queries scan many records…
Post: Percona Server 5.5.11-20.2 Stable Release
April 28, 2011 By 1 Comment
… 11th, 2011. InnoDB fast index creation now works with mysqldump, ALTER TABLE and OPTIMIZE TABLE. (Alexey Kopytov) Variable Changes Variable innodb_extra_rsegments was… an InnoDB table to a MEMORY table could fail due to a bug in innodb_expand_fast_index_creation.patch. (Alexey Kopytov) Bug #764395 – InnoDB…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
Running many benchmarks on fast storage (FusionIO, SSDs) and multi-cores CPUs system I constantly … problem is index->lock mutex. InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index for write operation… global variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to perform much more operations reading secondary keys. How…
