MySQL 每天自行統計慢日誌指令碼

maohaiqing0304發表於2015-07-06

MySql指令碼

連結：http://blog.itpub.net/28602568/viewspace-1724524/

標題：MySQL 每天自行統計慢日誌指令碼

前言：

MySQL中提供了一個慢查詢的日誌記錄功能(有點類似 oracle的v$session_longops )，可以把查詢SQL語句時間大於多少秒的語句寫入慢查詢日誌，日常維護中可以通過慢查詢日誌的記錄資訊快速準確地判斷問題所在；

可以通過mysqldumpslow、mysqlsla、pt-query-digest ..等工具進行分析慢日誌, mysqldumpslow、mysqlsla文字檢視，而pt-query-digest工具可匯入資料庫，方便檢視；

可參考之前部落格 MySQL slow query [慢查詢] 資料整理；

本文主要通過pt-query-digest工具將所需內容匯入到資料庫(sql按照發起sql者區分),最終將各個使用者下所有資訊匯入到一張總表.再進行分析..

指令碼內容：

#!/bin/bash

# Descirption:mysql slow_log Statistics
# DATE: 2015年7月1日
# Author: 毛海晴

#指令碼目的：
#每天將慢查詢sql及資訊匯入到mysql資料庫表作分析（需要資訊：執行sql使用者{database}、sql文字、執行次數、總執行時間、平均時間、查詢行數、返回行數）

#指令碼編寫思路

#pt-query-digest未發現直接按照database過濾自帶引數（問題當提供慢sql時需要知道sql在哪個庫執行的的情況該怎麼辦）
#不過可以考慮使用pt-query-digest自帶使用者限制引數--filter '($event->{user} || "") =~ m/^使用者/i' 可以簡單用使用者充當database再結合mysql.user再分析')
#通過pt-query-digest user 限制後匯入資料庫，但匯入後並沒有某欄位記錄database_name資訊

#所以考慮迴圈限制並匯入database+date命名資料表後手動加database_name欄位，預設值為相應sql執行的使用者名稱
#最終會生成多表（例如：slow_log_his_2015_07_01_retail_uc、slow_log_his_2015_07_01_retail_pms...）第一個欄位是database_name和相應的執行sql使用者名稱

#表整理：將多表追加到一張slow_log_his_2015_07_01表，再通過mysql sql 取出我們需要欄位。

# *********************************************步驟********************************************
## 變數設定（註釋：變數定義=等號前後不能出現空格，否則會被系統理解成命令）
#登陸名
user='root'
#登陸密碼

slow_root='root'

slow_password=123456
slow_host=寫入遠端庫IP
slow_port='3306'
slow_dbname='test'

#日期設定，生成慢查詢表命令YYYY_MM_DD 'train_public'字尾名避免多庫匯入同一遠端資料庫表名重複，資料覆蓋問題。

pt_table=slow_log_his_`date +%Y_%m_%d`_train_public

#IP獲取

# ip=`ifconfig |grep "Bcast"|awk -F ':' '{print $2}'|awk -F ' ' '{print $1}'`
echo '------------- '`date +%Y-%m-%d' '%H:%M:%S`' 開始 "'`hostname`'"(IP...) 慢查詢統計 -------------'

slow_name=`mysql -u$slow_root -p$slow_password -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`

#--&gt 一、整合表'slow_log_his_'$date配置配置

# 表資料要保證為空，避免“ERROR 1062 (23000) at line 1: Duplicate entry 'XX' for key 'PRIMARY'”
#--》1 本次偽例限制--filter '($event->{user} || "") =~ m/^mhq/i'匯入空表 mhq使用者並不存在
#--》或2 方案，在insert之前先 delete from 表；
#（1）刪除已有並建立
mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N -e " drop table if exists ${pt_table};"

pt-query-digest --user=${slow_root} --password=${slow_password} --no-report --history h=${slow_host},D=${slow_dbname},t=${pt_table} --create-history-table --filter '($event->{user} || "") =~ m/^mhq/i' $slow_name.`date +%Y%m%d`

#（2）加database_name 欄位，保證各個表結構相同

mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N -e "alter table ${pt_table} add COLUMN database_name varchar(255) NOT NULL FIRST ; "

#--&gt 二、查詢database_name，開始迴圈匯入database_name+date命名資料庫表

#（1）查詢所有database_name
for username in `mysql -uroot -hlocalhost -p123456 -N -B -e "select distinct user from mysql.user;"`
do
#（2）使用pt工具將通過--filter 過濾database_name迴圈匯入database_name+date命名資料庫表(存在先刪除..)

mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N -e "drop table if exists ${pt_table}_${f};"

pt-query-digest --user=${slow_root} --password=${slow_password} --filter '($event->{user} || "") =~ m/^'$username'/i' --no-report --history h=${slow_host},D=${slow_dbname},t=${pt_table}_${f} --create-history-table $slow_name.`date +%Y%m%d`

# 由於後續sql過濾只用到history表，所以未加“--review h=$host,D=$dbname,t='slow_log_'$date'_'$username --create-review-table”引數

#--&gt 三、處理迴圈匯入的database_name+date命名錶

mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N -e "

#（1）向每張表加欄位，預設值為database_name，用來查詢使用

alter table ${pt_table}_${f} add COLUMN database_name varchar(255) NOT NULL default '$username' FIRST ;

#（2）迴圈將database_name+date命名錶資料讀到'slow_log_his_'$date表.

insert into ${pt_table} select * from ${pt_table}_${f};
#（3）清理database_name+date命名錶

drop table ${pt_table}_${f};
"
## -e " 冒號需要寫在同一行，否則會提示“mysql: option '-e' requires an argument”

#結束迴圈
done

echo '------------- '`date +%Y-%m-%d' '%H:%M:%S`' 結束 "'`hostname`'"(IP...) 慢查詢統計 -------------'

# pt-query-digest用到的引數註釋：
# --user mysql使用者名稱
#--password mysql使用者密碼
#--history 將分析結果儲存到表中，分析結果比較詳細，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區間和歷史表中的不同,則會記錄到資料表中，可以通過查詢同一CHECKSUM來比較某型別查詢的歷史變化
# --no-report 不列印分析log
# --host mysql伺服器地址
# h 要匯入mysql伺服器地址
# D 要匯入到的database
# t 要匯入到的table
#--create-history-table 當使用--history引數把分析結果輸出到表中時，如果沒有表就自動建立。
# --filter 對輸入的慢查詢按指定的字串進行匹配過濾後再進行分析
#--limit限制輸出結果百分比或數量，預設值是20,即將最慢的20條語句輸出，如果是50%則按總響應時間佔比從大到小排序，輸出到總和達到50%位置截止。
# 其他引數
#--since 從什麼時間開始分析，值為字串，可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統計。
#--until 截止時間，配合—since可以分析一段時間內的慢查詢。

# 查詢SQL

#未使用表自帶欄位“*_*_pct_95”平均值，是因為 sum / ts_cnt 並不相等於*_pct_95欄位
SELECT
     his.*
FROM
     ( SELECT
               database_name AS "sql發起者",
               sample sql_text,
               ts_cnt AS "執行次數",
               round(Query_time_sum, 2) AS "執行時間(總)",
               round(Query_time_sum / ts_cnt, 2) AS "執行時間(每)",
               round(Lock_time_sum, 3) AS "鎖時間(總)",
               round(Lock_time_sum / ts_cnt, 3) AS "鎖時間 (每)",
               round(Rows_examined_min / ts_cnt) AS "參加運算的記錄平均行數",
               round(Rows_sent_sum / ts_cnt) AS "平均返回記錄數"
          FROM
               slow_log_his_2015_07_03_train_public t
          #WHERE database_name LIKE 'retail_%' #database_name not LIKE 'retail_%'
          ORDER BY
               4 DESC
     ) his
LIMIT 10;

顯示結果:

注意:

1、需要在慢啟動日誌切割後執行。

2、/*!40001 SQL_NO_CACHE */ 表示: 不把查詢結果儲存在查詢快取中 (類似oracle 的direct path read) ...本環境是由mysqldump引起/*!40001 SQL_NO_CACHE */

3、pt-query- digest小瑕疵：

選中部分SQL SELECT /*!40001 SQL_NO_CACHE */ * FROM `item_sku` 執行次數 =9 ；

而實際上是select * from 三個分別三次不同的表 ... (review和history 表都記錄到一條資料，執行次數為 9)

而mysqldumpslow 工具結果如下：

Count: 3 Time=9.89s (29s) Lock=0.00s (0s) Rows=2453000.0 (7359000), root[root]@localhost

SELECT /*!N SQL_NO_CACHE */ * FROM `item_sku`

Count: 3 Time=6.68s (20s) Lock=0.00s (0s) Rows=1545142.0 (4635426), root[root]@localhost

SELECT /*!N SQL_NO_CACHE */ * FROM `item_extension`

Count: 3 Time=4.01s (12s) Lock=0.00s (0s) Rows=328886.0 (986658), root[root]@localhost

SELECT /*!N SQL_NO_CACHE */ * FROM `item`

