使用Performance_schema监控SQL

背景：

在AWS Aurora上如果设置slow_query_time=0，抓取全量日志分析，会导致日志文件过大，限制CPU性能发挥。因此使用Performance_schema分析sql。
可根据需要的指标，在SQL语句上添加响应字段。

#!/bin/bash
source /etc/profile
shopt -s expand_aliases


## databases
dbs=`cat /data/dba/yanhao/shell/performance_schema_shell/dbs.list`
## addresses
address=`cat /data/dba/yanhao/shell/performance_schema_shell/address.list`
## sql摘要
digest=/data/dba/yanhao/shell/performance_schema_shell/digest.list
## 具体的SQL语句
aSQL=/data/dba/yanhao/shell/performance_schema_shell/sql.list

cat /dev/null > $digest
cat /dev/null > $aSQL

## 获取sql摘要
for db in $dbs
do  
#    echo "============"
    dbalogin $address  -e "use performance_schema;
        SELECT DIGEST_TEXT, COUNT_STAR as \"查询次数\",
            FIRST_SEEN, LAST_SEEN, time_to_sec(timediff(LAST_SEEN,FIRST_SEEN)) as time,
            (time_to_sec(timediff(LAST_SEEN,FIRST_SEEN)))/COUNT_STAR as \"tims_s\/call\"   
            FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME = '$db' ORDER BY COUNT_STAR desc limit 10 \G"  2> /dev/null | tee -a  $digest
done


## 根据sql摘要获取详细sql语句
while IFS= read -r line;
do 
    sql=`echo "$line" | grep -i 'digest_text' | awk -F ": " '{print $2}' | sed 's/\*/\\\*/g'`
    if [[ ${#sql} != 0 ]]
    then

        dbalogin $address -e "use performance_schema;
            SELECT  SQL_TEXT  FROM events_statements_history WHERE DIGEST_TEXT =\"$sql\" limit 1;" 2> /dev/null >> $aSQL
    fi
done < $digest

## 查询某个sql的具体信息
while IFS= read -r line;
do
  sql=`echo "$line" | grep -iv 'sql_text' | sed 's/\*/\\\*/g'`
  if [[ ${#sql} != 0  ]]
  then

      dbalogin $address -e "use performance_schema;
            SELECT SQL_TEXT,
                ROWS_EXAMINED,
                ROWS_SENT,
                TIMER_START,
                TIMER_END,
                (TIMER_END-TIMER_START)/1000000000 as time_ms 
                FROM events_statements_history WHERE SQL_TEXT =\"$sql\" limit 1 \G" 2> /dev/null
  fi  
done < $aSQL