Databases Reference
In-Depth Information
A good way to get a feel for your overall workload is to compare values within a group
of related status variables—for example, look at all the
Select_*
variables together, or
all the
Handler_*
variables. If you're using
innotop
, this is easy to do in Command
Summary mode, but you can also do it manually with a command like
mysqladmin
extended -r -i60 | grep Handler_
. Here's what
innotop
shows for the
Select_*
variables
on one server we checked:
____________________ Command Summary _____________________
Name Value Pct Last Incr Pct
Select_scan 756582 59.89% 2 100.00%
Select_range 497675 39.40% 0 0.00%
Select_full_join 7847 0.62% 0 0.00%
Select_full_range_join 1159 0.09% 0 0.00%
Select_range_check 1 0.00% 0 0.00%
The first two columns of values are since the server was booted, and the last two are
since the last refresh (10 seconds ago, in this case). The percentages are over the total
of the values shown in the display, not over the total of all queries.
For a side-by-side view of current and previous snapshots and the differences between
them, you can also use the
pt-mext
tool from Percona Toolkit, or this clever query from
Shlomi Noach:
2
SELECT STRAIGHT_JOIN
LOWER(gs0.VARIABLE_NAME) AS variable_name,
gs0.VARIABLE_VALUE AS value_0,
gs1.VARIABLE_VALUE AS value_1,
(gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) AS diff,
(gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) / 10 AS per_sec,
(gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) * 60 / 10 AS per_min
FROM (
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
UNION ALL
SELECT '', SLEEP(10) FROM DUAL
) AS gs0
JOIN INFORMATION_SCHEMA.GLOBAL_STATUS gs1 USING (VARIABLE_NAME)
WHERE gs1.VARIABLE_VALUE <> gs0.VARIABLE_VALUE;
+-----------------------+---------+---------+------+---------+---------+
| variable_name | value_0 | value_1 | diff | per_sec | per_min |
+-----------------------+---------+---------+------+---------+---------+
| handler_read_rnd_next | 2366 | 2953 | 587 | 58.7 | 3522 |
| handler_write | 2340 | 3218 | 878 | 87.8 | 5268 |
| open_files | 22 | 20 | −2 | −0.2 | −12 |
| select_full_join | 2 | 3 | 1 | 0.1 | 6 |
| select_scan | 7 | 9 | 2 | 0.2 | 12 |
+-----------------------+---------+---------+------+---------+---------+
2. First published at
http://code.openark.org/blog/mysql/mysql-global-status-difference-using-single-query
.