MYSQL慢日誌分析引數的涵義

` ts_min ` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最早執行時間' ,

` ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最晚執行時間' ,

` ts_cnt` float DEFAULT NULL COMMENT '總共執行次數' ,

` Query_time_sum` float DEFAULT NULL COMMENT '總查詢時間' ,

` Query_time_min` float DEFAULT NULL COMMENT '最小查詢時間' ,

` Query_time_max` float DEFAULT NULL COMMENT '最大查詢時間' ,

` Query_time_pct_95` float DEFAULT NULL COMMENT '平均查詢時間' ,

` Query_time_stddev` float DEFAULT NULL COMMENT '查詢時間標準差' ,

` Query_time_median` float DEFAULT NULL COMMENT '查詢時間中位數' ,

` Lock_time_sum` float DEFAULT NULL COMMENT '總鎖定時間' ,,

` Lock_time_min` float DEFAULT NULL COMMENT '最小鎖定時間' ,

` Lock_time_max` float DEFAULT NULL COMMENT '最大鎖定時間' ,

` Lock_time_pct_95` float DEFAULT NULL COMMENT '平均鎖定時間' ,

` Lock_time_stddev` float DEFAULT NULL COMMENT '鎖定時間標準差' ,

` Lock_time_median` float DEFAULT NULL COMMENT '鎖定時間中位數' ,

` Rows_sent_sum` float DEFAULT NULL COMMENT '總返回記錄行數' ,

` Rows_sent_min` float DEFAULT NULL COMMENT '最小返回記錄數' ,

` Rows_sent_max` float DEFAULT NULL COMMENT '最大返回記錄數' ,

` Rows_sent_pct_95` float DEFAULT NULL COMMENT '平均返回記錄數' ,

` Rows_sent_stddev` float DEFAULT NULL COMMENT '傳送返回數標準差' ,

` Rows_sent_median` float DEFAULT NULL COMMENT '返回記錄數中位數' ,

` Rows_examined_sum` float DEFAULT NULL COMMENT '參加運算的記錄總行數' ,

` Rows_examined_min` float DEFAULT NULL COMMENT '最少參加運算的記錄行數' ,

` Rows_examined_max` float DEFAULT NULL COMMENT '最多參加運算的記錄行數' ,

` Rows_examined_pct_95` float DEFAULT NULL COMMENT '平均參加運算的記錄行數' ,

` Rows_examined_stddev` float DEFAULT NULL COMMENT '參加運算的記錄行數標準差' ,

` Rows_examined_median` float DEFAULT NULL COMMENT '參加運算的記錄行數中位數' ,

` Rows_affected_sum` float DEFAULT NULL COMMENT '受影響記錄總行數' ,

` Rows_affected_min` float DEFAULT NULL COMMENT '最少受影響記錄行數' ,

` Rows_affected_max` float DEFAULT NULL COMMENT '最大受影響記錄行數' ,

` Rows_affected_pct_95` float DEFAULT NULL COMMENT '平均受影響記錄行數' ,

` Rows_affected_stddev` float DEFAULT NULL COMMENT '受影響記錄行數標準差' ,

` Rows_affected_median` float DEFAULT NULL COMMENT '受影響記錄行數中位數' ,

` Rows_read_sum` float DEFAULT NULL COMMENT '讀入記錄行總數' ,

` Rows_read_min` float DEFAULT NULL COMMENT '讀入記錄行最小數' ,

` Rows_read_max` float DEFAULT NULL COMMENT '讀入記錄行最大數' ,

` Rows_read_pct_95` float DEFAULT NULL COMMENT '讀入記錄行平均數' ,

` Rows_read_stddev` float DEFAULT NULL COMMENT '讀入記錄行數標準差' ,

` Rows_read_median` float DEFAULT NULL COMMENT '讀入記錄行數中位數' ,

` Merge_passes_sum` float DEFAULT NULL COMMENT '資料表合併總數' ,

` Merge_passes_min` float DEFAULT NULL COMMENT '資料表合併最小數' ,

` Merge_passes_max` float DEFAULT NULL COMMENT '資料表合併最大數' ,

` Merge_passes_pct_95` float DEFAULT NULL COMMENT '資料表合併平均數' ,

` Merge_passes_stddev` float DEFAULT NULL COMMENT '資料表合併數標準差' ,

` Merge_passes_median` float DEFAULT NULL COMMENT '資料表合併數中位數' ,

` InnoDB_IO_r_ops_min` float DEFAULT NULL COMMENT '統計計劃需要讀取的最小頁數' ,

` InnoDB_IO_r_ops_max` float DEFAULT NULL COMMENT '統計計劃需要讀取的最大頁數' ,

` InnoDB_IO_r_ops_pct_95` float DEFAULT NULL COMMENT '統計計劃需要讀取的平均頁數' ,

` InnoDB_IO_r_ops_stddev` float DEFAULT NULL COMMENT '統計計劃需要讀取的頁數標準差' ,

` InnoDB_IO_r_ops_median` float DEFAULT NULL COMMENT '統計計劃需要讀取的頁數中位數' ,

` InnoDB_IO_r_bytes_min` float DEFAULT NULL COMMENT '統計計劃需要讀取的最小位元組數' ,

` InnoDB_IO_r_bytes_max` float DEFAULT NULL COMMENT '統計計劃需要讀取的最大位元組數' ,

` InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL COMMENT '統計計劃需要讀取的平均位元組數' ,

