Database Reference
In-Depth Information
The “MySQL Uncertainty Principle”
Heisenberg's uncertainty principle for measurement of quantum phenomena has a
MySQL analog. If you monitor MySQL's status to see how it changes over time, you
might notice the curious effect that, for some of the indicators, each time you take a
measurement, you change the value you're measuring! For example, to determine the
number of statements the server has received, use this statement:
SHOW
GLOBAL
STATUS
LIKE
'Queries'
However, that statement is itself a statement, so each time you issue it, you cause the
Queries
value to change. In effect, your performance assessment instrument contami‐
nates its own measurements, something you might want to take into account.
The preceding discussion uses
Queries
, which indicates the total number of statements
executed. Options for more fine-grained analysis are available:
• The server maintains a set of
Com_
xxx
status variables that count executions of
particular statements. For example,
Com_insert
and
Com_update
count
INSERT
and
UPDATE
statements, respectively.
• To find out
which
queries were executed, check the general query log. To discover
which ones were slow, check the slow query log. As mentioned previously, log tables
are easier to analyze than logfiles because you can apply SQL statements to them.
For example, this query summarizes number of statements per user, most active
users first:
SELECT
COUNT
(
*
),
user_host
FROM
mysql
.
general_log
GROUP
BY
user_host
ORDER
BY
COUNT
(
*
)
DESC
;
A similar query shows which statements appear most often in the slow query log:
SELECT
COUNT
(
*
),
sql_text
FROM
mysql
.
slow_log
GROUP
BY
sql_text
ORDER
BY
COUNT
(
*
)
DESC
;
For information about summary and statistical techniques, see
Chapter 8
and
Chapter 15
.
Recipe 20.14
shows log-analysis queries in a related context (Apache
logging).
How many simultaneous connections does the server permit? Is it close to running out?
It's
often the case that a server function is assessed using a combination of configuration
settings plus current operational status. Typically, the former comes from system vari‐
ables, whereas the latter comes from status variables. Connection management is an
example of this concept. The
max_connections
system variable indicates the maximum
number of simultaneous connections the server permits, and the
Threads_connected
status variable shows how many clients are currently connected. If
Threads_connected
