This chapter from High Performance MySQL by Jeremy Zawodny and Derek J. Balling. (O'Reilly Media, ISBN: 0-596-00306-4, April 2004) talks about binary distributions, the sections in a configuration file, and some SHOW commands that provide a window into whatís going on inside MySQL. This book is for the MySQL administrator who has the basics down but realizes the need to go further.
The other SHOW command weíll look at is SHOW PROCESSLIST . It outputs a list of what each thread is doing at the time you execute the command.* Itís roughly equivalent to the ps or top commands in Unix or the Task Manager in Windows.
(* Not all threads appear in the SHOW PROCESSLIST output. The thread that handles incoming network connec tions, for example, is never listed.)
Executing it produces a process list in tabular form:
mysql> SHOW PROCESSLIST;
+----+---------+-----------+------+-------------+------+-------+-------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+-----------+------+-------------+------+-------+-------------------+
Itís common for the State and Info columns to contain more information that pro duces lines long enough to wrap onscreen. So itís a good idea to use the G escape in the mysql command interpreter to produce vertical output rather than horizontal output:
Id: 17 User: jzawodn Host: localhost db: NUL L Command: Quer y Time: 0 State: NUL L Info: show processlis t
No matter which way you look at it, the same fields are included:
I d The number that uniquely identifies this process. Since MySQL is a multi-threaded server, it really identifies the thread (or connection)and is unrelated to process IDs the operating system may use. As the operating system does with processes, MySQL starts numbering the threads at 1 and gives each new thread an ID one higher than the previous thread.
The name of the MySQL user connected to this thread.
The name of the host or IP address from which the user is connected. db The database currently selected. This may be NULL if the user didnít specify a database.
This shows the command state (from MySQLís internal point of view) that the thread is currently in. Table 1 lists each command with a description of when you are likely to see it. The commands roughly correspond to various function calls in MySQLís C API. Many commands represent very short-lived actions. Two of those that donít, Sleep and Query , appear frequently in day-to- day usage.
Table 1. Commands in SHOW PROCESSLIST output
The slave thread is reading queries from the masterís binary log.
The client is logging in as a different user.
A new client is connecting.
The slave thread is connecting to the master to read queries from its binary log.
A new database is being created.
The thread is producing debugging output. This is very uncommon.
The thread is processing delayed inserts.
A database is being dropped. Field List The client has requested a list of fields in a table.
The thread is changing to a different database, typically as the result of a USE command.
The thread is executing a KILL command.
The client is pinging the server to see if itís still connected. Processlist The client is running SHOW PROCESSLIST.
The thread is currently executing a typical SQL query: SELECT, INSERT, UPDATE, DELETE. This is the most common state other than Sleep.
The thread is being terminated as part of the server shutdown process.
The thread is issuing the FLUSH PRIVILEGE command.
A slave has connected and is registering itself with the master.
The server is being shut down.
The thread is idle. No query is being run.
Table and index statistics are being gathered for the query optimizer.
The number of seconds that the process has been running the current com mand. A process with a Time of 90 and Command of Sleep has been idle for a minute and a half.
Additional human-readable information about the state of this thread. Hereís an example:
Slave connection: waiting for binlog update
This appears on the master server when a slave is actively replicating from it.
This is the actual SQL currently being executed, if any. Only the first 100 charac ters are displayed in the output of SHOW PROCESSLIST . To get the full SQL, use SHOW FULL PROCESSLIST .
If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!