ansaurus

Question

how to customize `show processlist` in mysql?

Answer 1

A:

I'm afraid the SHOW ... commands don't support ordering.

If somebody could prove me wrong, please do so, because ordering this would bevery useful.

Piskvor 2009-05-30 11:56:20

Answer 2

+2 A:

I would just capture the output and pass it through sort, 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

paxdiablo 2009-05-30 11:58:44

Answer 3

+6 A:

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()

Andomar 2009-05-30 12:09:46

It's so nice,but it's the same as `show full processlist`,say,the `INFO` part is complete,is there a short version?Or maybe I need to use substr

Shore 2009-05-30 12:14:29

Answer 4

A:

...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.

2009-08-21 19:04:59

ansaurus

tags:

views:

answers:

how to customize `show processlist` in mysql?

related questions