How to work with a long process list in MySQL

I am generally a big fan of command line tools. This also applies to MySQL client software such as mysql or mysqladmin. To those spoiled by graphical interfaces, working in text mode may seem crude or even difficult. But the truth is that once you get used to these tools, you will be able to accomplish many things a lot faster than with any GUI client. Of course, using text terminal, which is the environment for any command line tool, has its drawbacks and limitations. For example on a relatively busy MySQL server, every so often when you run SHOW [FULL] PROCESSLIST, which lists client threads connected to a database, you can receive an output that will be many screens long. Sometimes it might be due to the high number of established connections – each takes at least one line on the screen, or sometimes due to some longish queries spanning over multiple lines. Finding relevant information there usually isn’t easy and having to scroll backwards doesn’t really help. What do I do in such cases to help the workflow?

The MySQL command line client doesn’t only rely for execution whatever you type into it. It comes with a tiny bit of extra features too. They are accessible through a set of commands that follow this simple pattern: \<command character>. For example you can get the complete list of available commands by typing \h into mysql prompt.

(garfield:~) maciek% mysql -u root -p
[..]
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \h
[..]
List of all MySQL commands:
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
[..]
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.

Among these commands you will find one called pager, which says “(\P) Set PAGER [to_pager]. Print the query results via PAGER”. What is so special about it, you ask? Well, by definition a pager is a program used to view the contents of a text file moving down the file one line or one screen at a time. In this case there is no physical file to deal with, however a pager can also work on an output from another command. If you enable it inside the MySQL command line client, it will work on its output, i.e. on a query output. So when you run SHOW PROCESSLIST once pager is enabled, any result spanning over multiple screens will be buffered and waiting for you until you decide to scroll down, line by line or screen by screen – completely up to you. No more hundreds or thousands of lines flying through your screen without any control.

In order to use it, just type \P and MySQL client will pick up the default pager configured in your system. By specifying an argument to \P you can also choose a different program.

mysql> \P
PAGER set to '/usr/bin/less'
mysql> \P more
PAGER set to 'more'

less and more are two pager applications commonly available in any Linux distribution and also in other Unix-like systems. Please note, however, that MySQL client does not verify whether the program you specified exists or not. If you define something that does not exist, you won’t get any output:

mysql> \P mored
PAGER set to 'mored'
mysql> show processlist;
sh: mored: command not found

The command having the opposite effect to \P is \n. It clears the pager setting and restores the original behavior.

But hey, since we are basically setting the pager to an external program such as less, why not just try some other application and figure out even better ways to get information out of a long process list? If for example there was a thousand connections hanging in MySQL and out of that thousand nearly all were doing nothing, wouldn’t it be better to just see those, which were actually running a query rather than browse through all? You can recognize an idle connection by its state, which in the MySQL process list appears in the column called Command.

mysql> show processlist;
+--------+---------+-----------+------+---------+-------+-------+------------------+
| Id     | User    | Host      | db   | Command | Time  | State | Info             |
+--------+---------+-----------+------+---------+-------+-------+------------------+
| 109353 | root    | localhost | NULL | Sleep   |     2 |       | NULL             |
| 109530 | root    | localhost | NULL | Query   |     0 | NULL  | show processlist |

If a connection is in Sleep state, it means it is not doing anything in that particular moment. Why not just filter out the sleeping connections?

mysql> \P grep -v Sleep
PAGER set to 'grep -v Sleep'
mysql> show processlist;
+--------+---------+-----------+------+---------+-------+-------+------------------+
| Id     | User    | Host      | db   | Command | Time  | State | Info             |
+--------+---------+-----------+------+---------+-------+-------+------------------+
| 109530 | root    | localhost | NULL | Query   |     0 | NULL  | show processlist |

What happened here? We are not relying on the pager feature to allow us move down through the process list anymore. We leveraged the possibility of running the query output through an external application, any external application, to do something entirely different.

So how about sorting idle client threads by age, then?

mysql> \P awk -F\| '$2 ~ /[0-9]+/ && $6 ~ /Sleep/' | sort -t \| -k 7 -n -r
PAGER set to 'awk -F\| '$2 ~ /[0-9]+/ && $6 ~ /Sleep/' | sort -t \| -k 7 -n -r'
mysql> show processlist;
| 106092 | wp_usr | localhost | wp   | Sleep   | 25140 |       | NULL             |
| 106090 | wp_usr | localhost | wp   | Sleep   | 25140 |       | NULL             |
| 106091 | wp_usr | localhost | wp   | Sleep   | 25139 |       | NULL             |
| 109751 | root   | localhost | NULL | Sleep   |   244 |       | NULL             |

This is already a very complex example, but I wanted to show you the potential. The possibilities for post-processing the query output are practically limitless if you either know or are willing to learn some basic text utilities such as grep, awk, sort and a few others. But nothing is also stopping you from sending the output through a small Perl script if that seems easier. If the process list on your MySQL server can become several screens long, knowing how to use the pager can make your life a lot easier and you might be able to diagnose problems much faster.

One interesting observation to make is that I kept referring to query output. That’s right! Even though this post is focused on dealing with long process lists, you can apply pager on the output from any query. For example on a SELECT that’s returning thousands of rows.

[MySQL Health Check]
About Maciej Dobrzanski

A MySQL consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. @linkedin

Comments

  1. Joseph says:

    Maciej: This is awsome! Great job, waiting for more tips & tricks from you.

  2. Przemek says:

    Having the ability to use system commands like awk in mysql client is great indeed, however I prefer much faster and easier to understand way.
    How about:
    SELECT * from information_schema.processlist WHERE command=”Sleep” ORDER BY time DESC LIMIT 20;
    This gives basically the same output like your example, isn’t it?
    But this is really the beginning! Enjoy using GROUP BYs, different kind of WHEREs, SUBSTRs, COUNTs, etc. For me that’s the best way in bringing the mess into order if processlist gets huge when your server suffers from overload.

    • Przemek,

      That’s right. These days you can also use information_schema and sometimes achieve even better results with it, and it could be yet another blog post :-)

    • Wasif says:

      Its a nice post by Maciej on use of external programs to use with query output not just processlist. Using INFORMATION_SCHEMA seems to be easy for the sake of processlist as suggested by Przemek. At times (in really busy server) you don’t want to use INFOMRATION_SCHEMA approach due to its MyISAM table nature. Your INFOMRATION_SCHEMA query may block other processes of MySQL for sometime.

  3. mysql says:

    Hi,
    I am new to shell, may you please let me know script where i can pull out query taking more then 70 secs and mail them. i am using mysql 5.0 version.

Speak Your Mind

*