監控innodb status指標
輸入被監控機器的ip 實時獲取innodb相關status指標,包含:
---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---QPS---|select insert update delete| read inserted updated deleted| logical physical
#需要編譯指令碼 寫入賬號密碼
#可以給自己維護的所有DB建立一個監控專用賬號 密碼統一 這樣寫死在指令碼中 直接用即可
]$ cat innodb_status.sh
#!/bin/bash
#引數變數
host=$1
port=$2
username=使用者
passwd=密碼
#echo "sh $0 $host $username $passwd $port"
#fountion 指令碼說明、幫助
help_info(){
echo "NAME"
echo " $0"
echo "SYNOPSIS"
echo " $0 is a shell script and monitor innodb status,eg:qps,tps......"
echo "DESCRIPTION"
echo " option like mysql client -h host -u username -p password -P port"
echo "Usage:"
echo " sh $0 ipaddr username password port"
echo " E.g sh $0 10.111.7.31 3306 yujx yujxpw"
echo
}
#function 列印innodb相關指標
server_id(){
mysql -h $host -P$port -u$username -p$passwd -e "show variables like 'server_id'"|grep -iv value
}
innodb_status(){
mysqladmin -h $host -P$port -u$username -p$passwd -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'
}
# 預設埠3306
if [ ! $port ]; then
port=3306
fi
#echo $#,$1
# 執行指令碼
if [ $# -le 0 ]||[ "$1"x = "-h"x ]||[ "$1"x = "--help"x ]
then
help_info
else
echo "................"
server_id
echo "..................."
innodb_status
fi
#執行
]$ ./innodb_status.sh ip地址
................
Warning: Using a password on the command line interface can be insecure.
server_id 111111
...................
Warning: Using a password on the command line interface can be insecure.
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical
09:37:24 | 10526| 318 66 5021 0| 18124 66 10061 0| 537009 87
09:37:25 | 10596| 298 96 5048 0| 21170 96 10118 0| 535858 79
09:37:26 | 10300| 238 65 4947 0| 21968 65 9914 0| 525109 91
參考:
---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---QPS---|select insert update delete| read inserted updated deleted| logical physical
#需要編譯指令碼 寫入賬號密碼
#可以給自己維護的所有DB建立一個監控專用賬號 密碼統一 這樣寫死在指令碼中 直接用即可
]$ cat innodb_status.sh
#!/bin/bash
#引數變數
host=$1
port=$2
username=使用者
passwd=密碼
#echo "sh $0 $host $username $passwd $port"
#fountion 指令碼說明、幫助
help_info(){
echo "NAME"
echo " $0"
echo "SYNOPSIS"
echo " $0 is a shell script and monitor innodb status,eg:qps,tps......"
echo "DESCRIPTION"
echo " option like mysql client -h host -u username -p password -P port"
echo "Usage:"
echo " sh $0 ipaddr username password port"
echo " E.g sh $0 10.111.7.31 3306 yujx yujxpw"
echo
}
#function 列印innodb相關指標
server_id(){
mysql -h $host -P$port -u$username -p$passwd -e "show variables like 'server_id'"|grep -iv value
}
innodb_status(){
mysqladmin -h $host -P$port -u$username -p$passwd -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'
}
# 預設埠3306
if [ ! $port ]; then
port=3306
fi
#echo $#,$1
# 執行指令碼
if [ $# -le 0 ]||[ "$1"x = "-h"x ]||[ "$1"x = "--help"x ]
then
help_info
else
echo "................"
server_id
echo "..................."
innodb_status
fi
#執行
]$ ./innodb_status.sh ip地址
................
Warning: Using a password on the command line interface can be insecure.
server_id 111111
...................
Warning: Using a password on the command line interface can be insecure.
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical
09:37:24 | 10526| 318 66 5021 0| 18124 66 10061 0| 537009 87
09:37:25 | 10596| 298 96 5048 0| 21170 96 10118 0| 535858 79
09:37:26 | 10300| 238 65 4947 0| 21968 65 9914 0| 525109 91
參考:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27000195/viewspace-2125668/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mongodb 監控指標MongoDB指標
- EMQ 監控指標MQ指標
- Orabbix監控指標指標
- Java程式監控指標Java指標
- mysql效能監控指標MySql指標
- 淺談Orabbix監控指標指標
- 系統監控&JVM監控指標資料查詢JVM指標
- 微服務:指標和健康監控微服務指標
- 運維監控指標彙總運維指標
- MYSQL和SQLServer效能監控指標MySqlServer指標
- beta版 tomcat 應用監控指標Tomcat指標
- 基於 prometheus 的微服務指標監控Prometheus微服務指標
- 04、MySQL Case-MySQL常用監控指標MySql指標
- 分散式架構的監控與指標分散式架構指標
- 如何高效利用 Grafana 監控分析 TiDB 指標GrafanaTiDB指標
- 使用Prometheus監控Linux系統各項指標PrometheusLinux指標
- PostgreSQL實時健康監控大屏-低頻指標SQL指標
- 【系統設計】指標監控和告警系統指標
- 自己寫的一個MYSQL監控show global status工具用於檢視指定時間內變化的指標MySql指標
- [MySQL5.6]Innodb新的監控表INNODB_METRICSMySql
- 實戰| 配置DataDog監控Apache Hudi應用指標Apache指標
- go 服務監控指標(metric)上報open-falconGo指標
- MySQL監控專案指標值解釋和說明MySql指標
- Oracle記憶體中的幾個重要監控指標Oracle記憶體指標
- MySQL:Innodb DB_ROLL_PTR指標解析MySql指標
- .Net Core服務監控報警指標上報Prometheus+Grafana指標PrometheusGrafana
- 重構指標之如何監控程式碼圈複雜度指標複雜度
- 中介軟體IIS監控指標、配置和Windbg除錯分析指標除錯
- 圖解JanusGraph系列 - JanusGraph指標監控報警(Monitoring JanusGraph)圖解指標
- 生產經營監控系統指標體系的梳理指標
- MySQL的show engine innodb statusMySql
- show engine innodb status 詳解
- mysql 5.6效能監控表innodb_metricsMySql
- MySQL監控InnoDB Buffer Pool命中率MySql
- PostgreSQL實時健康監控大屏-高頻指標(伺服器)SQL指標伺服器
- 簡單4步,利用Prometheus Operator實現自定義指標監控Prometheus指標
- 使用metrics-server監控k8s的資源指標ServerK8S指標
- MySQL show engine innodb status 詳解MySql