… different time periods or different regions. The flights represent the type of investigations and drill-downs that are common in OLAP… 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…
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…_size=0 query_cache_type=0 performance_schema=0 #56only loose-innodb_flush_neighbors=0 loose-metadata_locks_hash_instances=256 innodb… loose-innodb_io_capacity_max = 15000 #Percona only innodb_adaptive_hash_index_partitions=8 innodb_buffer_pool_instances=1 innodb_adaptive_flushing…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash…_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+———-+——-+—————+—————+———+————————-+——-+———-+————-+ | 1 | SIMPLE | supplier | index | PRIMARY | i…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 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… level so these are different. We can also see different types of IO submited by Innodb – pages to be flushed via…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range… 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: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
…would merge the delta index with the main index periodically. Sphinx Search also supports a supplementary in-memory RT index type that supports … and indexes. Have you benchmarked it? According to their documentation, MemSQL supports hash indexes and skip list indexes, but not full-text indexes, …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… this means that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ… revenue, o_orderdate, o_shippriority from customer, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey… intend to run tests to see what specific types of queries would benefit from Hash Join as compared to Nested Loop Join…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… queries. MySQL Server does not provide any information of this type, Percona Server however adds number of tables to Information Schema…_name, innodb_sys_indexes.name AS index_name, cnt, dirty, hashed, ROUND(cnt * 100 / index_size, 2) fit_pct FROM (SELECT index_id, COUNT(*) cnt…) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb_sys_indexes ON id = index_id JOIN innodb…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… Extra: Using where *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL… when on disk table is required (as in this case) – Hash indexes used by MEMORY table are very efficient and do not…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… online advertising, user behavior analysis, and many similar types of work. The first thing these …expensive. If MySQL supported sort-merge or hash joins, you’d have other possibilities, but … can we improve here? Especially assuming that there are indexes other than the primary key, we can …
