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

5 comments:

Seattle Clear Alleys Program said...
This comment has been removed by the author.
golan said...

Why not just use mytop?

Tor Haxson said...

mysqladmin -u root -i 1 processlist

Céd said...

You can also use this way on MySQL 5.1 :

CREATE EVENT MONITOR_PROCESSLIST
ON SCHEDULE EVERY '5' SECONDS
DO INSERT INTO db1.processlist
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

I like this method because data are stored in base and in this case, work on the data is easier.

Regs
Cédric

Marc Firth said...

Awesome. Just what I neeeded.