… storage engine. In my testing I discovered that innodb_old_blocks_time had an effect on performance of the benchmark. There… used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld…=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_size = 4G innodb_log_files_in_group = 2 innodb_log_block_size=4096 #####plugin options innodb_read_io_threads = 16 innodb… options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G myisam…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Key_blocks_not_flushedblogpercona.commanual Key_blocks_unusedblogpercona.commanual Key_blocks_usedblogpercona.commanual key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…Key_blocks_not_flushed’, ’0′ ‘Key_blocks_unused’, ’28987′ ‘Key_blocks_used’, ’1332′ ‘Key_read_requests’, ’1552507′ ‘Key_reads’, ’1654′ ‘Key_write_requests’, ’114303′ ‘Key…Slow_queries’, ’27405′ ‘Sort_merge_passes’, ’376′ ‘Sort_range’, ’56479689′ ‘Sort_rows’, ’1344092495′ ‘Sort_scan’, ’935242′ ‘Table_…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost… the performance of secondary key lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So… cover it in my next post which will be on Block Access Join Algorithms. Conclusion There is a huge speedup when…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… will sort the…key%”; +————————+———+ | Variable_name | Value | +————————+———+ | Key_blocks_not_flushed | 6516 | | Key_blocks_unused | 0 | | Key_blocks_used | 7248 | | Key_read_requests | 2051451 | | Key_reads | 198208 | | Key_write_requests | 469220 | | Key…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… compare Block Nested Loop (BNL) Join of MySQL 5.5.24, Batched Key Access (BKA) Join of MySQL 5.6.5 and Block…_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ mrr_buffer_size… then scanning the right-side table becomes costly in comparison. Block Nested Loop Join would perform better than Hash Join when…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
… – sorting was used in corelated subquery which was executed hundreds of thousands of times but only few rows were sorted. 32K block is allocated fully in user space, while 32M block requires mmap() call and a…(10) default NULL, `d` int(11) default NULL, PRIMARY KEY (`id`), KEY `d` (`d`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 select sql_no…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process. Mark Callaghan… in their tree back in early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will… 1Mil rows Sysbench table. Buffer Length | alter table sbtest add key(c) 1MB 34 sec 8MB 26 sec 100MB 21 sec…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… which may take too long to complete Index build by sort. This is in ToDo but so important I’ll still… artificial second column is ugly and inconvenient. Having independent CLUSTER=key option would be great, it also would help to avoid… are able to handle it – even MyISAM has 1K-4KB key blocks so it is well possible Scalability It is actually bug…
