… 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 myisam_max_sort_file_size = 10G #myisam… good settings out-of-box, but I am ready to tune something if there are suggestions.
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… query workloads. Both have a key_buffer_size of 4GB. Now can you tell which server is badly tuned? — No, you can’t tell…. How to choose a key_buffer_size Let’s recap. So far I’ve shown you the fallacy of tuning by ratio, and told…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
…_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size… 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: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…_id INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id) -> ON DELETE CASCADE -> … is 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: 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…per thread or global memory allocations which are not tuned as buffers. Watch for these especially now with many …
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… database size you need to check if there are any restrictions on Innodb Buffer Size you… include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be… Buffer Pool and few other buffers, which may have other performance benefits as well. Tuning …
Post: Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
…for MySQL Performance analyses. Too many people end tuning on plans and indexes while there well could… These already can be used for Innodb Buffer Pool and Key Buffer but could be used for other large … MySQL should get smarter in terms of which buffer size should be used, even for sake of speeding…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… measure the memory usage for each test. I did allocate buffers in what I hope was a realistic way. For instance…_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… customizable as Solr. With Solr, I was able to fine tune search and I still feel there are tons of additional…
Comment: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
Thanks Peter, nice summary of these tuning settings. A question: I have a database using only MyISAM … box with 4GB of memory. I’ve currently got the key_buffer_size set to 1.5GB, and I’m finding performance on… meantime can you suggest a suitable size for the key buffer? My current thinking is to *lower* the size of it in order to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… is the importance of the size of the buffer used for sorting the secondary key tuples. If the buffer size is large enough only a… 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… optimizer is choosing the query execution plan, is not sufficiently tuned and it is recommended to turn this off. The query…
