Pager - but not on call!

[Chris]

Over the past few years I have found that "pager" inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.

-- Example 1
Lets say you have a MySQL server that is really busy with extremely long queries. You run a “show full processlist” and everything going on streams before your eyes, new lines and all. A more readable way to see what is going on is…

Mysql> pager less –S

By running the above statement the full processlist will NOT appear in the traditional, word wrapped format. The output will show in a very readable, right extended, format. All you need to do now figure out what is going on.

Here is a helpful hint. Set a tee file for MySQL client output.

Mysql> \\\\T /location/to/file.txt

Then, make sure pager is set to “less –S”. From there you can get out of the MySQL client and write a shell script to sort or do whatever to the file.

-- Example 2
Lets say you’re the DBA that is responsible to ensure that all of NEW MySQL SQL statement are functionally equivalent. Or, lets say that you are responsible for optimizing NEW SQL and to ensure that is hold integrity from the old version to the new version. You might say, “is an easy way to tell if you have an exact data set match or not?’ Well, yeppers, there is! All you need to do is use “pager”.

Mysql> pager md5sum

Then run the OLD SQL statement, gather the md5sum, then run the NEW and improved SQL and gather the md5sum. If both are the same then both datasets returned in either SQL statement are exact. Below is an example with the world database, just to illustrate the point.

mysql> select * from City as c, Country ct where c.countrycode = ct.code;
3c8c6b8331c9270f8565edd1a8b8d7a5 -
4079 rows in set (0.05 sec)

mysql> select * from City c join Country ct ON(c.countrycode = ct.code);
3c8c6b8331c9270f8565edd1a8b8d7a5 -
4079 rows in set (0.05 sec)

These are just a couple cool ways to use “pager” inside of the MySQL client. Oh, and before I forget, to turn pager off inside the MySQL command line run the following:

Mysql> nopager

Or just log out and log back in.

[Dov Endress]

On an instance with 10 of thousands of tables, we are often flushing tables. Occasionally flush tables with read lock to take a cold snapshot for backup purposes etc, this process fails every so often:

pager grep -i flush; is invaluable in those instances...

[PaulM]

Plenty of good examples exist for pager (using tee in particular).
My favourite is sorting SHOW PROCESSLIST by age.

"pager sort -r -n -t "|" -k 7|more"

Have Fun