[ MySQL ] CPU 부하 Query 모니터링

모니터링 스크립트

• 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