… 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… concerned with write performance, so I went ahead to choose a table structure with no secondary indexes. Other important things to consider…
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…_pool_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
…_pool_read_ahead 1152 23231 130919 23228 130731 131497 Innodb_buffer_pool_read_requests 12947073 10228611 7816154 10251697 9319281 9393396 Innodb_buffer_pool_reads…_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: 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
… are 4 read-ahead requests, 64 pages each. What seems to be happening (I’m using “seems” as there are no detailed enough stats available) is once thread has issued read-ahead request it has to wait for… 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 which…
Post: read_ahead (disabled) as steroid
May 12, 2009 By 4 Comments
…) on ext3, 32GB of RAM. As you see there almost no difference and you may say what’s the reason in… “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…
Comment: InnoDB: look after fragmentation
November 7, 2009 By 12 Comments
… and hence at least 16K worth of data would be read each time. The number of rows of course depends on… rows per page. Now note in the worse case scenario no read-aheads will trigger and all IO will be done by single… be looking at about 3MB/sec read speed which is 30-50 times slower than sequential read speed of the same drive.
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… overwritten in buffer pool by large full table scan HT: No PZ: Another possible area of optimization. I frequently see batch…. 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…
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…, removing one bottleneck moves the contention to another place. Thus, no general rules can be established. I have assigned Inaam as…
Post: The performance effects of new patches
September 9, 2008 By 14 Comments
…, we will publish them eventually). There are no delays between transactions (no thinking time, no keying time), it uses MySQL C API and… 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…
