how to customize `show processlist` in mysql?
MysqlFilterSql Order-ByProcesslistMysql Problem Overview
I want to order by Time,but seems no way to do that ?
mysql> show processlist;
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 1 | system user | | NULL | Connect | 226953 | Waiting for master to send event | NULL |
| 2 | system user | | v3 | Connect | 35042 | Locked | update postings a
left join cities b on b.id=a.job_city_id
left join states h on h.id=b.stat |
| 313888 | irnadmin | 172.19.0.239:40136 | v3 | Sleep | 0 | | NULL |
| 314075 | irnadmin | 172.19.0.239:41113 | v3 | Sleep | 0 | | NULL |
| 314118 | irnadmin | 172.19.0.239:41282 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 314686 | irnadmin | 172.19.0.239:43251 | v3 | Sleep | 0 | | NULL |
| 314732 | irnadmin | 172.19.0.239:43436 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 314984 | irnadmin | 172.19.0.239:44366 | v3 | Sleep | 2 | | NULL |
| 315051 | irnadmin | 172.19.0.239:44713 | v3 | Query | 0 | NULL | NULL |
| 315198 | irnadmin | 172.19.0.239:51569 | v3 | Sleep | 2 | | NULL |
| 315280 | irnadmin | 172.19.0.239:51849 | v3 | Query | 34978 | freeing items | SELECT id, email_address, type, closed, robotno FROM accounts WHERE screen_name = 'ShantanuS' |
| 315320 | irnadmin | 172.19.0.239:52045 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 315384 | irnadmin | 172.19.0.239:52463 | v3 | Sleep | 1 | | NULL |
| 452248 | irnadmin | 172.19.0.28:54899 | v3 | Query | 34978 | freeing items | SELECT id, email_address, type, closed, robotno FROM accounts WHERE screen_name = 'LIZW0218' |
| 452291 | irnadmin | 172.19.0.28:55045 | v3 | Sleep | 1 | | NULL |
| 452316 | irnadmin | 172.19.0.28:55144 | v3 | Sleep | 0 | | NULL |
| 452353 | irnadmin | 172.19.0.28:55278 | v3 | Sleep | 0 | | NULL |
| 452382 | irnadmin | 172.19.0.28:55371 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 452413 | irnadmin | 172.19.0.28:55479 | v3 | Sleep | 1 | | NULL |
| 452541 | irnadmin | 172.19.0.28:55946 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 452626 | irnadmin | 172.19.0.28:56215 | v3 | Sleep | 2 | | NULL |
| 452711 | irnadmin | 172.19.0.28:39916 | v3 | Sleep | 0 | | NULL |
| 452781 | irnadmin | 172.19.0.28:40161 | v3 | Sleep | 1 | | NULL |
| 452904 | irnadmin | 172.19.0.28:40955 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. |
| 453014 | irnadmin | 172.19.0.28:41291 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 453057 | irnadmin | 172.19.0.28:41377 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. |
| 453084 | irnadmin | 172.19.0.28:41441 | v3 | Sleep | 0 | | NULL |
| 453112 | irnadmin | 172.19.0.28:41536 | v3 | Sleep | 0 | | NULL |
| 453156 | irnadmin | 172.19.0.28:41653 | v3 | Query | 34978 | freeing items | SELECT protocol FROM accounts WHERE email_address= '***@gtalk.jabber.jobirn.c |
| 453214 | irnadmin | 172.19.0.28:41800 | v3 | Sleep | 5 | | NULL |
| 453243 | irnadmin | 172.19.0.28:41991 | v3 | Sleep | 0 | | NULL |
| 453313 | irnadmin | 172.19.0.28:42255 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 453396 | irnadmin | 172.19.0.28:53718 | v3 | Sleep | 2 | | NULL |
| 453476 | irnadmin | 172.19.0.28:54019 | v3 | Sleep | 0 | | NULL |
| 453561 | irnadmin | 172.19.0.28:54352 | v3 | Sleep | 3 | | NULL |
| 453594 | irnadmin | 172.19.0.28:54456 | v3 | Sleep | 0 | | NULL |
| 453727 | irnadmin | 172.19.0.28:55166 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 453786 | irnadmin | 172.19.0.28:55320 | v3 | Sleep | 4 | | NULL |
| 610140 | irnadmin | 172.19.0.28:33848 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. |
| 685119 | irnadmin | 172.19.0.27:37251 | v3 | Query | 34980 | Sending data | select postings.id id,category, job_desc_title,
IF(c1.name is not null,c1.name,IF(c2.name is not n |
| 685226 | irnadmin | 172.19.0.139:57274 | v3 | Query | 34735 | Locked | SELECT job_desc_title,job_desc,job_state_name,job_city_name,company_categories.name,postings.categor |
| 685229 | irnadmin | 172.19.0.139:57278 | v3 | Query | 34735 | Locked | SELECT job_desc_title,job_desc,job_state_name,job_city_name,company_categories.name,postings.categor |
| 685232 | irnadmin | 172.19.0.139:57283 | v3 | Query | 34734 | Locked | select job_desc_title,job_desc from postings where id=287650 |
| 685233 | irnadmin | 172.19.0.139:57286 | v3 | Query | 34734 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job |
| 685235 | irnadmin | 172.19.0.28:37502 | v3 | Query | 34734 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job |
| 686496 | irnadmin | 172.19.0.239:33306 | v3 | Query | 32589 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job |
| 686503 | irnadmin | 172.19.0.28:54051 | v3 | Query | 32588 | Locked | SELECT job_desc_title, job_desc, IF(postings.category IS NOT NULL, postings.category, job_categories |
| 709550 | root | localhost | v3 | Query | 0 | NULL | show processlist |
| 710084 | irnadmin | 172.19.0.27:53285 | NULL | Query | 0 | removing tmp table | show status where Variable_name='Threads_running' |
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
49 rows in set (0.00 sec)
Mysql Solutions
Solution 1 - Mysql
Newer versions of SQL support the process list in information_schema:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
You can ORDER BY in any way you like.
The INFORMATION_SCHEMA.PROCESSLIST table was added in MySQL 5.1.7. You can find out which version you're using with:
SELECT VERSION()
Solution 2 - Mysql
The command
show full processlist
can be replaced by:
SELECT * FROM information_schema.processlist
but if you go with the latter version you can add WHERE clause to it:
SELECT * FROM information_schema.processlist WHERE `INFO` LIKE 'SELECT %';
For more information visit this
Solution 3 - Mysql
Another useful tool for this from the command line interface, is the pager command.
eg
pager grep -v Sleep | more; show full processlist;
Then you can page through the results.
You can also look for certain users, IPs or queries with grep or sed in this way.
The pager command is persistent per session.
Solution 4 - Mysql
You can just capture the output and pass it through a filter, something like:
mysql show processlist
| grep -v '^\+\-\-'
| grep -v '^| Id'
| sort -n -k12
The two greps strip out the header and trailer lines (others may be needed if there are other lines not containing useful information) and the sort is done based on the numeric field number 12 (I think that's right).
This one works for your immediate output:
mysql show processlist
| grep -v '^\+\-\-'
| grep -v '^| Id'
| grep -v '^[0-9][0-9]* rows in set '
| grep -v '^ '
| sort -n -k12
Solution 5 - Mysql
If you use old version of MySQL you can always use \P combined with some nice piece of awk code. Interesting example here
http://www.dbasquare.com/2012/03/28/how-to-work-with-a-long-process-list-in-mysql/
Isn't it exactly what you need?
Solution 6 - Mysql
...We don't have a newer version of MySQL yet, so I was able to do this (works only on UNIX):
host=maindb
echo "show full processlist\G" | mysql -h$host | grep -B 6 -A 1 Locked
The above will query for all locked sessions, and return the information and SQL that is involved.
...So- assuming you wanted to query for sessions that were sleeping:
host=maindb
echo "show full processlist\G" | mysql -h$host | grep -B 6 -A 1 Sleep
Or, assuming you needed to provide additional connection parameters for MySQL:
host=maindb
user=me
password=mycoolpassword
echo "show full processlist\G" | mysql -h$host -u$user -p$password | grep -B 6 -A 1 Locked
With a couple of tweaks, I'm sure a shell script could be easily created to query the processlist the way you want it.
Solution 7 - Mysql
Use the "|" as separator
mysqladmin processlist| sort -t\| -n -k7
From sort manual ( man sort )
> -k, --key=KEYDEF > sort via a key; KEYDEF gives location and type
> -t, --field-separator=SEP > use SEP instead of non-blank to blank transition
> -n, --numeric-sort > compare according to string numerical value
> -r, --reverse > reverse the result of comparisons