0

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.

Sreeraj
  • 464
  • 1
  • 5
  • 15
  • hmmm....just for curiosity what specific problem are you worried about? – mdpc Dec 09 '14 at 06:55
  • I am writing a monitoring script to replace the current monitoring script which is very 'messy'. It is being written in Python and gets the output of `show full processlist` to create the data. So, for a week or so we will be running these two scripts together. Just wanted to make sure it is okay to run multiple `show full processlist` at the same time. – Sreeraj Dec 09 '14 at 09:08

2 Answers2

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.

Sven
  • 98,649
  • 14
  • 180
  • 226
1

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)
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84