how to customize show processlist in mysql

Question

I want to order by Time but seems no way to do that    mysql gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

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?

User · Answer

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

[mysql] how to customize `show processlist` in mysql?

The answer is

Examples related to mysql

Examples related to filter

Examples related to sql-order-by

Examples related to processlist

Tags