…how effective is MRR when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set…Innodb_buffer_pool_read_ahead which shows that the access pattern was sequential and hence InnoDB decided to do read_ahead, while in MySQL…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…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…how effective are the join optimizations when the workload is IO bound. For the purpose of benchmarking IO bound workload, the InnoDB buffer pool size…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL…Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages 23419 I…
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… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description. Innodb Buffer Pool…MySQL With 10GB Innodb buffer pool for example and see how…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… be tuned in MySQL Server straight after installation, assuming it was installed with default settings. I‘m surprised how many people fail …_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do…
Post: InnoDB memory allocation, ulimit, and OpenSUSE
August 23, 2010 By 7 Comments
… innodb_buffer_pool_size was set to anything more than 62 GB. I …how much was being allocated and whether it was successful. I discovered something interesting. I…innodb_buffer_pool_size estimations when the system has a lot of RAM and you want to have the largest possible innodb_buffer_pool_size. Do…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…how this affects sequential read performance. I was very interested to see how…I used default MySQL settings for MyISAM (table_cache=64) and set innodb_buffer_pool_size=8G innodb_flush_logs_at_trx_commit=2 innodb_log_file_size=256M innodb…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
…wins. If MySQL is actually …I see are not configuring the InnoDB buffer pool size or log file size, and not using InnoDB…I/O. In addition to unlikely circumstances, I sometimes see unlikely settings, such as making the sort buffer size…than configuration To do a proper…server, including how the data, …
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…set is, say, 100 GB, then increasing the buffer pool size does not help. PZ: I should note for most application working set…a mechanical disk. I do not know how it is for …I just wrote the post on this this matter the other day – choosing innodb_buffer_pool_size Q36: There have been several MySQL…
