Home / server / Questions / 650170
In Process
Sreeraj
Asked: 2014-12-09 21:34:43 +0800 CST

Running 'show process list' simultaneously

  • 772

I have two scripts put into a cron that runs every single minute. Both the scripts connect to the same database and runs a show full processlist. Is it okay if two show full processlist commands are run at the same time?

P.S:- One of the scripts will be removed in a week or so, so this setup is only for one week at the most.

mysql

2 Answers

  1. Yes, it is OK. Even if not, it would be MySQL's job to deal with the problem, e.g. by delaying the second until the first is finished.

    • 1

  2. What is the difference between SHOW PROCESSLIST; and SHOW FULL PROCESSLIST; ?

    • SHOW PROCESSLIST; displays the first 100 characters of the INFO column
    • SHOW FULL PROCESSLIST; displays the entire INFO column

    From the INFORMATION_SCHEMA.PROCESSLIST point-of-view:

    • SHOW PROCESSLIST; effectively runs SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,100) INFO FROM information_schema.processlist;
    • SHOW FULL PROCESSLIST; effectively runs SELECT * FROM information_schema.processlist;

    There is only one circumstance I can think of where you do not want to run SHOW FULL PROCESSLIST;.

    When you a loading a mysqldump into a MySQL instance, you run SHOW PROCESSLIST; to track the progress of a table's load. We know that mysqldumps have extended INSERTs. That single INSERT can have hundreds, or even thousands, or rows. If you run SHOW FULL PROCESSLIST;, you can spill the text representation of those hundreds, or even thousands, or rows to the console or to a log. That can quickly generate huge logs of SQL queries with data visible as plain text.

    So, be very careful not to be running multiple SHOW FULL PROCESSLIST; commands during a load of a mysqldump.

    If you have do some monitoring of the processlist, you can either

    • Run SHOW PROCESSLIST;
    • Run SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,300) INFO FROM information_schema.processlist; (setting LEFT(INFO,xxx) to whatever manageable length)
    • 1