Collation of mysql performance knowledge points
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!
