Description:
The buf_print_io() function in 5.1 and buf_print_io_instance() in 5.5
and 5.6 (http://bazaar.launchpad.net/~mysql/mysql-server/5.5/view/head:/storage/innobase/buf/buf0bu...)
are used prominently by SHOW INNODB STATUS to show data about the buffer
cache. Some of the data is *per second*. For example:
Pages made young 764892283, not young 12854192
8.99 youngs/s, 48.95 non-youngs/s
What is not clear in documentation, or in the STATUS itself, is that the
the per-second calculations are based on the interval between the
last time the status was printed and now.
In buf_stats_get_pool_info():
pool_info->page_not_made_young_rate =
(buf_pool->stat.n_pages_not_made_young
- buf_pool->old_stat.n_pages_not_made_young) / time_elapsed;
And time_elapsed is:
time_elapsed = 0.001 + difftime(current_time,
buf_pool->last_printout_time);
This is not necessarily the incorrect implementation (although one could
imagine setting time_elapsed to be standard, say 1 minute), but it has
important side-effects. Consider the case where a statistics gathering tool
regularly accesses this stat every 10 minutes. It would correctly show
changes over time — in the evening, say, the rate would decrease.
However, imagine that an administrator or another stats gathering tool
runs SHOW INNODB STATUS 20 seconds before the every-10-minutes stats gathering tool does.
Now, since the interval is based on last printout, the per-second
stat recorded this time around is not based on a 10 minute interval, but on a
20 second interval. This may result in what appears to be a spike, or a drop,
but is in fact an artifact of a different, unexpected polling interval.
The documentation, for example, "Monitoring the Buffer Pool" in https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html, does not indicate
that the per-second stats depend on the last printout interval. For example:
"youngs/s non-youngs/s: The number of accesses to old pages that have resulted in making them young or not. This metric differs from that of the previous item in two ways. First, it relates only to old pages. Second, it is based on number of accesses to pages and not the number of pages. (There can be multiple accesses to a given page, all of which are counted.)"
https://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html does not mention it either.
SHOW INNODB STATUS itself *does* at least give this data on its very first line:
"Per second averages calculated from the last 18 seconds"
However, there is no indication of *what* determines that interval — that is only clear from reading
source.
How to repeat:
Read documentation, compare with source
Suggested fix:
Improve documentation to describe how the interval for calculation is actually determined.
