Mark wrote about how to find situations where InnoDB read-ahead is a bottleneck. What he didn’t disclose, though, is his trick to disable read-ahead without restart or recompile of MySQL. See, there’s no internal “disable read ahead knob”. But there is…
buf_read_ahead_random(...){ ... if (srv_startup_is_before_trx_rollback_phase) { /* No read-ahead to avoid thread deadlocks */ return(0); }
This variable is tested at two functions – buf_read_ahead_linear() and buf_read_ahead_random() and nowhere else. So yeah, “server startup is before transaction rollback phase” is another way of saying “don’t do read ahead, please please”.
gdb -ex "set srv_startup_is_before_trx_rollback_phase=1" \ --batch -p $(pidof mysqld)
And many servers bottlenecked on this became much much much faster (and 2000 concurrent threads running dropped to 10). Of course, this is most visible in high-latency-high-throughput I/O situations, but we’re hitting this contention spot on local disk setups too.
Don’t forget to have the fix if gdb decides to be nasty and locks up your server :)
“… at two functions – buf_read_ahead_linear() and buf_read_ahead_random() …”
lv, ačiū ;)
Domas Mituzas discovered that mysqld does not properly handle errors during execution of certain SELECT statements..
\m/
:)
coolcold: ??