Last time I wrote about a few tips that can make you more efficient when using the command line on Unix. Today I want to focus more on pager.
The most common usage of pager is to set it to a Unix pager such as less. It can be very useful to view the result of a command spanning over many lines (for instance SHOW ENGINE INNODB STATUS):
|
1 2 3 4 |
mysql> pager less PAGER set to 'less' mysql> show engine innodb statusG [...] |
Now you are inside less and you can easily navigate through the result set (use q to quit, space to scroll down, etc).
Reminder: if you want to leave your custom pager, this is easy, just run pager:
|
1 2 |
mysql> pager Default pager wasn't set, using stdout. |
Or n:
|
1 2 |
mysql> n PAGER set to stdout |
But the pager command is not restricted to such basic usage! You can pass the output of queries to most Unix programs that are able to work on text. We have discussed the topic, but here are a few more examples.
Sometimes you don’t care about the result set, you only want to see timing information. This can be true if you are trying different execution plans for a query by changing indexes. Discarding the result is possible with pager:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> pager cat > /dev/null PAGER set to 'cat > /dev/null' # Trying an execution plan mysql> SELECT ... 1000 rows in set (0.91 sec) # Another execution plan mysql> SELECT ... 1000 rows in set (1.63 sec) |
Now it’s much easier to see all the timing information on one screen.
Let’s say you are rewriting a query and you want to check if the result set is the same before and after rewrite. Unfortunately, it has a lot of rows:
|
1 2 3 |
mysql> SELECT ... [..] 989 rows in set (0.42 sec) |
Instead of manually comparing each row, you can calculate a checksum and only compare the checksum:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> pager md5sum PAGER set to 'md5sum' # Original query mysql> SELECT ... 32a1894d773c9b85172969c659175d2d - 1 row in set (0.40 sec) # Rewritten query - wrong mysql> SELECT ... fdb94521558684afedc8148ca724f578 - 1 row in set (0.16 sec) |
Hmmm, checksums don’t match, something is wrong. Let’s retry:
|
1 2 3 4 |
# Rewritten query - correct mysql> SELECT ... 32a1894d773c9b85172969c659175d2d - 1 row in set (0.17 sec) |
Checksums are identical, the rewritten query is much likely to produce the same result as the original one.
SHOW PROCESSLISTIf you have lots of connections on your MySQL, it’s very difficult to read the output of SHOW PROCESSLIST. For instance, if you have several hundreds of connections and you want to know how many connections are sleeping, manually counting the rows from the output of SHOW PROCESSLIST is probably not the best solution. With pager, it is straightforward:
|
1 2 3 4 5 |
mysql> pager grep Sleep | wc -l PAGER set to 'grep Sleep | wc -l' mysql> show processlist; 337 346 rows in set (0.00 sec) |
This should be read as ‘337 out 346 connections are sleeping’.
Slightly more complicated now: you want to know the number of connections for each status:
|
1 2 3 4 5 6 7 8 |
mysql> pager awk -F '|' '{print $6}' | sort | uniq -c | sort -r PAGER set to 'awk -F '|' '{print $6}' | sort | uniq -c | sort -r' mysql> show processlist; 309 Sleep 3 2 Query 2 Binlog Dump 1 Command |
Astute readers will have noticed that these questions could have been solved by querying INFORMATION_SCHEMA. For instance, counting the number of sleeping connections can be done with:
|
1 2 3 4 5 6 |
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep'; +----------+ | COUNT(*) | +----------+ | 320 | +----------+ |
and counting the number of connection for each status can be done with:
|
1 2 3 4 5 6 7 8 |
mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL DESC; +-------------+-------+ | COMMAND | TOTAL | +-------------+-------+ | Sleep | 344 | | Query | 5 | | Binlog Dump | 2 | +-------------+-------+ |
True, but:
As you can see, pager is your friend! It’s very easy to use and it can solve problems in an elegant and very efficient way. You can even write your custom script (if it is too complicated to fit in a single line) and pass it to the pager.
Resources
RELATED POSTS
For the lazy admins, use P. Saves a few keystrokes 🙂
ha ha ha lolll!!!
And for those who don’t use common_schema, Creating SQL which can be copied can be done with sed:
dveeden@localhost [TEST] > pager sed -e ‘s/^|//’ -e ‘s/|$//’
PAGER set to ‘sed -e ‘s/^|//’ -e ‘s/|$//”
dveeden@localhost [TEST] > SELECT CONCAT(‘ALTER TABLE ‘,table_name,’ ENGINE=InnoDB;’) stmt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’TEST’;
+——————————-+
stmt
+——————————-+
ALTER TABLE t1 ENGINE=InnoDB;
ALTER TABLE t2 ENGINE=InnoDB;
ALTER TABLE t3 ENGINE=InnoDB;
+——————————-+
3 rows in set (0.00 sec)
dveeden@localhost [TEST] > ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 0 rows affected (0.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
dveeden@localhost [TEST] > ALTER TABLE t2 ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
dveeden@localhost [TEST] > ALTER TABLE t3 ENGINE=InnoDB;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
Some slick ideas here. I have needed the /dev/null and the checksum ones; definitely welcome additions to my toolbox.
pager md5sum doesn’t seem to work properly e.g.
mysql> pager md5sum
PAGER set to ‘md5sum’
mysql> select pkCaseId from tblCase where pkCaseId=1;
e8f6937bca3138aa2d09439a0450a146 –
1 row in set (0.01 sec)
mysql> select fkCaseId from tblCaseSegment where fkCaseId=1;
906f654071b2b83572668b94b235c655 –
1 row in set (0.00 sec)
Both queries return same value i.e. 1 but why the checksum is different?
Row header for one query is pkCaseId and for the other query the row header is fkCaseId so the output checksum is different, try select fkCaseId as pkCaseId from tblCaseSegment where fkCaseId=1;
aftab,
The name of the column is passed to md5sum along with the value, that’s why you get different checksums.
For instance:
mysql> select 1;
3ec930f74d6ec7d7bdd7aa8544440835 –
1 row in set (0.00 sec)
mysql> select 1 as res;
8928fcd16d3e56628ad18041c987d551 –
1 row in set (0.00 sec)
The show processlist trick is not quite right: it is also counting the header lines. I modified it as follows:
mysql> pager grep -v ‘-+-‘ | grep -v ” Id[ |]*User ” | awk -F ‘|’ ‘{print $6}’ | sort | uniq -c | sort -r
PAGER set to ‘grep -v ‘-+-‘ | grep -v ” Id[ |]*User ” | awk -F ‘|’ ‘{print $6}’ | sort | uniq -c | sort -r’
mysql> show processlist;
5 Sleep
2 Connect
1 Query
8 rows in set (0.00 sec)
No more entries for ‘Command’ or blank…