Monitoring DML Stats per table in MySQL
Most of time as an MySQL admin we need to keep track of the DML statements per table , If we are not using any specific tool for doing this
We can use the shell script for doing this which I came across the idea is to use the binlog to get all of the DML.
The script which we can use is mysqlbinlog options to filter the specific data based on time etc..
mysqlbinlog `pwd`/`ls -tr mysql-bin* | tail -1` | \
grep -i -e “^update” -e “^insert” -e “^delete” -e “^replace” -e “^alter” | \
cut -c1-100 | tr ‘[A-Z]‘ ‘[a-z]‘ | \
sed -e “s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//” | sed -e “s/ where .*$//” | \
sort | uniq -c | sort –nr
The output of the command would be
33389 update e_acc
17680 insert into r_b
17680 insert into e_rec
14332 insert into rcv_c
13543 update e_rec
10805 update loc
3339 insert into r_att
2781 insert into o_att
Thanks
Pankaj Joshi
Most of time as an MySQL admin we need to keep track of the DML statements per table , If we are not using any specific tool for doing this
We can use the shell script for doing this which I came across the idea is to use the binlog to get all of the DML.
The script which we can use is mysqlbinlog options to filter the specific data based on time etc..
mysqlbinlog `pwd`/`ls -tr mysql-bin* | tail -1` | \
grep -i -e “^update” -e “^insert” -e “^delete” -e “^replace” -e “^alter” | \
cut -c1-100 | tr ‘[A-Z]‘ ‘[a-z]‘ | \
sed -e “s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//” | sed -e “s/ where .*$//” | \
sort | uniq -c | sort –nr
The output of the command would be
33389 update e_acc
17680 insert into r_b
17680 insert into e_rec
14332 insert into rcv_c
13543 update e_rec
10805 update loc
3339 insert into r_att
2781 insert into o_att
July 22nd, 2010 at 11:04 pm
This is a usefull post.