… by the cold test already. The buffer pool and adaptive hash index are cold for the cold test. All tests were done… 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… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… query traffic except for replication. The buffer pool and adaptive hash index on the standby server will not have recently accessed pages…_server:3307/slow | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… related to BUG#67808. For MySQL 5.6.11 top mutexes is &buf_pool->mutex,&new_index->lock. I profiled 5.6…-innodb_flush_neighbors=0 loose-metadata_locks_hash_instances=256 innodb_buffer_pool_instances=16 # MySQL 5.5 and 5.6 loose-innodb_io_capacity_max = 15000 #Percona only innodb_adaptive_hash_index_partitions=8 innodb…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… a six-hour tutorial about Percona XtraDB Cluster (PXC) for MySQL. I actually had more material than I covered (by design… replication feedback mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the lack of understanding… lookups into the certification index, which is a hash table. A small fc_limit does however keep the certification index smaller in memory…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…,mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_conne ction,start_thread,clone 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…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length)… Internal hash tables (constant factor + variable factor) Adaptive hash index 4422068288 (2039977928 + 2382090360) Page hash 127499384 …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But …commanual innodbblogpercona.commanual innodb_adaptive_flushingblogpercona.commanual innodb_adaptive_hash_indexblogpercona.commanual innodb_additional_mem_pool_sizeblogpercona.commanual innodb…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… optimization in many cases. When you apply it though remember hash can have collisions so you will need to have your… prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix… aware which again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE… true, but there is a reason. HASH index stores list of matching values for each hash value. In this case the key value…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… Adaptive Hash Index (AHI) Scan the buffer pool and for each page in the buffer pool If the page has a hash index built… time) and a shared reader-writer lock on the Adaptive Hash Index latch. The buffer pool mutex is a major point of… –test=oltp –oltp-table-size=20000000 –mysql-table-engine=innodb –mysql-socket=/tmp/mysql_sandbox5524.sock –mysql-db=test –oltp-table-name=sbtest_20m…
