Database Reference
In-Depth Information
—
SHOW
output cannot be saved using only SQL. Should you require further pro‐
cessing of an
INFORMATION_SCHEMA
query result, you can use
INSERT
INTO
…
SELECT
to save the results in a table for further analysis (see
Recipe 4.2
). To obtain
an individual value, assign a scalar subquery result to a variable:
SET
@
queries
=
(
SELECT
VARIABLE_VALUE
FROM
INFORMATION_SCHEMA
.
GLOBAL_STATUS
WHERE
VARIABLE_NAME
=
'Queries'
);
• Some storage engines make information available about themselves. InnoDB, for
example, has its own system and status variables. It also provides its own
INFORMA
TION_SCHEMA
tables and a set of InnoDB Monitors. The
INFORMATION_SCHEMA
tables
provide more structured information and thus are more amenable to analysis using
SQL, if they contain the information you want. To see which InnoDB-related tables
are available, use this statement:
SHOW
TABLES
FROM
INFORMATION_SCHEMA
LIKE
'innodb%'
;
The Monitors produce unstructured output. You can eyeball it, but for program‐
matic use, you must parse or extract the information somehow. In some cases, a
simple
grep
command might suffice:
%
mysql -E -e "SHOW ENGINE INNODB STATUS" | grep "Free buffers"
Free buffers 4733
• The Performance Schema is designed for monitoring and provides a wealth of
measurements, from high-level information such as which clients are connected
down to fine-grained information such as which locks a statement holds or which
files it has open. To use the Performance Schema, it must be enabled. This is the
default as of MySQL 5.6.6; to enable it explicitly at server startup, use this config‐
uration setting:
[mysqld]
performance_schema=1
• Server logs provide several types of information. Here are some suggestions for
using them:
— The error log alerts you to serious problems the server encounters. It's most
suited to visual inspection because messages can originate from anywhere in the
server and there is no fixed format to aid programmatic analysis. It's often only
the last part of the file that's of interest, anyway, because you typically check this
file to find the reason for the most recent problems.
— The general query log indicates what queries clients are running. It can aid as‐
sessing the nature of the server's workload.
— The slow log contains queries that may be inefficient. It can help you find can‐
didates for optimization.