Home arrow MySQL arrow Page 9 - Back to Basics


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.

  1. Back to Basics
  2. Binary Versus Compiled From Source Installations
  3. MySQL.com Binary Versus Distribution Binary
  4. Configuration Files
  5. File Format
  6. Sample Files
  7. Reconfiguration
  8. The SHOW Commands
By: O'Reilly Media
Rating: starstarstarstarstar / 19
June 08, 2004

print this article



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:


   +----+---------+-----------+------+-------------+------+-------+-------------------+    | Id  |   User   |    Host    |  db   | Command | Time | State |         Info          |    +----+---------+-----------+------+-------------+------+-------+-------------------+

   | 17 | jzawodn | localhost | NULL |   Query     |   0    | NULL  | show processlist |    +----+---------+-----------+------+-------------+------+-------+-------------------+

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:


*************************** 1. row ************************** *

  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



Binlog DumpThe slave thread is reading queries from the masterís binary log.
Change userThe client is logging in as a different user.
ConnectA new client is connecting.
Connect OutThe slave thread is connecting to the master to read queries from its binary log.
Create DBA new database is being created.
DebugThe thread is producing debugging output. This is very uncommon.
Delayed_insertThe thread is processing delayed inserts.
Drop DBA database is being dropped. Field List The client has requested a list of fields in a table.
Init DBThe thread is changing to a different database, typically as the result of a USE command.
KillThe thread is executing a KILL command.
PingThe client is pinging the server to see if itís still connected. Processlist The client is running SHOW PROCESSLIST.
QueryThe thread is currently executing a typical SQL query: SELECT, INSERT, UPDATE, DELETE. This is the most common state other than Sleep.
QuitThe thread is being terminated as part of the server shutdown process.
RefreshThe thread is issuing the FLUSH PRIVILEGE command.
Register SlaveA slave has connected and is registering itself with the master.
ShutdownThe server is being shut down.
Sleep The thread is idle. No query is being run.
Statistics 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 . 

Buy the book!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!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: