Shulou(Shulou.com)05/31 Report--

This article mainly explains the "mysql performance knowledge points collation", the article explains the content is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "mysql performance knowledge points collation" bar!

Mysql performance problem monitoring has always been relatively simple, in addition to slow log and show processlist, there is no particularly good way, the following script can help collect the behavior of the database over a period of time, you can also import the table, make charts, easy to determine the database performance and capacity trends.

Touch lastdat.txt

Touch tempdat.txt

Touch mystat.sh

Chmod 755 mystat.sh

Mysqladmin extended-status | grep-E 'Bytes_received > | Bytes_sent > | Com_delete > | Com_insert > | Com_select > | Com_update > | Connections >' | awk'{print $4}'> / root/tempdat.txt

Byr= `sed-n '1p' / root/ tempdat.txt`

Bys= `sed-n '2p' / root/ tempdat.txt`

Del= `sed-n '3p' / root/ tempdat.txt`

Ins= `sed-n '4p' / root/ tempdat.txt`

Sel= `sed-n '5p' / root/ tempdat.txt`

Upd= `sed-n '6p' / root/ tempdat.txt`

Conn= `sed-n '7p' / root/ tempdat.txt`

Ct= `wc-l / root/lastdat.txt | awk'{print $1}'`

If [$ct-eq 0]

Then

Echo $byr > / root/lastdat.txt

Echo $bys > > / root/lastdat.txt

Echo $del > > / root/lastdat.txt

Echo $ins > > / root/lastdat.txt

Echo $sel > > / root/lastdat.txt

Echo $upd > > / root/lastdat.txt

Echo $conn > > / root/lastdat.txt

Else

Val_new=$byr

Val_old= `sed-n '1p' / root/ lastdat.txt`

Valbyr= `expr $val_new-$val_old `

Valbyr= `expr $valbyr / 1024`

Val_new=$bys

Val_old= `sed-n '2p' / root/ lastdat.txt`

Valbys= `expr $val_new-$val_old `

Valbys= `expr $valbys / 1024`

Val_new=$del

Val_old= `sed-n '3p' / root/ lastdat.txt`

Valdel= `expr $val_new-$val_old `

Val_new=$ins

Val_old= `sed-n '4p' / root/ lastdat.txt`

Valins= `expr $val_new-$val_old `

Val_new=$sel

Val_old= `sed-n '5p' / root/ lastdat.txt`

Valsel= `expr $val_new-$val_old `

Val_new=$upd

Val_old= `sed-n '6p' / root/ lastdat.txt`

Valupd= `expr $val_new-$val_old `

Val_new=$conn

Val_old= `sed-n '7p' / root/ lastdat.txt`

Valconn= `expr $val_new-$val_old `

Echo `date + "% Y-%m-%d% k:%M:%S" `, $valbyr,$valbys,$valdel,$valins,$valsel,$valupd,$valconn > > / root/ `hostname`.csv

Echo $byr > / root/lastdat.txt

Echo $bys > > / root/lastdat.txt

Echo $del > > / root/lastdat.txt

Echo $ins > > / root/lastdat.txt

Echo $sel > > / root/lastdat.txt

Echo $upd > > / root/lastdat.txt

Echo $conn > > / root/lastdat.txt

Fi

# catchsql.sh

Log_dir=/usr/checklog

Interval=3

# run every 3 seconds

Wtlimit=10

# grab sql with a running time of more than 10 seconds

Maxtime=20

# time alarm limit of the slowest SQL

Trap "exit;exit" 2

While [1-eq 1]

Do

Mysql-e "show full processlist" | awk'$2mom racket'"(system)" / {print $0}'| sed-n '2Sleep p' | sort-n-k 6-r | awk' $6 >'"$wtlimit" & & $7 million = "NULL" & & $6 million = "NULL" & $5 million = "Sleep" & $5 million = "Binlog" {print $0}'> $log_dir/topsql.log

Ct= `cat $log_dir/topsql.log | wc-l`

If [$ct-gt 0]

Then

Echo'- -'> $log_dir/slowsql.log

Echo `date "+% Y-%m-%d% H:%M:%S" `> > $log_dir/slowsql.log

Echo'- -'> $log_dir/slowsql.log

Cat $log_dir/topsql.log | tee-a > > $log_dir/slowsql.log

Mt= `cat $log_dir/topsql.log | sed-n '1p' | awk' {print $6}'`

If [$mt-gt $maxtime]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:some SQL elapse too long, pls check slowsql.log.Time Threshold: $maxtime" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Cat $log_dir/topsql.log > > $log_dir/ `date +% Y% m% d`.log

Fi

Fi

Sleep $interval

Done

Thank you for your reading, the above is the "mysql performance knowledge points collation" content, after the study of this article, I believe you have a deeper understanding of the mysql performance knowledge points collation of this problem, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.