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.
Search WWH ::




Custom Search