使用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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码!GitHub神秘组织3小时极速复刻Manus,手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署