Description:
At this location http://dev.mysql.com/doc/refman/5.7/en/innodb-performance.html#innodb-performance-buffer-p... is this text:
"When the innodb_max_dirty_pages_pct threshold is crossed, InnoDB can begin aggressively flushing pages to disk. The innodb_max_dirty_pages_pct_lwm option specifies a higher value at which InnoDB begins gently flushing pages, ideally preventing the percentage of dirty pages from reaching innodb_max_dirty_pages_pct. A value of innodb_max_dirty_pages_pct_lwm=0 disables this “preflushing” behavior."
and
"All of these options are most applicable for servers running heavy workloads for long periods of time, when there is rarely enough idle time to catch up with changes waiting to be written to disk. The innodb_flushing_avg_loops lets you distinguish between a server that is running at full capacity 24x7 and one that experiences periodic spikes in workload. For a server with a consistently high workload, keep this value high so that the adaptive flushing algorithm responds immediately to changes in the I/O rate. For a server that experiences peaks and troughs in its workload, keep this value low so that InnoDB does not overreact to sudden spikes in DML activity."
Both descriptions contain significant errors.
How to repeat:
Not applicable, though some of the suggested fix material might usefully be used in the 5.6 manual version, since the descriptions apply to that version as well as 5.7.
Suggested fix:
"When the innodb_max_dirty_pages_pct threshold is crossed, InnoDB can begin aggressively flushing pages to disk,. The innodb_max_dirty_pages_pct_lwm option specifies a _lower_ value at which InnoDB begins gently flushing pages, ideally preventing the percentage of dirty pages from reaching innodb_max_dirty_pages_pct. A value of innodb_max_dirty_pages_pct_lwm=0 disables this “preflushing” behavior."
Obvious enough: a higher value can't prevent reaching a lower value, has to be lower. Documentation should also recommend setting innodb_max_dirty_pages_pct_lwm 1% or so below innodb_max_dirty_pages_pct by default to get more smooth dirty page flushing, going to greater differences if small stalls or undesired write bursts interfere with the foreground activities.
_ only used for some changes to the next one, too much complete rewriting done:
"_Most_ of these options are most applicable for servers running _write-_heavy workloads for long periods of time, when there is rarely enough _reduced load_ time to catch up with changes waiting to be written to disk. The innodb_flushing_avg_loops lets you manage how rapidly the flushing activity responds to foreground load changes. A high value and slow change rate is most desirable to reduce positive feedback between foreground and background work but it is necessary to ensure that the amount of InnoDB log space used never reaches 75% and that innodb_max_dirty_page_pct can keep the number of dirty pages to an appropriate level for the workload. Systems with consistent workloads, large innodb_log_file_size and smaller spikes that never cause 75% to be reached should use high values of innodb_flushing_avg_loops to keep flushing as smooth as possible. Systems with extreme load spikes or log files that don't allow much space may need much smaller values to allow flushing to track the load well enough to avoid reaching 75%."
Most because systems with low write activity can benefit from reducing some defaults. It's write-dominated loads, not all high loads. innodb_flushing_avg_loops was described in pretty much the exact opposite of how to use it, and the relationship to log file size that allows more flexibility wasn't mentioned.
The basics of initial tuning are fairly simple to describe:
1. Set innodb_io_capacity to the lowest value that allows insert buffer merging, flushing and purging to keep up. The default is too high for low write load systems.
2. Set innodb_io_capacity_max high enough to deal with load surges. The default is too high for low write load systems.
3. Set innodb_adaptive_flushing off and set innodb_adaptive_flushing_lwm to 70 so adaptive flushing will start more smooth flushing before reaching 75%.
4. Set innodb_flushing_avg_loops high.
5. Increase innodb_io_capacity so the background threads can keep up. Increase innodb_io_capcity_max similarly to innodb_io_capacity, about two to one is a good ratio, use a larger ratio for very spiky loads if the log file size doesn't allow enough time smoothing of flushing.
6. If log file use gets to 75% first make the log files bigger. If it proves impossible to keep up or if io is too spiky, reduce innodb_adaptive_flushing_lwm to give it more margin. Log space use that varies between 50% and 65% during busy times can be a good target range for serious production servers that doesn't allocate excessive RAM for caching the InnoDB log files in OS buffers but still allows lots of write combining. Consider setting innodb_io_capacity lower than required to keep up at peak times, using the log space use flexibility to handle the peak surge, or consider using higher flushing before peak to increase margin.
7. Set innodb_max_dirty_pages_pct to a high value for write dominated loads, a low one for read-dominated, so the buffer pool gets an appropriate mixture of pages used for caching modified or unmodified pages. Adjust to minimise total disk io as an initial goal, perhaps with a bias to higher value to reduce write load for SSDs to increase drive life.
8. If the trouble is foreground load spikes, like bulk insert jobs, reduce innodb_flushing_avg_loops so flushing can respond more quickly to the surge in flushing demand. A system with stable or small batches will be fine with a much bigger setting than one that has the same amount of work done in concentrated batches.
The expertise comes in finding the best blend for a particular workload and knowing when a server is getting into trouble, why and how best to fix it. In general the idea is to minimise total io by background threads through write combining while decoupling background work from foreground load spikes (so background doesn't take io when the foreground most needs it) without ever hitting 75% log space use or getting io surges that disrupt the foreground response times.
Perhaps also note Bug #69174, which explains that innodb_max_dirty_pages_pct is effectively completely broken in 5.6 for its normal use, working only when the server is idle instead of when it's highly loaded, when the option is supposed to be working at its most aggressive level. The bug gives the workarounds.
The descriptions here apply to 5.6 and above, though if the 5.6-specific options are removed it's also correct for older versions.
