…InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet… intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block, which can use like this: ———————- BUFFER POOL AND MEMORY ———————- Total…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 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… are values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool_reads are…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each… 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…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
…. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really big…? What about InnoDB tuning? You might be wondering, what about InnoDB tuning? What is the best way to choose an innodb_buffer_pool_size setting…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
… at global 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. If you’re using MyISAM seriously you can also add the size of…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…_cache_size=1500M –max-connections=1500 –table-cache=512 –net_read_timeout=30 –net_write_timeout=30 –backlog=128 MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8 –max…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
Looking for documentation for read_rnd_buffer_size you would find descriptions such as “The read_rnd_buffer_size is used after a sort… very similar to read_buffer_size which is currently only used by MyISAM tables I thought read_rnd_buffer_size is also MyISAM only… only row pointers together with key value – which are offsets for MyISAM and primary key values for Innodb or storing full data which…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… than with single query. The problem in this case is key buffer contention which unlike popular belief not fully fixed by changes… second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=0 Threads… we proposed in this case was converting table t1 into InnoDB, and results: Threads Queries/sec 1 296 2 341 4…
