Database Reference
In-Depth Information
does not apply here because that works only for system variables. Nor can we capture
the result from a
SHOW
statement using only SQL. However, we can obtain any individual
status variable value as a scalar subquery result. The following SQL script uses that
approach to fetch the relevant status variables into user-defined variables and compute
the hit rates:
#
hitrate
.
sql
:
Show
InnoDB
and
MyISAM
key
cache
hit
rate
statistics
USE
INFORMATION_SCHEMA
;
SET
@
reads
=
(
SELECT
VARIABLE_VALUE
FROM
GLOBAL_STATUS
WHERE
VARIABLE_NAME
=
'INNODB_BUFFER_POOL_READS'
);
SET
@
requests
=
(
SELECT
VARIABLE_VALUE
FROM
GLOBAL_STATUS
WHERE
VARIABLE_NAME
=
'INNODB_BUFFER_POOL_READ_REQUESTS'
);
SET
@
hit_rate
=
TRUNCATE
(
IFNULL
(
1
-
(
@
reads
/@
requests
),
0
),
4
);
SELECT
'InnoDB key cache hit rate'
AS
Message
,
@
reads
,
@
requests
,
@
hit_rate
;
SET
@
reads
=
(
SELECT
VARIABLE_VALUE
FROM
GLOBAL_STATUS
WHERE
VARIABLE_NAME
=
'KEY_READS'
);
SET
@
requests
=
(
SELECT
VARIABLE_VALUE
FROM
GLOBAL_STATUS
WHERE
VARIABLE_NAME
=
'KEY_READ_REQUESTS'
);
SET
@
hit_rate
=
TRUNCATE
(
IFNULL
(
1
-
(
@
reads
/@
requests
),
0
),
4
);
SELECT
'MyISAM key cache hit rate'
AS
Message
,
@
reads
,
@
requests
,
@
hit_rate
;
Invoke the script to determine the key cache hit rates on demand:
%
mysql -t < hitrate.sql
+---------------------------+--------+-----------+-----------+
| Message | @reads | @requests | @hit_rate |
+---------------------------+--------+-----------+-----------+
| InnoDB key cache hit rate | 6280 | 70138276 | 0.9999 |
+---------------------------+--------+-----------+-----------+
+---------------------------+--------+-----------+-----------+
| Message | @reads | @requests | @hit_rate |
+---------------------------+--------+-----------+-----------+
| MyISAM key cache hit rate | 23269 | 8902674 | 0.9973 |
+---------------------------+--------+-----------+-----------+
It looks like both caches are large enough to operate well.
The
hitrate.sql
script does the job, but four queries to get four status variables seems
inefficient. You can see from the example how cumbersome it is to fetch their values
individually. Manipulating status variables using a programming API permits a more
straightforward process:
• Retrieve the entire set of status variables with a single query and store the result in
a data structure that associates variable names with their values. This structure
suffices no matter how many variables you monitor.
• Furthermore, it's reasonable to suppose that if we have one task that requires status
variable values, we'll have other such tasks in the future. So it makes sense to write
a routine to extract this information. It's necessary to write the routine only once
