…=monotonically increasing ID $…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…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes …other correspond to global memory allocation and will result in increased memory allocation until server is restarted. I would suspect …
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… the manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size because these have an impact on…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
…key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer…sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size. innodb_log_buffer_size…
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…increasing sort_buffer_size from 1MB to 4MB and 1000 max_connections increases…
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… allowing MySQL to handle larger queries. Makes sense as amount of memory available is much larger these days. join_buffer_size have been increased…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… FT index, I made sure to increase innodb_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… declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out of memory …
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… by the buffering step introduced by MRR, and hence, there is a slight increase in query time for MySQL 5.6, increase of 0…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
…insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool_size and large innodb_log_file_size to perform load effectively Load Options There two main ways to load data in MySQL…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…mysql/slow-queries.log max_connections=650 open_files_limit=6144 max_connect_errors=10000 interactive_timeout=3600 wait_timeout=3600 key_buffer_size=32M # Only for MyISAM which isnt used. sort_buffer_size=2M query_cache_size=10M # Might be increased thread_cache_size=40 …
