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:
Why not just use mytop?
mysqladmin -u root -i 1 processlist
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
Awesome. Just what I neeeded.
Post a Comment