I solved the problem by adding the apt repository mysql-tools
:
deb http://repo.mysql.com/apt/debian/ stretch mysql-tools
It works for Debian 9 (stretch). Not tested for Debian 8 (jessie).
A value of thousands per second is pretty normal. (Manually compute Handler_read_key / Uptime.)
Do the following to get a better feel for what it, and its siblings, are about:
FLUSH STATUS; -- resets the Handler counters (and a few other things)
SELECT ...
SHOW SESSION STATUS LIKE 'Handler_%';
You will see read_key
for "point queries" and for "range queries", possibly one each. You will also see a larger value for a "range query" in read_next
.
Handler_write%
values indicate that a temp table needed to be built and reread -- such as for GROUP BY
and/or ORDER BY
.
I find the Handler
values to be handy in comparing two queries -- bigger numbers is a good indication of a less-well optimized query.
If you like, present us with a small number of SELECTs
, together with SHOW CREATE TABLE
and EXPLAIN SELECT
and the Handler%
values. We can go into more details 'by example'.
Handler_read_key
I think read_key
is bumped by one in these cases:
- Reaching into a table, either for a specific row, or to start a range scan.
- At least once per table in a query with multiple tables due to
JOIN
or subqueries.
- Once per row in a "nested loop join". This is the usual case for a simple
JOIN
two tables. It scans the first table (one Handler_read_key
, many Handler_read_next
) and reaches into the next table repeatedly (many Handler_read_key
)
Think of it this way: Most reads from a table work in 1 or 2 steps:
- Use a "key" to find the row (or the first row of a range) -->
Handler_read_key
bumped by 1.
- If multiple rows needed, then get the "next" row -->
Handler_read_next
bumped by 1 per extra row. If using something like ORDER BY .. DESC
, then bump Handler_next_prev
.
Best Answer
The max value is
innodb_buffer_pool_size / innodb_buffer_pool_instances
or some integral fraction of that.But why bother changing the setting? It should not make much difference.
Reference, and more discussion: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size