08 January 2010

How to SHOW PROCESSLIST Every .5 Seconds

Yes, it has been a while since I last posted, so we'll see how this year goes...

Just the other evening I came across something that I should have done much sooner. Run SHOW PROCESSLIST every second without hitting "ENTER" repetitively.

The Setup

1) Create a temporary user with a non-sensitive password on localhost with SUPER privs. Yes, this crazy, but I did say *temporary*, right?
mysql> GRANT SUPER ON *.* TO tmp_user@localhost IDENTIFIED BY 'tmp_user_passwd_2102394567';

Now In Action

2) Go back to the shell and type this:

$ watch -n1 'mysql -utmp_user -ptmp_user_passwd_2102394567 --exec="SHOW PROCESSLIST"'

Simple, eh?

This is a good way to get a quick rundown of mysql's process list in a dev or test environment, such as simple web page load tests, or even an environment where you have a long running query and you are wondering when it will be completed.

Of course, there are a lot of fantastic tools out there that will do much more, and are better, but this is a good "quick & dirty" processlist check that works as a good substitute for typing SHOW PROCESSLIST in mysql 20 times over...

DON'T FORGET to DROP this user when you're done!

Until next time,
David