Databases Reference
In-Depth Information
Notice that the call to
prepare()
specified to “use” the result instead of “buffering”
it. You can also specify this when connecting, which will make every statement
unbuffered:
my $dbh = DBI->connect('DBI:mysql:;
mysql_use_result=1
', 'user', 'p4ssword');
Query states
Each MySQL connection, or
thread
, has a state that shows what it is doing at any given
time. There are several ways to view these states, but the easiest is to use the
SHOW FULL
PROCESSLIST
command (the states appear in the
Command
column). As a query progresses
through its lifecycle, its state changes many times, and there are dozens of states. The
MySQL manual is the authoritative source of information for all the states, but we list
a few here and explain what they mean:
Sleep
The thread is waiting for a new query from the client.
Query
The thread is either executing the query or sending the result back to the client.
Locked
The thread is waiting for a table lock to be granted at the server level. Locks that
are implemented by the storage engine, such as InnoDB's row locks, do not cause
the thread to enter the
Locked
state. This thread state is the classic symptom of
MyISAM locking, but it can occur in other storage engines that don't have row-
level locking, too.
Analyzing
and
statistics
The thread is checking storage engine statistics and optimizing the query.
Copying to tmp table [on disk]
The thread is processing the query and copying results to a temporary table, prob-
ably for a
GROUP BY
, for a filesort, or to satisfy a
UNION
. If the state ends with “on
disk,” MySQL is converting an in-memory table to an on-disk table.
Sorting result
The thread is sorting a result set.
Sending data
This can mean several things: the thread might be sending data between stages of
the query, generating the result set, or returning the result set to the client.
It's helpful to at least know the basic states, so you can get a sense of “who has the ball”
for the query. On very busy servers, you might see an unusual or normally brief state,
such as
statistics
, begin to take a significant amount of time. This usually indicates
that something is wrong, and you should use the techniques shown in
Chapter 3
to
capture detailed diagnostic data when it happens.
