Server Administration - MySQL - page 772

The server is able to write the general query and slow query logs to files, tables, or
both. Log tables facilitate analysis better than the files; they are more structured and
hence subject to analysis using SQL statements. The contents are also easier to
interpret. Each query row in the general_log table shows the user associated with
it. With the logfile, users are named only on connection lines. To identify a user's
queries, you must extract the connection ID from the connection line and look for
subsequent query lines with the same ID.
In addition, log tables are managed by the CSV storage engine, so the table datafiles
are written in comma-separated values format. Look in the mysql directory under
the server's data directory for files named general_log.CSV and slow_log.CSV . You
can process them with tools that read CSV files.
To get information from a log, it must be enabled (see Recipe 22.3 for instructions).
• The EXPLAIN statement can be useful for checking long-running queries. Although
EXPLAIN is most often used to see execution plans for prospective queries, MySQL
5.7.2 and up has the capability of using EXPLAIN to examine queries currently exe‐
cuting in other sessions. If a query seems to be stuck, this may help you understand
why. Use SHOW PROCESSLIST or the INFORMATION_SCHEMA PROCESSLIST table to de‐
termine the connection ID of the session running the problem query, then point
EXPLAIN at it:
EXPLAIN FOR CONNECTION connection_id ;
EXPLAIN can produce output in tabular or JSON format. The latter can be parsed
and manipulated by standard JSON modules in your programming language of
choice.
Using monitoring information
After considering the information sources available to you, think about which is best
suited to the question you seek to answer. If multiple sources apply, the best choice may
depend on the context in which you intend to use it. The preceding summary of sources
includes some remarks about their suitability to different contexts. Let's see how that
works out in practice, by revisiting the original monitoring questions given at the be‐
ginning of this discussion and considering how to go about answering them. Keep in
mind that these examples are illustrative, not exhaustive. For most, you can make im‐
plementation choices other than those shown.
Is the server running? If so, how long has it been up? To tell whether the server is running,
just try connecting to it. If the connection succeeds or you get an error that's from the
server itself, the server is up. mysqladmin ping is a good choice here, for interactive use
or from within shell scripts. This result indicates the server is running: