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.