… used: ## 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_adaptive_flushing_method = estimate innodb…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
…InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer…/etc/my.cnf | grep log_file # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M [root@tstlx1 …
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
….cnf Also make sure that innodb_buffer_pool_size is for performance reasons set to x4 of log file size. On my system I… recommend you to be on the safe / perfect side. innodb_log_file_size is always a fixed parameter and it is quite… 32M x 4 = 128M innodb_buffer_pool_size). According to my previous posts in this blog a dynamic innodb log file RECREATE did NOT…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… – 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
… is shown by the high numbers of Innodb_buffer_pool_read_ahead when the buffers are sized appropriately, which shows that the IO access… 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
… purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that… purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed up, so… 136163 136435 Innodb_buffer_pool_read_ahead 0 20920 23669 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description. Innodb Buffer Pool… you have decided with database size you need to check if there are any restrictions on Innodb Buffer Size you can use. Typically you…
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… – which are offsets for MyISAM and primary key values for Innodb or storing full data which is being retrieved (good for…
