背景:
在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
标签:shell,SQL,sql,Performance,performance,digest,schema
From: https://www.cnblogs.com/tyhA-nobody/p/18456931