… 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…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 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 116 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 4 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 18 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 38 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…
Comment: What to tune in MySQL Server after installation
September 29, 2006 By 116 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: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs… balance between reasonable recovery time and good performance innodb_log_buffer_size=4M 4M is good for most cases unless you’re… keys (try to use it in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… about last failed foreign key constraint: ———————— LATEST FOREIGN KEY ERROR ———————— 060717 4:29…log_buffer_size is optimal – if you see more than 30% of log buffer size … active database size is smaller than allocated buffer pool size so you can tune it down…
