…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_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql… but I am ready to tune …
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… care about the key cache hit ratio. Bad advice #2: you should set your key_buffer_size according to this ratio. Tuning by ratio is… to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size setting, but there are many…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server… are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql…size have been increased from 20 to 300 which I guess is one of the component of purging fine tuning in MySQL…on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
…_buffer_pool_size to 50% of my RAM, and then when I tested MyISAM FT index, I reallocated that memory to into key_buffer_size… FT index before declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out of memory fatal error, and… with MySQL and it wouldn’t be as flexible and customizable as Solr. With Solr, I was able to fine tune search…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5.6.10 has many additional tuning…buffer pool in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer…10), KEY(C_Name), KEY(C_City), KEY(C_Region), KEY(C_Phone), KEY(C_…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… is the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR can…_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL 5.6) mrr_buffer_size… optimizer is choosing the query execution plan, is not sufficiently tuned and it is recommended to turn this off. The query…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; –… why these two variables are most often tuned in concert). To continue the example, if… 1 innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
…size you need to check if there are any restrictions on Innodb Buffer Size…MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer…
