… = estimate innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file… I was mostly concerned with write performance, so I went ahead to choose a table structure with no secondary indexes. Other…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 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 120548 102948 76882 102672 76832 Innodb…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Handler_read_rnd_next 23078 22780 22757 22867 23049 23221 Innodb_buffer_pool_read_ahead 1152 23231 130919 23228 130731 131497 Innodb_buffer_pool_read_requests 12947073 10228611 7816154 10251697 9319281 9393396 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 …
Post: Can Innodb Read-Ahead reduce read performance ?
June 26, 2007 By 2 Comments
… non read ahead requests happening as well, at least 10 times more than number of read-ahead requests. May be it is poor read-ahead performance plus multiple threads doing scattered O_DIRECT (so no OS read-ahead) single page reads… in this project from MySQL AB side) – to improve how Innodb read-ahead works by having more overlap between requests so thread would…
Post: read_ahead (disabled) as steroid
May 12, 2009 By 4 Comments
… of “read_ahead“. And if in the same benchmark you disable read-ahead (innodb_read_ahead=none), you can see improvement about 15% Actually control of read_ahead is very simple patch and can easily be included in 5.4 or InnoDB-plugin. For curiosity… – in the same time gaps can be smoother with disabled read_ahead, however disabling it does not show such improvement like on…
Post: Effect from innodb log block size 4096 bytes
January 3, 2011 By 18 Comments
… innodb_adaptive_checkpoint=keep_average innodb_thread_concurrency=0 innodb_flush_method = O_DIRECT innodb_read_ahead = none innodb_flush_neighbor_pages = 0 innodb_write_io_threads=16 innodb_read_io_threads=16 innodb…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… gets it wrong. Q10: When Innodb decides to schedule sequential read-ahead, random read ahead ? HT: InnoDB schedules a sequential read-ahead when it notices an ascending access… used for read ahead and results were significant. They however were not merged in Innodb mainline yet. Q11: If read-ahead is happening and Innodb needs…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… one read-ahead can happen at the same time. How many read-aheads can be waiting in Queue when or Innodb will schedule more read-aheads only when given read-ahead is completed? HT…
Post: The performance effects of new patches
September 9, 2008 By 14 Comments
…. innodb_read_ahead (default 3) This controls [enable/disable] of read-ahead. 3: normal 2: enable linear read-ahead only 1: enable random read-ahead only 0: disable both innodb… the performance. * This test uses “innodb_read_ahead = 0″ (The both of read-ahead are disabled) “innodb_ibuf_contract_const = 50000″ “innodb_ibuf_contract_burst = 50000″ (For…
