• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Showing running queries in MySQL

Showing running queries in MySQL

MySQL has a statement called "show processlist" to show you the running queries on your MySQL server. This can be useful to find out what’s going on if there are some big, long queries consuming a lot of CPU cycles, or if you’re getting errors like "too many connections".

The syntax is simply:

show processlist;

which will output something along these lines:

+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id     | User   | Host      | db     | Command | Time | State                | Info                                                                                                 |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 708163 | root   | localhost | NULL   | Query   |    0 | NULL                 | show processlist                                                                                     |
| 708174 | test   | localhost | test   | Query   |    2 | Copying to tmp table | select dist.name, dist.filename, count(*)
from orders_header h
inner join orders_detail d on h.ord |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The "info" column shows the query being executedm or NULL if there’s nothing currently happening. When running "show processlist" it will only show the first 100 characters of the query. To show the full query run "show full processlist" instead.

Running the above command from the MySQL command line interface with a ; delimiter can make it difficult to read the output, especially if the queries are long and span multiple lines. Using the G delimiter instead will show the data in what is often a more readable format, although it consumes more rows in your terminal. This is espcially useful when running "show full processlist" because some of the queries displayed may be quite long.

mysql> show processlistG

*************************** 6. row ***************************
     Id: 708163
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 7. row ***************************
     Id: 708174
   User: test
   Host: localhost
     db: test
Command: Query
   Time: 3
  State: Copying to tmp table
   Info: select dist.name, dist.filename, count(*)
from orders_header h
inner join orders_detail d on h.ord
2 rows in set (0.00 sec)

If you are running as an ordinary user who doesn’t have the "process" privilege, then "show processlist" will only show the processes you yourself are currently running. If the user has the process privilege then they can see everything.

Check Out These Related posts:

  1. Get a MySQL table structure from the INFORMATION_SCHEMA
  2. Show indexes for a table with MySQL
  3. Listing tables and their structure with the MySQL Command Line Client
  4. Running queries from the MySQL Command Line

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap