모니터링 스크립트
- TOP을 이용해서 MySQL의 Top10 Thread_OS_ID 확인
- Thread_OS_ID를 이용하여 MySQL Query 수행
- Thread_OS_ID, Processlist_ID, Query 출력
- 실행 시 password 변수 변경하여 사용
[root@db-test1 ~]# cat lee.sh
#!/bin/bash
password='wl@2020Toaxj'
### Search Top10 MySQL Thread_OS_ID
lee=`top -b -n 1 -H -p \`ps -ef | grep mysqld | grep -v "mysqld_safe" | grep -v "grep" | awk '{print $2}'\` | grep -v "Threads\|top\|KiB\|%Cpu(s)\|PID" | sort -nr -k 9 | head -10 | awk '{print $1}'`
echo -e "\n####################################################################################"
echo "##### Thread Level CPU USAGE #####"
echo -e "####################################################################################\n"
echo " Tid USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND"
top -b -n 1 -H -p `ps -ef | grep mysqld | grep -v "mysqld_safe" | grep -v "grep" | awk '{print $2}'` | grep -v "Threads\|top\|KiB\|%Cpu(s)\|PID" | sort -nr -k 9 | head -10
### Create Query IN
String=`echo $lee | sed -e "s/ /','/g"`
Thread_OS_ID="'${String}'"
### MYSQL Query
Check_Query=`MYSQL_PWD=${password} mysql -uroot --binary-mode -N -e "select concat('Thread_OS_ID: ',THREAD_OS_ID,' ID: ',PROCESSLIST_ID),'|',PROCESSLIST_INFO,'|' from performance_schema.threads where name='thread/sql/one_connection' and THREAD_OS_ID in ($Thread_OS_ID) and PROCESSLIST_ID!=(select connection_id());"`
Check_Query2=`echo ${Check_Query}`
### Create Array & RUN
echo -e "\n####################################################################################"
echo "##### Thread Info #####"
echo -e "####################################################################################\n"
IFS='|' read -ra array <<< "$Check_Query2"
echo $length
for (( i=1; i<=${#array[@]}; i++ )) ; {
echo ${array[`expr $i - 1`]}
}
스크립트 결과
####################################################################################
##### Thread Level CPU USAGE #####
####################################################################################
Tid USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11591 mysql 20 0 19.623g 3.724g 17732 S 33.3 12.0 1:50.33 mysqld
10630 mysql 20 0 19.623g 3.724g 17732 S 33.3 12.0 2:20.25 mysqld
10624 mysql 20 0 19.623g 3.724g 17732 S 33.3 12.0 2:19.56 mysqld
3969 mysql 20 0 19.623g 3.724g 17732 S 26.7 12.0 2:20.96 mysqld
10629 mysql 20 0 19.623g 3.724g 17732 S 26.7 12.0 2:21.00 mysqld
10628 mysql 20 0 19.623g 3.724g 17732 S 26.7 12.0 1:46.82 mysqld
10626 mysql 20 0 19.623g 3.724g 17732 R 26.7 12.0 2:20.18 mysqld
10625 mysql 20 0 19.623g 3.724g 17732 S 26.7 12.0 2:06.21 mysqld
3885 mysql 20 0 19.623g 3.724g 17732 S 13.3 12.0 1:47.06 mysqld
10631 mysql 20 0 19.623g 3.724g 17732 S 13.3 12.0 1:42.91 mysqld
####################################################################################
##### Thread Info #####
####################################################################################
Thread_OS_ID: 10626 ID: 614
COMMIT
Thread_OS_ID: 10624 ID: 615
SELECT s_quantity, s_data, s_dist_01 s_dist \n\t FROM stock1 \n\t WHERE s_i_id = 39026 AND s_w_id= 9 FOR UPDATE
Thread_OS_ID: 3885 ID: 616
COMMIT
Thread_OS_ID: 11591 ID: 617
COMMIT
Thread_OS_ID: 10628 ID: 618
UPDATE warehouse1\n\t SET w_ytd = w_ytd + 3949 \n\t WHERE w_id = 1
Thread_OS_ID: 10625 ID: 619
COMMIT
Thread_OS_ID: 10631 ID: 620
COMMIT
Thread_OS_ID: 10629 ID: 621
SELECT d_next_o_id, d_tax \n FROM district1 \n WHERE d_w_id = 10 \n AND d_id = 2 FOR UPDATE
Thread_OS_ID: 3969 ID: 622
COMMIT
Thread_OS_ID: 10630 ID: 623
COMMIT