` InnoDB_IO_r_bytes_stddev` float DEFAULT NULL COMMENT '統計計劃需要讀取的位元組數標準差' ,

` InnoDB_IO_r_bytes_median` float DEFAULT NULL COMMENT '統計計劃需要讀取的位元組數中位數' ,

` InnoDB_IO_r_wait_min` float DEFAULT NULL COMMENT '讀取記錄時產生鎖等待的最小時間' ,

` InnoDB_IO_r_wait_max` float DEFAULT NULL COMMENT '讀取記錄時產生鎖等待的最大時間' ,

` InnoDB_IO_r_wait_pct_95` float DEFAULT NULL COMMENT '讀取記錄時產生鎖等待的平均時間' ,

` InnoDB_IO_r_wait_stddev` float DEFAULT NULL COMMENT '讀取記錄時產生鎖等待時間標準差' ,

` InnoDB_IO_r_wait_median` float DEFAULT NULL COMMENT '讀取記錄時產生鎖等待時間中位數' ,

` InnoDB_rec_lock_wait_min` float DEFAULT NULL COMMENT '讀取記錄時產生行鎖等待的最小時間' ,

` InnoDB_rec_lock_wait_max` float DEFAULT NULL COMMENT '讀取記錄時產生行鎖等待的最大時間' ,

` InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL COMMENT '讀取記錄時產生行鎖等待的平均時間' ,

` InnoDB_rec_lock_wait_stddev` float DEFAULT NULL COMMENT '讀取記錄時產生行鎖等待時間標準差' ,

` InnoDB_rec_lock_wait_median` float DEFAULT NULL COMMENT '讀取記錄時產生行鎖等待時間中位數' ,

` InnoDB_queue_wait_min` float DEFAULT NULL COMMENT '等待進入 InnoDB 佇列或在佇列中等待執行所消耗的最小時間' ,

` InnoDB_queue_wait_max` float DEFAULT NULL COMMENT '等待進入 InnoDB 佇列或在佇列中等待執行所消耗的最大時間' ,

` InnoDB_queue_wait_pct_95` float DEFAULT NULL COMMENT '等待進入 InnoDB 佇列或在佇列中等待執行所消耗的平均時間' ,

` InnoDB_queue_wait_stddev` float DEFAULT NULL COMMENT '等待進入 InnoDB 佇列或在佇列中等待執行所消耗時間標準差' ,

` InnoDB_queue_wait_median` float DEFAULT NULL COMMENT '等待進入 InnoDB 佇列或在佇列中等待執行所消耗時間中位數' ,

` InnoDB_pages_distinct_min` float DEFAULT NULL COMMENT '讀取的獨立頁最小個數' ,

` InnoDB_pages_distinct_max` float DEFAULT NULL COMMENT '讀取的獨立頁最大個數' ,

` InnoDB_pages_distinct_pct_95` float DEFAULT NULL COMMENT '讀取的獨立頁平均個數' ,

` InnoDB_pages_distinct_stddev` float DEFAULT NULL COMMENT '讀取的獨立頁個數標準差' ,

` InnoDB_pages_distinct_median` float DEFAULT NULL COMMENT '讀取的獨立頁個數中位數' ,

` QC_Hit_cnt` float DEFAULT NULL COMMENT 'QUERY緩衝區的命中次數' ,

` QC_Hit_sum` float DEFAULT NULL COMMENT 'QUERY緩衝區的命中總數' ,

` Full_scan_cnt` float DEFAULT NULL COMMENT '全表掃描次數' ,

` Full_scan_sum` float DEFAULT NULL COMMENT '全表掃描總數' ,

` Full_join_cnt` float DEFAULT NULL COMMENT '資料表全連線次數' ,

` Full_join_sum` float DEFAULT NULL COMMENT '資料表全連線總數' ,

` Tmp_table_cnt` float DEFAULT NULL COMMENT '記憶體中使用臨時表次數' ,

` Tmp_table_sum` float DEFAULT NULL COMMENT '記憶體中使用臨時表總數' ,

` Disk_tmp_table_cnt` float DEFAULT NULL COMMENT '磁碟上使用臨時表總數' ,

` Disk_tmp_table_sum` float DEFAULT NULL COMMENT '磁碟上使用臨時表總數' ,

` Filesort_cnt` float DEFAULT NULL COMMENT '記憶體排序次數' ,

` Filesort_sum` float DEFAULT NULL COMMENT '記憶體排序總數' ,

` Disk_filesort_cnt` float DEFAULT NULL COMMENT '磁碟排序次數' ,

` Disk_filesort_sum` float DEFAULT NULL COMMENT '磁碟排序總數'

mysql version = 5.6.19 欄位差異

--&gt增加：

checksum => 校驗值

sample => sql 樣本

--&gt差異：

原欄位改後欄位

Disk_tmp_table_cnt => Tmp_table_on_disk_cnt

Disk_tmp_table_sum => Tmp_table_on_disk_sum

Disk_filesort_cnt => Filesort_on_disk_cnt

Disk_filesort_sum => Filesort_on_disk_sum

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 MySQL 分類目錄。將固定連線加入收藏夾。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1724524/，如需轉載，請註明出處，否則將追究法律責任。

PHP慢指令碼日誌和Mysql的慢查詢日誌
2017-11-12
PHP指令碼MySql
開啟php-fpm慢指令碼日誌
2017-11-21
PHP指令碼
MySQL慢日誌全解析
2021-05-18
MySql
mysql開啟慢日誌
2020-09-27
MySql
MySQL：慢查詢日誌
2023-05-09
MySql
MySQL慢日誌優化
2021-12-16
MySql優化
mysql 鎖的慢日誌
2021-12-13
MySql
mysql慢查詢日誌
2008-11-13
MySql
mysql5.7 慢日誌配置
2018-11-20
MySql
檢查Linux系統日誌error和mysql錯誤日誌的指令碼薦
2013-01-03
LinuxErrorMySql指令碼
MySQL 慢查詢日誌——讓“慢”無所遁形
2017-04-07
MySql
Mysql 慢日誌分析工具Mysqldumpslow
2020-09-21
MySql
【MySQL】慢查詢日誌不列印
2017-02-06
MySql
mysqlsla 分析mysql慢查詢日誌
2015-10-19
MySql
mysql之 slow log 慢查詢日誌
2018-08-30
MySql
MySQL Slow Query log(慢查詢日誌)
2022-03-17
MySql
mysql慢查詢日誌分析工具使用
2017-11-10
MySql
開啟mysql和php慢日誌方法
2017-11-14
MySqlPHP
Mysql慢查詢日誌分析工具mysqlsla
2015-06-24
MySql
【MySql】慢日誌查詢工具之mysqlsla
2012-02-18
MySql
【MySql】mysql 慢日誌查詢工具之mysqldumpslow
2012-02-24
MySql
mysql每天定時備份指令碼
2018-01-22
MySql指令碼
【shell】日誌切割指令碼
2017-03-24
指令碼
日誌刪除指令碼
2017-09-22
指令碼
日誌轉儲指令碼
2017-09-13
指令碼
關於MySQL 通用查詢日誌和慢查詢日誌分析
2018-10-10
MySql
redis慢日誌
2017-07-20
Redis
MySQL慢查詢日誌相關設定
2021-11-26
MySql
【轉載】MySQL慢查詢日誌總結
2016-09-16
MySql
mysql慢查詢和錯誤日誌分析
2018-04-19
MySql
MYSQL開啟慢查詢日誌實施
2013-09-06
MySql
mysql開啟檢視慢查詢日誌
2016-03-02
MySql
【Mysql】一天一個慢日誌
2016-03-18
MySql
用命令過濾MySQL慢查詢日誌
2015-11-17
MySql
oracle自動備份，儲存每天備份日誌的指令碼編寫
2008-01-18
Oracle指令碼
MySQL資料庫中的日誌檔案---(3)慢查詢日誌
2011-09-28
MySql資料庫
nginx日誌分割小指令碼
2014-08-02
Nginx指令碼
linux 分享日誌指令碼
2012-06-18
Linux指令碼

MySQL 每天自行統計慢日誌 指令碼

相關文章

MySQL 每天自行統計慢日誌指令碼