…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_size=8M default_tmp…old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_table_type = InnoDB thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM 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…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 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…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); …buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table… –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… default key_buffer_size which is not large enough to fit all key blocks from temporary table index, so we get a lot of key writes…: mysql> set global key_buffer_size=8000000; Query OK, 0 rows affected (0.03 sec) mysql> set global max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set global tmp_table_size=1000000000; Query OK, 0…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp | | TABLE_OPEN_CACHE | 400 | 2000 | | INNODB_VERSION | 5.5.30 | 1.2.10 | | INNODB_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key…timestampblogpercona.commanual tmp_table_sizeblogpercona….
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…-locking key_buffer_size = 2000M max_allowed_packet = 32M # table_cache=20M # open-files-limit=20000 table_cache = 3072 open_files_limit = 9216 tmp_table_size=1000M sort_buffer_size = 100M read_buffer_size = 100M read_rnd_buffer_size = 100M myisam_sort_buffer_size = 100M max…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…=’mrr_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also note that the following changes… w/ join_buffer_size=6M & mrr_buffer_size=6M MariaDB 5.5 Hash Join Disabled w/ join_buffer_size=4M & mrr_buffer_size=4M Created_tmp_disk_tables 0 0…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…-locking key_buffer = 384M max_allowed_packet = 256M table_cache = 512M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache_size…_heap_table_size=1073741824 local_infile=0 ft_min_word_len=3 tmpdir = /opt/ key_buffer_size=256M connect_timeout=180 wait_timeout=360 tmp_table_size=851443712…
