The impact from InnoDB readahead
The impact from InnoDB readahead
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_sequentia
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.
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:
Data from SHOW INNODB STATUS for the server with readahead disabled:
The Facebook MySQL patch has new my.cnf variables to disable readahead: innodb_readahead_random and innodb_readahead_sequentia
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