… and cons to using secondary indexes. While secondary indexes cause read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and hence having secondary indexes is an obvious choice… 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
…_buffer_pool_read_ahead 0 20920 23669 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads 120548…_read_ahead which shows that the access pattern was sequential and hence InnoDB decided to do read_ahead, while in MySQL 5.5 no read_ahead was done because the access pattern was not sequential. Another thing to note is that more read_ahead is done…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Handler_read_next 2651500 2628103 2586036 2628103 2592816 2615612 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 …
Comment: Testing Samsung SSD SATA 256GB 830 - not all SSD created equal
April 25, 2012 By 10 Comments
That depends, did you run the read-only case closely following the writes? GC normally runs at … if you’re constantly running benchmarks, it could just go ahead and do it anyway. That’s my theory, at any…
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…
Post: read_ahead (disabled) as steroid
May 12, 2009 By 4 Comments
… “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… – in the same time gaps can be smoother with disabled read_ahead, however disabling it does not show such improvement like on…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
…. 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 pattern… – how many random and sequential read aheads can be happening at the same time HT: All read-aheads are performed in the Unix version…
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: A query thread normally posts about 64 page read requests per a…
Post: The performance effects of new patches
September 9, 2008 By 14 Comments
… added. 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… may help the performance. * This test uses “innodb_read_ahead = 0″ (The both of read-ahead are disabled) “innodb_ibuf_contract_const = 50000″ “innodb…
