mysql processlist logging

<shell command>

최대 50개만 저장하는 코드 

date=$(date +...) # set the desired date format here
[ $(mysql -uroot -e "show full processlist" | tee plist-$date.log | wc -l) -lt 51 ] && rm plist-$date.log

 

while [ true ]; 
	do mysql -u MYSQLUSERNAME -p'MYSQLUSERPASSWORD' --execute='SHOW FULL processlist;'; 
    sleep 1; 
done;

 

 

MySQL 실행 프로세스 리스트 확인하기

일단 MySQL 에서 실행중인 프로세스 리스트를 확인해보자.

-- 서버 내에서 실행중인 스레드 집합에서 현재 수행중인 작업을 보여준다.
SHOW PROCESSLIST; -- Info 필드의 100자 까지만 보여준다.

SHOW FULL PROCESSLIST; -- Info 필드의 전체를 보여준다.

-- information_schema 에서도 확인할 수 있다. 
SELECT *
FROM information_schema.processlist
ORDER BY id;

각 필드의 의미는 아래와 같다.

‘ID’	‘The connection identifier.’
‘USER’	‘The MySQL user who issued the statement.’
‘HOST’	‘The host name of the client issuing the statement.’
‘DB’	‘The default database, if one is selected, otherwise NULL.’
‘COMMAND’	‘The type of command the thread is executing.’
‘TIME’	‘The time in seconds that the thread has been in its current state.’
‘STATE’	‘An action, event, or state that indicates what the thread is doing.’
‘INFO’	‘The statement that the thread is executing, or NULL if it is not executing any statement.’
‘TIME_MS’	‘The time in milliseconds that the thread has been in its current state.’
‘ROWS_EXAMINED’	‘The number of rows examined by the statement being executed (NOTE: This column is not updated for each examined row so it does not necessarily show an up-to-date value while the statement is executing. It only shows a correct value after the statement has completed.).’
‘ROWS_SENT’	‘The number of rows sent by the statement being executed.’
‘TID’	‘The Linux Thread ID. For Linux, this corresponds to light-weight process ID (LWP ID) and can be seen in the ps -L output. In case when Thread Pool is enabled, “TID” is not null for only currently executing statements and statements received via “extra” connection.’

Example

+----+------+-----------+--------------------+---------+------+-----------+---------------------------+---------+-----------+---------------+
| ID | USER | HOST      | DB                 | COMMAND | TIME | STATE     | INFO                      | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+----+------+-----------+--------------------+---------+------+-----------+---------------------------+---------+-----------+---------------+
| 12 | root | localhost | information_schema | Query   |    0 | executing | select * from processlist |       0 |         0 |             0 |
+----+------+-----------+--------------------+---------+------+-----------+---------------------------+---------+-----------+---------------+