… loaded by the cold test already. The buffer pool and adaptive hash index are cold for the cold test. All tests were done… believe the adaptive hash index is improving performance of InnoDB significantly during the hot run, as hash indexes are faster than a b-tree index. Also… data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… 5.6.11 top mutexes is &buf_pool->mutex,&new_index->lock. I profiled 5.6.11 in this IO bound…=0 #56only loose-innodb_flush_neighbors=0 loose-metadata_locks_hash_instances=256 innodb_buffer_pool_instances=16 # MySQL 5.5…_capacity_max = 15000 #Percona only innodb_adaptive_hash_index_partitions=8 innodb_buffer_pool_instances=1 innodb_adaptive_flushing_method=keep_average innodb_flush…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… process query traffic except for replication. The buffer pool and adaptive hash index on the standby server will not have recently accessed pages…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… used by adaptive hash index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index efficiency. There is currently not much you can do to adjust adaptive hash index or insert…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… P.S Disabling adaptive hash index makes things a lot worse in this workload and innodb_adaptive_hash_index_partitions=16 available for partitioning adaptive hash index does not help because everything hits the same index.
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… there a facility we can run to show any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE… and reorganizes small portions of table online. Q38: Seems that adaptive hash indexes are not used for primary key lookups or not showed…) 0.00 hash searches/s, 15979.01 non-hash searches/s HT: Strange, primary key equality searches should work through the adaptive hash index! What…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…. That would clearly save indexing space. Is this a reasonable performance optimization. (Keep in mind that the prefix adaptive hashing would fail here, because the first 10 or so characters usually are the same). Of course UNIQUE index optimizations…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… performed. But InnoDB’s adaptive hash indexes require that the prefix is stored in every record R. An adaptive hash index tries to guess where our… can’t wait for ability to create physically sorted indexes with Innodb (with index built by sort) this is when this should… single extent. For big tables, a secondary index reserves different extents from the clustered index. Q4: Does Innodb ever merges sequential pages…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… the Adaptive Hash Index (AHI) Scan the buffer pool and for each page in the buffer pool If the page has a hash index built… access time) and a shared reader-writer lock on the Adaptive Hash Index latch. The buffer pool mutex is a major point of…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… is to disable adaptive_search (it is possible via global variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to perform much more operations reading secondary keys. How it can be solved internally: I think some hashing…
