… the lookup will be performed. So key-ordered scan is basically an extension of MRR to secondary keys. You can read… the join optimizations when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool… are the join optimizations when the workload is IO bound. For the purpose of benchmarking IO bound workload, the InnoDB buffer pool…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… like to hear extending it with basics of Hardware OS And Application optimization. I call this Innodb Performance Optimization Basics so these are general guidelines which work well for wide range of applications, though the optimal…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… query execution for Innodb). This basic information means it does not change whole that quickly at extent to affect optimizer plans. If you… report MySQL Optimizer Bug 
But now lets see in the difference of behavior of ANALYZE TABLE for MyISAM vs Innodb. I used…. Now let us populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from antest; Query…
Post: How to calculate a good InnoDB log file size
November 21, 2008 By 33 Comments
… a little. The basic point is that your log file needs to be big enough to let InnoDB optimize its I/O, but… set to ‘grep sequence’ mysql> show engine innodb status\G select sleep(60); show engine innodb status\G Log sequence number 84 3836410803 1… default, divide that in half, and now you can set innodb_log_file_size=64M Does that look surprisingly small? It…
Page: Presentations
May 3, 2006 By
…Innodb Optimization MySQL Users Conference 2005, Santa Clara, CA This presentation has a lot of details about optimizing applications for Innodb…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 5 Comments
… to really understand this relationship. Some basic facts Most of us know that writing into Innodb updates buffer pool pages in memory… on its own. So, how can we optimize here? The short of it is: make innodb flush more dirty pages. However, I…/or use the global tablespaces) *cough ext3 cough*. innodb_adaptive_flushing: An Innodb plugin/5.5 setting that tries to be smarter…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… ascending order is. InnoDB has a similar heuristic for descending index scans. The InnoDB sequential read-ahead is not optimal at all. It… 8G write ahead log and tell innodb to do a full write of the database (basically dump memory to disk) once every… tablespace when an InnoDB instance is created. PZ: Sequential “dump” of buffer pool would need more optimizations to work – Innodb would also need…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information… have been flushed you can check if your innodb_log_buffer_size is optimal – if you see more than 30% of log…
Post: How InnoDB handles REDO logging
February 3, 2011 By 18 Comments
…, InnoDB uses Physiological logging, this basically means that it combines the two techniques to provide a logging mechanism that is both optimal in… consistent page writes. Why log? Since InnoDB tries to keep the working set in memory (InnoDB Buffer Pool), therefore the changes made… hardware will affect the speed at which InnoDB can log. You can request that InnoDB force its REDO records to the transaction…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
… have to consider on case by case basics before recommending the move. Is MyISAM used… paths in the MySQL server – in particular Optimizer may have harder time because costs between… Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs…
