There are cases where prefetch improves performance. A query that does a full table scan was run with prefetch enabled and disabled. It was 35% slower with prefetch disabled.
Percona and Matt have written about potential performance problems from this feature. There isn't much data to indicate when this feature should be enabled. I have published data for a few IO-bound benchmarks. On these tests, the prefetching done by InnoDB reduces performance. The tests run were:
- insert benchmark
- tpcc-mysql with data cached by InnoDB
- tpcc-mysql with too much data to cach
Update -- my peers just reminded me to add support for per-session (dynamic) usage of the new my.cnf parameters (innodb_readahead_random, innodb_readahead_sequential).
Posts
The patch can separately enable/disable read-ahead on random-access as well as sequential reads. That should cover the two main distinctive use cases.
ReplyDeleteIs that patch in OurDelta?
ReplyDeleteThe performance impact would be minimum in OLTP or heavy write enviornments. Look at the actualy call to try and read the page off disk:
ReplyDelete-----------------------------------------------
if (!ibuf_bitmap_page(i)) {
count += buf_read_page_low(
&err, FALSE,
ibuf_mode | OS_AIO_SIMULATED_WAKE_LATER,
space, tablespace_version, i);
-----------------------------------------------
So if I am reading through the function correctly skip reading any page that has data in the insert buffer. I just ran into this as a problem with Waffle today in fact, the insert buffer is applied to pages read from disk during a call to buf_page_io_complete which is only called during sync calls to buf_read_page_low. I wager that in high write enviornments like yours the counter showing the # of readaheads is misleading as you may call the readahead function, but may not load more then 1 or 2 pages because of the insert buffer... oops :)
Matt,
ReplyDeleteDefine 'minimum impact'. The results I published show that tpcc-mysql is 12% to 20% faster with prefetch disabled.
Also, regardless of the readahead counts, the total number of pages read is much higher. That data too is on the results that I published. And the readahead counts are accurate in my case.
Sorry, should have said the benefit or effectiveness of having it enabled is reduced do bypassing the insert buffer ( maybe the reason for the drop in performance? ) I totally agree that all my tests ( in oltp land ) show better performance with it disabled.
ReplyDeleteI have other changes in the v4 Google patch and Percona has similar changes that both:
ReplyDelete1) let you enforce a limit on the max size of the insert buffer to much less than the 50% limit used today
2) merge insert buffer records much faster to enforce that limit
With that, you still get a lot of benefit from the insert buffer without using too much of the buffer pool.
In this particular case, the readahead counters were accurate -- that is they explain the difference in pages read.
Keep on publishing data. We need more test results to understand the current behavior.
I have observed quite a bit of innodb prefetch activity during a recent experiment, but have not quantified its impact. I found it using inniostat (http://blogs.sun.com/realneel/entry/inniostat_innodb_io_statistics) It is good to know that there are tunables for turning it off. As you say, the values of Innodb_buffer_pool_read_ahead_seq etc are quite confusing!
ReplyDelete"As for engines, Mark Callaghan contributed a fine blog on the performance impact of prefetching in InnoDB. Mark writes, “There isn’t much data to indicate when[...]"
ReplyDeleteLog Buffer #149