MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats. I would like per table stats.
You can achieve this with tools such as MySQL Proxy and
mk-query-digest, however there is actually a very simple solution
that requires no additional tools.
The following 1 line Linux command (reformatted for ease of reading) gave me exactly what I wanted, and it had ZERO impact on the database.
$ mysqlbinlog /path/to/mysql-bin.000999 | \ 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 33389 update e_acc 17680 insert into r_b 17680 insert into e_rec 14332 insert into rcv_c 13543 update e_rec 10805 …[Read more]