…innodb options innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method = O_DIRECT innodb_log_buffer_size…
Comment: MySQL and Percona Server in LinkBench benchmark
May 11, 2013 By 18 Comments
… for this workload-hardware innodb_io_capacity = 20000 innodb_io_capacity_max = 100000 innodb_read_ahead_threshold = 0 innodb_change_buffer_max_size = 1 innodb_lru_scan_depth…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… 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… by echo 0 > /proc/sys/vm/swappiness is another helpful change though it does not always save you from swapping. Some…
Post: InnoDB page size
June 4, 2006 By 4 Comments
As maybe you know InnoDB uses hard page size 16Kb for datafiles and for buffer pool. However this size can be changed if you need…). Possible values for UNIV_PAGE_SIZE is 8K, 16K, 32K, 64K. You also have to change UNIV_PAGE_SIZE_SHIFT (according comment it must be 2-logarithm of UNIV_PAGE_SIZE). For pagesize 8K – UNIV_PAGE_SIZE_SHIFT…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
… Innodb Tablespace. Insert Buffer size is also restricted to half of the buffer pool size (can be changed via innodb_ibuf_max_size option) which can be significant size… looking at SHOW INNODB STATUS this is how you can see Insert Buffer Size: Ibuf: size 108931, free list len 64619, seg size 173551, In…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… log buffer as well as when last checkpoint was performed. Innodb uses fuzzy checkpointing so this line hold log sequence, all changes up to which has been flushed from buffer pool. Changes having… you can check if your innodb_log_buffer_size is optimal – if you see more than 30% of log buffer size being unflushed you may…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… lot better than 5MB. Good to see innodb log re-sizing is made automatic now. innodb_old_blocks_time = 1000 Great. This is… to deal with simple and complicated queries efficiently automatically. join_buffer_size = 256k The default is now double of what it was… just looking to reduce number of different buffer sizes used. sync_master_info = 10000 Quite useless change in my opinion. Syncing master info…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… swap out MySQL out of memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size 70-80% of memory is a… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs but… hardware without battery backed up cache with no OS changes and have no changes done to application which was written keeping MyISAM…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… posts about 64 page read requests per a readahead. The InnoDB buffer is 256 page read requests for the aio read thread… them are Innodb specific and it well can affect your workload. So benchmark to be sure. In Innodb besides scalability changes in later… post on this this matter the other day – choosing innodb_buffer_pool_size Q36: There have been several MySQL bugs opened about multi…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 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… InnoDB…file size workload and even when crash happen (how many unflushed changes we had) Repeating test with innodb_…
