You are here

Neat tricks for the MySQL command-line pager

MySQL Performance Blog - Tue, 24/06/2008 - 2:57am

How many of you use the mysql command-line client?  And did you know about the pager command you can give it?  It's pretty useful.  It tells mysql to pipe the output of your commands through the specified program before displaying it to you.

Here's the most basic thing I can think of to do with it: use it as a pager.  (It's scary how predictable I am sometimes, isn't it?)

PLAIN TEXT SQL:
  1. mysql> pager less
  2. mysql> SHOW innodb STATUS\G

For big result sets, it's a pretty handy way to be able to search and scroll through. No mouse required, of course.

But it doesn't have to be this simple! You can specify anything you want as a pager. Hmm, you know what that means? It means you can write your own script and push the output through it. You can't specify arguments to the script, but since you can write your own, that's not really a limitation. For example, here's a super-simple script that will show the lock waits in the output of SHOW INNODB STATUS. Save this file as /tmp/lock_waits and make it executable.

PLAIN TEXT CODE:
  1. #!/bin/sh
  2.  
  3. grep -A 1 'TRX HAS BEEN WAITING'

Now in your mysql session, set /tmp/lock_waits as your pager and let's see if there are any lock waits:

PLAIN TEXT SQL:
  1. mysql> pager /tmp/lock_waits
  2. PAGER SET TO '/tmp/lock_waits'
  3. mysql> SHOW innodb STATUS\G
  4. ------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED:
  5. RECORD LOCKS space id 0 page no 52 n bits 72 INDEX `GEN_CLUST_INDEX` of TABLE `test/t` trx id 0 14615 lock_mode X waiting
  6. 1 row IN SET, 1 warning (0.00 sec)

Pretty useful, isn't it? But we can do even more. For example, the Maatkit tools are specifically designed to be useful at the command line in the traditional Unix pipe-and-filter manner. What sort of goodies can we think of here?

PLAIN TEXT SQL:
  1. mysql> pager mk-visual-EXPLAIN
  2. PAGER SET TO 'mk-visual-explain'
  3. mysql> EXPLAIN SELECT * FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id);
  4. JOIN
  5. +- Bookmark lookup
  6. |  +- TABLE
  7. |  |  TABLE          actor
  8. |  |  possible_keys  PRIMARY
  9. |  +- UNIQUE INDEX lookup
  10. |     KEY            actor->PRIMARY
  11. |     possible_keys  PRIMARY
  12. |     key_len        2
  13. |     ref            sakila.film_actor.actor_id
  14. |     rows           1
  15. +- JOIN
  16.    +- Bookmark lookup
  17.    |  +- TABLE
  18.    |  |  TABLE          film_actor
  19.    |  |  possible_keys  PRIMARY,idx_fk_film_id
  20.    |  +- INDEX lookup
  21.    |     KEY            film_actor->idx_fk_film_id
  22.    |     possible_keys  PRIMARY,idx_fk_film_id
  23.    |     key_len        2
  24.    |     ref            sakila.film.film_id
  25.    |     rows           2
  26.    +- TABLE scan
  27.       rows           1022
  28.       +- TABLE
  29.          TABLE          film
  30.          possible_keys  PRIMARY
  31. 3 rows IN SET (0.00 sec)

Now, that's handy.

What are your favorite ideas?

Entry posted by Baron Schwartz | No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks