InnoDB uses background threads to handle readahead (prefetch) requests. Requests are generated when it detects sequential or random access to most blocks in an extent. This is described in the 5.0 MySQL manual with new behavior in the plugin. The Facebook MySQL patch has new my.cnf variables to disable readahead: innodb_readahead_random and innodb_readahead_sequential. I don't know whether readahead is useful for my workload. This is hard to determine because SHOW STATUS counters for InnoDB readahead display the number of times the readahead generation functions are called rather than the number of readahead requests issued (fixed in the Facebook patch, fix pending in official InnoDB). There are also no counters for the number of pages fetched by readahead that are eventually used (not yet fixed in the Facebook patch). I ran a mirror of the production workload on two test servers and disabled readahead on one of them. Based on the results below, I think we should disable readahead.

Results

The server with readahead enabled did 6% more InnoDB page read requests 3.8% (264544) fewer synchronous page read requests. Based on these results, readahead appears to be effective as it reduces the number of synchronous page reads. Note that the server with readahead read 7444152 pages and (255016 + 428633) were from readahead. I estimate that 39% of the readahead requests were useful. The server with readahead did (255016 + 428633) readahead requests and 264544 fewer synchronous page read requests and (264544) / (255016 + 428633) ~= 0.39. From the data on the number of IO requests above, I think that readahead is somewhat effective. But my opinion changes when I consider the IO latency statistics. While it did fewer synchronous read requests the server with readahead enabled used 18.6% more time for them (106827 versus 90021 seconds) and the average latency to service a synchronous read was 23% higher (15.87 milliseconds versus 12.87). It also spent much more time for asynchronous read requests but that is expected given that prefetch requests use async reads. Alas, I did not get iostat results. The Facebook patch for MySQL displays much more data in SHOW INNODB STATUS. Some of it is included below from the test servers. Data from SHOW INNODB STATUS for the server with readahead enabled:

Pages read 7444152, created 94756, written 2758813
Read ahead: 255016 random, 428633 sequential
Sync reads: 6730101 requests, 0 old, 16384.30 bytes/r, svc: 106827.43 secs, 15.87 msecs/r, 807.76 max msecs, wait: 106827.57 secs 15.87 msecs/r, 807.76 max msecs
Async reads: 374094 requests, 0 old, 31269.07 bytes/r, svc: 3037.55 secs, 8.12 msecs/r, 473.97 max msecs, wait: 24448.33 secs 65.35 msecs/r, 473.97 max msecs

Data from SHOW INNODB STATUS for the server with readahead disabled:

Pages read 7025361, created 89550, written 2778099
Read ahead: 0 random, 0 sequential
Sync reads: 6994645 requests, 0 old, 16384.29 bytes/r, svc: 90021.08 secs, 12.87 msecs/r, 1289.22 max msecs, wait: 90021.21 secs 12.87 msecs/r, 1289.22 max msecs
Async reads: 20932 requests, 0 old, 24078.19 bytes/r, svc: 267.37 secs, 12.77 msecs/r, 326.25 max msecs, wait: 347.28 secs 16.59 msecs/r, 326.25 max msecs