…is index->lock mutex. InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index for write operation and … adaptive_search (single mutex for ALL table and ALL indexes), so write query blocks ALL select queries. Usually first action is to disable adaptive_search…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
Post: Looking for InnoDB/XtraDB hacker
October 25, 2011 By 3 Comments
…-flushing/ Fix InnoDB index locking Figure out InnoDB adaptive locking issue, e.g: http://www.mysqlperformanceblog.com/2010/02/25/index-lock-and-adaptive-search-next-two-biggest-innodb-problems/ Make some InnoDB tables non-transactional 
Make InnoDB page size setting per tables and per index…
Comment: Index lock and adaptive search - next two biggest InnoDB problems
March 1, 2010 By 12 Comments
… index->lock and adaptive_search lock, there is single rollback segment and single lock on intrenal data dictionary. It is not as bad as for MyISAM table level lock…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…locks. For these information both for exclusive locks and for shared locks… efficiency. ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf for space …searches/s, 175.05 non-hash searches/s This section shows insert buffer and adaptive…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…lock_s_lock_spin,pfs_rw_lock_s_lock_fu nc,btr_search_build_page_hash_index,btr_search_info_update_slow,btr_search_info_update,btr_cur_search… adaptive hash index makes things a lot worse in this workload and innodb_adaptive_hash_index_partitions=16 available for partitioning adaptive hash index…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… information is stored, and a search almost as efficient as a binary search can be performed. But InnoDB’s adaptive hash indexes require that the… not lock the ‘gaps’, and it does not lock rows whose latest committed version does not match the WHERE condition in a search. Please…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… the option and variable reference …adaptive_flushingblogpercona.commanual innodb_adaptive_hash_indexblogpercona.commanual innodb_additional_mem_pool_sizeblogpercona.commanual innodb_autoextend_incrementblogpercona.commanual innodb_autoinc_lock…commanual optimizer_search_depthblogpercona.commanual…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… to implement online reorganize which locks and reorganizes small portions of table online. Q38: Seems that adaptive hash indexes are not used for primary….00 hash searches/s, 15979.01 non-hash searches/s HT: Strange, primary key equality searches should work through the adaptive hash index! What is…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…, click tracking, search, paginated displays — we’ve seen these and …box (I have to adapt to whatever environment I’…/mysqld.pid –skip-external-locking –port=3306 –socket=/…and bring big gains. Schema, query, and index optimization Analyzing and optimizing a server’s table and index structures, and…
Post: MySQL 5.6: Improvements in the Nutshell
January 27, 2013 By 16 Comments
…Adaptive Flushing – Page Cleaner/Separate Flush Thread – Group Commit for Binary Log – Fight Cache Coherence and…LOCK_open – Support for multiple table_open_cache instances – Large (over 4GB) redo logs support Optimizer and Execution – Index… queries – Full Text Search index for Innodb – Microsecond …
