Database Reference
In-Depth Information
1. Determine which of the available information sources pertain to the problem at
hand.
2. Choose an approach for using the information: Are you asking a one-time question?
If so, maybe a few interactive queries are sufficient. If you're trying to solve an issue
that may recur or for which you need continuous monitoring, a program-oriented
approach is better. Will a script written entirely in SQL do the job, or do you need
to write a program that queries the server and performs additional manipulation
of the information obtained? (This is typical for operations that cannot be done in
pure SQL, that have special output formatting requirements, and so forth.) If a task
must run periodically, maybe you need to set up a scheduled event or cron job. For
browser display, write a web script.
Sources of monitoring information
To follow the procedure just outlined, consider what information sources are available
so that you can evaluate which are applicable and how usable they are for particular
questions:
• System variables tell you how the server is configured. ( Recipe 22.1 covers how to
check these values.)
• Status variables provide information about operations the server is performing,
such as number of statements executed, number of disk accesses, memory use, or
cache efficiency. Status information can help indicate when configuration changes
are needed, such as increasing the size of a too-small buffer to improve performance,
or decreasing the size of an underused resource to reduce the server's memory
footprint.
SHOW statements and tables in the INFORMATION_SCHEMA database provide informa‐
tion ranging from processes running in the server to active storage engines and
plug-ins to system and status variables. In many cases, these two sources provide
the same or similar information, but in different display formats. (For example, the
SHOW PLUGINS statement and the PLUGINS table are related.) Familiarity with both
sources helps you choose which is more usable in a given situation:
— For interactive use, SHOW is often more convenient because it involves less typing
than INFORMATION_SCHEMA queries. Compare these two statements, which pro‐
duce the same result:
SHOW GLOBAL STATUS LIKE 'Threads_connected' ;
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA . GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Threads_connected' ;
INFORMATION_SCHEMA queries use SELECT , which is more expressive than SHOW
and can be used for highly specific or complex queries, including joins.
Search WWH ::




Custom Search