Eric Bergen

No, not the kind that wakes you up a 2am. The kind that lets you easily slice and present data from the mysql client in a more managable form. This little known feature of the mysql client is one of my favoriate and has helped me slice through large process lists and page through result sets for years.

Basically the pager command allows you to attach commands to the output of the mysql client to manipulate the output just like pipes in bash. Do you hate wading through all those sleeping connections to find the important ones? Yeah me too:

Normal Processlist:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 6 | root | localhost | NULL | Sleep | 3 | | NULL |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql>


The new way:

mysql> pager grep -v Sleep
PAGER set to 'grep -v Sleep'
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)


Of course this isn’t fool proof. If you happen to have a table called Sleep or some field in a query is called Sleep it will also be stripped out. Since anything longer than pager grep or page less is too much to type on the command line each time pager can also be set from ~/.my.cnf. This makes it possible to write a wicked filtering script and have mysql execute it each time by setting pager=perl -e wicked_filter_script.pl If someone feels like writing a script to do syntax highlighting of mysql cli output I would gladly use it

To reset the pager just type nopager*enter*

mysql> nopager
PAGER set to stdout


The slow query log can be a great tool for diagnosing and fixing slow queries. It can also slow the server to a crawl if you are doing bulk inserting. MySQL isn’t afraid to write any query to the slow query log. Even ones that are hundreds of megs. If you are using the multiple-row insert syntax and a query takes longer than long_query_time when it completes mysql will write the entire query out to the slow query log effectively doubling the ammount of data written to the disk.