…: ## 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_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_max…
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 set… recommend you to be on the safe / perfect side. innodb_log_file_size is always a fixed parameter and it is quite… of 32M x 4 = 128M innodb_buffer_pool_size). According to my previous posts in this blog a dynamic innodb log file RECREATE did NOT…
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… 120502…]# cat /etc/my.cnf | grep log_file # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M [root@tstlx1 mysql]#
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool… purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed up, so that it… numbers 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 done…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool… purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed up, so that it… Innodb_buffer_pool_read_ahead 0 20920 23669 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads 120548 102948 76882 102672 76832 Innodb…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…:10M: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_wait…
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 …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of … (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old …
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 is…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
… / index_size, 2) fit_pct FROM (SELECT index_id, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb_sys_indexes ON id = index_id JOIN innodb_sys_tables ON table_id = innodb… tune buffer pool invalidation strategy, for example play with innodb_old_blocks_pct and innodb_old_blocks_time actually observing data stored in buffer pool rather…
