…. Notes MySQL version used: 5.6.11, custom compiled to remove performance_schema For the InnoDB tests, a 64GB buffer pool was…=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size…_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…-time=7200 –max-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ –oltp-read-only=off run…_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size… Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size… (key_buffer_size). There are many examples of this. What about join_buffer_size? I saw a my.cnf with a 128M join_buffer_size… per-join-per-thread, in special cases. A join buffer is allocated to cache rows from each table in a join when the join can…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But…io_threadsblogpercona.commanual insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_on_createblogpercona.commanual Key_blocks…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | SORT_BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER… now allowing MySQL to handle larger queries. Makes sense as amount of memory available is much larger these days. join_buffer_size have…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… posted the great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are a lot… able to deal with simple and complicated queries efficiently automatically. join_buffer_size = 256k The default is now double of what it… or is it just looking to reduce number of different buffer sizes used. sync_master_info = 10000 Quite useless change in…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… (BNL) Join of MySQL 5.5.24, Batched Key Access (BKA) Join of MySQL 5.6.5 and Block Nested Loop Hash (BNLH) Join of…_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join_buffer_space_limit=32M Note that MariaDB includes a new variable ‘join_cache…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL… MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5 MariaDB 5.5 w/ join_buffer_size=6M & mrr_buffer…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… you’re dealing with very large data sets are Buffers, Indexes and Joins. Buffers First thing you need to take into account is… in MySQL is lack of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it…
