… ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size…_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling symbolic-links is…
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 = 0 query_cache…size = 10G #myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…=7200 –max-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ –oltp-read-only=off …cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 key…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single… innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_… 2000 max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 …
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…as, depending on the size of the partitions, the whole partition can be cached, as opposed to…not the whole table. 3. Insert on Duplicate Key (I dont do replace… ever)… if across …Mysql can fire off one thread per partition (at least I have seen this happen in a MyIsam environment on similar sized…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, …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_write_requestsblogpercona.commanual Key…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… tuning MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key… key cache hit ratio should be very high, ideally above 99.9%” or “if your key cache hit ratio is low, your key_buffer_size is… scientific and authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… but Key Cache Mutex will still hunt you. If you aware of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL… of them), allocating key_cache proportional to their size and load, but no more than the index size (assuming table sizes are relatively static) To…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are MySQL Caches – Innodb Buffer Pool and Key Buffer are… caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do “set global key_buffer_size=0; set global key_buffer_size=DEFAULT…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache size of 0 which makes it disabled. I wish query_cache_size…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
